The best solution in php to prevent SQL injection

  • 2020-05-30 19:45:51
  • OfStack

If the user enters a query directly inserted into an SQL statement, the application is vulnerable to an SQL injection, as shown in 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:

INSERT INTO table (column) VALUES('VALUE'); DROP TABLE table;'

How can we prevent this? Let's take a look at Theo's answers

Use prepared statements and parameterize queries. sql statements with any parameters are sent to the database server and parsed! It is impossible for an attacker to inject sql maliciously!

There are basically two options for achieving 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);
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    // do something with $row

PDO(PHP data object)

Note that the actual prepared statements are not used by default when using PDO to access the MySQL database! To solve this problem, you must disable the mock prepared statements. An example of creating a connection using PDO is as follows:

$dbConnection = new PDO('mysql:dbname=dbtest;host=;charset=utf8', 'user', 'pass');
$dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

The error mode ERRMODE in the above example is not strictly required, but it is recommended that it be added. This method script does not stop when running errors produce fatal errors. And give the developer a chance to catch any errors (when an PDOException exception is thrown).

That 1 line, setAttribute(), is mandatory, telling PDO to disable the mock prepared statement and use the real prepared statement. This ensures that statements and values are not parsed by PHP before they are sent to the MySQL database server (there is no opportunity for an attacker to inject malicious SQL).

You can, of course, set character set parameters in the constructor option, especially noting that the 'old' PHP version (5.3.6) ignores character set parameters in DSN.

Explanation (explanation)

What happens when the sql prepared statement you passed is parsed and compiled by the database server? By specifying the character (in the example above like a? Or, like :name) tells the database engine what you want to filter, and then calls execute to execute the combination of the prepared statement and the parameter values you specify.

Here the most important of all, the parameter value is and precompiled statements, rather than a SQL string. SQL injection works created through deception SQL scripts include malicious string sent to the database. Therefore, by sending the actual separate sql parameters, you can reduce risk. Using a prepared statement, you send any parameters, will only be regarded as a string (although the database engine would do 1 parameter optimization, may of course end up to digital). In the example above, If the variable $name contains 'sarah'; DELETE * FROM employees, the result will only be 1 search string "'sarah'; DELETE * FROM employees", you will not get an empty table.

Another advantage 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, giving you a little bit of speed.
Well, since you asked how to insert, here's an example (using PDO) :

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

Related articles: