Prepared statements are very useful for preventing MySQL injection.
SQL injection is to insert SQL commands into Web form submissions or enter domain names or query strings for page requests, ultimately tricking the server into executing malicious SQL commands.
Preprocessing statements and bound parameters
Preprocessing statements are used to execute multiple identical SQL statements with high execution efficiency higher.
The working principle of prepared statements is as follows:
1. Preprocessing: Create a SQL statement template and send it to the database. Reserved values are marked with the parameter "?". For example:
INSERT INTO MyGuests (firstname, lastname, email) VALUES(?, ?, ?)
2. Database Parse, compile, perform query optimization on SQL statement templates, and store the results without outputting them. The application can execute the statement multiple times if the parameter values are different.
3. Execution: Finally, the application-bound value is passed to the parameter ("?" mark), and the database executes the statement.
Compared to directly executing SQL statements, prepared statements have two main advantages
· The prepared statement greatly reduces the analysis time and only makes one query (although the statement is executed multiple times).
· Binding parameters reduces server bandwidth. You only need to send the parameters of the query instead of the entire statement.
· Prepared statements are very useful for SQL injection, because different protocols are used after parameter values are sent, ensuring the legality of the data.
MySQLi prepared statements
The following The example uses prepared statements in MySQLi and binds corresponding parameters:
<?php
header("Content-type:text/html;charset=utf-8"); //设置编码
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "test";
// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);
// 检测连接
if ($conn->connect_error) {
die("连接失败: " . $conn->connect_error);
}
// 预处理及绑定
$stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES(?, ?, ?)");
$stmt->bind_param("sss", $firstname, $lastname, $email);
// 设置参数并执行
$firstname = "John";
$lastname = "Doe";
$email = "john@example.com";
$stmt->execute();
$firstname = "Mary";
$lastname = "Moe";
$email = "mary@example.com";
$stmt->execute();
$firstname = "Julie";
$lastname = "Dooley";
$email = "julie@example.com";
$stmt->execute();
echo "新记录插入成功";
$stmt->close();
$conn->close();
?> Parse each line of code in the following example:
"INSERT INTO MyGuests (firstname, lastname, email) VALUES(?, ?, ?)"
In the SQL statement, we use the question mark (?), here we can Replace question marks with integers, strings, doubles, and booleans.
Next, let’s take a look at the bind_param() function:
$stmt->bind_param("sss", $firstname, $lastname, $email );
This function binds SQL parameters and tells the database the value of the parameters. The "sss" parameter column handles the data types of the remaining parameters. There are several (?) above and several data types below. The s character tells the database that the parameter is a string.
Parameters have the following four types:
· i - integer (integer type)
· d - double (double precision floating point type)
· s - string (string)
· b - BLOB (binary large object: binary large object)
Each parameter needs to specify the type.
How to specify the data type has been introduced in the previous section
By telling the data type of the database parameter, the risk of SQL injection can be reduced.
The above code execution result:
New record inserted successfully
Check if your data is there Insertion successful?
Example
Let us insert the data in the form into the database
First An HTML page
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>PHP中文网</title>
</head>
<body>
<form action="insert.php" method="post">
Firstname: <input type="text" name="firstname" /><br/>
Lastname: <input type="text" name="lastname" /><br/>
email: <input type="text" name="email" /><br/>
<input type="submit" />
</form>
</body>
</html>
is submitted to the PHP page
<?php
header("Content-type:text/html;charset=utf-8"); //设置编码
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "test";
// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);
// 检测连接
if ($conn->connect_error) {
die("连接失败: " . $conn->connect_error);
}
// 预处理及绑定
$stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES(?, ?, ?)");
$stmt->bind_param("sss", $firstname, $lastname, $email);
// 设置参数并执行
$firstname = $_POST['firstname'];
$lastname =$_POST['lastname'];
$email = $_POST['email'];
$stmt->execute();
echo "新记录插入成功";
$stmt->close();
$conn->close();
?>Through the above two programs, the data in our form can be inserted into the database
Next Section