Shulou(Shulou.com)06/02 Report--

This article mainly introduces "how to prevent SQL injection". In daily operation, I believe many people have doubts about how to prevent SQL injection. The editor consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "how to prevent SQL injection". Next, please follow the editor to study!

One: what is sql injection

SQL injection is one of the more common network attacks, it does not use the BUG of the operating system to achieve the attack, but aims at the programmer's negligence in writing, through the SQL statement to achieve no account login, or even tamper with the database.

Second: the general idea of SQL injection attack

1: find the location of SQL injection

2: determine the server type and background database type

3: SQL injection attacks based on different server and database characteristics

3: SQL injection attack example String sql = "select * from user_table where username='" + userName+ "'and password='" + password+ "';-- when the user name and password above are entered, the above SQL statement becomes: SELECT * FROM user_table WHERE username=''or 1user1-- and password=''"--analyze SQL statement:-- username=" or 1username equals "or 1room1 after the condition, then this condition will be successful. -- and then add two-- which means comments, which annotate the following statements so that they don't work, so that the statements will always be executed correctly, and the user can easily fool the system and get a legal identity. -- this is relatively gentle, if it is the implementation of SELECT * FROM user_table WHEREusername=''; DROP DATABASE (DB Name)-- "the consequences of and password=''-- can be imagined." Four: how to prevent SQL injection

Note: all programs with SQL injection vulnerabilities are because the program accepts variables input from client users or parameters passed by URL, and this variable or parameter is part of the SQL statement. We should always be vigilant about the content entered by users or parameters passed. This is the principle of "external data can not be trusted" in the security field. Throughout the various attacks in the field of Web security. Most of them are caused by developers violating this principle, so what you can naturally think of is to start with the detection, filtering and verification of variables to ensure that variables are what developers expect.

1. Check the variable data type and format

If your SQL statement is similar to where id= {$id}, and all the id in the database are numbers, then you should check to make sure that the variable id is the int type before the SQL is executed; if you accept a mailbox, you should check and strictly ensure that the variable must be in the mailbox format, and other types such as date, time and so on are the same. To sum up: as long as it is a variable with a fixed format, it should be checked strictly according to the fixed format before the execution of the SQL statement to make sure that the variable is in our expected format, which can largely avoid SQL injection attacks.

For example, in our previous example of accepting username parameters, our product design should have a user name rule at the beginning of user registration, such as 5-20 characters, which can only be composed of uppercase and lowercase letters, numbers and some safe symbols, without special characters. At this point we should have a function of check_username to perform a unified check. However, there are still many exceptions that cannot be applied to this criterion, such as article publishing systems, comment systems, etc., where users must be allowed to submit arbitrary strings, which requires filtering and other solutions.

2. Filter special symbols

For variables that cannot be determined in a fixed format, special symbol filtering or escape processing must be performed.

3. Bind variables and use precompiled statements

The mysqli driver of MySQL provides support for precompiled sentences. Different programming languages have their own methods of using precompiled sentences.

In fact, binding variables using precompiled statements is the best way to prevent SQL injection. The semantics of precompiled SQL statements do not change. In SQL statements, variables use question marks? Indicates that no matter how skilled the hacker is, he cannot change the structure of the SQL statement.

What is sql precompilation 1.1: what are precompiled sentences

Usually, one of our sql can be divided into the following three processes when the final execution is received by the db:

Lexical and semantic analysis

Optimize sql statements and make execution plan

Execute and return the result

We call this common statement Immediate Statements.

But in many cases, one of our sql statements may be executed repeatedly, or only individual values will be different each time (for example, the where clause of query is different, the set clause of update is different, and the values value of insert is different).

If you need to go through the above lexical and semantic parsing, sentence optimization, making an execution plan, etc., then the efficiency is obviously not good.

The so-called precompiled sentence is to replace the value in this kind of sentence with placeholder, which can be regarded as template or parameterization of sql sentence. This kind of sentence is generally called Prepared Statements or Parameterized Statements.

The advantages of precompiled sentences are summarized as follows: compiling once, running many times, eliminating the process of parsing and optimization; in addition, precompiled sentences can prevent sql injection.

Of course, as far as optimization is concerned, in many cases, the optimal execution plan can not only be determined by knowing the template of sql statements, but also need to estimate the cost through specific values.

Pre-compilation function of 1.2:MySQL

Note that the old version of MySQL (before 4.1) does not support server-side precompilation, but based on the current industry production environment, it can be considered that MySQL supports server-side precompilation.

Let's take a look at the use of precompiled sentences in MySQL.

(1) create a table

First of all, we have a test table t, which is structured as follows:

Mysql > show create table t\ gateway * 1. Row * * Table: tCreate Table: CREATE TABLE `t` (`a` int (11) DEFAULT NULL, `b` varchar (20) DEFAULT NULL, UNIQUE KEY `ab` (`a`, `b`) ENGINE=InnoDB DEFAULT CHARSET=utf8 (2) compilation

Next, we precompile a sql statement through the syntax of PREPARE stmt_name FROM preparable_stm

Mysql > prepare ins from 'insert into t select?,?; Query OK, 0 rows affected (0 sec) Statement prepared (3) execution

We use EXECUTE stmt_name [USING @ var_name [, @ var_name]...] To execute precompiled sentences

Mysql > set @ astat.999GrampGetWork query OK, 0 rows affected (0.00 sec) mysql > execute ins using @ aMagic MagicalQuery OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql > select * from twitterMattel + | a | b | +-+ | 999 | hello | +-+-+ 1 row in set (0.00 sec)

As you can see, the data has been successfully inserted into the table.

The scope of precompiled statements in MySQL is at the session level, but we can control the global maximum stored precompiled statements through the max_prepared_stmt_count variable.

Mysql > set @ @ global.max_prepared_stmt_count=1;Query OK, 0 rows affected (0.00 sec) mysql > prepare sel from 'select * from tactility error 1461 (42000): Can't create more than max_prepared_stmt_count statements (current value: 1)

When the number of precompiled entries has reached the threshold, you can see that MySQL will report the error shown above.

(4) release

If we want to release a precompiled statement, we can use the syntax of {DEALLOCATE | DROP} PREPARE stmt_name:

Mysql > deallocate prepare ins;Query OK, 0 rows affected (0.00 sec) six: why PrepareStatement can prevent sql injection

The principle is to use the method of pre-compilation, first compile the parameter set that can be controlled by the client in the SQL statement to generate the corresponding temporary variable set, and then use the corresponding setting method to assign values to the elements in the temporary variable set. The assignment function setString () will force type checking and security check on the incoming parameters, so the generation of SQL injection is avoided. The following is a detailed analysis

(1): why Statement is injected by sql

Because Statement is injected by sql because the structure of the SQL statement has changed. For example:

"select*from tablename where username='" + uesrname+ "'and password='" + password+ "'"

The structure of the sql statement changes after the user enters'or true or'.

Select * from tablename where username=''or true or'' and password=''

In this way, it will only be counted when it is judged that the user name and password match, but after the change, it becomes the logical relationship of or, regardless of whether the user name and password match or not, the return value of this formula is always true.

(2) Why Preparement can prevent SQL injection.

Because the Preparement style is

Select * from tablename where username=? And password=?

The SQL statement is pre-compiled with the database before getting input from the user, so that no matter what user name and password the user enters, the judgment is always a logical relationship of union, which prevents SQL injection.

To sum up, the reason why parameterization can prevent injection is that the statement is the statement, the parameter is the parameter, the value of the parameter is not part of the statement, the database only runs according to the semantics of the statement, as to whether to run with an ordinary backpack or a monster, it will not affect the route, but the difference between the fast point and the slow point.

Seven: how does mybatis prevent SQL injection

First, take a look at the difference between the following two sql statements:

Select id, username, password, rolefrom userwhere username = # {username,jdbcType=VARCHAR} and password = # {password,jdbcType=VARCHAR} select id, username, password, rolefrom userwhere username = ${username,jdbcType=VARCHAR} and password = ${password,jdbcType=VARCHAR} mybatis the difference between # and $:

1. # treat all the incoming data as a string and put a double quotation mark on the automatically passed data.

For example: where username=# {username}, if the value passed in is 111, then the value parsed into sql is where username= "111l". If the value passed in is id, the parsed sql is where username= "id".

2. $directly display the incoming data and generate it in sql.

For example: where username=$ {username}. If the value passed in is 111, the value when parsed to sql is where username=111.

If the value passed in is; drop table user;, the sql parsed into: select id, username, password, role from user where username=;drop table user

3. # mode can prevent sql injection to a great extent, while $mode can not prevent Sql injection.

4. The $method is generally used to pass in database objects, such as table names.

5. Generally, do not use $if you can use #. If you have to use parameters such as "${xxx}", filter it manually to prevent sql injection attacks.

6. In MyBatis, parameters in the format "${xxx}" will directly participate in SQL compilation, so injection attacks cannot be avoided. However, when it comes to dynamic table and column names, only parameter formats such as "${xxx}" can be used. Therefore, such parameters need to be handled manually in the code to prevent injection.

[conclusion] when writing the mapping statement of MyBatis, the format of "# {xxx}" should be adopted as far as possible. If you have to use a parameter like "${xxx}", filter it manually to prevent SQL injection attacks.

How does mybatis prevent sql injection

MyBatis framework as a semi-automatic persistence layer framework, its SQL statements have to be manually written by ourselves, at this time, of course, we need to prevent SQL injection. In fact, MyBatis's SQL is an "input + output" function, similar to the structure of the function, refer to the above two examples. Where parameterType represents the input parameter type and resultType represents the output parameter type. In response to the above, if we want to prevent SQL injection, of course we have to work on the input parameters. The part of the above code that uses #, that is, the input parameters are spliced in the SQL. After passing in the parameters, print out the executed SQL statement, and you will see that the SQL looks like this:

Select id, username, password, role from user where username=? And password=?

No matter what parameters you enter, the printed SQL looks like this. This is because MyBatis enables the precompilation function. Before SQL execution, the above SQL will be sent to the database for compilation. During execution, the compiled SQL will be directly used to replace the placeholder "?" Just do it. Because SQL injection only works for the compilation process, this approach avoids the problem of SQL injection.

[underlying implementation principle] how does MyBatis precompile SQL? In fact, at the bottom of the framework, it is the PreparedStatement class in JDBC that works. PreparedStatement is a familiar subclass of Statement, and its object contains compiled SQL statements. This "ready" approach not only improves security, but also improves efficiency when the same SQL is executed multiple times. The reason is that SQL has been compiled and does not need to be compiled when it is executed again

At this point, the study on "how to prevent SQL injection" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.