Solution to the Failure of mybatis Frame order by as Parameter

mybatis order by passed in as parameter failure

The statement in mxl is as follows

<select id="statToday" resultType="com.dahua.la.business.model.vo.StatSysResultVO">
      select a,
             b,
        count(1) as total
      from table
      where  a is not null
      and b is not null
      and operateTime >= #{startTime,jdbcType=TIMESTAMP}
      and operateTime <= #{endTime,jdbcType=TIMESTAMP}
      group by a, b
   order by
   <foreach collection="orderItems" item="item" separator=",">
      #{item.orderBy} #{item.order}
   </foreach>
</select>

At runtime, the sql log is printed through the log as follows

select a, b, count(1) as total 
from table 
where a is not null and b is not null 
and operateTime >= ? and operateTime <= ? 
group by a, b 
order by ? ?

When the parameters are added to Navicat for execution, there is no problem at all, and the sorting is normal.

But it just doesn't work in code, and the final sorting doesn't take effect at all.

In fact, I left out quotation marks when I filled in the parameters, because # {item. orderBy} will put a quotation mark on the incoming data, and if you go to Navicat with quotation marks, the sorting will not take effect.

The cause of the problem has been found

Replace directly with the form of ${item. orderBy}, and simply replace strings without quotation marks.

<foreach collection="orderItems" item="item" separator=",">
    ${item.orderBy} ${item.order}
</foreach>

The program is normal at this time.

When using order by dynamic parameters for MyBatis sorting, be careful to use $instead of #

String substitution

By default, using a syntax in the # {} format causes MyBatis to create a preprocessing statement property and set a safe value against it (such as?) .

This is safe, fast and preferred, and sometimes you just want to insert an unchanged string directly into the SQL statement.

For example, like ORDER BY, you can use it like this:

ORDER BY ${columnName}

Here MyBatis does not modify or escape the string.

Important:

It is not safe to accept the output from the user and feed it to an immutable string in the statement.

This can lead to a potential SQL injection attack, so you should not allow users to enter these fields, or usually escape and check them by yourself.