How to prevent SQL injection in PHP? _PHP Tutorial

Problem description:

If the data entered by the user is inserted into a SQL query statement without processing, then the application will likely be subject to SQL injection attacks, as in the following example:

<ol class="dp-sql"><li class="alt"><span><span>$unsafe_variable = $_POST[</span><span class="string">'user_input'</span><span>];   </span></span></li><li><span>   </span></li><li class="alt"><span>mysql_query(</span><span class="string">"INSERT INTO `table` (`column`) VALUES ('"</span><span> . $unsafe_variable . </span><span class="string">"')"</span><span>);   </span></li></ol>

Because the user’s input may look like this:

<ol class="dp-sql"><li class="alt"><span><span>value'); </span><span class="keyword">DROP</span><span> </span><span class="keyword">TABLE</span><span> </span><span class="keyword">table</span><span>;</span><span class="comment">--</span><span> </span></span></li></ol>

Then the SQL query will become as follows:

<ol class="dp-sql"><li class="alt"><span><span class="keyword">INSERT</span><span> </span><span class="keyword">INTO</span><span> `</span><span class="keyword">table</span><span>` (`</span><span class="keyword">column</span><span>`) </span><span class="keyword">VALUES</span><span>(</span><span class="string">'value'</span><span>); </span><span class="keyword">DROP</span><span> </span><span class="keyword">TABLE</span><span> </span><span class="keyword">table</span><span>;</span><span class="comment">--')</span><span> </span></span></li></ol>

What effective methods should be taken to prevent SQL injection?

The best answer comes from Theo):

Use prepared statements and parameterized queries. The prepared statements and parameters are sent to the database server for parsing respectively, and the parameters will be treated as ordinary characters. This approach prevents attackers from injecting malicious SQL. You have two options to implement this method:

1. Use PDO:

<ol class="dp-sql"><li class="alt"><span><span>$stmt = $pdo-></span><span class="keyword">prepare</span><span>(</span><span class="string">'SELECT * FROM employees WHERE name = :name'</span><span>);  </span></span></li><li><span>   </span></li><li class="alt"><span>$stmt-></span><span class="keyword">execute</span><span>(array(</span><span class="string">'name'</span><span> => $</span><span class="keyword">name</span><span>));  </span></li><li><span>   </span></li><li class="alt"><span>foreach ($stmt </span><span class="keyword">as</span><span> $row) {  </span></li><li><span>    // do something </span><span class="keyword">with</span><span> $row  </span></li><li class="alt"><span>} </span></li></ol>

2. Use mysqli:

<ol class="dp-sql"><li class="alt"><span><span>$stmt = $dbConnection-></span><span class="keyword">prepare</span><span>(</span><span class="string">'SELECT * FROM employees WHERE name = ?'</span><span>);  </span></span></li><li><span>$stmt->bind_param(</span><span class="string">'s'</span><span>, $</span><span class="keyword">name</span><span>);  </span></li><li class="alt"><span>   </span></li><li><span>$stmt-></span><span class="keyword">execute</span><span>();  </span></li><li class="alt"><span>   </span></li><li><span>$result = $stmt->get_result();  </span></li><li class="alt"><span>while ($row = $result->fetch_assoc()) {  </span></li><li><span>    // do something </span><span class="keyword">with</span><span> $row  </span></li><li class="alt"><span>} </span></li></ol>

PDO

Note that using PDO by default does not allow the MySQL database to execute real prepared statements (see below). To solve this problem, you should disable PDO emulation of prepared statements. An example of correctly using PDO to create a database connection is as follows:

<ol class="dp-sql"><li class="alt"><span><span>$dbConnection = new PDO(</span><span class="string">'mysql:dbname=dbtest;host=127.0.0.1;charset=utf8'</span><span>, </span><span class="string">'user'</span><span>, </span><span class="string">'pass'</span><span>);  </span></span></li><li><span>   </span></li><li class="alt"><span>$dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, </span><span class="keyword">false</span><span>);  </span></li><li><span>$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); </span></li></ol>

In the above example, the error reporting mode (ATTR_ERRMODE) is not necessary, but it is recommended to add it. In this way, when a fatal error (Fatal Error) occurs, the script will not stop running, but gives the programmer an opportunity to catch PDOExceptions so that the error can be properly handled. However, the first setAttribute() call is required, which disables PDO from simulating prepared statements and uses real prepared statements, i.e. MySQL executes prepared statements. This ensures that statements and parameters have not been processed by PHP before being sent to MySQL, which will prevent attackers from injecting malicious SQL. To understand the reason, please refer to this blog post: Analysis of PDO anti-injection principle and precautions for using PDO . Note that in older versions of PHP (<5.3.6), you cannot set the charset through the DSN in the PDO constructor, see: silently ignored the charset parameter.

Analysis

What happens when you send a SQL statement to the database server for preprocessing and parsing? Tell the database engine where you want to filter by specifying the placeholder a ? or a :name as in the example above). When you call execute, the prepared statement will be combined with the parameter values ​​you specify. The key point is here: the parameter value is combined with the parsed SQL statement, not the SQL string. SQL injection is triggered by scripts that contain malicious strings when constructing SQL statements. So, by separating SQL statements and parameters, you prevent the risk of SQL injection. Any parameter values ​​you send will be treated as ordinary strings and will not be parsed by the database server. Going back to the above example, if the value of the $name variable is 'Sarah'; DELETE FROM employees, then the actual query will be to find records in employees where the name field value is 'Sarah'; DELETE FROM employees. Another benefit of using prepared statements is that if you execute the same statement many times in the same database connection session, it will only be parsed once, which can improve execution speed a bit. If you want to ask how to do insertion, please see the following example using PDO):

<ol class="dp-sql"><li class="alt"><span><span>$preparedStatement = $db-></span><span class="keyword">prepare</span><span>(</span><span class="string">'INSERT INTO table (column) VALUES (:column)'</span><span>);  </span></span></li><li><span>   </span></li><li class="alt"><span>$preparedStatement-></span><span class="keyword">execute</span><span>(array(</span><span class="string">'column'</span><span> => $unsafeValue)); </span></li></ol>

Translation link: http://blog.jobbole.com/67875/

http://www.bkjia.com/PHPjc/778655.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/778655.htmlTechArticleProblem description: If the data entered by the user is inserted into a SQL query statement without processing, then the application It is very likely to be subject to SQL injection attacks, as in the following example: $...