This article analyzes how to prevent SQL injection in PHP with examples. Share it with everyone for your reference. The specific analysis is as follows:
1. 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:
Because the user’s input may look like this:
Then the SQL query will become as follows:
2. Solution analysis:
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:
2. Use mysqli:
3. PDO
Note that using PDO by default does not allow the MySQL database to execute real 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:
4. 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):
I hope this article will be helpful to everyone’s PHP programming design.
I translated the questions and answers with the most votes. Question: If user input can be directly inserted into the SQL statement, then the application is vulnerable to SQL injection attacks. For example: $unsafe_variable = $_POST['user_input']; mysqli_query("INSERT INTO table (column) VALUES ('" . $unsafe_variable . "')"); Users can enter values such as: value'); DROP TABLE table;-- , and the SQL statement becomes like this: INSERT INTO table (column) VALUES('value') ; DROP TABLE table;--') (Translator's Note: The result of this is to delete the table) What can we do to prevent this situation? Answer: Use prepared statements and parameterized queries. These SQL statements are sent to the database server, and their parameters are all parsed individually. Using this method, it is impossible for an attacker to inject malicious SQL. 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. Using 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 It should be noted that when using PDO to access the MySQL database, the real prepared statements are not used by default. To solve this problem, you need to disable 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 above example, the error reporting mode is not mandatory or required, but it is recommended that you add it. In this way, the script will not terminate with a fatal error when something goes wrong, but will throw PDO Exceptions, which gives the developer the opportunity to catch the error. However, the setAttribute() in the first line is mandatory, it makes PDO disable simulated prepared... The rest of the full text >>
If the user input is a query that is inserted directly into a SQL statement, the application will be vulnerable to SQL injection, such as the following example: $unsafe_variable = $_POST['user_input']; mysql_query("INSERT INTO table ( column) VALUES ('" . $unsafe_variable . "')"); This is because the user can enter something like VALUE "); DROP TABLE table; - , making the query become: Use prepared statements and parameterized queries. SQL statements with any parameters will be sent to the database server and parsed! It is impossible for an attacker to maliciously inject SQL! 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 real prepared statements are not used by default when using PDO! To solve this problem, you must disable emulation of prepared statements. An example of using PDO to create a 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);The error mode ERRMODE is not strictly required in the above example, but it is recommended to add it. This method does not stop the script when a fatal error occurs. And give the developer a chance to catch any errors (when PDOException is thrown). The setAttribute() line is mandatory, it tells PDO to disable emulated prepared statements and use real prepared statements. This ensures that statements and values are not parsed by PHP before being sent to the MySQL database server (an attacker has no chance of injecting malicious SQL). Of course you can set the character set parameter in the constructor options, paying special attention to 'old' PHP versions ( 5.3.6) will ignore the character set parameter in the DSN. The most important thing here is that the parameter value is combined with a precompiled statement, not with a SQL string. The working principle of SQL injection is that the SQL script created by deception includes a malicious string... The rest of the full text>>
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
