今天想实现一个功能,根据给定的字段,以及是否升降序来对数据动态排序,xml实现如下
order by #{orderBy} #{orderType}
<if test="pageSearch!=null and 'true'.toString() == pageSearch.toString()">
limit #{limit} offset #{offset}
</if>
结果发现怎么都没用,查看debug日志,发现sql语句为:
2020-02-19 15:53:25.090 [DEBUG] [] [] [JakartaCommonsLoggingImpl.java:54] ==> Preparing: select distinct purchaseNo from TB_YX_PFU_PURCHASE_SEARCH_INFO WHERE 1=1 order by ? ?
2020-02-19 15:53:25.106 [DEBUG] [] [] [JakartaCommonsLoggingImpl.java:54] ==> Parameters: submitTime(String), desc(String)
2020-02-19 15:53:25.117 [DEBUG] [] [] [JakartaCommonsLoggingImpl.java:54] <== Total: 2
2020-02-19 15:53:25.120 [DEBUG] [] [] [JakartaCommonsLoggingImpl.java:54] Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4cb084b]
2020-02-19 15:53:25.120 [DEBUG] [] [] [DataSourceUtils.java:327] Returning JDBC Connection to DataSource
["CGD_20200210-0021","CGD_20200210-0022"]
可以看到传入的排序字段 submitTime
,和排序类型 desc
作为了字符串参数,这就是问题所在,我们的xml中需要将 #{orderBy} #{orderType}
改为 ${orderBy} ${orderType}
,得到的查询语句:
2020-02-19 16:01:33.289 [DEBUG] [] [] [JakartaCommonsLoggingImpl.java:54] ==> Preparing: select distinct purchaseNo from TB_YX_PFU_PURCHASE_SEARCH_INFO WHERE 1=1 order by submitTime desc
2020-02-19 16:01:33.307 [DEBUG] [] [] [JakartaCommonsLoggingImpl.java:54] ==> Parameters:
2020-02-19 16:01:33.318 [DEBUG] [] [] [JakartaCommonsLoggingImpl.java:54] <== Total: 2
2020-02-19 16:01:33.322 [DEBUG] [] [] [JakartaCommonsLoggingImpl.java:54] Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@47fe4c3e]
2020-02-19 16:01:33.322 [DEBUG] [] [] [DataSourceUtils.java:327] Returning JDBC Connection to DataSource
["CGD_20200210-0022","CGD_20200210-0021"]
但是,$
方式无法防止Sql注入,所以,对于使用前端传过来的值的时候,一定要进行转义,不要直接使用,一般能用#
的就别用$
,$
方式一般用于传入数据库对象,例如传入表名、排序规则等。