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注入的风险，对参数做好校验处理