The most practical and effective PHP to prevent SQL injection _ PHP Tutorial

The most practical and effective PHP to prevent SQL injection. Description of the most practical and effective prevention of SQL injection in PHP: if the data entered by the user is inserted into an SQL query statement without being processed, the application may suffer from SQL injection in PHP, which is the most practical and effective way.

Problem description:

If the data entered by the user is inserted into an SQL query statement without being processed, the application may be vulnerable to SQL injection attacks, as shown in the following example:

The code is as follows:
$ Unsafe_variable = $ _ POST ['User _ input']; Mysql_query ("insert into 'Table' ('column') VALUES ('". $ unsafe_variable ."')");

Because the user input may be like this:

The code is as follows:
Value '); drop table table ;--

The SQL query will be changed to the following:

The code is as follows:
Insert into 'Table' ('column') VALUES ('value'); DROP table TABLE ;--')

What effective methods should be taken to prevent SQL injection?



Best answer (from Theo ):

Use pre-processing statements and parameterized queries. The pre-processing statements and parameters are sent to the database server for resolution. the parameters are processed as common characters. This method prevents attackers from injecting malicious SQL statements. You have two options to implement this method:

1. use PDO:

The code is as follows:
$ Stmt = $ pdo-> prepare ('select * FROM employees WHERE name =: name '); $ Stmt-> execute (array ('name' => $ name )); Foreach ($ stmt as $ row ){ // Do something with $ row }

2. use mysqli:

The code is as follows:
$ Stmt = $ dbConnection-> prepare ('select * FROM employees WHERE name =? '); $ Stmt-> bind_param ('s ', $ name ); $ Stmt-> execute (); $ Result = $ stmt-> get_result (); While ($ row = $ result-> fetch_assoc ()){ // Do something with $ row }

PDO

Note that the use of PDO by default does not allow the MySQL database to execute the true pre-processing statement (the reason is described below ). To solve this problem, you should disable PDO to simulate preprocessing statements. An example of using PDO to create a database connection is as follows:

$ DbConnection = new PDO ('MySQL: dbname = dbtest; host = 127.0.0.1; charset = utf8', 'user', 'pass ');
$ DbConnection-> setAttribute (PDO: ATTR_EMULATE_PREPARES, false );
$ DbConnection-> setAttribute (PDO: ATTR_ERRMODE, PDO: ERRMODE_EXCEPTION );

In the preceding example, the error reporting mode (ATTR_ERRMODE) is not required, but we recommend that you add it. In this way, when a Fatal Error occurs, the script does not stop running, but gives the programmer a chance to capture PDOExceptions to properly handle the Error. However, the first setAttribute () call is required. it prohibits PDO from simulating preprocessing statements. Instead, it uses a true preprocessing statement, that is, MySQL executes preprocessing statements. This ensures that the statements and parameters have not been processed by PHP before being sent to MySQL, which prevents attackers from injecting malicious SQL statements. For the reason, refer to this blog post: analysis of the PDO anti-injection principle and precautions for using PDO. Note that in earlier versions of PHP (<5.3.6), you cannot set the character set on the DSN of the PDO constructor. for details, refer to: silently ignored the charset parameter.

Analysis

What happens when you send SQL statements to the database server for preprocessing and parsing? By specifying a placeholder (? Or name: name) in the preceding example to tell the database engine where you want to filter. When you call execute, the pre-processing statement will be combined with the parameter value you specified. The key point is here: the parameter value is combined with the parsed SQL statement, rather than the SQL string. SQL injection contains malicious strings when constructing SQL statements by triggering scripts. Therefore, separating SQL statements from parameters prevents the risk of SQL injection. Any parameter value you send will be treated as a normal string and will not be parsed by the database server. Return to the example above. if the value of the $ name variable is 'Sara'; delete from employees, the actual query is to find that the value of the name field in employees is 'Sara '; DELETE records FROM employees. Another advantage of using pre-processing statements is that if you execute the same statement multiple times in the same database connection session, it will be parsed only once, which improves the execution speed. If you want to know how to insert data, see the following example (using PDO ):

The code is as follows:
$ PreparedStatement = $ db-> prepare ('Insert INTO table (column) VALUES (: column )'); $ PreparedStatement-> execute (array ('column' => $ unsafeValue ));

Explain problem description: if the data entered by the user is inserted into an SQL query statement without processing, the application may suffer from SQL injection...