Escape SQL Strings with mysqli_real_escape_string in PHP

Securing user input is a fundamental step in PHP database programming. This article provides a straightforward guide on how to use the mysqli_real_escape_string() function to escape special characters in user-provided strings before inserting them into a raw SQL query. By learning this technique, you can safeguard your application against basic SQL injection vulnerabilities and prevent database syntax errors caused by special characters like quotes.

Understanding mysqli_real_escape_string()

The mysqli_real_escape_string() function prepends backslashes to special characters in a string for use in an SQL statement. The characters affected include NUL (ASCII 0), \n, \r, \, ', ", and Control-Z.

To function correctly, this function requires an active database connection. This is because it takes into account the current character set of the database connection to prevent bypass techniques that exploit multi-byte character sets.

Procedural Style Syntax

In PHP’s procedural MySQLi style, the function requires two arguments: the active database connection link and the string to escape.

// Establish connection
$connection = mysqli_connect("localhost", "db_user", "db_password", "my_database");

// Check connection
if (!$connection) {
    die("Connection failed: " . mysqli_connect_error());
}

// Raw user input containing special characters
$user_input = "O'Connor";

// Escape the string
$safe_input = mysqli_real_escape_string($connection, $user_input);

// Now safe to use in a raw SQL query
$query = "SELECT * FROM users WHERE last_name = '$safe_input'";
$result = mysqli_query($connection, $query);

Object-Oriented Style Syntax

If you prefer object-oriented programming, the MySQLi class provides a real_escape_string() method (or its alias escape_string()) which only requires the string as an argument.

// Establish connection
$mysqli = new mysqli("localhost", "db_user", "db_password", "my_database");

// Check connection
if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

// Raw user input
$user_input = '"Admin" OR 1=1';

// Escape the string
$safe_input = $mysqli->real_escape_string($user_input);

// Construct the query
$query = "SELECT * FROM products WHERE category = '$safe_input'";
$result = $mysqli->query($query);

Key Considerations

While mysqli_real_escape_string() is effective for sanitizing strings, you must keep the following rules in mind: