本人微信公众号,欢迎扫码关注!
在具体改造时,必须使用PreparedStatement来防止SQL注入,普通SQL语句比较容易改造,本重点探讨在拼接查询条件的时候如何方式SQL注入,具体思路请参考下面的示例代码。
@Test
public void statementTest() {
String username = "tom";
String sex = "1";
String address = "' or '1'='1";
Statement stat = null;
ResultSet res = null;
Connection conn = ConnectionFactory.getConnection();
String sql = "SELECT * FROM user WHERE 1 = 1";
sql += username == null ? "" : " AND username = '" + username + "'";
sql += sex == null ? "" : " AND sex = '" + sex + "'";
sql += address == null ? "" : " AND address = '" + address + "'";
System.out.println(sql);
try {
stat = conn.createStatement();
res = stat.executeQuery(sql);
printRes(res);
} catch (SQLException e) {
e.printStackTrace();
} finally {
ResourceClose.close(res, stat, conn);
}
}
SELECT * FROM user WHERE 1 = 1 AND username = 'tom' AND sex = '1' AND address = '' or '1'='1'
10 tom 2014-07-10 1 beijing
16 tom 2018-07-31 1 shanghai
22 tom 2019-04-16 2 shanghai
24 tom 2019-06-22 1 guangzhou
25 tom 2019-01-22 2 guangzhou
28 tom 2018-07-31 1 shenzhen
别担心,此时我们使用一个小小的技巧,具体参考下面的示例代码
@Test
public void prepareStatementTest() {
String username = "tom";
String sex = null;
String address = "' or '1'='1";
PreparedStatement stat = null;
ResultSet res = null;
Connection conn = ConnectionFactory.getConnection();
String sql = "SELECT * FROM user WHERE 1 = 1";
List<Object> param = new ArrayList<>();
if (username != null) {
sql += " AND username = ?";
param.add(username);
}
if (sex != null) {
sql += " AND sex = ?";
param.add(sex);
}
if (address != null) {
sql += " AND address = ?";
param.add(address);
}
System.out.println(sql);
try {
stat = conn.prepareStatement(sql);
for (int i = 0; i < param.size(); i++) {
stat.setObject(i+1,param.get(i));
}
res = stat.executeQuery();
printRes(res);
} catch (SQLException e) {
e.printStackTrace();
} finally {
ResourceClose.close(res, stat, conn);
}
}
SELECT * FROM user WHERE 1 = 1 AND username = ? AND address = ?
但进行类似条件拼接这种操作时,可以先把参数放入一个集合中,然后遍历集合,同时利用setObject(index,obj)这个方法就可以动态的获取参数的索引了,而且不用关心参数是何种类型。
如果实在避免不了使用的话一定要使用可以需编译的prepareStatement对象,避免被SQL注入带来的风险。
原文:https://www.cnblogs.com/intflag/p/10780577.html