How to prevent SQL injection attacks in PHP?

Question

If user input is inserted into a SQL query without modification, the application becomes vulnerable to SQL injection attacks, as shown in the following example:

<code>$unsafe_variable = $_POST['user_input'];

mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')");
</code>

This is because the user can enter something like value'); DROP TABLE table;-- and the query will become:

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

How can I prevent this from happening?

Answer 1

To use parameterized queries, you need to use Mysqli or PDO. To rewrite your example using mysqli, we would need code similar to the following.

<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("服务器", "用户名", "密码", "数据库名称");

$variable = $_POST["user-input"];
$stmt = $mysqli->prepare("INSERT INTO 表名 (列名) VALUES (?)");
// "s"表示数据库期望一个字符串
$stmt->bind_param("s", $variable);
$stmt->execute();

The key function you may want to read about is mysqli::prepare.

Also, as others have suggested, you may find it more useful/easier to use a higher level abstraction layer like PDO.

Please note that the situation you mention is quite simple, more complex situations may require more sophisticated methods. in particular:

• If you want to change the structure of your SQL based on user input, parameterized queries won't help you, and the required escaping is not included in mysql_real_escape_string. In this case, it's better to pass the user's input through a whitelist to ensure that only "safe" values ​​are allowed through.

Answer 2

No matter which database you use, the correct way to avoid SQL injection attacks is to separate the data from the SQL so that the data remains in the form of data and can never be used by SQL The parser interprets as a command. It is possible to create SQL statements with properly formatted data parts, but if you don't fully understand the details, you should always use prepared statements and parameterized queries . These are SQL statements that are sent separately from any parameters and analyzed by the database server. This way, attackers cannot inject malicious SQL.

There are basically two ways to achieve this:

1. Using PDO (works with any supported database driver):

   $stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name');
   $stmt->execute([ 'name' => $name ]);
   
   foreach ($stmt as $row) {
       // 对$row进行操作
   }
   

2. UsingMySQLi (for MySQL):
   Since PHP 8.2 , we can use the execute_query() method to prepare, bind parameters and execute SQL statements:

   $result = $db->execute_query('SELECT * FROM employees WHERE name = ?', [$name]);
    while ($row = $result->fetch_assoc()) {
        // 对$row进行操作
    }
   

   Before PHP8.1:

    $stmt = $db->prepare('SELECT * FROM employees WHERE name = ?');
    $stmt->bind_param('s', $name); // 's'指定变量类型 => 'string'
    $stmt->execute();
    $result = $stmt->get_result();
    while ($row = $result->fetch_assoc()) {
        // 对$row进行操作
    }
   

If you are connecting to a database other than MySQL, you can refer to the second driver-specific option (for example, for PostgreSQL, you can use pg_prepare() and pg_execute()). PDO is a universal option.

---

Set up the connection correctly

PDO

Please note that when using PDO to access a MySQL database, real prepared statements are not used by default . To resolve this issue, you need to disable simulation of prepared statements. The following is an example of creating a connection using PDO:

$dbConnection = new PDO('mysql:dbname=dbtest;host=127.0.0.1;charset=utf8mb4', 'user', 'password');

$dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

In the example above, error mode is not strictly required, but it is recommended to add it . This way PDO will notify you of all MySQL errors by throwing PDOException.

However, what must is the first line setAttribute(), which tells PDO to disable simulated prepared statements and use real prepared statements. This ensures that statements and values ​​are not parsed by PHP before being sent to the MySQL server (so potential attackers cannot inject malicious SQL).

While you can set the charset in the constructor's options, it is important to note that "older" versions of PHP (prior to 5.3.6) silently ignore the charset argument in the DSN .

Mysqli

For mysqli, we need to follow the same routine:

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); // 错误报告
$dbConnection = new mysqli('127.0.0.1', 'username', 'password', 'test');
$dbConnection->set_charset('utf8mb4'); // 字符集

---

explain

The SQL statements you pass to prepare are parsed and compiled by the database server. By specifying a parameter (in the example above, this could be ? or a named parameter like :name), you tell the database engine where you want to filter. Then, when you call execute, the prepared statement is combined with the specified parameter values.

What is important here is the combination of parameter values ​​and compiled statements, not the combination with SQL strings. SQL injection works by tricking a script into including a malicious string when creating the SQL to be sent to the database. Therefore, by sending the actual SQL separately from the parameters, you limit the risk of unexpected results.

Any parameters sent using prepared statements will be treated as strings (although the database engine may perform some optimizations on parameters, so parameters may end up being numbers). In the example above, if the $name variable contains 'Sarah'; DELETE FROM employees, the result will only be the search string "'Sarah'; DELETE FROM employees" , you will not get an empty table.

Another benefit of using prepared statements is that if the same statement is executed multiple times in the same session, it will only be parsed and compiled once, thus improving some speed.

Oh, since you asked how to operate on inserts, here is an example (using PDO):

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

$preparedStatement->execute([ 'column' => $unsafeValue ]);

---

Are prepared statements suitable for dynamic queries?

While you can still use prepared statements for query parameters, the structure of the dynamic query itself cannot be parameterized, and certain query functions cannot be parameterized.

For these specific scenarios, best practice is to use a whitelist filter to limit the possible values.

// 值白名单
// $dir只能是'DESC'，否则将为'ASC'
if (empty($dir) || $dir !== 'DESC') {
   $dir = 'ASC';
}