Mysql Php Escaping single quotes with real_escape_string

Question

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?

You probably have magic quotes on. stackoverflow.com/questions/220437/magic-quotes-in-php — Pekka
– Pekka, Commented Feb 6, 2011 at 19:39
magic_quotes (when on) adds automatically a backslash, so mysql_real_escape_string will add another one. Use the method I posted below, it should work in all cases (magic_quotes on or not) — Damien Pirsy
– Damien Pirsy, Commented Feb 6, 2011 at 19:46

Damien Pirsy · Accepted Answer · 2011-02-06 19:45:30Z

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

answered Feb 6, 2011 at 19:45

Damien Pirsy

25.5k8 gold badges72 silver badges77 bronze badges

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

10 Comments

Damien Pirsy Over a year ago

Does this snippet returns you backslashes nonetheless?

andrew Over a year ago

When I use mysqli::real_escape_string I get backslashes added to my query.

andrew Over a year ago

Here is my url: intranet_newbase/…'

andrew Over a year ago

And slashes are being added to the post_id. I tried stripping slashes before real_escape_string but that did not help

Damien Pirsy Over a year ago

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.

|

Collectives™ on Stack Overflow

Mysql Php Escaping single quotes with real_escape_string

1 Answer 1

10 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Linked

Related