sql注入及防护
一、常见获取变量
request.getParameter()
request.getCookies()
防护1:
如何防止sql注入 数字型： Integer.parseInt(s) 字符型： String.replace("'","''"); //oracle、 mssql String.replace("'","\\'");//mysql
防护2:正则表达式过滤
防护3:使用参数化查询执行sql语句（预编译的方式）
使用参数化查询执行sql语句（预编译的方式） String selectStatement = "SELECT * FROM User WHERE userId = ? "; PreparedStatement prepStmt = con.prepareStatement(selectStatement); prepStmt.setString(1, userId); ResultSet rs = prepStmt.executeQuery();
二、实战演练
测试路径http://127.0.0.1:8080/wavsep/index-sql.jsp
1、返回值500报错的注入漏洞：
(1)、登陆字符绕过（Case1-InjectionInLogin-String-LoginBypass-WithErrors.jsp）
Injection into string values in the scope of a query within a login page with erroneous responses. Barriers: None SQL Statement Context: SELECT (WHERE Clause) Sample Exploit Structures: 'or'[value]'='[value] 'or [value]=[value][comment][space] Examples of Login Bypass Exploits: Exploit (both input fields): 'or'7'='7 Independent Exploit 1: ' or 7=7--%20 Independent Exploit 2: ' or 7=7#%20 Independent Exploit 3: ' or 7=7/*%20
进入测试一：
访问显示 login failed登陆失败，用户名密码不正确，发现登陆是通过get在url里面提交参数 username&password
测试利用exp both来尝试：
?username='or'7'='7&password='or'7'='7
绕过登陆获取user1的回显。
进行分析源码的漏洞存在原因：
数据库登陆查询的语句：存在字符串拼接
String username = request.getParameter("username"); String password = request.getParameter("password"); ... String SqlString = "SELECT username, password " + "FROM users " + "WHERE username='" + username + "'" + " AND password='" + password + "'"; ...
用户登陆插入：?username='or'7'='7&password='or'7'='7 SQL语句发生变化如下：
原SQL查询语句 String SqlString = "SELECT username, password " + "FROM users " + "WHERE username='" + username + "'" + " AND password='" + password + "'"; ----------------------------------------------------------- 插入?username='or'7'='7&password='or'7'='7 String SqlString = "SELECT username, password " + "FROM users " + "WHERE username='" + 'or'7'='7 + "'" + " AND password='" + 'or'7'='7 + "'"; 转换一下实际的数据库的查询的SQL语句： select username,password from users where username='' or 7'='7'And password='' or 7'='7';
通过username='' or 7'='7'And password='' or 7'='7' ，表述传参数查询额度数据是空或者是为真数据库返回回来的值是true，查询成功，最后用户登陆成功。
exp1是第一个两个条件username password需要两个参数都需要为真（true）。
Independent Exploit 1: ' or 7=7--%20 Independent Exploit 2: ' or 7=7#%20 Independent Exploit 3: ' or 7=7/*%20
上面的三个是通过--%20、#%20、/*%20三个将数据后面的参数注释了，后面的参数将不参与数据的参数查询。
怎么进行防护
如何防止sql注入 数字型： Integer.parseInt(s) 字符型： String.replace("'","''"); //oracle、 mssql String.replace("'","\\'");//mysql
本次测试使用的是mysql数据看
防护的代码：replace("'","\\'")
Connection conn = null; try { String username = request.getParameter("username").replace("'","\\'"); #使用replace函数过滤单引号 String password = request.getParameter("password").replace("'","\\'"); #使用replace函数过滤单引号 conn = ConnectionPoolManager.getConnection(); System.out.print("Connection Opened Successfully\n"); String SqlString = "SELECT username, password " + "FROM users " + "WHERE username='" + username + "'" + " AND password='" + password + "'"; Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(SqlString); if(rs.next()) { out.println("hello " + rs.getString(1)); } else { out.println("login failed"); }
再次提交参数：不能绕过登陆
还有另外一种方式可以通过预编译方式：
使用参数化查询执行sql语句（预编译的方式）
预编译方式： "select * from users where username=? and password=?" 使用参数化查询执行sql语句（预编译的方式） String selectStatement = "SELECT * FROM User WHERE userId = ? "; PreparedStatement prepStmt = con.prepareStatement(selectStatement); prepStmt.setString(1, userId); ResultSet rs = prepStmt.executeQuery();
实际代码修改：
原始代码： Connection conn = null; try { String username = request.getParameter("username"); String password = request.getParameter("password"); conn = ConnectionPoolManager.getConnection(); System.out.print("Connection Opened Successfully\n"); String SqlString = "SELECT username, password " + "FROM users " + "WHERE username='" + username + "'" + " AND password='" + password + "'"; Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(SqlString); if(rs.next()) { out.println("hello " + rs.getString(1)); } else { out.println("login failed"); } 修改的代码： ------------------------------------------------------------------------------ Connection conn = null; try { String username = request.getParameter("username"); String password = request.getParameter("password"); conn = ConnectionPoolManager.getConnection(); System.out.print("Connection Opened Successfully\n"); String SqlString = "SELECT username, password " + "FROM users " + "WHERE username=?" + #修改将username改为？ 采用预编译的方式 " AND password=?"; #修改将password改为？ 采用预编译的方式 PreparedStatement prepStmt = conn.prepareStatement(SqlString); #修改的连接方式查询 prepStmt.setString(1, username); #添加对应参数的查询 prepStmt.setString(2, password); ResultSet rs = prepStmt.executeQuery(); if(rs.next()) { out.println("hello " + rs.getString(1)); } else { out.println("login failed"); }
修改后的访问结果：可以防止SQl注入
(2)、Union查询注入（Case2-InjectionInSearch-String-UnionExploit-WithErrors.jsp）
Injection into string values in the scope of a query within a search page with erroneous responses. Barriers: None SQL Statement Context: SELECT (WHERE Clause) Sample Exploit Structures: ' UNION SELECT [int], [string], [string] FROM [table] [comment mark][space] Examples of Union Exploits: Exploit: 'or'7'='7 MSSQL Exploit: ' UNION SELECT id, name, 'jfks' FROM sysobjects--%20 MySQL Exploit: ' UNION SELECT 1, table_name, 'jfks' FROM information_schema.tables--%20 MySQL Exploit (No Comments): ' UNION SELECT 1, table_name, 'jfks' FROM information_schema.tables WHERE table_name like'%25
通过union联合查询返回获取数据库的表名：' UNION SELECT 1, table_name, 'jfks' FROM information_schema.tables--%20
查看对应的代码：
主要是代码模糊查询。还有就是SQL语句拼接查询，没有进行参数预编译和参数替换。
String msg = request.getParameter("msg"); //获取参数-前台参数传递获取 conn = ConnectionPoolManager.getConnection(); System.out.print("Connection Opened Successfully\n"); //SQL查询语句 String SqlString = "SELECT msgid, title, message " + "FROM messages " + "WHERE message like'" + msg + "%'"; //sql语句模糊查询 Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(SqlString);
(2)、order查询（Case5-InjectionInSearchOrderBy-String-BinaryDeliberateRuntimeError-With200Errors.jsp）
存在问题代码：
特殊的情况的是sql防止注入：（下面的情况不是数字型也不是字符型）
String order = request.getParameter("orderby"); ... String SqlString = "SELECT msgid, title, message " + "FROM messages " + "ORDER BY " + order; Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(SqlString); 问题分析：还是存在字符串拼接的问题 下面的order通过表里面的字段，这个字段应该有自己相应的长度，所以限制order传入字段的长度 通过限制sql查询的传入参数的长度，来限制sql注入。 ------------------------------------------------------------------------------ String order = request.getParameter("orderby"); if (order.length() > 10){ //添加的长度的判断，限制查询的传入长度 return; } ... String SqlString = "SELECT msgid, title, message " + "FROM messages " + "ORDER BY " + order; Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(SqlString); 尝试预编译方式： String order = request.getParameter("orderby"); ... String SqlString = "SELECT msgid, title, message " + "FROM messages " + "ORDER BY ?";//预编译方式 PreparedStatement prepStmt = conn.prepareStatement(SqlString); prepStmt.setString(1, order); ResultSet rs = prepStmt.executeQuery();
修改限制访问数据长度对于的防护代码：if (order.length() > 10) return；
不再有相关的sql异常的报错回显
不能获取数据
采用预编译方式：还是能够正常的获取数据
获取数据
三、防护总结：
获取前段的传递的参数。
request.getParameter() request.getCookies()
防护1:
如何防止sql注入 数字型： Integer.parseInt(s) 字符型： String.replace("'","''"); //oracle、 mssql String.replace("'","\\'");//mysql
防护2:正则表达式过滤
防护3:使用参数化查询执行sql语句（预编译的方式）
使用参数化查询执行sql语句（预编译的方式） String selectStatement = "SELECT * FROM User WHERE userId = ? "; PreparedStatement prepStmt = con.prepareStatement(selectStatement); prepStmt.setString(1, userId); ResultSet rs = prepStmt.executeQuery();
防护4:一些特殊的更具查询的具体需要，可以限制查询传入的参数的长度（orderby的情况）
四、课后了解（需要科学上网）
https://www.owasp.org/index.php/Preventing_SQL_Injection_in_Java
sql注入的代码造成的原因：字符串的拼接或者没有使用参数编译过程查询。
公众号：
thelostworld：
网友评论