If user input processing takes place without conversion to an SQL query, SQL code can be injected into the application code, for example, as in the following example:

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

The reason for this is that the user can enter any string of characters, for example: value'); DROP TABLE table;-- value'); DROP TABLE table;-- , and the query will take the following form:

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

What can be done to prevent this from happening?

How can I prevent SQL-injection in PHP? » @Andrew G. Johnson .

1 answer 1

Use prepared statements and parameterized queries. There are SQL expressions that are sent and processed on the database server separately from the parameters. Thus, the attacker will not be able to inject malicious SQL code.

In general, you have two ways to achieve it.

  1. Use PDO (for any supported database driver):
 $stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name'); $stmt->execute(array('name' => $name)); foreach ($stmt as $row) { // do something with $row }
  1. Use MySQLi (for MySQL):
 $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 }

If the database to which you are connecting is not managed by MySQL, you can use the second option (depending on the driver used, for example, pg_prepare() and pg_execute() for PostgreSQL); universal version - PDO.

Correct connection establishment

Note that when using PDO to establish a connection to the MySQL database, real prepared statements are not used by default . To remedy the situation, you will need to turn off the emulation of trained operators. Connection establishment example using PDO:

 $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 example above, the error handling mode is not necessary, but we highly recommend using it . In this case, if a problem occurs, the script will not stop at Fatal Error . It also gives the developer a chance to “catch” an error (one or more) that causes a PDOException exception.

However, the prerequisite here is the setAttribute() string, which “tells” the PDO to discard the emulated prepared statements and use the real prepared statements. This ensures that both the operator and the values ​​are not analyzed by PHP before being sent to the MySQL server (which prevents the attacker from injecting malicious code).

Of course, you can select the charset in the constructor options, but it is important to remember that the “older” versions of PHP (<5.3.6) simply ignored this parameter in the DSN.

Explanation

The following happens: The SQL statement that you submit to “prepare” is analyzed and compiled by the database server. By specifying parameters ( ? Or a named type parameter :name in the above example), you tell the database engine where you want to filter the data. After that, when you call execute , the prepared statement is combined with the parameter values ​​you specified.

It is important that the values ​​of the parameters are combined with the compiled statement, and not with the SQL string. The SQL injection “tricks” the script by sending malicious SQL strings to the database. Therefore, sending SQL separately from the parameters reduces the risk of getting unexpected and undesirable results. Any parameters sent using a prepared operator are perceived as strings (although, of course, the database engine can produce certain optimizations and parameters, after all, can be converted to a numerical format).

In the above example, if the variable $name contains 'Sarah'; DELETE FROM employees 'Sarah'; DELETE FROM employees result is a search for the string "'Sarah'; DELETE FROM employees" and not an empty table .

Another advantage of using prepared statements is that if the same statement is executed many times during one session, it will be analyzed and compiled only once, which will also give you speed optimization.

Yes, and since you asked how to deal with the injection code, here is an example (PDO is used):

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

Is it possible to use prepared statements for dynamic queries?

Although you can use prepared parameters as query parameters, the structure of a dynamic query cannot be parameterized - as well as some features of the query.

For such special scenarios, it is best to use a white list filter, which will limit the range of possible values.

 // Value whitelist // $dir can only be 'DESC' or 'ASC' $dir = !empty($direction) ? 'DESC' : 'ASC';

Translation of the answer “ How can I prevent SQL-injection in PHP? " @Theo .