Current Location: Home> Latest Articles> Introduction to the basic usage methods of real_escape_string function and mysqli extension in PHP

Introduction to the basic usage methods of real_escape_string function and mysqli extension in PHP

gitbox 2025-05-27

When using PHP for web development, operating databases is one of the most common tasks in daily work. In order to prevent security issues such as SQL injection, we need to perform appropriate escape processing on the data entered by the user. When using MySQL databases, PHP's mysqli extension provides a commonly used and secure function: real_escape_string , used to escape strings, thereby improving the security of database operations.

1. Introduction to mysqli extension

The mysqli (MySQL Improved) extension is an extension module in PHP for interacting with MySQL databases. Compared with the early MySQL extensions, mysqli provides richer functions, such as supporting preprocessing statements, transaction control and a more complete error handling mechanism.

To use the mysqli extension, you first need to create a database connection object as follows:

<code> $host = 'localhost'; $user = 'db_user'; $password = 'db_password'; $database = 'test_db';

$conn = new mysqli($host, $user, $password, $database);

if ($conn->connect_error) {
die('Connection failed: ' . $conn->connect_error);
}
</code>

This code uses the mysqli constructor to establish a connection to the MySQL server and determines whether the connection is successful.

2. Use of real_escape_string function

real_escape_string is a method provided by mysqli object to escape strings used in SQL statements. This can prevent special characters (such as quotes, backslashes, etc.) from destroying SQL syntax, thereby effectively preventing SQL injection attacks.

Its typical usage is as follows:

<code> $user_input = "O'Reilly"; $safe_input = $conn->real_escape_string($user_input); $sql = "SELECT * FROM users WHERE name = '$safe_input'"; $result = $conn->query($sql); </code>

In this example, if the O'Reilly entered by the user is not escaped, it will cause an error in the SQL statement or even cause security problems. The real_escape_string method automatically escapes single quotes to O\'Reilly , thus constructing a legal and safe SQL statement.

3. Things to note

  1. Must be used after the database connection
    real_escape_string depends on the character set of the current connection, so it must be called after the connection is successful.

  2. Not equal to a replacement for preprocessing statements <br> Although real_escape_string can provide some security, it cannot completely replace preprocessing statements. Preprocessing statements are the safest way to prevent SQL injection.

  3. Character sets need to be consistent <br> Ensure that the character set used by PHP and the database is consistent (such as utf8mb4), otherwise it may lead to escape failure or garbled characters.

4. Practical examples of combining HTML forms

Here is an example of using an HTML form to receive user input and process it through real_escape_string :

<code> <form method="POST" action="https://gitbox.net/process.php"> Username: <input type="text" name="username"> <input type="submit" value="submit"> </form> </code>

Process in process.php :

<code> $conn = new mysqli('localhost', 'user', 'password', 'database');

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

$username = $_POST['username'];
$safe_username = $conn->real_escape_string($username);

$sql = "SELECT * FROM users WHERE username = '$safe_username'";
$result = $conn->query($sql);

if ($result && $result->num_rows > 0) {
echo "The user already exists";
} else {
echo "The user does not exist";
}
</code>

5. Summary

mysqli::real_escape_string is one of the important tools in PHP to prevent SQL injection. It protects the legitimacy of SQL statements by escaping special characters in user input. However, for greater security, developers should prioritize the use of preprocessing statements. After understanding the basic usage of real_escape_string , you can process user input more confidently and interact with the database securely.