Deep Anatomy of SQL injection

1. Reasons for SQL injection

The reason for SQL injection is, on the face of it, stitching strings, composing SQL statements, not using SQL statements to precompile, binding variables.

The deeper reason, however, is that the user-entered string is executed as an "SQL statement."

For example, the above String sql = "Select Id,no from user where id=" + ID;

We want the user to enter the value of the ID, just as a string literal, into the database execution, but when entered: 2 or 1=1, where the or 1=1 is not the literal value of where id=, but as a SQL statement to execute. So its essence is to put the user's input data as a command to execute.

3. The defense of SQL injection

1> basically everyone knows that using SQL statements to precompile and bind variables is the best way to protect against SQL injection . But the underlying causes are not necessarily understandable.

String sql = "SELECT ID, no from user where id=?";        PreparedStatement PS = conn.preparestatement (SQL);        Ps.setint (1, id); Ps.executequery ();

As shown above, it is typical to use SQL statements to precompile and bind variables. Why is it that you can prevent SQL injection?

The reason for this is:with preparedstatement, the SQL statement will be: "select ID, no from user where id=?" Pre-compiled, that is, the SQL engine will pre-parse the syntax, generate a syntax tree, generate the execution plan , that is, the parameters you entered later, no matter what you entered, will not affect the syntax structure of the SQL statement , because parsing is done, and parsing is primarily about SQL commands such as SELECT, from, where, and, or, order by, and so on. So even if you enter these SQL commands later, they won't be executed as SQL commands.because of the execution of these SQL commands, the execution plan must be generated through parsing, and since the parsing has already been done and has been precompiled, the arguments entered later are absolutely impossible to execute as SQL commands and will only be treated as string literal parameters 。 So SQL statement precompilation can protect against SQL injection.

2> but not all scenarios can be pre-compiled with SQL statements, some scenarios must be in the form of string concatenation, at this time, we strictly check the parameters of the data type, there are some security functions can be used to approach SQL injection.

For example, String sql = "Select Id,no from user where id=" + ID;

When we receive the user input parameters, we strictly check the ID, only the int type. Complex situations can be judged by using regular expressions . This can also prevent SQL injection.

The use of security functions, such as:

Mysqlcodec codec = new Mysqlcodec (Mode.standard);        Name = Esapi.encoder (). Encodeforsql (codec, name); String sql = "Select Id,no from user where name=" + name;

Esapi.encoder (). Encodeforsql (codec, name)

This function encodes some of the special characters contained in name so that the SQL engine does not parse the string in name as an SQL command.

Note:

In the actual project, we generally use various frameworks, such as Ibatis, Hibernate,mybatis and so on. They generally also default to SQL Precompilation. For Ibatis/mybatis, if you are using the form #{name}, then SQL precompilation, using ${name}, is not precompiled by SQL .

Deep Anatomy of SQL injection