How to prevent SQL injection in PHP?

Question

If user input is inserted into a SQL query without modification, the application is vulnerable to SQL injection, as 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;--, the query becomes:

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

What can be done to 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 the following.

<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("server", "username", "password", "database_name");

$variable = $_POST["user-input"];
$stmt = $mysqli->prepare("INSERT INTO table (column) VALUES (?)");
// "s" means the database expects a string
$stmt->bind_param("s", $variable);
$stmt->execute();

The key function you need to read is mysqli::prepare.

Also, as others have suggested, you may find using something like PDO< 之类的东西来提升抽象层很有用/更容易/a>.

Please note that the case you are asking about is fairly simple, more complex cases may require more sophisticated methods. in particular:

• If you want to change the SQL structure based on user input, parameterized queries will not help, and mysql_real_escape_string does not contain the required escapes. In this case, you'd be better off passing the user's input through a whitelist to ensure 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 is still data and > never Will be interpreted as a command by the SQL parser. It is possible to create SQL statements with properly formatted data portions, but if you don't understand the details at all, you should always use prepared statements and parameterized queries. is a SQL statement sent to and parsed by the database server separately from any parameters. This way it is impossible for an attacker to inject malicious SQL.

You basically have two options to achieve this:

1. Using

   PDO (for any supported database driver):

   $stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name');
   $stmt->execute([ 'name' => $name ]);
   
   foreach ($stmt as $row) {
       // Do something with $row
   }
   

2. Using

   MySQLi (for MySQL):
   Starting with PHP 8.2, we can use execute_query()< code> to prepare, bind parameters and execute SQL statements in one method:

   $result = $db->execute_query('SELECT * FROM employees WHERE name = ?', [$name]);
    while ($row = $result->fetch_assoc()) {
        // Do something with $row
    }
   

   Up to PHP8.1:

    $stmt = $db->prepare('SELECT * FROM employees WHERE name = ?');
    $stmt->bind_param('s', $name); // 's' specifies the variable type => 'string'
    $stmt->execute();
    $result = $stmt->get_result();
    while ($row = $result->fetch_assoc()) {
        // Do something with $row
    }
   

If you are connecting to a database other than MySQL, you can refer to the second driver-specific option (for example,

pg_prepare() and pg_execute() for PostgreSQL) . PDO is a universal option. < /code>

---

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 must disable simulation of prepared statements. An example of creating a connection using PDO is:

$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 necessary,

but it is recommended to add it . This way, PDO will notify you of all MySQL errors by throwing PDOException.

However,

forcing is the first setAttribute() line, which tells PDO to disable simulated prepared statements and use real prepared statement statements . This ensures that statements and values ​​are not parsed by PHP before being sent to the MySQL server (giving a would-be attacker no chance to inject malicious SQL).

While you can set the

character set in the options of the constructor, it is important to note that "older" versions of PHP (prior to 5.3.6) silently ignore the Character set parameters .

Mysqli

For mysqli we have to follow the same routine:

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); // error reporting
$dbConnection = new mysqli('127.0.0.1', 'username', 'password', 'test');
$dbConnection->set_charset('utf8mb4'); // charset

---

illustrate

The SQL statements you pass to

prepare are parsed and compiled by the database server. You tell the database engine where you want to filter by specifying parameters (? or named parameters, like :name in the example above). Then, when you call execute, the prepared statement is combined with the parameter values ​​you specified.

What matters here is that the parameter value is combined with the compiled statement, not the SQL string. SQL injection works by tricking a script into containing a malicious string when it creates the SQL to be sent to the database. So by sending the actual SQL separately from the parameters, you limit the risk of ending up with something unexpected.

Any parameters you send when using prepared statements will be treated as strings (although the database engine may do some optimizations, so parameters may of course end up being treated as numbers as well). In the example above, if the $name variable contains 'Sarah'; DELETE FROMEmployees the result is simply the search string "'Sarah'; DELETE FROMEmployees", and You will not end up with an empty table.

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

Oh, since you asked how to do the insert, here's an example (using PDO):

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

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

---

Can prepared statements be used for dynamic queries?

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

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

// Value whitelist
// $dir can only be 'DESC', otherwise it will be 'ASC'
if (empty($dir) || $dir !== 'DESC') {
   $dir = 'ASC';
}