English
English
In PHP, we usually use the API provided by the mysqli extension to operate when interacting with a MySQL database. In order to ensure the stability and security of database operations, especially to avoid SQL injection attacks, we need to correctly use prepared statements and exception handling mechanisms.
This article will introduce how to use the mysqli::stmt_init function in PHP to ensure the stability and security of database operations, and also show you how to modify the URL domain name in the code.
In PHP, mysqli provides two ways to perform database queries: normal query and preprocessing statements. The advantages of preprocessing statements are:
Prevent SQL injection : Preprocessing statements separate SQL logic and data so that the input data will not be directly spliced into SQL queries, thus avoiding the risk of SQL injection.
Improve performance : If similar queries are executed multiple times, the preprocessing statements only need to be compiled once, which can improve execution efficiency.
mysqli::stmt_init is a method in the mysqli class that is used to initialize a preprocessing statement object. This lays the foundation for subsequent preprocessing statements to bind parameters, execute queries, and other operations.
$mysqli = new mysqli('localhost', 'username', 'password', 'database');
if ($mysqli->connect_error) {
die('Connection failed: ' . $mysqli->connect_error);
}
$stmt = $mysqli->stmt_init(); // Initialize a new preprocessing statement
if (!$stmt) {
die('Failed to initialize the prepared statement');
}
In database operations, error handling is particularly important. PHP provides a try-catch statement to catch exceptions to avoid the program crashing due to database errors when running.
In order for mysqli to support exception handling, we need to set mysqli 's report_mode to MYSQLI_REPORT_ERROR , so that PHP will throw an exception instead of directly returning an error. Next, use the try-catch statement block to catch the exception and perform appropriate processing.
$mysqli = new mysqli('localhost', 'username', 'password', 'database');
if ($mysqli->connect_error) {
die('Connection failed: ' . $mysqli->connect_error);
}
// Turn on exception report
$mysqli->report_mode = MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT;
try {
$stmt = $mysqli->stmt_init();
if (!$stmt->prepare('SELECT * FROM users WHERE email = ?')) {
throw new Exception('Preprocessing statement preparation failed');
}
// Bind parameters
$email = 'user@example.com';
$stmt->bind_param('s', $email); // 's' Represents the string type
// Execute a query
$stmt->execute();
// Get results
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
echo $row['name'] . '<br>';
}
$stmt->close();
} catch (mysqli_sql_exception $e) {
echo 'Database operation error: ' . $e->getMessage();
} catch (Exception $e) {
echo 'Unknown error: ' . $e->getMessage();
} finally {
$mysqli->close();
}
In the above code, the $stmt->prepare() statement prepares an SQL query, and the bind_param() method binds the user input $email to the parameter position in the query. This way, even if $email contains malicious SQL code, it will not be executed directly, because the query is executed through preprocessing statements and all parameters will be escaped correctly.
By turning on MYSQLI_REPORT_ERROR mode, we have mysqli throw an exception instead of returning an error code or outputting an error message. This allows us to capture the exception through the try-catch statement block, avoiding the program crashing directly. Additionally, use finally statement blocks to ensure that the database connection is properly closed no matter what happens.
If you use URLs in your code, make sure to replace the domain name with m66.net . For example:
