Mysql_Real_Escape_String VS Addslashes

mysql_real_escape_string VS addslashes

What you quote is probably from the doc, but as far as I know it's not necessarily true.

addslashes adds slashes to characters that are commonly disturbing. mysql_real_escape_string escapes whatever MySQL needs to be escaped. This may be more or less characters than what addslashes takes care of.

Also, mysql_real_escape_string will not necessarily add slashes to escape. While I think it works if you do it that way, recent versions of MySQL escape quotes by putting two of them together instead of by putting a slash before it.

I believe you should always use your data provider's escape function instead of addslashes, because addslashes may either do too much or not enough work for the purpose you use it. On the other hand, mysql_real_escape_string knows what to do to prepare a string for embedding it in a query. Even if the specs change about how to escape stuff and suddenly it's not backslashes that you should use anymore, your code will still work because mysql_real_escape_string will be aware of it.

What is the difference between mysql_real_escape_string and addslashes?

string mysql_real_escape_string ( string $unescaped_string [, resource $link_identifier ] )

mysql_real_escape_string() calls MySQL's library function mysql_real_escape_string, which prepends backslashes to the following characters: \x00, \n, \r, \, ', " and \x1a.

string addslashes ( string $str )

Returns a string with backslashes before characters that need to be quoted in database queries etc. These characters are single quote ('), double quote ("), backslash (\) and NUL (the NULL byte).

They affect different characters. mysql_real_escape_string is specific to MySQL. Addslashes is just a general function which may apply to other things as well as MySQL.

What does mysql_real_escape_string() do that addslashes() doesn't?

Addslashes is generally not good enough when dealing with multibyte encoded strings.

What's the difference between PHP's addslashes and mysql(i)_escape_string?

First of all: do not use mysql_escape_string, it is deprecated (for a reason)!

If you have to support a legacy application that connects to the database through the mysql extension (which has been deprecated), use mysql_real_escape_string instead. Otherwise switch immediately to mysqli, where prepared statements and bound parameters provide a more robust mechanism for escaping user input.

That said, the answer can be found by reading the description of mysql_real_escape_string and addslashes:

Difference #1

addslashes does not know anything about MySql connection encodings. If you pass it a string containing bytes representing an encoding other than the encoding used by the MySql connection, it will happily escape all bytes having the values of the characters ', ", \ and \x00. This may not be the same as all the characters ', ", \ and \x00 if you are using an encoding other than 8-bit encodings and UTF-8. The result will be that the string received by MySql will be corrupted.

To trigger this bug, try using iconv to convert your variable to UTF-16 and then escape it with addslashes. See what your database receives.

This is one reason why addslashes should not be used for escaping.

Difference #2

In contrast to addslashes, mysql_real_escape_string also escapes the characters \r, \n, and \x1a. It appears that these characters have to be escaped as well when talking to MySql, otherwise a malformed query may be the result

This is the other reason why addslashes should not be used for escaping.

Confusion about mysql_real_escape_string and strip_slashes

Thank you everyone for the answers. I will award the +50 out, but I wanted to tell my real solution here, all which people did help with...

I was performing mysql_real_escape_string on all of the data AS SOON as it posted (before any processing). So, a slash was added to escape the ' character that was submitted. This, we know is normal.

However, there was no reason that the backslash \ should show up in the DB entry, right? The escape was there to be sure the ' was entered.

Turns out, AFTER escaping, I would then save the variable to be reloaded to the page in the session, in case the user had an error that PHP found while validating all of the form fields. In this case, the user's input (formerly O'riley was now printed to their screen as O\'riley. Then, the user didn't catch this - so they would often just fix their error that PHP caught during validation (unrelated to the name field), and thus the O\'riley would land in the database because mysql_real_escape_string would escape the characters.

Lesson:
When processing a form, FIRST save data for form-refill use. SECOND validate form fields. THIRD escape the data for processing into the database.

Or better yet, use PDO and avoid this =).

Comments welcome. THANKS ALL!

Htmlentities vs addslashes vs mysqli_real_escape_string

There are different contexts for your data. The context of inserting data into the database needs to be escaped differently than the context of rendering html/xml or even an email message.

Escaping data going into a db should be deprecated in all new code in favor of prepared statements. Anyone who tells you otherwise is doing you a great disservice.

Escaping data going to the browser needs to be escaped in a number of different ways depending on the target. Sometimes htmlspecialchars is enough, sometimes you need to use htmlentities. Sometimes you need numeric entities. It is a topic you should do some research on to know all of the nuances.

The general rule I live by is validate (not filter, reject if incorrect) input & escape output (based on context).

Bug with mysql_real_escape_string and addslashes

Don't bother using addslashes at all, mysql_real_escape_string is more than capable of doing it on its own. So use:

$title = mysql_real_escape_string($_POST['title']);

And remove the stripslashes from your retrieval code. If the problem persists, you probably have one of the magic_quotes options on - if so, turn it off!

SQL Injection and addSlashes

PDO and MySQLi have built-in functions for creating prepared queries which let you use quotes and apostrophes without any problems.

But for your question: Replace addslashes() with mysql_real_escape_string().


addslashes can be passed by very easily and is not safe at all.

Read this article for more info about bypassing addslashes.

---

Related Topics