5

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:

  1. This function must always (with few exceptions) be used to make data safe before sending a query to MySQL.
  2. 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?

Community
  • 1
  • 1
undone
  • 7,857
  • 4
  • 44
  • 69
  • 1
    You should use [PDO](http://php.net/manual/en/book.pdo.php). Doing your personal website is a great opportunity to learn it. – kapa Apr 21 '12 at 14:36
  • Point 2 refers to `LIKE` clauses, and is not relevant for using string data in other contexts. – mario Apr 21 '12 at 14:36
  • one of the exception might be an already escaped string. – hjpotter92 Apr 21 '12 at 14:36
  • possible duplicate of [Can mysql_real_escape_string ALONE prevent all kinds of sql injection ?](http://stackoverflow.com/questions/9814642/can-mysql-real-escape-string-alone-prevent-all-kinds-of-sql-injection) – mario Apr 21 '12 at 14:39
  • possible duplicate of [SQL injection that gets around mysql_real_escape_string()](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) – mario Apr 21 '12 at 14:39

3 Answers3

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.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
  • 1
    simply with the same slash. `addCslashes($data,"%_");` will do the trick – Your Common Sense Apr 21 '12 at 14:50
  • 1
    But only do that for the pieces going into a pattern-matching context. Otherwise, you will see a literal `\%` in your SQL. – Matthew Apr 21 '12 at 14:51
  • @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? – undone Apr 21 '12 at 15:57
  • 1
    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: http://stackoverflow.com/a/9821406/285587 or, if you want, here is another one with full protection how-to: http://stackoverflow.com/a/2995163/285587 – Your Common Sense Apr 22 '12 at 06:23
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.

Jon
  • 428,835
  • 81
  • 738
  • 806
  • 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. – Your Common Sense Apr 21 '12 at 14:44
  • @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. – Jon Apr 21 '12 at 14:47
  • `However! It can never hurt you to escape all input,` congratulations, you just invented `magic quotes` feature! – Your Common Sense Apr 21 '12 at 14:52
  • @YourCommonSense: I 'm not going to dignify that with an answer. – Jon Apr 21 '12 at 14:53
  • 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 – Your Common Sense Apr 21 '12 at 14:55
  • @YourCommonSense: If you took just one second to step outside your self-centered view, you might realize that the OP almost certainly knows about quoting and that I do so as well. It's just not mentioned *because it should be taken for granted*. But I might as well ask for pigs to fly. – Jon Apr 21 '12 at 14:59
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.

Community
  • 1
  • 1
gopi1410
  • 6,567
  • 9
  • 41
  • 75
  • first link is for `MSSQL` not `mysql`:-) – undone Apr 21 '12 at 14:59
  • 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.. – gopi1410 Apr 21 '12 at 16:20