Detailed explanation of preventing SQL injection in PHP, detailed explanation of phpsql injection_PHP tutorial

Detailed explanation of methods to prevent SQL injection in PHP, detailed explanation of phpsql injection

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

Copy code The code is as follows:

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

Because the user’s input may be like this:

Copy code The code is as follows:

value'); DROP TABLE table;--

Then the SQL query will become as follows:

Copy code The code is as follows:

INSERT INTO `table` (`column`) VALUES('value'); DROP TABLE table;--')

What effective methods should be adopted 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:

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

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

Copy code The 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 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 (

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

Copy code The code is as follows:

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

http://www.bkjia.com/PHPjc/932482.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/932482.htmlTechArticleDetailed explanation of methods to prevent SQL injection in PHP, detailed explanation of phpsql injection Problem description: If the data entered by the user is not processed In the case of inserting a SQL query statement, the application will most likely...