mysql real escape string - is this normal?

Question

When I use mysqli->real_escape_string and add a string value to the db such as "who's", it adds the string without modifying the quote in any way. When I check the db, the value within it is "who's".

When I do not use mysqli->real_escape_string and add a string with a single quote like "who's", it just doesn't get added to the database. It is nowhere to be found.

This isn't normal, is it?

How are you viewing the database? This might be the reason: cleaning data for ease of reading. — Francisc, Jul 11 '12 at 23:50
I think it depends on version, but it might be unstripping slashes so that you can read data more easily. Try to look at the data from the MySQL Bash for example. Also, are your MySQL settings default or are they altered? — Francisc, Jul 11 '12 at 23:53

score 2 · Accepted Answer · answered Jul 11 '12 at 23:50

2

Yes, it is. It's a little difficult to answer this question in any more detailed a way than that.

real_escape_string() only escapes the data so it can be safely used in a query, it doesn't modify it in any way. When you don't escape it, the query has a syntax error in it so it fails - and the data is not inserted.

However the safest way to escape your data is to use prepared statements.

answered Jul 11 '12 at 23:50

DaveRandom

87,921
11
154
174

According to the manual, it escapes the followig: `Characters encoded are NUL (ASCII 0), \n, \r, \, ', ", and Control-Z`. – Francisc Jul 11 '12 at 23:52
I was looking at this url, and Powerlords comment made me think it should be escaped: http://stackoverflow.com/questions/2289214/how-can-i-tell-if-mysql-real-escape-string-is-working – steeped Jul 11 '12 at 23:54
@steeped it is escaped - the `'` in the string that `real_escape_string()` returns is escaped with a backslash, but when the data is stored in the database the data is stored without the backslash. The escaped character is only there to make the query syntactically valid. – DaveRandom Jul 11 '12 at 23:57

score 1 · Answer 2 · answered Jul 11 '12 at 23:53

It is normal, and correct. the real_escape_string allows your string to be added to the database (including the quote). Without it, the apostrophe is interfering with the SQL, thus likely throwing an error, and that's why it's "nowhere to be found".

score 1 · Answer 3 · answered Jul 12 '12 at 00:31

What real_escape_string does is, it escapes necessary characters only while adding these in the database. So it's like escaping the same type of quote in a string in PHP, for example:

$str = 'We escape single quotes like in O\'Connor';

And the single quote (apostrophe) there is to prevent the code from breaking, but the string value does not actually contain it. Likewise…

INSERT INTO table (`name`) VALUES ('O\'Connor')

in this example the backslash will not be inserted in the row. This is for security reasons, of course. Check Hackipedia's page to see some possible cases.

Hoping this was what you were asking.

mysql real escape string - is this normal?

3 Answers3