mysql_real_escape_string

Introduction to mysql_real_escape_string

mysql_real_escape_string() is used to escape special characters like ‘\’,’\n’ etc in a query string before sending the query to mysql server. The given unescaped_string is encoded and returns an escaped sql string as an output. mysql_real_escape_string() function returns the length of the encoded or escaped sqlstring. This mysql_real_escape_string() function is used to Escapes special characters like \,\n in a string that can further used in the sql statement.

Syntax and Parameters

Below is the syntax of the mysql_real_escape_string() function:

String mysql_real_escape_string(string unescaped_string,resource link_identifier= =NULL);

This function contains two arguments, first is unescaped_string and the second is link identifier.

mysql_real_escape_string calls the function, which adds a backslashes before the following special characters: \x00, \n, \r, \, ‘, ” and \x1a. This function must always be written with an exception to through an exception when any abnormal behavior happens to the data and to save the data before sending a query to mysql.

Parameters

unescaped_string: The given string needs to be escaped or encoded.

link_identifier: It is used to specify the link for establishing a connection. If the link identifier is not stated, the last link opened by mysql_connect is supposed. If no link is established, it will create one link with mysql_connect function with no arguments passed. At last, after doing all this, then also if we are failing in establishing the connection with the link it will display an e-warning error message in the console.

Return Values: it will either returns an escaped string as an output if everything will be right otherwise it will display an error message or returns FALSE flag in the output console.

Errors/Exceptions: If we execute the mysql_real_escape_string function without establishing the connection of function with the mysql server then it will throw an E_warning message. mysql_real_escape_string() function will only get executed when the full connection is established with the mysql server. Executing this function without a mysql connection present will also through E_warning as an error message. Only execute this function with a valid mysql connection present. It is very important that the function is able to escape the string otherwise the query gets susceptible to sqlinjection (sqli) attacks.

Sql injection: It is a mechanism that allows an attacker or hacker to view data that cannot be easily retrieved. This includes data of the other users or the data that only the application access it itself.

The special characters that cannot be escaped by mysql_real_escape_string function are % (percentage)and _(underscore).If these two characters are combined with a clause like grant, revoke, and like these are called wildcards in MySQL. _(underscore matches a single character in as string)

%: matches any number of characters or zero numbers of characters in string.

It adds a backslash before every special character in the string given. List of special characters that mysql_real_escape_string can encode are shown below:

0x00 (null)
Newline (\n)
Carriage return (\r)
Double quotes (")
Backslash (\)
0x1A (Ctrl+Z)

We should be very careful while using mysql_real_escape_string() function to encode numeric parameters since they are usually written in the query without quotes. Below sample code shows if the mysql_real_escape _string is not implemented properly it could lead to sql injection.

Examples of mysql_real_escape_string

Following are the examples are given below:

Example #1

Suppose,the numeric user input is

500 OR 1=1

Sanitizing input

$prod_id = mysql_real_escape_string($_GET['id']);

Below is the generated query:

select prod_name from product where id=500 OR 1=1

The above-mentioned query will lead to sql injection since the id parameter is not enclosed within quotes.

Note: In order to avoid SQL injection(sqli), the parameter passed in the function must be enclosed between quotes.

Correction in the query to avoid sql injection is shown below in the query.

select prod_name from products where id=500 OR 1=1'

Since the id parameter in the above query is within quotes it will not lead to sql injection.

Like Operator

The function does not escape SQL wildcards for LIKE operator. Since these characters cannot be escaped, they are measured as classic wildcards by the LIKE operator: To prevent this from error we will use a backslash before the wildcard LIKE operator.

Example #2

User_input
Xyz’spq_

Escape all characters:

$value=mysql_real_esape_string($_GET[‘p’]);
$value=str_replace(“%”,”%”,$val);
$value=str_replace(“_”,”_”,$val);

Query

select * from employee where name LIKE ‘%Xyz\’s pq\_%’;

Disadvantages of the Function

Without any doubt, the mysql_real_escape_string function is the best way to avoid sql injections. But it has some demerits also like if we call the function so many times, the database server will get down slow. However, if we call the function twice on the same data by mistake we will have incorrect information or data in our database. Because of all these mentioned reasons, we can implement alternative solutions for example parameterized statements method or stored procedure.

Unsigned long mysql_real_escape_string(mysql * mysql,char * to,const char * from,unsigned long);

• mysql: a mysqllink, that was connectedpreviously by mysql_init() or mysql_real_connect().
• to: the encoded string. In the worst scenario, every character of the string needs to be escaped. Moreover, a 0 character will be appended.
• from: a given string that needs to be encoded by the function.
• long: the length of the given string.

Example #3

//establish connection with the mysql server
$link=mysql_connect(‘mysql_host’,’mysql_user’,’mysql_password’);
//sql query
$query=print(“select * from employee where user_name=’%s’ and pass-word=’’%s”,mysql_real_escape_string($user_name),mysql_real_escape_string($pass_word));
select * from employee where user_name=”%ta” and pass_word=”_123”

This query will login to the system with employee username ending with “ta” and starting with any number of characters and having a password with any single alphabet followed by 123.

Conclusions

In this article, we have learned how to use the mysql_real_escape_string() function. We have also learned to use the function to avoid sql injection. We have also learned about the specials characters that can be escaped by this function. The function is explained with various examples for a better understanding of the user.

Recommended Articles

This is a guide to mysql_real_escape_string. Here we also discuss syntax and parameter along with different examples and its code implementation. You may also have a look at the following articles to learn more –

1. MySQL REVOKE
2. MySQL DML
3. MySQL Window Functions
4. MySQL Foreign Key