A summary of methods for defending against SQL injection _mssql

SQL injection is a type of attack that is extremely damaging. Although the damage is great, the defense is far less difficult than XSS.

SQL injection can see: https://en.wikipedia.org/wiki/SQL_injection

SQL injection vulnerability exists because of the concatenation of SQL parameters. That is, the query parameters that will be used for input are directly spliced into SQL statements, resulting in SQL injection vulnerabilities.

1. demo of classic SQL injection

We see: Select Id,no from user where id=2;

If the statement is a concatenation of SQL strings, such as: String sql = "Select Id,no from user where id=" + ID;

Where the ID is a user input parameter, then if the user entered a 2, then see a data found above, if the user entered the 2 or 1=1 for SQL injection attack,

Then you see that the above statement (select Id,no from user where id=2 or 1=1) identifies all the records in the user table.

This is a typical SQL injection.

Look at one more column:

We see that through SQL injection can directly delete the table Sqlinject! Visible its harm!

2. Reasons for SQL injection

The reason for SQL injection, ostensibly because of the concatenation of strings, constitute an SQL statement, not using SQL statements precompiled, binding variables.

But the deeper reason is that the string that the user entered is executed as an "SQL statement."

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

The value of the ID that we want the user to enter, just as a string literal, is passed in to the database execution, but when you enter: 2 or 1=1, the or 1=1 is not the literal value of the where id=, but is executed as a SQL statement. So its essence is to execute the user's input data as a command.

3. The defense of SQL injection

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

 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 do you prevent SQL injection?

The reason for this is that, with PreparedStatement, the SQL statement will be : "SELECT ID, no from user where id=?" pre-compiled, that is, the SQL engine will parse the syntax in advance, generate a syntax tree, build the execution plan, that is, the parameters you enter later, no matter what you enter, will not affect the syntax of the SQL sentence structure , because the parsing has been completed, Parsing is mainly about parsing SQL commands, such as SELECT, from, where, and, or, and so on. So even if you enter these SQL commands later, it will not be executed as SQL commands, because the execution of these SQL commands, must first through the syntax analysis, generate the execution plan, since the parsing has been completed, has been precompiled, then the parameters entered later, is absolutely impossible to execute as an SQL command, but only as a string literal argument. So SQL statement precompilation can defend against SQL injection.

2> but not all scenarios can be precompiled SQL statements, there are some scenes must be string concatenation, at this time, we strictly check the data types of parameters, as well as the use of some security functions, to the way SQL injection.

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

When we receive the parameters entered by the user, we strictly check the ID, only the int type. Complex situations can be judged by using regular expressions. This is also a way to 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 actual projects, we usually use a variety of frameworks, such as Ibatis, Hibernate,mybatis and so on. They are generally also by default SQL precompiled. For Ibatis/mybatis, if you are using the form of #{name}, then SQL precompilation, using ${name} is not SQL precompiled.

The above is a summary of the SQL injection defense method, I hope that after the study has helped.