Search code examples
phpmysqlisql-injection

When Should I Use PHP mysqli_real_escape_string() Function?

I know that mysqli_real_escape_string Function can be used to prevent SQL injections. ( However, mysql_real_escape_string() will not protect you against some injections)

My question is when should I use mysqli_real_escape_string() function?

Situation 01

I have a registration form with 4 fields called First Name, Last Name, Email, Password.

Should I use mysqli_real_escape_string() to insert query also? All four fields?

Or is it enough to use in login form?

Situation 02

I have a profile page like profile.php?user_name=damith

I have used $_GET['user_name'] in many functions in this page.

Should I use mysqli_real_escape_string() in all those functions?

Solution

  • mysqli_real_escape_string() is no longer the best way to ensure the data you save in your database is safe. Instead, you should be using prepared statements: http://php.net/manual/en/mysqli.quickstart.prepared-statements.php

    As to your question: Anytime you are putting data that you are unsure of (especially if that data comes from unknown sources like a web form) into your database you should be making sure that it is properly formatted for your database. mysqli_real_escape_string() can only do that for string literals which is why prepared statements are the better approach. Anytime you execute a query that relies on user submitted data, you should be using prepared statements.

    When you output data to display to the user, you don't need to use mysqli_real_escape_string(), but should instead be escaping for the web using htmlspecialchars() (http://php.net/htmlspecialchars)

    situation 1 - YES DEFINITELY, and even better would be to use prepared statements.

    situation 2 - If you are displaying data to the user on a web page, you do not need to use mysqli_real_escape_string() but should instead use htmlspecialchars() to decrease the risk of XSS and other code injection attacks.

    A few examples: 

    <?php 
// Prepared statement.  Save the user's first name to the database:
$stmt = $mysqli->prepare("INSERT INTO users(first_name) VALUES (?)");
$stmt->bind_param("s", $first_name);
$stmt->execute();

// Echo the user's first name back to them
echo "Saved your first name: " . 
      htmlspecialchars($first_name) . " to the database.";

    For more information on preventing SQL injection, see this excellent answer: How can I prevent SQL injection in PHP?