Detailed introduction to preprocessing statements for operating databases in PHP (with code)

This article will give you a detailed introduction to the preprocessing statements for operating databases in PHP (with code). It has certain reference value. Friends in need can refer to it. I hope it will be helpful to everyone.

Preprocessing statements for operating databases in PHP

The content of today’s article is actually very basic, but in In modern development, everyone uses frameworks, and few people encapsulate or often write the underlying database operation code themselves. So this time we will review the content of prepared statements in related extensions in the database.

What are prepared statements?

A prepared statement can be thought of as a compiled template of the SQL statement you want to run, which can be controlled using variable parameters. Prepared statements can bring two major benefits:

• 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.

• Parameters provided to prepared statements do not need to be enclosed in quotes, the driver will handle them automatically. If your application uses only prepared statements, you can be sure that SQL injection will not occur. (However, if other parts of the query are constructed from unescaped input, there is still a risk of SQL injection).

The above content is excerpted from the official documentation, but in fact, the most intuitive benefit that prepared statements bring us is that it can effectively prevent SQL injection. Regarding the content of SQL injection, we will study it in depth when learning MySQL in the future, so I won’t introduce it too much here. Anyway, prepared statements can complete this work.

PDO operates prepared statements

Among PHP extensions, PDO is already the mainstream core database extension library, and naturally it also has very comprehensive support for prepared statements. of.

$pdo = new PDO('mysql:host=localhost;port=3306;dbname=blog_test', 'root', '');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// :xxx 占位符
$stmt = $pdo->prepare("insert into zyblog_test_user (username, password, salt) values (:username, :password, :salt)");
$stmt->bindParam(':username', $username);
$stmt->bindParam(':password', $password);
$stmt->bindParam(':salt', $salt);

$username = 'one';
$password = '123123';
$salt = 'aaa';
$stmt->execute();

$username = 'two';
$password = '123123';
$salt = 'bbb';
$stmt->execute();

In the code, we use the prepare() method to define prepared statements, which returns a PDOStatement object. Use placeholder symbols like :xxx within prepared statements, and bind variables to these placeholders externally using the bindParam() method of the PDOStatement object. Finally, execute() is used to actually execute the SQL statement.

From this code, we can see the two major advantages of prepared statements. The first is placeholders. After using placeholders, we do not need to write single quotes in SQL statements. Single quotes are often the main source of SQL injection vulnerabilities. The bindParam() method automatically converts the type of bound data. Of course, the bindParam() method can also specify the bound data type in the optional parameters, which can make our code safer. You can check the relevant documents.

Another advantage is the ability of templates. We only define one PDOStatement object, and then by changing the content of the data, we can use the execute() method multiple times to execute the prepared statement.

There is another way to write placeholders, which is to use a question mark as a placeholder symbol. In this case, the key name of the bindParam() method will use a numeric subscript. It should be noted here that the numerical subscripts start from 1.

// ? 占位符
$stmt = $pdo->prepare("insert into zyblog_test_user (username, password, salt) values (?, ?, ?)");
$stmt->bindParam(1, $username);
$stmt->bindParam(2, $password);
$stmt->bindParam(3, $salt);

$username = 'three';
$password = '123123';
$salt = 'ccc';
$stmt->execute();

In our query, we can also easily use the function of prepared statements to query data. Here, we use execute() directly to pass parameters for the placeholder.

// 查询获取数据
$stmt = $pdo->prepare("select * from zyblog_test_user where username = :username");

$stmt->execute(['username'=>'one']);

while($row = $stmt->fetch()){
    print_r($row);
}

mysqli operation prepared statements

Although the mainstream is PDO, and most frameworks also use PDO, but we are writing scripts or need to test quickly When it comes to some functions, I still use mysqli to develop quickly. Of course, mysqli also supports prepared statement-related functions.

// mysqli 预处理
$conn = new mysqli('127.0.0.1', 'root', '', 'blog_test');
$username = 'one';
$stmt = $conn->prepare("select username from zyblog_test_user where username = ?");
$stmt->bind_param("s", $username);

$stmt->execute();

echo $stmt->bind_result($unames);

var_dump($unames);

while ($stmt->fetch()) {
    printf("%s\n", $unames);
}

It can be seen that in addition to the different method names of mysqli, the key names of the binding parameters are not exactly the same. Here we use the question mark placeholder. In the bind_param() method, we use s To indicate the symbol position, if there are multiple parameters, it should be written as sss... like this.

Summary

The ability of prepared statements has been encapsulated for us in the current framework. In fact, we don’t need to care too much, just like using it in Laravel When DB::select() performs database operations, we can see the application of prepared statements.
You can check the select() method in vendor/laravel/framework/src/Illuminate/Database/Connection.php by yourself.

Test code:

https://github.com/zhangyue0503/dev-blog/blob/master/php/202008/source/PHP%E4%B8%AD%E6%93%8D%E4%BD%9C%E6%95%B0%E6%8D%AE%E5%BA%93%E7%9A%84%E9%A2%84%E5%A4%84%E7%90%86%E8%AF%AD%E5%8F%A5.php

Recommended learning: php video tutorial

The above is the detailed content of Detailed introduction to preprocessing statements for operating databases in PHP (with code). For more information, please follow other related articles on the PHP Chinese website!