An article analyzing how PHP's preprocessed queries prevent SQL injection

This article brings you relevant knowledge about PHP. It mainly talks about what is a preprocessing statement? How does PHP's preprocessed query prevent SQL injection? Friends who are interested can take a look below. I hope it will be helpful to everyone.

#How does PHP's preprocessed query prevent SQL injection?

Currently the most effective way to prevent SQL injection is to use prepared statements and parameterized queries.

Take the most commonly used PHP PDO extension as an example.

Introduction to prepared statements in the official documentation

What are prepared statements?

Think of it as a compiled template of the SQL you want to run, which can be customized using variable parameters.

Two major benefits of prepared statements:

1; The query only needs to be parsed (or preprocessed) once, but can be executed multiple times with the same or different parameters. When a query is ready, the database analyzes, compiles, and optimizes the plan for executing the query. This process takes longer for complex queries and can significantly slow down your application if the same query needs to be repeated multiple times with different parameters. By using prepared statements, you can avoid repeated analysis/compile/optimization cycles. Simply put, prepared statements use fewer resources and therefore run faster.

2. The parameters provided to the prepared statement do not need to be enclosed in quotation marks, the driver will automatically handle them. If your application only uses prepared statements, you can ensure that SQL injection does not occur. (However, if other parts of the query are constructed from unescaped input, there is still a risk of SQL injection).

The characteristic of PDO is that when the driver does not support preprocessing, PDO will simulate processing. At this time, the preprocessing-parameterized query process is completed in the PDO simulator. The PDO simulator locally escapes the input parameters according to the character set specified in the DSN, and then splices them into a complete SQL statement and sends it to the MySQL server.

So, whether the PDO simulator can correctly escape input parameters is the key to intercepting SQL injection.

For PHP versions less than 5.3.6, DSN (Data Source Name) ignores the charset parameter by default. At this time, if you use PDO's local escape, it may still lead to SQL injection.

Therefore, the bottom layer of the Laravel framework will directly set PDO::ATTR_EMULATE_PREPARES=false to ensure that SQL statements and parameter values ​​will not be parsed by PHP before being sent to the MySQL server.

The implementation of PHP

// 查询
$calories = 150;
$colour = 'red';  
$sth = $dbh->prepare('SELECT name, colour, calories FROM fruit WHERE calories < :calories AND colour = :colour');  
$sth->bindValue(':calories', $calories, PDO::PARAM_INT);  
$sth->bindValue(':colour', $colour, PDO::PARAM_STR);  
$sth->execute();

// 插入，修改，删除
$preparedStmt = $db->prepare('INSERT INTO table (column) VALUES (:column)');
$preparedStmt->execute(array(':column' => $unsafeValue));

The underlying implementation of Laravel

// 查询的实现
public function select($query, $bindings = [], $useReadPdo = true)
{
    return $this->run($query, $bindings, function ($query, $bindings) use ($useReadPdo) {
        if ($this->pretending()) {
                return [];
        }
        $statement = $this->prepared(
                $this->getPdoForSelect($useReadPdo)->prepare($query)
        );
        $this->bindValues($statement, $this->prepareBindings($bindings));
        $statement->execute();
        return $statement->fetchAll();
    });
}
// 修改删除的实现
public function affectingStatement($query, $bindings = [])
{
    return $this->run($query, $bindings, function ($query, $bindings) {
        if ($this->pretending()) {
                return 0;
        }
        $statement = $this->getPdo()->prepare($query);
        $this->bindValues($statement, $this->prepareBindings($bindings));
        $statement->execute();
        $this->recordsHaveBeenModified(
                ($count = $statement->rowCount()) > 0
        );
        return $count;
    });
}

Recommended learning: "PHP Video Tutorial"

The above is the detailed content of An article analyzing how PHP's preprocessed queries prevent SQL injection. For more information, please follow other related articles on the PHP Chinese website!