mysql_real_escape_string adds a slash '\' when saving word with apostrophe in it to database

Question

Possible Duplicate:
mysql_real_escape_string and single quote

EDIT: I have checked and my hosting service' mysql database is set up as:

  Language: English (en-utf-8)
   MySQL charset: UTF-8 Unicode (utf8)

The 'userItem' variable below came from a user-entered text string on a form.

When I type in "this is A's user item" -- and I use mysql_real_escape_string on it -- here's what is saved to the database:

       "this is A\'s user item"

BAD. A backslsh got saved with my test string.

If I comment out the mysql_real_escape_string($userItem) and just use $userItem in the query string -- this is what gets stored in the database:

       "this is A's user item"

GOOD!! No backslash.

But let's face it, I really want to use mysql_real_escape_string for safety reasons -- I've been led to believe it is safer to use mysql_real_escape_string on strings before saving them to the database.

THE PROBLEM: when I retrieve my text string "this is A\'s user item" and display it in the browser -- it has the slash in the string. And it surprises the user -- they did NOT type "this is A\'s user item", they typed "this is A's user item".

Here is the code:

 $newItemInsertQuery = "INSERT INTO " . Dbases::$USERITEMS_TABLE 
   . " VALUES "
   . "('" 
   . mysql_real_escape_string( $loggedInUser ) . "', '" 
  //. mysql_real_escape_string($userItem) . "', '" COMMENTED OUT due to extraneous backslash
   . $userItem . "', '"
   . mysql_real_escape_string($description) . "', '" 
   . mysql_real_escape_string($itemImage) . "', '"
   . mysql_real_escape_string($userSubfolder) .  "')";

   $result = mysql_query($newItemInsertQuery);

I suspect the following;

• my expectations that it is a good thing to use mysql_real_escape_string on strings prior to a database insert is valid.

• but I'm unaware of some other step that must be taken

Answer 1

I highly suggest using parameterized queries via either mysqli or PDO (both of which are built-in to PHP).

mysql_real_escape_string() is a stopgap measure at best. Using parameterized queries is both much less of a hassle (avoiding issues like this) and more secure - plus for many things it can also be more efficient.

It will also make it much clearer exactly what data is being passed in and going into your database, since it does not need to perform any escaping of any kind.