SQL injection of Network security series

1. SQL Injection Overview

A SQL injection vulnerability is likely to be introduced in a program that constructs an SQL statement using an unverified external input.

• Injection attack
  For string input, if the string is to be interpreted as some kind of instruction, special attention is required to prevent injection attacks. SQL injection, OS command injection, and XML injection are typical types of attacks.

2. SQL Injection Test Tool

You can use the Burpsuite tool to intercept requests sent by the browser and modify the parameters, attempting to inject the attack.
Simply modify the agent settings on the browser to configure the IP port that the agent listens on for the Burp tool.

3. SQL Injection Defense method

• The main source of the problem:

1. Executes an external number stitching SQL statement.
2. Execute an external incoming full SQL statement
3. The SQL statement in the configuration file does not use a precompiled placeholder.
4. The checksum function has a defect or a placeholder use error.

• Main Defense methods:
  1. Pre-compile and bind variables with SQL statements
  2, the use of white list and blacklist to achieve input inspection
  3. Using Esapi for escape processing in dynamic SQL and splicing SQL scenarios

    // ESAPI转义，防SQL注入    publicstaticencodeForSql(String input) {        newMySQLCodec(MySQLCodec.Mode.STANDARD);        return ESAPI.encoder().encodeForSQL(mysqlCodec, input);    }    // 说明： esapi需要有两个配置文件：ESAPI.properties、validation.properties

4. SQL Injection Defense Example 2.1 when using JDBC, the SQL statement is spliced

Scenario 1, when using statement's ExecuteQuery, execute, executeupdate and other functions, the incoming SQL statements are spliced with the external non-trusted parameters

错误示例：    String userName = ctx.getAuthenticatedUserName(); //this is a constant    //itemName是外部读入的参数拼接到SQL语句    String sqlString = "SELECT * FROM t_item WHERE owner=‘" + userName + "‘ AND itemName=‘" + request.getParameter("itemName") + "‘";    stmt = connection.createStatement();    rs = stmt.executeQuery(sqlString);

• Workaround

1. Use precompiled methods (untrusted data as field values).
2. Whitelist validation of external parameters that are stitched into SQL statements (untrusted data as table name, field name, Sort method).

正确示例：使用白名单校验方式校验itemName： String userName = ctx.getAuthenticatedUserName(); //this is a constantString itemName=getCleanedItemName(request.getParameter("itemName"));    String sqlString = "SELECT * FROM t_item WHERE owner=‘" + userName + "‘ AND itemName=‘" + itemName + "‘";    stmt = connection.createStatement();    rs = stmt.executeQuery(sqlString);

Scenario 2, when using Connection's preparedstatement, the SQL statement used to stitch the external non-trusted parameters

错误示例：    String userName = ctx.getAuthenticatedUserName//this is a constant    //itemName是外部读入的参数拼接到SQL语句    String itemName = request.getParameter("itemName");    // ...Ensure that the length of userName and itemName is legitimate    // ...    "SELECT * FROM t_item WHERE owner=? AND itemName=‘"+itemName+"‘";    PreparedStatement stmt = connection.prepareStatement(sqlString);    stmt.setString(1, userName);    rs = stmt.executeQuery();

• Workaround

1. Change stitching to placeholder mode.
2. White-List validation of external parameters that are stitched into SQL statements.

正确示例：所有的参数使用占位符String userName = ctx.getAuthenticatedUserName(); //this is a constant    String itemName = request.getParameter("itemName");    // ...Ensure that the length of userName and itemName is legitimate    // ...    String sqlString = "SELECT * FROM t_item WHERE owner=? AND itemName=?";    PreparedStatement  stmt = connection.prepareStatement(sqlString);    stmt.setString(1, userName); // jdbc编号从1开始    stmt.setString(2, itemName);    rs = stmt.executeQuery();

Scenario 3, stored Procedures build SQL statements in a dynamic manner, resulting in SQL injection risk

错误示例：REATE PROCEDURE sp_queryItem    @userName varchar(50),    @itemName varchar(50) AS BEGIN     DECLARE @sql nvarchar(500);     SET @sql = ‘SELECT * FROM t_item                 WHERE owner = ‘‘‘ + @userName + ‘‘‘                AND itemName = ‘‘‘ + @itemName + ‘‘‘‘;    EXEC(@sql); END GO

• Workaround

1. How to use parameterized queries

正确示例：采用参数化查询的方式CREATE PROCEDURE sp_queryItem    @userName varchar(50),     @itemName varchar(50) AS BEGIN     SELECT * FROM t_item      WHERE userName = @userName    AND itemName = @itemName; END GO

2.2 When using hibernate, the incoming SQL statement has stitching external parameters when invoking the API

Scenario 1: When calling CreateQuery, the incoming SQL statement is spliced with an external non-trusted parameter

错误示例：//SQL语句拼接不可信参数String itemName = request.getParameter("itemName");Query hqlQuery = session.createQuery("from Item as item where item.itemName = ‘" + itemName + "‘");List<Item> hrs = (List<Item>) hqlQuery.list();

• Workaround

1. White-List validation of external parameters that are stitched into SQL statements.
2. Use Hibernate to configure the mapping relationship mode.

正确示例：对外部参数进行白名单校验String itemName = request.getParameter("itemName");itemName=getCleanedItemName(itemName);//白名单校验Query hqlQuery = session.createQuery("from Item as item where item.itemName = ‘" + itemName + "‘");List<Item> hrs = (List<Item>) hqlQuery.list();

2.3 When using MyBatis, the SQL statement uses the $Placeholder

Scenario 1: Configuration file using $ placeholders

错误示例：//使用$,底层将使用简单拼接<select id="getItems" resultClass="Item">     SELECT * FROM t_item WHERE owner = $userName$ AND itemName = $itemName$</select>

• Workaround

1. Change the $ placeholder to a # placeholder
2. If the external non-trusted data is the table name, the field name, and the sort method, the outer parameters are whitelisted

正确示例：使用#占位符方式<select id="getItems" resultClass="Item">     SELECT * FROM t_item WHERE owner = #userName# AND itemName =#itemName#</select>

Scenario 2: SQL statement for the function label in the MyBatis interface, using $ placeholders

错误示例：public interface IUserDAO {   //标注中的SQL语句通过$表示占位符，内部实现是单纯的拼接@Select("select *from User where id=${id})    User getUser(@Param("id")String id);}

正确示例：标注中的SQL语句通过‘#‘表示占位符，内部实现是参数化预处理public interface IUserDAO { @Select("select *from User where id=#{id})    User getUser(@Param("id")String id);}

--end.

SQL injection of Network security series