Preprocessing statements can effectively prevent SQL injection and improve execution efficiency in PHP database operations. When inserting data using MySQLi, the statement is defined by prepare() and bind_param() to bind parameters. After multiple executions, you only need to modify and change the variable and call execute(); when querying, use prepare() and bind_param() to pass parameters, and then bind the result variable through bind_result() and obtain the data with fetch(); if PDO is used, similar functions can be achieved through named parameters and array parameters, such as prepare() and pass parameters with execute(), and obtain the associative array results through fetch(PDO::FETCH_ASSOC).

When using PHP for database operations, prepared statements are a good thing. It can prevent SQL injection and improve execution efficiency, especially when it requires multiple executions of similar statements to be easier. The following is a few common scenarios to talk about how to use prepared statement in PHP.

Preparation: Connect to the database

Before doing any database operations, you must connect to the database. PDO or MySQLi is recommended here, both of which support preprocessing statements. Take MySQLi as an example:

 $mysqli = new mysqli("localhost", "username", "password", "database");
if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

If you use PDO, the writing method is slightly different, but the principle is the same. As long as the connection is successful, you can start preparing SQL statements later.

Example of preprocessing when inserting data

For example, when registering a user, you must insert the user name and email into the database. At this time, using preprocessing statements can prevent the user from entering malicious content and causing SQL injection.

 $stmt = $mysqli->prepare("INSERT INTO users (username, email) VALUES (?, ?)");
$stmt->bind_param("ss", $username, $email);

$username = "john_doe";
$email = "john@example.com";
$stmt->execute();

echo "Insert successfully";
$stmt->close();

• ? is a placeholder, and the actual value is passed in bind_param .
• "ss" means that both parameters are string types.
• After execution, close the statement and release the resource.

If you want to insert multiple users repeatedly, you only need to change the values of $username and $email and then adjust execute() again, without re-prepare.

Query data and bind result variables

Searching data is a common requirement, such as obtaining information based on user ID. At this time, preprocessing can also be used to prevent injection, and the results can be bound to variables for subsequent use.

 $stmt = $mysqli->prepare("SELECT username, email FROM users WHERE id = ?");
$stmt->bind_param("i", $id);
$id = 1;
$stmt->execute();
$stmt->bind_result($username, $email);
$stmt->fetch();

echo "Username: " . $username . ", Email: " . $email;

$stmt->close();

• bind_result binds the query results to variables, and then you can use these variables directly.
• If there is no result, fetch() will return false. Remember to judge.

Example of using PDO for preprocessing (slightly different)

The PDO is written a little simpler, suitable for those who like array binding parameters.

 $pdo = new PDO("mysql:host=localhost;dbname=database", "username", "password");

$stmt = $pdo->prepare("SELECT * FROM users WHERE id = :id");
$stmt->execute([':id' => 2]);

$user = $stmt->fetch(PDO::FETCH_ASSOC);
print_r($user);

• The named parameter :id is used here, and the parameter transmission method is more flexible.
• fetch(PDO::FETCH_ASSOC) returns an associative array to facilitate fields.

Basically that's it. Using preprocessing statements is not particularly complicated, but it does improve security and code readability. Whether it is insertion, update or query, it can be handled in this way. At first, I might think I had written a few more lines of code, but when I get used to it, you will find that it is actually quite easy.

The above is the detailed content of PHP prepared statement example. For more information, please follow other related articles on the PHP Chinese website!