mysql advanced (twenty-four) summary of methods to defend against SQL injection

Summary of methods to defend against SQL injection

This article mainly explains the methods to defend against SQL injection, introduces what injection is, what is the reason for injection, and how to defend. Need Friends can refer to it.

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

SQL injection can be found at: http://www.php.cn/

The reason why SQL injection vulnerabilities exist is to splice SQL parameters . That is, the query parameters used for input are directly spliced ​​into the SQL statement, resulting in SQL injection vulnerabilities.

1. Demonstrate classic SQL injection

## We see: select id ,no from user where id=2;

If the statement is obtained by splicing sql strings, for example: String sql = "select id,no from user where id=" + id;

The id is a parameter entered by the user. Then, if the user enters 2, then we can see a piece of data found above. If the user enters 2 or 1=1, a SQL injection attack will be carried out.

So you can see that the above statement (select id,no from user where id=2 or 1=1;) has found all the records in the user table.

This is a typical sql injection.

Look at another column:

## We see that the table sqlinject can be deleted directly through sql injection ! The dangers can be seen!

2. Reasons for sql injection

The reason for sql injection is that on the surface it is because strings are spliced ​​to form sql statements, and sql statements are not precompiled and bound. caused by fixed variables.

But the deeper reason is that the string entered by the user is executed as a "sql statement".

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

We hope that the value of the id entered by the user is only passed as a string literal value. Enter the database for execution, but when 2 or 1=1 is entered, or 1=1 is not used as the literal value of where id=, but is executed as a sql statement. So its essence is to execute the user's input data as a command.

3. Defense against 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 not all of the underlying reasons are 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 a typical use of sql statement precompilation and bind variables. Why can this prevent sql injection?

The reason is: using PreparedStatement, the SQL statement: "select id, no from user where id=?" will be pre-compiled, that is, the SQL engine will perform syntax analysis in advance and generate a syntax tree. Generate an execution plan, that is to say, the parameters you enter later, no matter what you enter, will not affect the syntax structure of the SQL statement, because the syntax analysis has been completed, and syntax analysis mainly analyzes SQL commands, such as select ,from ,where ,and, or ,order by etc. So even if you enter these sql commands later, they will not be executed as sql commands, because the execution of these sql commands must first pass syntax analysis and generate an execution plan. Now that the syntax analysis has been completed, it has been precompiled. , then the parameters entered later are absolutely impossible to execute as sql commands, and will only be treated as string literal parameters. Therefore, SQL statement precompilation can prevent SQL injection.

2> However, not all scenarios can use sql statement precompilation. Some scenarios must use string splicing. At this time, we strictly check the data type of the parameters, and we can also use some safety functions to do this. sql injection.

For example, String sql = "select id,no from user where id=" + id;

When receiving parameters entered by the user, we strictly check the id, which can only be int type . Complex situations can be determined using regular expressions. This can also prevent sql injection.

The use of safe 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 Some special characters contained in the name will be encoded, so that the SQL engine will not treat the string in the name as a SQL command for syntax analysis.

Note

In actual projects, we generally use various frameworks, such as ibatis, mybatis, hibernate, etc. They generally default to sql precompiled. For ibatis/mybatis, if you use the form #{name}, then it is sql precompiled. If you use ${name}, it is not sql precompiled.

The above is a summary of SQL injection defense methods. I hope it will be helpful to everyone’s future study.

Beautiful text and beautiful pictures

##The above is MySQL Advanced (24) Summary of methods to defend against SQL injection. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!