Php operations on mysql to prevent SQL injection (collection)

This article will start with SQL Injection risks and compare the differences between preprocessing of addslashes, mysql_escape_string, mysql_real_escape_string, mysqli, and pdo. This article will start with SQL Injection risks and compare the differences between addslashes, mysql_escape_string, mysql_real_escape_string, mysqli, and pdo preprocessing to provide the best anti-injection methods?

When a variable is passed into php from a form, mysql needs to be queried and processed.
Example:
$ Unsafe_variable = $ _ POST ['User _ input'];
Mysqli_query ("insert into table (column) VALUES ('". $ unsafe_variable ."')");
You can enter such as: value'), drop table table, and the SQL statement becomes like this:
Insert into table (column) VALUES ('value'); drop table table ;')
The execution result is that the table is deleted.

This is a common SQL injection method. how can we prevent it in a program?

1. magic reference (Recommendation Index 3)
Addslashes () and stripslashes () are functions opposite to each other.
Addslashes () is used to add a \ slash to '"\ and NULL in the variable to avoid the format error of parameters passed into the SQL statement. if a subquery is injected, by adding \, you can interpret the parameter as content rather than executing the statement to avoid being executed by mysql.

However, the \ added by addslashes () is only used in php and will not be written to mysql.
Then, tripslashes () is used to remove the \ php variable \ added. Since \ is not written to mysql, the content queried from mysql does not need to be tripslashes ().

In terms of anti-injection, addslashes () can prevent most injections, but this function does not check the variable encoding. when using a Chinese gbk for example, because the length is long, some gbk codes are interpreted as two ascii codes, resulting in new injection risks. See 2 below.

If UTF-8 encoding is used for webpage forms, php, and mysql, this problem does not occur.
It is not recommended to use this function based on the risk. we recommend that you use the method in the following 3.
Reference: https://segmentfault.com/q/1010000005994443


2. mysql_real_escape_string () (Recommendation Index 4)
Because addslashes () does not detect character sets, there is a risk of wide byte injection, so this function is added to php.
This function was originally an extension of mysql, but due to the wide byte problem, php developed this function based on mysql extension.

SQL injection caused by the gbk wide character vulnerability
Reference: https://www.91ri.org/8611.html
Reference: http://www.cnblogs.com/suihui/archive/2012/09/20/2694751.html

Mysql_real_escape_chars () is an alternative to mysql_escape_chars.
Compared with addslashes (), it not only escapes '"\ NOL (ascii 0), but also escapes \ r \ n. Data encoding is also detected.
According to the official description of php, this function can be safely used for mysql.

This function will be used for database connection (because the character set needs to be checked) and perform different operations based on different character sets. If the current connection does not exist, the last connection is used.

Mysql_real_escape_string () anti-injection details
This method is not recommended after php5.5 and abolished in php7.
Reference: https://segmentfault.com/q/1010000006015688


3. pre-processing query (Prepared Statements) (Recommendation Index 5)
Prepared statements and parameterized queries can effectively prevent SQL injection.

Why can pre-processing and parametric query prevent SQL injection?
In the traditional writing method, SQL query statements are spliced in the program. anti-injection (with a slash) is processed in php, and then a statement is sent to mysql, in fact, mysql does not have a good way to determine which statements are normal and which are malicious. Therefore, the direct query method has the risk of being injected.
After mysql5.1, a pre-processing parameterized query similar to jdbc is provided. The query method is as follows:
A. pre-send an SQL template before
B. send the parameters to be queried to mysql.
Just like filling in the blank question, no matter how the parameter is injected, mysql can know that this is a variable and does not perform semantic parsing to prevent injection. this is done in mysql.

Refer:
How to prevent SQL injection in PHP
Http://blog.csdn.net/sky_zhe/article/details/9702489

Why does parameterized query prevent SQL injection?
Http://www.cnblogs.com/LoveJenny/archive/2013/01/15/2860553.html


I have provided a lot of information above, and I will sort it out based on my own understanding.

Preprocessing can be divided into two types:
A. Use mysqli: prepare () to implement
Let's look at a complete usage:
$ Mysqli = new mysqli ("example.com", "user", "password", "database ");
$ Stmt = $ mysqli> prepare ("SELECT id, label FROM test WHERE id =? ");
$ Stmt> bind_param (1, $ city );
$ Stmt> execute ();
$ Res = $ stmt> get_result ();
$ Row = $ res> fetch_assoc ();

A. write an SQL statement and then use? Placeholders replace variables in SQL
B. replace variables
C. execute
D. obtain a binary result set and retrieve the php result set from the binary result.
E. traverse the result set

When preprocessing is used, a query is performed in two steps. First, it is safe. It is also recommended for php5.5 and php7.

Refer:
Http://www.cnblogs.com/liuzhang/p/4753467.html


B. use pdo to implement
Pdo is a database abstraction layer officially recommended by php and provides many practical tools.

Using pre-processing parameterized query of pdo can effectively prevent SQL injection.
The usage is similar to the above. The difference is that pdo provides more diverse methods.
When this pdo> $ stmt object is used for query, it will be overwritten by the result set. the type is a two-dimensional array.

In the preceding pre-processing parameterized query, the anti-injection operation is performed in mysql. In fact, pdo also has a built-in pre-processing simulator called ATTR_EMULATE_PREPARES.
By default, PDO uses the character set specified in DSN to escape the input parameters locally (called native prepared statements in the PHP manual) and concatenates them into a complete SQL statement, send to MySQL Server. This is like splicing variables in our programs to SQL and then executing queries.

In this case, whether the PDO driver can correctly escape input parameters is the key to blocking SQL injection. However, PHP 5.3.6 and earlier versions do not support defining the charset attribute in DSN (which will be ignored). If the local escape of PDO is used, it may still cause SQL injection,

If ATTR_EMULATE_PREPARES = true (default), the pre-processing parameterized query is completed in the pdo simulator. the simulator processes the query based on the character set (dsn parameter) and sends the statement to mysql.

If ATTR_EMULATE_PREPARES = false, SQL sends the parameter to mysql twice. mysql processes the parameter based on its character set (set names) and completes the query.

However, due to different versions, the implementation of pdo in different versions is also different. for some versions and bugs, we use php5.3.6 as the demarcation line to describe:

Php5.3.6 or earlier
$ Pdo = new PDO ("mysql: host = localhost; dbname = test;", 'root', 'pwd ');
$ Pdo> setAttribute (PDO: ATTR_EMULATE_PREPARES, false );
$ Pdo> exec ('set names utf8 ');
$ Id = '0 or 1 = 1 order by id desc ';
$ SQL = "select from article where id =? ";
$ Statement = $ pdo> prepare ($ SQL );
$ Statement> bindParam (1, $ id );
$ Statement> execute ();
As shown in the preceding figure, the local preprocessing simulator is disabled and the parameters are directly distributed to mysql. mysql performs SQL injection based on the set name utf8 character set. The above code will not generate injection.

Php5.3.6 or later
$ Pdo = new PDO ("mysql: host = localhost; dbname = test; charset = utf8", 'root', 'pwd ');
$ Pdo> exec ('set names utf8 ');
$ Id = '0 or 1 = 1 order by id desc ';
$ SQL = "select from article where id =? ";
$ Statement = $ pdo> prepare ($ SQL );
$ Statement> bindParam (1, $ id );
$ Statement> execute ();
In php5.3.6 and later versions, ATTR_EMULATE_PREPARES is enabled by default. the simulator detects the attack based on charset = utf8 in new PDO () and completes anti-injection operations on the simulator. If you disable the simulator, it will also be sent to mysql for anti-injection processing as in earlier versions.

Refer:
Analysis of PDO anti-injection principle and precautions for using PDO
Http://zhangxugg163com.iteye.com/blog/1835721


BindParam of PHP 5.3.6 and earlier PDO versions, potential security risks of bindValue
Http://zhangxugg163com.iteye.com/blog/1855088

The pdo SQL Injection Vulnerability in php versions earlier than 5.3.6
Http://my.oschina.net/zxu/blog/163135

Segmentfault discussion
Https://segmentfault.com/q/1010000000723496



4. html output and prevention of xss injection

Special character output
For example, '"<> has a special meaning. if you write the code directly to html for output, the dom format may be disordered, so a special output method is required.