JDBC常见异常(10)—预编译模式下占位符动态排序字段失效

场景需求

需要根据不同的列进行对应的排序操作，实现动态列名排序 类似🐟动态查询或更新

但是JDBC预编译模式下占位符的排序字段失效

SQL语句

分页查询

select * from (select t.*, rownum rn from(select * from emp order by empno desc) t where rownum <= 5)    where rn > 0;

指定列排序失效

select * from (select t.*, rownum rn from(select * from emp order by ?  desc) t where rownum <= 5)    where rn > 0;

临时解决字符串拼接

• SQL注入问题

select * from (select t.*, rownum rn from(select * from emp order by "+ empno  +  "  desc) t where rownum <= 5)    where rn > 0;

预编译注入排序列名排序失效

PreparedStatement执行SQL时，如果order by之后的排序字段使用占位符，通过setString设置值的话，会导致排序失效

// 4 SQL 语句被预编译并存储在 PreparedStatement 对象中。然后可以使用此对象多次高效地执行该语句。
PreparedStatement pstmt = conn.prepareStatement(sql);// 如果SQL有？号  参数需要注入
pstmt.setString(1, sortColumnName); // 1 代表 第一个？  从1开始   以此类推

源码

/*** An object that represents a precompiled SQL statement.* <P>A SQL statement is precompiled and stored in a* {@code PreparedStatement} object. This object can then be used to* efficiently execute this statement multiple times.** <P><B>Note:</B> The setter methods ({@code setShort}, {@code setString},* and so on) for setting IN parameter values* must specify types that are compatible with the defined SQL type of* the input parameter. For instance, if the IN parameter has SQL type* {@code INTEGER}, then the method {@code setInt} should be used.** <p>If arbitrary parameter type conversions are required, the method* {@code setObject} should be used with a target SQL type.* <P>* In the following example of setting a parameter, {@code con} represents* an active connection:* <pre>{@code*   BigDecimal sal = new BigDecimal("153833.00");*   PreparedStatement pstmt = con.prepareStatement("UPDATE EMPLOYEES*                                     SET SALARY = ? WHERE ID = ?");*   pstmt.setBigDecimal(1, sal);*   pstmt.setInt(2, 110592);* }</pre>** @see Connection#prepareStatement* @see ResultSet* @since 1.1*/
public interface PreparedStatement extends Statement {/*** Sets the designated parameter to the given Java {@code String} value.* The driver converts this* to an SQL {@code VARCHAR} or {@code LONGVARCHAR} value* (depending on the argument's* size relative to the driver's limits on {@code VARCHAR} values)* when it sends it to the database.** @param parameterIndex the first parameter is 1, the second is 2, ...* @param x the parameter value* @throws SQLException if parameterIndex does not correspond to a parameter* marker in the SQL statement; if a database access error occurs or* this method is called on a closed {@code PreparedStatement}*/void setString(int parameterIndex, String x) throws SQLException;... 
}

核心解释

• 将指定的参数设置为给定的Java｛@code String｝值。
• 驱动程序转换此转换为SQL{@code VARCHAR}或{@code LONGVARCHAR}值

原因

PreparedStatement用占位符防止SQL注入的原理是，在为占位符设置值时，会将值转为字符串，然后转义，再将值放入反引号中，放置在占位符的位置上。

因此，当排序字段使用占位符后，原来的排序语句 order by empno（假设排序字段是empno），在实际执行时变成了 order by empno，根据字段排序变成了根据字符串常量值empno排序，导致排序失效，甚至任意的注入数值都不影响前面的查询结果

情况一

使用预编译的数据库操作对象在order by后面设置占位符，再通过pstmt.setString()方法填入参数会导致排序失败

情况二

使用mybatis的时候,在mapper sql映射.xml文件中，在order by 后面使用 #{参数名} 依然会导致排序失败，因为mybatis #{} 使用的是PrepareStatement

解决办法

• #{}方式传参数只能处理值参数 不能传递表名，字段等参数
• ${}字符串替换，可以动态处理表名，字段参数

把#{}改成${}, #{}是预编译，相当于PrepareStatement;${}是普通字符串的拼接，相当于Statement

但是必须注意SQL注入的风险，对参数做好校验处理