real_escape_string

In this article, we will discuss the mysqli_real_escape_string() function in PHP, which is used to escape special characters in strings that will be used in SQL queries.

Introduction to the mysqli_real_escape_string() function

This built-in PHP function escapes special characters in strings intended for SQL queries. It helps prevent SQL injection attacks, which occur when malicious users inject harmful SQL code into statements executed by the database.

How to use the mysqli_real_escape_string() function

Using the mysqli_real_escape_string() function is straightforward. Here's an example:

<‌?php
$con = mysqli_connect('localhost', 'username', 'password', 'database');

if (!$con) {
    exit('Could not connect: ' . mysqli_error($con));
}

// Set charset to prevent multi-byte character vulnerabilities
mysqli_set_charset($con, 'utf8mb4');

$name = "John O'Reilly";
$name = mysqli_real_escape_string($con, $name);

$sql = "INSERT INTO customers (name) VALUES ('$name')";

if (!mysqli_query($con, $sql)) {
    exit('Error: ' . mysqli_error($con));
}

echo '1 record added';

mysqli_close($con);
?>

In this example, we first establish a connection to a MySQL database using mysqli_connect(). We then define a $name variable containing a single quote. Calling mysqli_real_escape_string() escapes that quote, ensuring the SQL statement executes without syntax errors. We construct an INSERT query targeting the customers table, execute it with mysqli_query(), and output a success message.

Note: Always set the connection charset (e.g., mysqli_set_charset($con, 'utf8mb4')) before escaping to prevent multi-byte character vulnerabilities. While this function works, modern PHP development strongly recommends prepared statements with parameterized queries for enhanced security.

Conclusion

The mysqli_real_escape_string() function remains a practical tool for escaping special characters in SQL queries. Properly escaping user input helps keep your application secure and your data protected.

Practice

What does the PHP function mysql_real_escape_string function do?

It escapes the special characters in a string to prevent SQL injection.It deletes the special characters in a string.It reverses a string in PHP.It is used for security in the database by protecting it from SQL injection attacks.It acts as a string formatting function in PHP.