MySQL MariaDB

How to use mysqli_real_escape_string function

A mysqli_real_escape_string is a built-in function of the PHP that is used to control the unwanted and dangerous characters.  In this article, we will discuss with the help of an example what is the mysqli_real_escape_string function and how it can be used for the protection of the database.

What is a mysqli_real_escape_string

By definition, mysqli_real_escape_string() allows the special characters in a string to escape to the database by the SQL query, taking into account the current characters set of the established connection. In simple words, this function allows the special characters to be considered as a part of the string and saved in the database as a string. The hackers mostly use special characters such as ?,’,^,%, and !, for hacking the database or misuse the data of a database, so to prevent this act, this function is used which forces the PHP to be considered as the string only. The general syntax of this function is as:

mysqli_real_escape_string(connection_variable, string_variable)

In the general syntax, connection_variable is the result of the mysqli_connect() function stored in any variable and string_variable is the variable that is supposed to pass through this function to escape characters. For example, we create a code of PHP in which first we establish the connection of the PHP with database using the function, mysqli_connect() with the parameters of localhost; the database is on the same machine, user name; maadi, password; qwer1234 and name of database; Organization. Then we applied the if-else condition, to check whether the connection is established successfully or not by using a function, mysqli_real_escape_string(connection_variable, string_variable). After ensuring the successful establishment of the connection we will declare the variable, named, Firstname, and assign some string that includes some special character and then insert it in the database Linuxhint.

<?php
//Establishment of Connection with databas
$connection = mysqli_connect("localhost","maadi","Organization");

//checking status of connection
if (mysqli_connect_errno()) {
    echo "Failed to connect to MySQL: " .mysqli_connect_error();
    exit();
}
//declare variable
$firstname2 ="John'o Alexander";

//inserting into the database
$sql="INSERT INTO Linuxhint (Emp_name) VALUES ('$firstname2')");

//execution of mysql queries
$r = mysqli_query($connection, "INSERT into Linuxhint VALUES ('firstname2')");
if(!$r){
    print("Error occurred\n");
}
else{
    print("Record inserted successfully\n");
}
//connection closed
mysqli_close($connection);
?>

The output of this code should be an error because the string contains the special character ‘ between it. To check the output, open the terminal in Ubuntu and run this PHP file using the command of php with the file name in which the code is saved.

$ php file1.php

The error has occurred, to rectify this error, we will use the mysqli_real_escape_string() and save the string in the database.

<?php
//Establishment of Connection with databas
$connection = mysqli_connect("localhost","maadi","Organization");

//checking status of connection
if (mysqli_connect_errno()) {
    echo "Failed to connect to MySQL: " .mysqli_connect_error();
    exit();
}

//declare variable
$firstname2 ="John'o Alexander";

//pass from the mysqli_real_escape_string()
$firstname = mysqli_real_escape_string($connection,$firstname2);
//inserting into the database
$sql="INSERT INTO Linuxhint (Emp_name) VALUES ( '$firstname' )");

//execution of mysql queries
$r = mysqli_query($connection, "INSERT into Linuxhint VALUES ('firstname')");
if(!$r){
    print("Error occurred\n");
}
else{
    print("Record inserted successfully\n");
}
//connection closed
mysqli_close($connection);
?>

Again run the file in the terminal.

$ php file1.php

The output is successful. To verify it we will go to MySQL and run the following command to check whether the string is inserted in the database or not.

SELECT * FROM Linuxhint;

Conclusion

To secure the files is a prior concern of everyone as they may include some confidential data. Mostly in hacking, the special characters are used to connect with the database to retrieve its data for unethical uses. To prevent this we can put a variety of security checks on the data before it is inserted into the database. In this article, we have discussed one of the built-in functions of PHP which is used to ensure security in a way that no special characters interact in the database to harm it. Instead of that this function considered it as a normal string and inserted the string in the database.

About the author

Hammad Zahid

I'm an Engineering graduate and my passion for IT has brought me to Linux. Now here I'm learning and sharing my knowledge with the world.