#网络安全在我身边#
题记:今天给大家分享的是网络安全相关技术:在JDBC下对SQL注射的防御
什么是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))beginselect * 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出其他信息: