Backslashes in database

[neophyte]

Hello All -

This may be a stupid question, but I'm using mysql-real-escape-string() to format text strings before inclusion into my MySql DB.

Problem is (maybe) that when I check the db, I don't see any backslashes escaping single and double quotes.

I know that I'm using MRES() correctly - implementing it right before the DB INSERT - so this is kinda driving me crazy.

Are these backslashes really there... but perhaps are just being cloaked by my DB client (Navicat)?

[httpwebwitch]

no - the backslashes won't be in the database. You don't want them there anyways.

What you should have in your database is perfect, clean, raw and unslashed data. So if you decide to use that data you don't have to "unslash" it, unencode it, or anything like that.

What mysql_real_escape_string() does is add slashes to a string for inclusion in a SQL query, in case the string has quotes in it. It escapes the data so it can be enclosed in quotes without any funny things happening.

For example...

$query = "UPDATE table SET field = '" . $name . "'";

if $name has an apostrophe in it, the query will become:

UPDATE table SET field = 'O'Reilly'

see the problem there? SQL is going to hate that. And it's a SQL injection vulnerability.

if you use mysql_real_escape_string():

$query = "UPDATE table SET field = 'O'Reilly'" . mysql_real_escape_string($name) ."'";

then the query becomes

UPDATE table SET field = 'O\'Reilly'

and what gets put in your database is

O'Reilly

[httpwebwitch]

You'll know you're doing it right when you enter some data with apostrophes and quotes and backslashes in it, and when you look in the database you see exactly what you typed.

I'll test every field in every form by entering a little ascii art:

/"*"\'x'/

Then in navicat, look at the data and it should look exactly like that with no extra backslashes, and all the quotes should be exactly as they were typed.

If I'm feeling like it, I'll put some Chinese and Hebrew characters in too, to make sure that the data is being stored properly with UTF-8

If you pass that test, your SQL is safe

[neophyte]

httpwebwitch -

Thanks very very much for the explanation! Very helpful.