Summary of methods to prevent SQL injection

This article focuses on the defense of SQL injection methods, describes what is injected, what causes the injection, and how to defend, the needs of friends can refer to the next

SQL injection is a kind of attack form of great harm. Although the damage is great, defense is far less difficult than XSS.

SQL injection can be found in: https://en.wikipedia.org/wiki/SQL_injection

The reason SQL injection vulnerability exists is to stitch SQL parameters. That is, the query parameters that are used for input are stitched directly into the SQL statement, resulting in a SQL injection vulnerability.

1. Demo of the classic SQL injection

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

If the statement is obtained by concatenation of SQL strings, for example: String sql = "Select Id,no from user where id=" + ID;

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

So see, the above statement (select Id,no from user where id=2 or 1=1;) finds all the records in the user table.

This is the typical SQL injection.

Look at one more column:

We see that the table Sqlinject can be deleted directly by SQL injection! Visible its harm!

2. 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.

?

1234	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 that with PreparedStatement, the SQL statement: "SELECT ID, no from user where id=?" is pre-compiled, that is, the SQL engine pre-parses the syntax, generates a syntax tree, generates an execution plan, that is, The arguments you enter later, no matter what you enter, 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, it will not be executed as SQL command, because the execution of these SQL commands, must first through the parsing, generate execution plan, since the parsing has been completed, has been precompiled, then the input parameters, is absolutely impossible to execute as a SQL command and will only be treated as a string literal parameter. 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:

?

123	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.

The above is the SQL injection Defense method Summary, I hope that everyone after the study has helped.

Summary of methods to prevent SQL injection