参数注入
- 导致注入
String name = request.getParameter("name");
String query = String.format("SELECT account_balance FROM user_data WHERE user_name ='%s'", "table_a");
PreparedStatement pstmt = connection.prepareStatement( query );
pstmt.setString( 1, name);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
String id = rs.getString("id");
}
- 预防注入
String name = "table_a";
String query = "SELECT account_balance FROM user_data WHERE user_name = ? ";
PreparedStatement pstmt = connection.prepareStatement(query);
pstmt.setString( 1, name);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
String id = rs.getString("id");
}
表名注入
String tableName = "table_A";
PreparedStatement ps = dataSource.getConnection().prepareStatement("show full columns from ?");
ps.setString(1, tableName);
ResultSet rs = ps.executeQuery();
JDBC 不支持以表名作为 paramter,这里会报语法错误,因为生成的 sql 是 show full columns from ‘table_A’’’。
同理,这些都不行,prepared statements only allow parameters to be bound for “values” bits of the SQL statement.
参考:https://stackoverflow.com/questions/11312737/can-i-parameterize-the-table-name-in-a-prepared-statement
CREATE DATABASE IF NOT EXISTS ?
SELECT * FROM ?
like 参数注入
- 错误方式
String idValue = "100";
String sql = "SELECT * FROM flink_test_dst WHERE name LIKE '%?%'";
PreparedStatement ps = dataSource.getConnection().prepareStatement(sql);
ps.setObject(1, idValue);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
String id = rs.getString("id");
System.out.println("id: " + id);
}
这样会导致 PreparedStatement 认为没有参数。ps.setObject(1, idValue); 失败。
- 正确方式
String idValue = "%" + "100" + "%";
String sql = "SELECT * FROM flink_test_dst WHERE name LIKE ?";
PreparedStatement ps = dataSource.getConnection().prepareStatement(sql);
ps.setObject(1, idValue);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
String id = rs.getString("id");
System.out.println("id: " + id);
}