PHP real_escape_string() and mysqli_real_escape_string()

This article is created to cover the two functions of PHP, namely:

• real_escape_string()
• mysqli_real_escape_string()

Both functions are used when we need to escape special characters from a string. The only difference is that real_escape_string() is used with PHP MySQLi object-oriented script, whereas mysqli_real_escape_string() is used with PHP MySQLi procedural script.

PHP real_escape_string()

The PHP real_escape_string() function is used to escape special characters from a specified string in object-oriented style. For example:

<?php
   $server = "localhost";
   $user = "root";
   $pass = "";
   $db = "codescracker";
   
   $conn = new mysqli($server, $user, $pass, $db);
   
   if($conn->connect_errno)
   {
      echo "Database connection failed!<BR>";
      echo "Reason: ", $conn->connect_error;
      exit();
   }
   
   $username = $conn->real_escape_string($_POST['user']);
   $fullname = $conn->real_escape_string($_POST['name']);
   $email = $conn->real_escape_string($_POST['email']);
   
   $sql = "INSERT INTO `user`(`username`, `fullname`, `email`) 
      VALUES ('$username', '$fullname', '$email')";
   
   $qry = $conn->query($sql);
   if($qry)
   {
      echo "Data inserted successfully.";
      
      // block of code to process further...
   }
   else
   {
      echo "Something went wrong!<BR>";
      echo "Error Description: ", $conn->error;
   }
   $conn->close();
?>

In above example, the following code/statement:

$username = $conn->real_escape_string($_POST['user']);

is used to escape special characters (if any) from the data received by the form field whose name is user. Similar things go with the next two statements of real_escape_string(). In this way, all the special characters get escaped (if any) before sending or inserting the data into the database.

The above example can also be written as:

<?php
   $conn = mysqli_connect("localhost", "root", "", "codescracker");
   
   if(!$conn->connect_errno)
   {
      $username = $conn->real_escape_string($_POST['user']);
      $fullname = $conn->real_escape_string($_POST['name']);
      $email = $conn->real_escape_string($_POST['email']);
   
      $sql = "INSERT INTO `user`(`username`, `fullname`, `email`) 
         VALUES ('$username', '$fullname', '$email')";
      if($conn->query($sql))
      {
         echo "Data inserted successfully.";
         // block of code to process further...
      }
   }
   $conn->close();
?>

Note: The mysqli() function is used to open a connection to the MySQL database server in object-oriented style.

Note: The new keyword is used to create a new object.

Note: The connect_errno is used to get or return the error code (if any) from the last connect call in object-oriented style.

Note: The connect_error is used to get the error description (if any) from the last connection in object-oriented style.

Note: The exit() function is used to terminate the execution of the current PHP script.

Note: The query() function is used to perform queries on the MySQL database in object-oriented style.

Note: The error is used to return the description of the error (if any) from the most recent function call in object-oriented style.

Note: The close() function is used to close an opened connection in object-oriented style.

PHP real_escape_string() Syntax

The syntax of the real_escape_string() function in PHP is:

connectionVariable -> real_escape_string(string)

PHP mysqli_real_escape_string()

The PHP mysqli_real_escape_string() function escapes special characters from specified string data in procedural style. For example:

<?php   
   $conn = mysqli_connect("localhost", "root", "", "codescracker");
   
   if(!mysqli_connect_errno())
   {
      $username = mysqli_real_escape_string($conn, $_POST['user']);
      $fullname = mysqli_real_escape_string($conn, $_POST['name']);
      $email = mysqli_real_escape_string($conn, $_POST['email']);
   
      $sql = "INSERT INTO `user`(`username`, `fullname`, `email`) 
         VALUES ('$username', '$fullname', '$email')";
      
      if(mysqli_query($conn, $sql))
      {
         echo "Data inserted successfully.";
         // block of code to process further
      }
   }
   mysqli_close($conn);
?>

Note: The mysqli_connect() function is used to open a connection to the MySQL database server in procedural style.

Note: The mysqli_connect_errno() function is used to get or return the error code (if any) from the last connect call in procedural style.

Note: The mysqli_query() function is used to perform queries on the MySQL database in procedural style.

Note: The mysqli_close() function is used to close an opened connection to the MySQL database in procedural style.

PHP mysqli_real_escape_string() Syntax

The syntax of the mysqli_real_escape_string() function in PHP is:

mysqli_real_escape_string(connectionVariable, string)

PHP Online Test