题记:今天给大家分享的是网络安全相关技术:在JDBC下对SQL注射的防御
什么是SQL注入
详见文章:
JDBC下对SQL注射的防御
关于我的理解,则会将其总结为一句话:“被动态拼接执行的SQL语句中包含了不可信任的数据。”
什么是动态拼接?看看下面这条SQL语句:
select * from "+param_table+" where name='"+param_name+"'";
看到语句中的‘+’号了么,这意味着param_table和param_name并不是写死在语句中的,而我可以对其进行传参从而达到我的某些目的。那么假如我有student表:
teacher表:
我想从中查询hacker的信息那么将有如下代码:
String param_table = "student";
String param_name = "hacker";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select * from "+param_table+" where name='"+param_name+"'");
while(rs.next()) {
out.println(rs.getString(1)+"/"+
rs.getString(2)+"/"+
rs.getString(3));
}
于是构成了这样一条语句:
select * from student where name='hacker';
这样就可以查询到hacker的信息:
但是如果我将hacker修改为hacker’ or 1=1#:String param_name = “hacker’ or 1=1#”;则student表中所有数据被dump出来:
接着也可以将student修改为student union select * from teacher,于是连同teacher表的数据也被dump出来:
那该如何防护?这是重点,我以前挖SQL注入的时候,仅仅是给厂商提供了这样的建议,但对于厂商来说可能只是极其模糊的概念:
接下来我介绍几种在JDBC下对SQL注入的防御方式:
1.预编译:这里用到PreparedStatement类进行预编译,那么将有如下代码:
String param_table = "student";
String param_name = "hacker";
String stmt = "select * from ? where name= ?";
PreparedStatement ps = conn.prepareStatement(stmt);
ps.setString(1,param_table);
ps.setString(2,param_name);
ResultSet rs = ps.executeQuery();
while(rs.next()) {
out.println(rs.getString(1)+"/"+
rs.getString(2)+"/"+
rs.getString(3));
接着运行却出现了错误:
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''student' where name= 'hacker'' at line 1
最后经过调试发现param_table不能被绑定,并且发现字段名也不能被绑定,那么可能会用拼接的方式进行预编译再查询,代码如下:
String param_table = "student";
String param_name = "hacker";
PreparedStatement ps = conn.prepareStatement("select * from "+param_table+" where name=?");
ps.setString(1,param_name);
ResultSet rs = ps.executeQuery();
while(rs.next()) {
out.println(rs.getString(1)+"/"+
rs.getString(2)+"/"+
rs.getString(3));
}
但是param_table=student这里依旧产生了注入,如果修改为:
String param_table = "student union select * from teacher";
则:
那么我只能把student写死在语句中:
String param_name = "hacker";
String stmt = "select * from student where name=?";
PreparedStatement ps = conn.prepareStatement(stmt);
ps.setString(1,param_name);
ResultSet rs = ps.executeQuery();
while(rs.next()) {
out.println(rs.getString(1)+"/"+
rs.getString(2)+"/"+
rs.getString(3));
}
此时再将param_name修改为hacker’ or 1=1#:则会将hacker’ or 1=1#当做表名来查询,查不到这个表,当然无回显了:
2.存储过程:有这样一个对student表操作的存储过程:
create procedure `getstudent`(in aname varchar(20),out uname varchar(20),out uage int(11),out usex varchar(10))
begin
select * from student where name=aname into uname,uage,usex;
end;
那么我们可以用CallableStatement类来防止注入,代码如下:
String param_name = "hacker’ or 1=1#";
CallableStatement cs = conn.prepareCall("{call getstudent(?,?,?,?)}");
cs.setString(1,param_name);
cs.registerOutParameter(2,Types.VARCHAR);
cs.registerOutParameter(3,Types.INTEGER);
cs.registerOutParameter(4,Types.VARCHAR);
cs.executeQuery();
out.println(cs.getString(2)+"/"+
cs.getInt(3)+"/"+
cs.getString(4));
可以看到SQL注入的语句已经不再起作用:
3.白名单验证:前面的预编译和存储过程不能对表名进行操作,那么这里用白名单对表名进行过滤,代码如下:
String param_table = "student union select * from teacher";
String param_name = "hacker";
String stmt = "";
if(param_table.equals("student")) {
stmt = "select * from student where name=?";
}
else if(param_table.equals("teacher")) {
stmt = "select * from teacher where name=?";
}
else {
out.println("table name error!");
}
PreparedStatement ps = conn.prepareStatement(stmt);
ps.setString(1,param_name);
ResultSet rs = ps.executeQuery();
while(rs.next()) {
out.println(rs.getString(1)+"/"+
rs.getString(2)+"/"+
rs.getString(3));
}
尝试进行注入则会报错:
4.对输入进行编码:这里我使用十六进制对输入进行编码,方法的声明及定义代码如下:
public static String bytestoHex(byte[] byteArr) {
if(byteArr == null || byteArr.length < 1) return "";
StringBuilder sb = new StringBuilder();
for(byte t : byteArr) {
if((t & 0xF0) == 0) sb.append("0");
sb.append(Integer.toHexString(t & 0xFF));
}
return sb.toString().toUpperCase();
}
使用方法byte2HexStr对输入param_name进行编码,代码:
String param_name = "hacker' or 1=1#";
Statement stmt = conn.createStatement();
String hex_param_name = bytestoHex(param_name.getBytes());
out.println("编码后的param_name为:"+bytestoHex(param_name.getBytes()));
ResultSet rs = stmt.executeQuery("select * from student where hex(name)='"+hex_param_name+"'");
while(rs.next()) {
out.println(rs.getString(1)+"/"+
rs.getString(2)+"/"+
rs.getString(3));
}
由于hacker’ or 1=1#被编码为6861636B657227206F7220313D3123并被作为表名进行查询,因此不会dump出其他信息: