Sanitizing user input in PHP with MySQLi Escape String Function

When you have an online application containing a form to get inputs from a user, it is important to sanitize the inputs to avoid errors and to protect your database against SQL injection attacks. In PHP, there is a function called mysqli_real_escape_string() that escapes special characters in a string. Those special characters include a single quote, double quote, backslash, NUL, and line return, which have meaning to the SQL.

For example, we have an SQL statement to search for customers with the last name O’Malley:

1. SELECT * FROM customers WHERE last_name = 'O'Malley';

The above statement causes an error since there are three single quotes, and MySQL does not know where our string ends. The single quote in the last name needs to be “escaped” so that it will be treated as only data and not a special character in MySQL. To escape the string, we need to add a backslash before all single quotes in the string.

1. SELECT * FROM customers WHERE last_name = 'O\'Malley';

We can add the backslash manually if we already know what the string is. However, we cannot do that with dynamic data. For example, if the string is from a user’s input, we need a code that handles the escaping dynamically. In PHP, there is a function that does that for us, named mysqli_real_escape_string().

mysqli_real_escape_string() function

Syntax

1. mysqli_real_escape_string(connection, escapestring);

Parameter

The function returns the escaped string.Return value

PHP version

5+

We can only use this function when we have a connection to the database. Please note in the syntax that the first parameter is the database connection.

Why is the database connection needed? The mysqli_real_escape_string() function uses the connection to get the information about the character set used in the database. It is necessary to know how the string should be treated.

Example

Please see the below image. Let’s say we have a simple page using PHP to search customer full name based on the input parameter last name.

Example of mysqli_real_escape_string() in PHP

Here’s the HTML form for the page above:

1. <form method="get" action="<?php echo $_SERVER['PHP_SELF']; ?>">
2. <p>
3. <label for="searchtext">Enter a last name:</label>
4. <input type="search" name="searchtext" id="searchtext">
5. <input type="submit" name="search" value="Search">
6. </p>
7. </form>

The string in the textbox will be passed to the server and used in an SQL statement to get the customer data from the database. Please see the PHP code below:

1. <?php
2. if (isset($_GET['search'])) {
3. $db = new mysqli('localhost','root','password','dbname');
4. if ($db->connect_error) {
5. $error = $db->connect_error;
6. }
8. $searchtext = $_GET['searchtext'];
9. $sql = "SELECT * FROM customers WHERE last_name = '$searchtext'";
11. $result = mysqli_query($db, $sql);
12. }
13. ?>

There is an $sql variable that contains an SQL statement to query the customers’ data. The search text from the user input has been embedded directly in the SQL select query, and this can be potentially harmful.

For example, if the search text contains a single quote such as O’Malley, it will return an error since MySQL will treat the single quote as a special character.

So, let’s pass the $_GET[‘searchterm’] as an argument to the mysqli_real_escape_string() function so that the $searchtext variable becomes like this below:

1. $searchtext = mysqli_real_escape_string($db, $_GET['searchtext']);
2. $sql = "SELECT * FROM customers WHERE last_name = '$searchtext'";

Now the $searchtext has value O\’Malley instead of O’Malley, and the SQL code will work.

Another example, imagine someone is doing an SQL injection and inputting a string like this:

1. '; DROP TABLE payments; --

As we already applied the escape string function, the $sql string becomes:

1. SELECT * FROM customers WHERE last_name = '\'; DROP TABLE payments; -- '

As we can see, the string DROP statement is treated as just data, and the query won’t do any harm by removing the table payments from our database. The database is being protected from SQL injection attacks.

The difference with mysql_real_escape_string()

There is a function called mysql_real_escape_string() (without “i”) which has similar name with mysqli_real_escape_string(). This function also escapes special characters from a string. However, this function is deprecated in PHP 5 and was removed from PHP 7.

Syntax

1. mysql_real_escape_string(escapestring);

Example

1. <?php
2. $searchtext = "O'Malley";
3. $lastname = mysql_real_escape_string($lastname);
5. $query = "SELECT * FROM customers WHERE last_name = '$lastname'";
6. ?>

Please note that the syntax does not require parameter database connection which is different from mysqli_real_escape_string().

An alias with shorter name: mysqli_escape_string()  

The only problem with mysqli_real_escape_string() is that the name is super long. There is an alias to this function called mysqli_escape_string(). Imagine if we need to write many SQL statements using the functions. Using an alias with a shorter name saves our time.