Preprocessing statements for operating database in PHP

Time:2021-11-22

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

What are preprocessing statements?

The preprocessing statement can be regarded as a compiled template of the SQL statement you want to run, which can be controlled by variable parameters. Preprocessing statements can bring two benefits:

  • The query only needs to be parsed (or preprocessed) once, but can be executed multiple times with the same or different parameters. When the query is ready, the database analyzes, compiles, and optimizes the plan for executing the query. For complex queries, this process takes a long time. If you need to repeat the same query multiple times with different parameters, this process will greatly reduce the speed of the application. By using preprocessing statements, you can avoid repeated analysis / compilation / optimization cycles. In short, preprocessing statements take less resources and run faster.

  • The parameters provided to the preprocessing statement do not need to be enclosed in quotation marks, and the driver will process them automatically. If your application uses only preprocessing statements, you can ensure that SQL injection does not occur. (however, there is still a risk of SQL injection if the rest of the query is built from non escaped input).

The above contents are extracted from the official documents, but in fact, the most intuitive benefit of preprocessing statements is that they can effectively prevent SQL injection. We will learn more about SQL injection when learning MySQL in the future. There is not much to introduce here. Anyway, the preprocessing statement can complete this work.

PDO operation preprocessing statement

In the extension of PHP, PDO has been the mainstream core database extension library. Naturally, its support for preprocessing statements is also very comprehensive.

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

//: XXX placeholder
$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 the preprocessing statement, which will return a pdostatement object. Use placeholders such as: XXX in the preprocessed 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 embodiment of the two advantages of preprocessing statements. The first is placeholders. After using placeholders, we don’t need to write single quotes in SQL statements. Single quotes are often the main source of vulnerabilities in SQL injection. 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 more secure. You can refer to the relevant documents.

Another advantage is the ability of templates. We only define a pdostatement object, and then we can use the execute () method to execute preprocessing statements multiple times by changing the content of the data.

Another way to write placeholders is to use a question mark as a placeholder. In this case, the key name of the bindparam () method will use a numeric subscript. It should be noted here that the number subscript starts from 1.

// ?  placeholder 
$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 preprocessing statements to query data. Here, we use execute () directly to pass parameters to placeholders.

//Query to get data
$stmt = $pdo->prepare("select * from zyblog_test_user where username = :username");

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

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

Mysqli operation preprocessing statement

Although the mainstream is PDO, and PDO is also used in most frameworks, we still use mysqli to develop quickly when we write scripts or need to test some functions quickly. Of course, mysqli also supports the functions related to preprocessing statements.

//Mysqli preprocessing
$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 except for different method names, the key names of binding parameters are not exactly the same. Here we use the question mark placeholder in bind_ In the param () method, s is used to represent the symbol position. If it is multiple parameters, it should be written as SSS… So.

summary

The ability of preprocessing statements has been encapsulated in the current framework. In fact, we don’t need to care too much. For example, we can see the application of preprocessing statements when using DB:: select() for database operation in laravel.
You can refer to the select () method in vendor / laravel / framework / SRC / illuminate / database / connection. PHP.

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

Reference documents:

https://www.php.net/manual/zh/pdo.prepared-statements.php

===============

Official account: hard core project manager

Add wechat / QQ friends: [xiaoyuezigonggong / 149844827] get free PHP and project management learning materials

Tiktok, official account, voice, headline search, hard core project manager.

Station B ID: 482780532

Recommended Today

Apache sqoop

Source: dark horse big data 1.png From the standpoint of Apache, data flow can be divided into data import and export: Import: data import. RDBMS—–>Hadoop Export: data export. Hadoop—->RDBMS 1.2 sqoop installation The prerequisite for installing sqoop is that you already have a Java and Hadoop environment. Latest stable version: 1.4.6 Download the sqoop installation […]