Updated June 8, 2023
Introduction to mysql_real_escape_string
The mysql_real_escape_string() method is used to escape special characters such as “,”” ‘n,” etc., before sending a query to the MySQL server. The unescaped_string is encoded to produce an escaped SQL string. The mysql_real_escape_string() method returns the length of the encoded or escaped SQL text. This mysql_real_escape_string() function runs special characters like \,\n in a series that can be 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, the first is unescaped_string, and the second is link identifier.
mysql_real_escape_string calls the function, which adds a backslash before the following special characters: \x00, \n, \r, \, ‘,” and \x1a. Writing this function with exception handling is essential to ensure data integrity and handle abnormal behavior. The function should throw an exception when encountering any abnormal behavior with the data. Additionally, it is advisable to save the data before executing the query to MySQL.
Parameters
unescaped_string: The given string needs to be escaped or encoded.
link_identifier: It provides the connection’s link while creating the connection. The mysql_connect() function uses the most recent open link if the link identifier is not provided. Without existing links, the mysql_connect() function will establish a new link without arguments. At last, after doing all this, if we fail to develop a connection with the link, it will display an e-warning error message in the console.
Return Values: it will either return an escaped string as an output if everything is correct or display an error message or a FALSE flag in the output console.
Errors/Exceptions: If we execute the mysql_real_escape_string function without establishing the connection of the function with the mysql server, 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 send through an E_warning as an error message. Only execute this function with a valid mysql connection current. The function must escape the string; otherwise, the query gets susceptible to injection (sqli) attacks.
SQL injection is a mechanism that allows an attacker or hacker to view data that cannot be easily retrieved. This includes data from the other users or the data that only the application accesses.
The special characters that the mysql_real_escape_string function cannot escape are % (percentage)and _(underscore). When these two letters are coupled with a clause such as “grant,” “revoke,” or “like,” MySQL refers to them as wildcards. The underscore (_) is a wildcard character in MySQL that matches any single character in a string.
%: matches any number of characters or zero numbers of characters in the string.
It adds a backslash before every unique character in the string given. A list of special characters that mysql_real_escape_string can encode is shown below:
0x00 (null)
Newline (\n)
Carriage return (\r)
Double quotes (")
Backslash (\)
0x1A (Ctrl+Z)The sample code below demonstrates how improper implementation of the mysql_real_escape _string could result in SQL injection.
Examples of mysql_real_escape_string
Below are the examples.
Example #1
Suppose the numeric user input is
500 OR 1=1Sanitizing input
$prod_id = mysql_real_escape_string($_GET['id']);Below is the generated query:
select prod_name from product where id=500 OR 1=1The id parameter in the query above is not wrapped in quotes, which makes it vulnerable to SQL injection.
Note: To prevent SQL injection (sqli), it is indeed crucial to properly handle user input by ensuring that parameters supplied to functions are contained within quotes when appropriate.
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 the LIKE operator. Since it is impossible to avoid using these characters, the LIKE operator considers them regular wildcards: We shall use a backslash before the wildcard LIKE operator to prevent this problem.
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
Undoubtedly, 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 process so many times, the database server will get down slowly. 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 these 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 MySQL link connected previously by mysql_init() or mysql_real_connect().
- To: the encoded string. In the worst scenario, every string character needs to be escaped. 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"To log in to the system with an employee username ending with “ta” and starting with any number of characters and a password with any single alphabet followed by “123,” you can write a JavaScript function that checks for the desired pattern.
Conclusion
This article taught us how to use the mysql_real_escape_string() function. We have also learned to use the function to avoid sql injection. We have also covered the concept of escaping special characters within this function.
Recommended Articles
We hope that this EDUCBA information on “mysql_real_escape_string” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
