Summary of methods to prevent SQL injection

Time:2021-2-23

SQL injection is one of the most harmful attacks. Although the harm is great, the defense is far less difficult than XSS.

For SQL injection, please refer to:https://en.wikipedia.org/wiki/SQL_injection

The reason of SQL injection vulnerability is splicing SQL parameters. That is to say, the query parameters used for input are directly spliced into SQL statements, which leads to SQL injection vulnerability.

1. Demonstrate the classic SQL injection

We can 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 text align: Center” >

We can see that table sqlinject can be deleted directly through SQL injection! Visible its harm!

2. The reason of SQL injection

On the surface, the reason for SQL injection is to splice strings to form SQL statements. SQL statements are not used to precompile and bind variables.

But the deeper reason is that the string input by the user is executed as “SQL statement”.

For example, the string SQL above is “select id, no from user where select id, no from user where id =?”;

As shown above, SQL statements are typically used to precompile and bind variables. Why does this prevent SQL injection?

The reason is: if you use Preparedstatement, the SQL statement will be:”select id, no from user where id=?” Precompiled, that is, SQL Engine will perform syntax analysis in advance, generate syntax tree, and generate execution plan,in other words,The parameters you enter later, no matter what you enter, will not affect the syntax structure of the SQL statementBecause 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 input these SQL commands later, they will not be executed as SQL commands,Because the execution of these SQL commands must first generate the execution plan through syntax analysis. Since the syntax analysis has been completed and has been precompiled, the parameters entered later can never be executed as SQL commands,It will only be treated as a string literal parameterSo SQL statement precompiling can prevent SQL injection.

2> However, not all scenarios can be precompiled with SQL statements. Some scenarios must use string splicing. At this time, we strictly check the data types of parameters, and we can use some security functions to inject SQL.

For example, string SQL = select id, no from user where select id, no from user where name = “+ name;

ESAPI.encoder().encodeForSQL(codec, name)
This function encodes some special characters contained in the name, so that the SQL Engine will not parse the strings in the name as SQL commands.

Note:

In actual projects, we usually use various frameworks, such as ibatis, hibernate, mybatis and so on. They are usually SQL precompiled by default. For ibatis / mybatis, if you use the # {name} form, then it is SQL precompile, and using ${name} is not SQL precompile.

The above is the summary of SQL injection defense methods, and I hope it will be helpful for your later study.