Shulou(Shulou.com)05/31 Report--

What this article shares with you is about how to defend against SQL injection. The editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

SQL injection is a kind of attack with great harm. Although the harm is great, defense is not nearly as difficult as XSS.

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

The reason for the SQL injection vulnerability is to concatenate SQL parameters. That is, the query parameters used for input are directly concatenated in the SQL statement, resulting in a SQL injection vulnerability.

1. Demonstrate the classic SQL injection

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

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

Id is a parameter entered by the user, so, if the user enters 2, then a piece of data has been found above, and if the user enters 2 or 1 to conduct a sql injection attack

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

This is typical sql injection.

Look at one more column:

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

2. Reasons for sql injection

On the surface, the reason for sql injection is to concatenate strings to form sql statements, instead of using sql statements to precompile and bind variables.

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

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

We want the value of id entered by the user to be passed into the database for execution only as a string literal, but when: 2 or 1 is entered, or 1 is not the literal value of where id=, but is executed as a sql statement. So its essence is to execute the data entered by the user as a command.

3. Defense against sql injection

1 > basically everyone knows that precompiling SQL statements and binding variables is the best way to defend against sql injection. But the deep-seated reasons are not always 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 precompile and bind variables using sql statements. Why does this prevent sql injection?

The reason is that if you adopt PreparedStatement, you will use the sql statement: "select id, no from user where id=?" Pre-compiled, that is, the SQL engine will pre-parse to generate a syntax tree and 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 parsing has been completed, and parsing is mainly about parsing sql commands, such as select, from, where, and, or, order by and so on. 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 generate an execution plan through parsing, and since the parsing has been completed and has been pre-compiled, then the parameters entered later can never be executed as sql commands, but will only be treated as string literal parameters. So the precompilation of sql statements can protect against sql injection.

2 > however, not all scenarios can be precompiled with sql statements, and some scenarios must use string concatenation. In this case, 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 id=" + id

When we receive the parameters entered by the user, we strictly check the id, which can only be int. Complex situations can be judged by regular expressions. This also prevents sql injection.

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 special characters contained in name so that the sql engine does not parse strings in name as sql commands.

Note:

In actual projects, we generally use a variety of frameworks, such as ibatis, hibernate,mybatis and so on. They are generally precompiled by sql by default. For ibatis/mybatis, if it is in the form of # {name}, it is sql precompiled, and ${name} is not sql precompiled.

The above is how to defend against SQL injection. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.