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

Please echo `$userItem;` immediately before the `mysql_real_escape_string()`and post result - you might have additional escaping elsewhere. — Eugen Rieck, Apr 29 '12 at 17:15
@Eugen -- I've echoed out the $userItem and does not have any backslashes -- no other escaping being applied elsewhere. — wantTheBest, Apr 29 '12 at 17:24
Found this elsewhere: http://stackoverflow.com/questions/1522313/php-mysql-real-escape-string-stripslashes-leaving-multiple-slashes — GDP, Apr 29 '12 at 17:24
@wantTheBest where did you check it? Better uncomment it and echo the whole query — Your Common Sense, Apr 29 '12 at 17:29
this is the code that shows $userItem is not previously escaped: **echo "The useritem is: " . $userItem**; — wantTheBest, Apr 29 '12 at 17:30
**echo the query with $userItem contains the apostrophe and post it here** — Your Common Sense, Apr 29 '12 at 17:34
I just echoed the entire query string, WITHOUT the mysql_real_escape_string($userItem) -- IE. the query string inserts **$userItem** as shown in my sample code above -- and there is NO BACKSLASH IN THE echo'd QUERY STRING. And then no backslash in the database either. — wantTheBest, Apr 29 '12 at 17:37
How are you viewing the data that is stored in the database? — Hammerite, Apr 29 '12 at 18:09
@Hammerite -- I use the 1and1 hosting's phpMyAdmin tool that lets me view the database contents — wantTheBest, Apr 29 '12 at 18:32
my problem was that i was escaping the characters twice, make sure you're not doing mysql_real_escape_string in 2 places at the same time — Robert Sinclair, Jun 01 '17 at 20:16

Amber · Answer 1 · 2012-04-29T17:31:13.250

2

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.

edited Apr 29 '12 at 17:31

answered Apr 29 '12 at 17:25

Amber

507,862
82
626
550

4

That won't solve the problem (of the data being prematurely escaped) though. – Quentin Apr 29 '12 at 17:26
3

This should be a comment. It doesn't answer the question just points out the bad practice in it. – Esailija Apr 29 '12 at 17:27
there is nothing bad in this function *if used properly* – Your Common Sense Apr 29 '12 at 17:28
1

@YourCommonSense Aside from the fact that it's essentially asking for trouble... for instance if you forget to include quotes around its result when you substitute it in, et cetera. – Amber Apr 29 '12 at 17:29
1

it is not asking for anything. PDO using it's equivalent all the way. *Forgetting* is a subjective thing, and doesn't make the function itself bad. So, to make it straight, I'd agree with you partially: using this function in the code directly, not as a part of some higher level protection facility is apparently bad thing. – Your Common Sense Apr 29 '12 at 17:33
@YourCommonSense Some functions make it easier to forget things and have bad stuff happen than others do. That's no subjective, that's an objective standard for how hard it is to screw something up given use of a certain method. – Amber Apr 29 '12 at 17:34
Please, read my whole comment, not only first line :) – Your Common Sense Apr 29 '12 at 17:35
Great, a completely irrelevant answer with a pseudo forum thread going on in it. – Esailija Apr 29 '12 at 17:38

Your Common Sense · Answer 2 · 2012-04-29T17:48:51.667

0

it is safer to use mysql_real_escape_string on strings before saving them to the database.

it is not "safer".
It is a strict rule actually.

But your problem is coming not from this function, but from some other code or setting that spoils your data.

So, we have 2 problems here.

How to convince you that it is not this function to blame.
How to find the real cause.

For the first one please echo out the final query

echo $newItemInsertQuery;

and post here what you get

thank you for posting
now let's see
the query you posted will never be executed due to syntax error.

INSERT INTO useritems VALUES ('b@b.com', 'b' s 4th item', 'the 4th item', '', '')

see the emphasized part: here you have your string literal closed, without proper syntax keyword followed.

change your query call with this one

$result = mysql_query($newItemInsertQuery) or trigger_error(mysql_error()." ".$query);
exit;

and see

edited Apr 29 '12 at 17:48

answered Apr 29 '12 at 17:40

Your Common Sense

156,878
40
214
345

This is the echo'd query string prior to the insert into the database: **INSERT INTO useritems VALUES ('b@b.com', 'b's 4th item', 'the 4th item', '', '')** – wantTheBest Apr 29 '12 at 17:45
That seems unlikely, given that you have `'b's 4th item'` which would be a syntax error due to `'b'` being quoted string. Are you actually copy-pasting the output of your echo, or are you just re-typing it by hand? – Amber Apr 29 '12 at 17:48
@Amber -- that's the output. I have no incentive to dissemble here as it's probably (objectively) clear that I came here for help. But maybe the **manner** in which I output the query string to the browser is playing a roll? IDK, so here is my **debug output code** I've used since caveman days -- : **echo ''** – wantTheBest Apr 29 '12 at 18:08
@wantTheBest PLEASE JUST ECHO IT OUT AS A PLAIN TEXT – Your Common Sense Apr 29 '12 at 18:12
MEIN GOTT ES GEHT NICHT JETZT! I switched to a pure 'echo' and I have discovered that the alert() function in Javascript is REMOVING THE BACKSLASH. When I output to the browser using **echo** it reads: INSERT INTO useritems VALUES ('b@b.com', 'b\'s 6th item', 'cool 6th item', '', '') – wantTheBest Apr 29 '12 at 18:19
@wantTheBest do you have it with mysql_real_escape_string or without it? – Your Common Sense Apr 29 '12 at 18:24
That's without the mysql_real_escape_string -- that is the query string as shown above in my original post. I checked -- the form is form enctype="multipart/form-data" and the typed-in **userItem** arrives in the $_POST array then straight to my query string -- why does it have the backslash in it? I've done nothing (knowingly) to put a backslash before the apostrophe. – wantTheBest Apr 29 '12 at 18:29
@wantTheBest ther can be 2 reasons. One I mentioned in the "possible duplicate" comment couple hours ago. another is you are passing your POST data through some silly "general sanitizing" function. I bet for the first. – Your Common Sense Apr 29 '12 at 18:30
I added **php_flag magic_quotes_gpc Off** to my .htaccess and the 1and1 server displays: **Internal Server Error The server encountered an internal error or misconfiguration and was unable to complete your request.** – wantTheBest Apr 29 '12 at 18:43
I backed out the above change to my .htaccess and no more server error -- 1and1 apparently wants 'magic quotes' left on. – wantTheBest Apr 29 '12 at 18:50

wantTheBest · Answer 3 · 2012-04-29T20:23:37.363

0

I believe I've found the source of the extra backslash and also a solution. I'll post that here and come back to post the solution which I'm currently working on.

The form's input type="text" field is submitted and when the text string with an apostrophe in it is then extracted on the server side in PHP out of the $_POST array and displayed by way of an echo the slash is ALREADY IN THE TEXT STRING, like this:

"this is what\'s it look\'s like"

I am unable to use my .htaccess file in my website directory to turn off 'magic quotes' using the line

php_flag magic_quotes_gpc Off

as the 1and1 hosting server displays an error (see my comments about this above) and will not run my website.

I have a solution that I will post back once it is proven.

I have tested the following code and it works -- on my form, I type in the string "b's 6th item", and when the form is submitted, this code handles the form's POST:

 $itemName = $_POST['theItemname'];
 echo $itemName; 
 if (get_magic_quotes_gpc())
 {
    $stripped = stripslashes($itemName);
    echo "<br /> This is itemName string after stripping: " . $stripped;
 }

The output is:

     b\'s 6th item
     This is itemName string after stripping: b's 6th item

I have read your comments below and will look at all options. For right now, I needed a solution NOW and the above avoids calling stripslashes() unless 'magic quotes' is on -- it works.

I will ready the responses and try to find a more elegant way! Thanks to all.

edited Apr 29 '12 at 20:23

answered Apr 29 '12 at 19:58

wantTheBest

1,682
4
43
69

This is why the commenters on your question asked you to post what the string looked like when you echoed it without using it in a query. You said in answer to that that there was no apostrophe in it. – Hammerite Apr 29 '12 at 20:00
this solution is not a rocket science. you can find it here http://php.net/manual/en/security.magicquotes.disabling.php – Your Common Sense Apr 29 '12 at 20:03
the proper command have to be `php_flag magic_quotes_gpc 0` I believe, not `off` – Your Common Sense Apr 29 '12 at 20:03
On this page: http://www.boutell.com/newfaq/creating/magicquotes.html a method is outlined to undo magic-quotes without needing the ability to turn it off. Note however that you should really use PDO rather than the `mysql_*` functions. – Hammerite Apr 29 '12 at 20:04
1

It's the anonymous negging that has me using this site to my advantage and not giving back anything. Go ahead and neg me down to zero. There's no more highly effective way to FRICTION-UP this website than to 100% disincentivize users from WANTING TO GIVE BACK. No way am I ever answering any questions. You will have to rely on your 'whales' to answer all the questions. This "CURATION BY HUMILIATION" stuff? D-U-M-B. StackOverflow has a bad rep now because of it. Until I can 'block' anonymous users who hide behind anonymity and neg away instead of trying to help. – wantTheBest May 02 '12 at 03:02

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

3 Answers3