How can I avoid SQL injection in PHP?

Question:

If user input is processed without being converted to an SQL query, SQL injection can occur in application code, such as 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 character string, for example: value'); DROP TABLE table;-- , and the query will look like this:

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

What can be done to prevent this from happening?

Translation of the question “ How can I prevent SQL-injection in PHP? @Andrew G. Johnson .

Answer:

Use prepared statements and parameterized queries. There are SQL statements that are sent and processed by the database server separately from parameters. This prevents an attacker from injecting malicious SQL.

In general, you have two ways to achieve your goal.

  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 you are connecting to is not managed by MySQL, you can use the second option (depending on the driver you are using, for example pg_prepare() and pg_execute() for PostgreSQL); the universal option is PDO.

Correct connection establishment

Note that when using PDO to establish a connection to a MySQL database, actual prepared statements are not used by default . To fix the situation, you will need to disable prepared statement emulation. An example of establishing a connection 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 above example, the error handling mode is not necessary, but we strongly 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 the error (one or more) that throws a PDOException .

However, the prerequisite here is the setAttribute() , which tells PDO to discard the emulated prepared statements and use real prepared statements. This ensures that both the statement and the values ​​are not parsed by PHP before being sent to the MySQL server (thus preventing an attacker from injecting malicious code).

You can of course choose the charset in the constructor options, but it's important to remember that "older" PHP versions (<5.3.6) simply ignored this parameter in the DSN.

Explanation

What happens is: The SQL statement you pass to "prepare" is parsed and compiled by the database server. By specifying parameters ( ? Or a named parameter of type :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, however, that the parameter values ​​are combined with the compiled statement and not with the SQL string. SQL Injection tricks the script by sending malicious SQL strings to the database. Therefore, by sending SQL separately from parameters, you reduce the risk of getting unexpected and unwanted results. Any parameters passed using a prepared statement are treated as strings (although of course the database engine can do some optimizations and the parameters can eventually be converted to numeric format).

In the above example, if the $name variable contains 'Sarah'; DELETE FROM employees will search for the row "'Sarah'; DELETE FROM employees" , not an empty table .

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

Yes, and since you asked about how to handle the injection code, here's an example (using PDO):

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

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

Can prepared statements be used for dynamic queries?

Although you can use prepared parameters as query parameters, the structure of a dynamic query cannot be parameterized – as are some of the specifics of the query.

For these special scenarios, it is best to use a whitelist filter that 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 .

Scroll to Top