mysql 防止依赖注入

参数注入

• 导致注入

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);
}