Mysql real escape string and SQL select

Question

Question, if you mysql_real_escape_string your variable, how can it still work with you SELECT SQL query. For example:

My row has a column that holds the value 's-Gravenmoer. Now The user types in 's-Gravenmoer and I will escape this entered value for safety. However, the row with the colums that holds the value 's-Gravenmoer is never going to pop up, it added slahes before the single quote. If there a way to do this with mysql_real_escape_string?

Thanks guys! Sander

What's point? You have too many answers with this on stackoverflow. Look first on Stackoverflow tutorial info: stackoverflow.com/tags/php/info or this: php.net/language.types.string — Marin Sagovac
– Marin Sagovac, Commented Oct 16, 2012 at 20:51
So there are actually slashes in the database? Then just don't escape twice. You might want to check (read: disable) your magic_quotes setting: php.net/manual/en/security.magicquotes.php — Fabian Schmengler
– Fabian Schmengler, Commented Oct 16, 2012 at 20:52
net.tutsplus.com/tutorials/php/… PDO, and save yourself some headaches, this is a decent tutorial. It's much safer and you don't have to be as worried that you're going have security issues on the level using the mysql functions should keep you worried. — Adam
– Adam, Commented Oct 16, 2012 at 20:53
Using the mysql series of functions is a waste of time. Please switch to mysqli or PDO to save yourself all of this hassle. — tadman
– tadman, Commented Oct 16, 2012 at 20:54

Marc B · Accepted Answer · 2012-10-16 20:56:13Z

You misunderstand the point of escaping data for SQL usage. It doesn't permanently modify the data. Think of it as wrapping paper around a gift. You're making a gift of some data to the database, and such you wrap it up nicely (real_escape_string). Once it reaches the database, the DB server unwraps the gift (removes the escaping) and puts the 'gift' into its stash.

At no time would the backslashes you added EVER appear in the stored data, because they're removed by the DB server as it's doing the actual insertion. The escapes are there purely to ensure that whatever data you've inserted into the query do not "break" the SQL statement, e.g.

$name = "Miles O'Brien"; // <---note the '
$sql = "INSERT INTO people (names) VALUES ('$name')";

resulting sql:

INSERT INTO people (names) VALUES ('Miles O'Brien')
                                           ^--- oops, string terminated earlier, what's this "Brien" field/keyword?

with real_escape_string, your query looks like

... VALUES ('Miles O\'Brien')
                    ^^---no longer an SQL string delimiter, treat it like any other char now.

I found that my problem is that the system adds three slashes thanks to a brilliant function called 'magic quotes....'. Gonna work on that now, but thanks for your answer!

Collectives™ on Stack Overflow

Mysql real escape string and SQL select

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related