questions about mysql_real_escape_string

Question

I'm developing my personal web site using php. everything is ok but I just read mysql_real_escape_string manual in php.net and found two things:

This function must always (with few exceptions) be used to make data safe before sending a query to MySQL.

mysql_real_escape_string() does not escape % and _. These are wildcards in MySQL if combined with LIKE, GRANT, or REVOKE.

I have two questions:
1-what are these exceptions?
2- how to escape those characters?

You should use PDO. Doing your personal website is a great opportunity to learn it. — kapa
– kapa, Commented Apr 21, 2012 at 14:36
Point 2 refers to LIKE clauses, and is not relevant for using string data in other contexts. — mario
– mario, Commented Apr 21, 2012 at 14:36
possible duplicate of Can mysql_real_escape_string ALONE prevent all kinds of sql injection ? — mario
– mario, Commented Apr 21, 2012 at 14:39
possible duplicate of SQL injection that gets around mysql_real_escape_string() — mario
– mario, Commented Apr 21, 2012 at 14:39

Your Common Sense · Accepted Answer · 2012-04-21 15:04:26Z

5

This function must always (with few exceptions) be used to make data safe before sending a query to MySQL.

To my great disappointment, the manual page says complete rubbish, and they refuse to make it correct.
So, quite contrary, there are only few cases when you need this function. So to say ONLY ONE: when you are adding a string into SQL query.

mysql_real_escape_string() does not escape % and _. These are wildcards in MySQL if combined with LIKE, GRANT, or REVOKE.

It doesn't matter too much. As long as you are using LIKE operator on purpose, these characters won't do any harm.

But if you want to escape the string going to LIKE statement, you can use this code

$like = addCslashes($like,'\%_');

(note the slash - it is also required to be escaped as manual stating it. also note the C letter in the function name).
After this procedure you may use the resulting $like variable whatever way you are using to build your queries - either quote and escape them or use in the prepared statement.

edited Apr 21, 2012 at 15:04

answered Apr 21, 2012 at 14:37

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Your Common Sense Over a year ago

simply with the same slash. addCslashes($data,"%_"); will do the trick

Matthew Over a year ago

But only do that for the pieces going into a pattern-matching context. Otherwise, you will see a literal \% in your SQL.

Hassan Khodadadeh Over a year ago

@YourCommonSense I have one more question: I check input data by using regex and use returned data from mysql_real_escape_string in my sql queries. is it enough to prevent sql injection? if not, can you give me a good resource on this topic?

Your Common Sense Over a year ago

Both regex and escaping has nothing to do with injections. If you are "using data returned from mysql_real_escape_string in sql queries" it doesn't make your queries safe. A good enough resource is my answer to the question linked by mario in the comments: stackoverflow.com/a/9821406/285587 or, if you want, here is another one with full protection how-to: stackoverflow.com/a/2995163/285587

Jon · Accepted Answer · 2012-04-21 14:39:30Z

3

I 'm not sure what exceptions the manual is referring to when talking about making data safe. You could say that the exception is when the data is already known to be safe. For example, here are a few cases that come to mind:

the data is typed as a number (this is really a specialization of the next item)
you already know it does not contain any characters that need to be escaped (e.g. it comes from looking up something in a "whitelist" array that contains a few options you hardcoded)

For example, if you have $id = intval($_GET['id']) then you do not need to escape $id before injecting it into a query.

However! It can never hurt you to escape all input, and doing so eliminates the chance that you introduce vulnerabilities in your code (e.g. if you forget to escape, if the requirements change, or anything really). So I recommend getting into the habit of escaping everything and forgetting about "exceptions".

As for the % and _ characters as part of the input, these do not need to be escaped unless you are going to feed this input to a command that recognizes them. So for example, if you have a query like this:

$term = $_GET['term'];
$sql = sprintf("SELECT FROM table WHERE column LIKE '%%s%'",
               mysql_real_escape_string($term));

In this case, if the user types a % as part of $term it's reasonable to assume that they want to actually search for a literal %. Therefore in such cases you should escape % by replacing it with \% (\ is the default escape character). str_replace or strtr are two good options for this.

edited Apr 21, 2012 at 14:39

answered Apr 21, 2012 at 14:34

Jon

439k85 gold badges756 silver badges820 bronze badges

6 Comments

Your Common Sense Over a year ago

mysql_real_escape_string has nothing to do with safety. it is completely different matters (widely mistaked by PHP folks, I have to admit). As a matter of fact, escaping but merely a string formatting facility and nothing more. Even whatever "safe" data may contain a linebreak which have to be replaced for the logs readability. not to mention that to unsafe identifier it will do not a slightest good.

Jon Over a year ago

@YourCommonSense: Wrapping in quotes + mysql_real_escape_string = safe. It has everything to do with safety indirectly, because it's a prerequisite for what provides safety directly (quoting). Thanks for the DV anyway.

Your Common Sense Over a year ago

However! It can never hurt you to escape all input, congratulations, you just invented magic quotes feature!

Jon Over a year ago

@YourCommonSense: I 'm not going to dignify that with an answer.

Your Common Sense Over a year ago

You just prove the fact you do not entirely understand the matter. Were it was "It can never hurt you to escape and quote all input" - it were at least bearable. But this way it is going to be but a defamed magic quotes with ALL its drawbacks, disadvantages and vulnerabilities

|

Community · Accepted Answer · 2017-05-23 10:34:49Z

1

You may write your own function ;) See this thread for more information.

Else you may use the PDO library or any other such libraries.

edited May 23, 2017 at 10:34

CommunityBot

11 silver badge

answered Apr 21, 2012 at 14:42

gopi1410

6,65110 gold badges44 silver badges76 bronze badges

2 Comments

Hassan Khodadadeh Over a year ago

first link is for MSSQL not mysql:-)

gopi1410 Over a year ago

yeah, that was just to give an idea as how to implement the logic for writing a function manually for mysql. Same logic can be implemented for MySQL too..

Collectives™ on Stack Overflow

questions about mysql_real_escape_string

3 Answers 3

4 Comments

6 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related