Execution of SELECT queries using PHP's preprocessing statements can effectively prevent SQL injection and improve security. 1. Preprocessing statements separate SQL structure from data, send templates first and then pass parameters to avoid malicious input tampering with SQL logic; 2. PDO and MySQLi extensions commonly used in PHP implement preprocessing, among which PDO supports multiple databases and unified syntax, suitable for newbies or projects that require portability; 3. MySQLi is specially designed for MySQL, with better performance but less flexibility; 4. When using it, you should select appropriate placeholders (such as? or named placeholders) and bind parameters through execute() to avoid manually splicing SQL; 5. Pay attention to handling errors and empty results to ensure code robustness; 6. Close resources in time after query to improve program stability. Both methods can prevent injection, and the choice depends on project needs and personal preferences.

Executing SELECT queries with PHP preprocessing statements is a common practice to prevent SQL injection and improve database operation security. If you spliced ??SQL strings directly for querying, it is recommended to change to prepared statement as soon as possible.

What are preprocessing statements?

Prepared statements are a mechanism to separate SQL statement structure and data. It first sends a SQL template with placeholders to the database, and then passes the value in and executes after the parameters are ready. This "compile first and then pass parameters" method can effectively prevent SQL injection attacks, and can also improve the efficiency of executing similar queries multiple times.

In PHP, two commonly used database extensions support preprocessing statements: PDO and MySQLi. The following will explain how to use them for SELECT queries separately.

Perform SELECT query with preprocessing using PDO

PDO is a more general, support for multiple database extensions, unified syntax, and is recommended for beginners or those who want more portable code.

The basic steps are as follows:

• Connect to the database
• Prepare SQL statements
• Bind parameters (optional)
• Execute a query
• Get results

Sample code:

 $host = '127.0.0.1';
$db = 'test_db';
$user = 'root';
$pass = '';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$opt = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
];
$pdo = new PDO($dsn, $user, $pass, $opt);

$stmt = $pdo->prepare('SELECT id, name, email FROM users WHERE id = ?');
$stmt->execute([$_GET['id']]);
$user = $stmt->fetch();

if ($user) {
    echo &#39;ID: &#39; . $user[&#39;id&#39;] . &#39;<br>&#39;;
    echo &#39;Name: &#39; . $user[&#39;name&#39;] . &#39;<br>&#39;;
    echo &#39;Email: &#39; . $user[&#39;email&#39;];
} else {
    echo &#39;The user does not exist&#39;;
}

A few notes:

• ? is a placeholder, suitable for passing parameters in order.
• You can also use a named placeholder such as :id , and then bind via bindParam() or execute([':id' => $_GET['id']]) .
• Do not manually splice variables into SQL strings, otherwise it will lose the meaning of preventing injection.

Perform SELECT query with preprocessing using MySQLi

MySQLi is an extension designed specifically for MySQL, with slightly better performance, but not as flexible as PDO.

Here is a simple example of the object-oriented approach:

 $mysqli = new mysqli('localhost', 'root', '', 'test_db');

if ($mysqli->connect_error) {
    die('Connection failed: ' . $mysqli->connect_error);
}

$stmt = $mysqli->prepare('SELECT id, name, email FROM users WHERE id = ?');
$stmt->bind_param('i', $id);
$id = $_GET['id'];
$stmt->execute();
$result = $stmt->get_result();

if ($row = $result->fetch_assoc()) {
    echo &#39;ID: &#39; . $row[&#39;id&#39;] . &#39;<br>&#39;;
    echo &#39;Name: &#39; . $row[&#39;name&#39;] . &#39;<br>&#39;;
    echo &#39;Email: &#39; . $row[&#39;email&#39;];
} else {
    echo &#39;The user does not exist&#39;;
}

$stmt->close();
$mysqli->close();

Notes:

• The type identifier in bind_param() must be correct, such as 'i' represents an integer and 's' represents a string.
• If you are not sure about the input type, you can do verification or conversion first.
• Remember to close statements and connect resources. Although PHP will automatically release at the end of the script, it is a good habit to explicitly close it.

Which method is better?

There is no absolute answer to this question, depending on your project needs and personal preferences:

• If you need to be compatible with multiple databases, or like concise and unified writing, use PDO .
• If you only use MySQL and pursue the ultimate performance, you can use MySQLi .
• Both support preprocessing and can effectively prevent SQL injection.

In addition, no matter which method is used, you should pay attention to the following points:

• Don't splice parameters into SQL directly
• Error processing should be done after query, such as try catch or if to judge the return value
• If the query result is empty, it should also be handled to avoid errors.

Basically that's it. Pre-processing is actually not complicated, but it is easy to ignore details and lead to security issues. Just write according to the above pattern and you can write more robust code.

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