How Can Prepared Statements and Parameterized Queries Prevent SQL Injection in PHP?

In the pHP, prevent SQL from injecting

If the user input is not processed correctly and inserts it into the SQL query, SQL injection vulnerabilities will be generated. In order to understand this risk, please consider the following example:

In this scene, if the user is maliciously entering the value of

$unsafe_variable = $_POST['user_input'];

mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')");

, the query will become:

value'); DROP TABLE table;--

This opened the door for the malicious attack on the database.

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

Relieve technology:

No matter which database is used to prevent SQL from being injected with recommended security practice is to separate data from SQL. This means ensuring that data is considered data and will never be interpreted by SQL parser as command. The most effective way to achieve this goal is to use pre -processing statements and parameterized queries.

Pre -processing statement and parameterization query:

Pre -processing statements involve sending SQL queries and parameters to the database server, allowing the database to process their combination. This ensures that the data will not be tried to prevent malicious SQL injection before transmission. Implementation options:

There are two main methods for achieving pre -processing statements:

PDO (PHP data object):

This is a common method that is suitable for all supporting database drivers. The following is the example of its usage:

1. mysqli (mysql improved extension):

   $stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name');
   $stmt->execute([ 'name' => $name ]);
   
   foreach ($stmt as $row) {
       // 处理行
   }

   For MySQL database, you can use MySQLI. Starting from PHP 8.2, you can use the
method to prepare, bind parameters and execute SQL statements in one step:

2. For PHP 8.1 and below versions:

   execute_query()

   If you use databases other than MySQL, there will be alternatives specific to the driver, such as

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

   and

   of PostgreSQL.

   $stmt = $db->prepare('SELECT * FROM employees WHERE name = ?');
   $stmt->bind_param('s', $name); // 's' 表示'字符串'变量类型
   $stmt->execute();
   $result = $stmt->get_result();
   while ($row = $result->fetch_assoc()) {
       // 处理行
   }

   The correct connection settings:

When establishing a connection, it is important to disable the simulation of pre -processing sentences to improve performance and security. pg_prepare() pg_execute() PDO connection:

mysqli connection:

Conclusion:

By achieving pre -processing statements and setting connections correctly, you can effectively prevent SQL from injecting attacks and ensure the security and integrity of database applications.

$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);

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