当前位置：首页 > news >正文

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

news 来源：原创 2024/7/1 1:04:20

场景需求

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

但是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;... 
}