6

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?

Sasa1234
  • 898
  • 2
  • 13
  • 21
  • I'd recommend just always using parameterized queries. When you forget to use this function in some query you might have made a huge error which jeopardizes all data. With the parameterized you have to write the query to be susceptible. http://php.net/manual/en/mysqli.quickstart.prepared-statements.php – chris85 Mar 22 '16 at 03:48

2 Answers2

7

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?

Community
  • 1
  • 1
gabe.
  • 499
  • 2
  • 11
  • 1
    But please keep in mind to use real_escape_string _only_ on string literals, http://dev.mysql.com/doc/refman/5.7/en/string-literals.html . It's the only thing the function is designed for. http://dev.mysql.com/doc/refman/5.7/en/mysql-real-escape-string.html: `This function creates a legal SQL string for use in an SQL statement`. Nothing more, nothing less. – VolkerK Mar 22 '16 at 03:58
  • 1
    And [prepared, parametrized statements](http://docs.php.net/manual/en/mysqli.quickstart.prepared-statements.php) e.g. require more network roundtrips. IMO it's still the best default cookbook recipe to offer. Should you ever hit its limitation ...you will know ;-) – VolkerK Mar 22 '16 at 04:02
  • Thanks @VolkerK -- I updated my answer to try to make that more clear. – gabe. Mar 22 '16 at 04:02
  • @VolkerK Because of I am in shared hosting and what I am building is something like social network server resources are really important me. However, I could not understand your comment clearly. Can you please explain little bit. – Sasa1234 Mar 22 '16 at 04:05
  • 2
    Just ignore that comment for now (or maybe ever). a) Before it becomes relevant there are _so_ many other things you would/could/should optimize. And b) imo you would rather want to change your code coming from the parametrized (server-side) statement side of things than the other way round. cheeky version: Once a comparision between your project and google/facebook isn't laughable it _might_ or might not become relevant ;-) – VolkerK Mar 22 '16 at 04:16
  • So you are going to say that if you are dealing with data you are sure of, it doesn't have to be formatted? That's quite a peculiar idea. @Sasa you ought to accept an answer from VolkerK, as it's clear and direct answer to your question. While this one is just repeating old superstitions on "making data safe" and "user submitted data" – Your Common Sense Mar 22 '16 at 06:21
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.

VolkerK
  • 95,432
  • 20
  • 163
  • 226
  • Thanks. It means I have to use mysqli_real_escape_string almost every where? – Sasa1234 Mar 22 '16 at 03:42
  • 1
    No, it doesn't exactly mean that ;-) But ...often. You should not use real_ecape_string for number literals like e.g. `WHERE ID>5`; you wouldn't treat the `5` with escape_string but make sure that it's really a number literal. And you wouldn't treat identifiers with real_ecape_string, e.g. table/field names. But if you put it in single-quotes like `WHERE id='5'` then you have to use mysql_real_escape_string; (almost completely) regardless of the source of the parameter and regardless of whether you "think" the string literal will only contain digits. String literal->real_escape, no discussion. – VolkerK Mar 22 '16 at 03:45