The most practical and effective way to prevent SQL injection in PHP_PHP tutorial

The most practical and effective way to prevent SQL injection in PHP

Problem description:

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

The code is as follows

$unsafe_variable = $_POST['user_input'];

代码如下
$unsafe_variable = $_POST['user_input']; mysql_query("INSERT INTO `table` (`column`) VALUES ('" . $unsafe_variable . "')");

mysql_query("INSERT INTO `table` (`column`) VALUES ('" . $unsafe_variable . "')");

Because the user's input may be like this:

代码如下
value'); DROP TABLE table;--

Then the SQL query will become 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 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:

代码如下
$stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name'); $stmt->execute(array('name' => $name)); foreach ($stmt as $row) { // do something with $row }

The code is as follows

$stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name');

$stmt->execute(array('name' => $name));

代码如下
$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 }

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 using PDO by default does not cause the MySQL database to execute actual prepared statements (see below for reasons). 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:

$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 above example, the error reporting mode (ATTR_ERRMODE) is not required, 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 (
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 a 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 look at the following example (using PDO):

The code is as follows

代码如下
$preparedStatement = $db->prepare('INSERT INTO table (column) VALUES (:column)'); $preparedStatement->execute(array('column' => $unsafeValue));

$preparedStatement = $db->prepare('INSERT INTO table (column) VALUES (:column)'); $preparedStatement->execute(array('column' => $unsafeValue));

http://www.bkjia.com/PHPjc/906531.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/906531.htmlTechArticle

The most practical and effective way to prevent SQL injection in PHP Problem description: If the data entered by the user is not processed Inserted into a SQL query statement, the application will likely suffer SQL injection...