When Should I Use PHP mysqli_real_escape_string()

Question

This question already has answers here:

How can I prevent SQL injection in PHP? (28 answers)

Closed 3 years ago.

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?

Answer 1

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?

Answer 2

You should use real_escape_string on any parameter you're mixing as a string literal into the sql statement. And only on those string literal values.

Therefore the description of Situation 01 and Situation 02 is not sufficient to answer those concrete questions. It's probably yes.