0

I am having trouble figuring out how to clean my strings for safe queries while maintaining the meaning of the string. Given the table where some of the values have single quotes or other escapable characters. How do I use real_escape_string and still select those values?

my_table

Col1     Col2
-----------------
1        value's1
2        value's2

value's1 is coming from the url so I have to clean it with mysqli::real_escape_string Which means that my query looks like this

SELECT Col1,Col2 FROM my_table WHERE Col2 = 'value\'s1'

And of course because of this I am getting no results returned.

What are the various strategies for dealing with this problem?

Note: Just did phpinfo() and magic_quotes_gpc is 'off'. Is it neccessary for me to clean this value I don't see how someone could do an sql injection when php only allows one query at a time? Am i just being over cautious?

andrew
  • 5,096
  • 10
  • 43
  • 57

1 Answers1

1
if(get_magic_quotes_gpc())
{
    $string = stripslashes($string);
            $string = mysqli_real_escape_string($string);
}
else
{
    $string = mysqli_real_escape_string($string);
}

You might want to make a function out of this

Damien Pirsy
  • 25,319
  • 8
  • 70
  • 77
  • Does this snippet returns you backslashes nonetheless? – Damien Pirsy Feb 06 '11 at 19:49
  • When I use mysqli::real_escape_string I get backslashes added to my query. – andrew Feb 06 '11 at 19:50
  • Here is my url: http://intranet_newbase/index.php?bb=post_details&post_id=20001,1,'2011-02-06%2000:00:00' – andrew Feb 06 '11 at 19:55
  • And slashes are being added to the post_id. I tried stripping slashes before real_escape_string but that did not help – andrew Feb 06 '11 at 19:56
  • Uhm...try inserting `value's1` (resulting from the above function) with a INSERT statement, and then look at the database in phpmyadmin (or whatever) and look at the field. – Damien Pirsy Feb 06 '11 at 20:03
  • I am echoing out the query which is built by php. The reason that it is not working is because slashes are being added to the string. I remove real_escape_string and it works fine because slashes are not being added.Is this not the normal behaviour of real_escape_string? – andrew Feb 06 '11 at 20:10
  • No...mysql_real_escape_string ADDS slashes before ' and " to avoid SQL injection (in worst cases) or simple query failing..I mean, slashes should not be there BEFORE mysql_real_escape_string. Anyway, try an INSERT like I suggested and look at the DB, you should not see the slashes (but of course are there, otherwise the query would have returned an error). A question: what's the code you use to retrieve that value from url? does it pass through some sort of filtering function? – Damien Pirsy Feb 06 '11 at 20:17
  • At worst (but I think it's an ugly solution) you could always do somethng like `$string = mysqli::real_escape_string(stripslashes($string))` – Damien Pirsy Feb 06 '11 at 20:21
  • Interesting, I don't think that I quite understand how real_escape_string works. So do I need to strip the slashes from the values when I am retrieving them from the database? If not is this done automatically somehow. I think that slashes might already be in the string like you suggest and therefore slashes are being added to slashes. – andrew Feb 07 '11 at 04:38
  • Well, so, exactly, how are the different stages in your code? Try echoing at each stage, right from when you take the value up until you insert in into your DB, you'll eventually see where slashes are added! And, moreover, check if you have `get_magic_quotes_runtime()` on or off! (in phpinfo() or just write something like `if(get_magic_quotes_runtime()) echo "they're on!"`. I'm not sure bu I think this value overrides `magic_quotes_gpc`, but look at the manual I'm starting to be at loss with this :) – Damien Pirsy Feb 07 '11 at 06:39