How to Prevent SQL Injection Analysis in PHP and prevent SQL Injection in php

How to Prevent SQL Injection Analysis in PHP and prevent SQL Injection in php

This article describes how to prevent SQL Injection in PHP. Share it with you for your reference. The specific analysis is as follows:

I. 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:

Copy codeThe 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:

Copy codeCode: value'); drop table table ;--

The SQL query will be changed to the following:

Copy codeThe Code is as follows: insert into 'table' ('column ') VALUES ('value'); DROP table TABLE ;--')
Which effective methods should be taken to prevent SQL injection?

Ii. Solution Analysis:

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:

Copy codeThe 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:

Copy codeThe 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
}

3. 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:

Copy codeThe Code 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 more information about the causes, see the previous article: Analysis of PDO anti-injection principles 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.

Iv. 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 ):

Copy codeThe Code is as follows: $ preparedStatement = $ db-> prepare ('insert INTO table (column) VALUES (: column )');
 
$ PreparedStatement-> execute (array ('column' => $ unsafeValue ));

I hope this article will help you with PHP programming.

How to Prevent SQL Injection in PHP

I have translated the most frequently asked questions and answers. Question: If your input can be directly inserted into an SQL statement, this application is vulnerable to SQL injection attacks. For example: $ unsafe_variable = $ _ POST ['user _ input']; mysqli_query ("insert into table (column) VALUES ('". $ unsafe_variable. "')"); you can enter such as: value'); drop table table; --, the SQL statement becomes like this: INSERT INTO table (column) VALUES ('value'); drop table table? A: prepared statements (preprocessing statement) and parameterized query are used. These SQL statements are sent to the database server, and all their parameters are parsed independently. Using this method, attackers cannot inject malicious SQL statements. There are two main ways to achieve this: 1. use PDO: $ 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: $ 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 when using PDO to access the MySQL database, real prepared statements are not used by default. To solve this problem, you must disable the simulated prepared statements. The following is an example of using PDO to create a connection: $ 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 report mode is not mandatory or mandatory, but we recommend that you add it. In this way, the script will not be terminated by a fatal error when there is a problem, but will throw PDO Exceptions, which gives developers the opportunity to capture this error. However, the setAttribute () in the first line is mandatory, which enables PDO to disable simulated prepared ...... the remaining full text>

What is the best way to prevent SQL Injection in php?

If you input a query directly inserted into an SQL statement, the application will be vulnerable to SQL injection. For example: $ unsafe_variable = $ _ POST ['user _ input']; mysql_query ("insert into table (column) VALUES ('". $ unsafe_variable. "')"); this is because you can enter a VALUE similar to "); drop table;-To convert the query to: Use predefine statements and parameterized queries. SQL statements with any parameters will be sent to the database server and parsed! It is impossible for attackers to maliciously inject SQL statements! There are basically two options to achieve this goal: 1. use PDO (PHP Data Objects): $ 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: $ 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 (PHP Data Object) note that when you use PDO to access the MySQL database, the true pre-definition statements are not used by default! To solve this problem, you must disable the statements prepared by simulation. Example of using PDO to create a connection: $ 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 mode ERRMODE is not strictly required, but we recommend that you add it. This method does not stop when an error occurs and a fatal error occurs. And give developers the opportunity to capture any errors (when a PDOException exception is thrown ). The setAttribute () line is mandatory. It tells PDO to disable the simulation of the pre-Definition Statement and use the real pre-Definition Statement. This ensures that the statements and values are not parsed by PHP before being sent to the MySQL database server (attackers have no chance to inject malicious SQL statements ). of course, you can set character set parameters in the constructor options. Note that the 'old' PHP version (5.3.6) ignores character set parameters in DSN. The most important thing here is that this parameter value is combined with a pre-compiled statement, rather than an SQL string. the operating principle of SQL injection is that the SQL script created by deception includes malicious string sending ...... remaining full text>