Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

When Should I Use PHP mysqli_real_escape_string() Function? [duplicate]

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?

like image 703
Sasa1234 Avatar asked Mar 22 '16 03:03

Sasa1234


People also ask

When should I use mysqli_real_escape_string?

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.

What is the use of mysqli_real_escape_string () function?

The mysqli_real_escape_string() function is used to escape characters in a string, making it legal to use in an SQL statement.

Do I need mysqli_real_escape_string?

Graham recently asked me: Do I still need to used mysqli_real_escape_string when used prepared statements in PHP? The simple answer is no. The way it used to work is that you would take form input data, put that into a variable, and inject that data into your MySQL query in order to add that data to the database.

Is mysqli_real_escape_string deprecated?

This extension was deprecated in PHP 5.5. 0, and it was removed in PHP 7.0.


2 Answers

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?

like image 134
gabe. Avatar answered Sep 23 '22 08:09

gabe.


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.

like image 34
VolkerK Avatar answered Sep 22 '22 08:09

VolkerK