How Can I Prevent SQL Injection in PHP Applications?

prevent SQL injection of SQL in PHP

SQL injection is a vulnerability. This vulnerability will appear when an unmodified user enters the SQL query error. This may lead to an attacker's execution of any SQL code, which will cause catastrophic consequences to the application.

In order to prevent SQL injection, it is important to separate the data from SQL to ensure that the data is always kept as data, and it will never be interpreted by the SQL parser as a command. This can be implemented by using pre -processing sentences with parameters. The pre -processing statement will develop SQL query from any parameter to the database server for analysis. In this way, an attacker cannot inject malicious SQL.

There are two ways to achieve:

Use PDO (suitable for any support database driver)

Use mysqli (suitable for MySQL)

<code class="language-php">$stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name');
$stmt->execute(['name' => $name]);

foreach ($stmt as $row) {
    // 处理 $row
}</code>

• PHP 8.2:

PHP 8.1 and lower versions:

<code class="language-php">$result = $db->execute_query('SELECT * FROM employees WHERE name = ?', [$name]);
while ($row = $result->fetch_assoc()) {
    // 处理 $row
}</code>

If you are connected to a non -MySQL database, you can refer to the second option that is specific to the driver (for example,

and ) of PostgreSQL. PDO is a general choice.

The correct connection configuration

<code class="language-php">$stmt = $db->prepare('SELECT * FROM employees WHERE name = ?');
$stmt->bind_param('s', $name); // 's' 指定变量类型 -> 'string'
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    // 处理 $row
}</code>

pg_prepare() When using PDO to access the MySQL database, real pre -processing sentences will not be used by default. To solve this problem, the simulation of pre -processing statements is needed. Here are how to create a connection example with PDO: pg_execute()

For mysqli, the same operation needs to be performed:

Explanation

<code class="language-php">$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);</code>

The SQL query you passed to is parsed and compiled by the database server. By specifying parameters (whether

or naming parameters, such as the

example above), you tell the database kernel what you want to filter. Then, when you call , the pre -processing query is combined with the parameter values ​​you provided.

<code class="language-php">mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); // 错误报告
$dbConnection = new mysqli('127.0.0.1', 'username', 'password', 'test');
$dbConnection->set_charset('utf8mb4'); // 编码</code>

It is important that the parameter value is combined with the compiled query, not combined with the SQL string. SQL is injected into the script through the script, and the SQL that is sent to the database contains a malicious strings to work. Therefore, by sending the actual SQL and parameters, you can reduce the risk of obtaining accidents. Any parameters sent by pre -processing statements will be simply regarded as string (although the database core may perform some optimization, so the parameters may also be numbers). In the above example, if the variable

contains

, the result will only search the string prepare, and your watch will not be emptied. ? :name Another advantage of using the pre -processing statement is that if you perform the same query in the same session, only analyze and compile it once to improve the speed. execute

The above is the detailed content of How Can I Prevent SQL Injection in PHP Applications?. For more information, please follow other related articles on the PHP Chinese website!