Having trouble understanding mysql_real_escape_string

Discussions of secure PHP coding. Security in software is important, so don't be afraid to ask. And when answering: be anal. Nitpick. No security vulnerability is too small.

Moderator: General Moderators

Post Reply
weismana81
Forum Newbie
Posts: 20
Joined: Mon Feb 07, 2011 3:36 am

Having trouble understanding mysql_real_escape_string

Post by weismana81 »

This seems really basic, but I'm having some trouble getting my mind around mysql_real_escape_string. The main thing I'm having trouble with is how to handle the escaped content. For example...

Code: Select all

$comment = "You're awesome";
$comment = mysql_real_escape_string($comment);
echo $comment;
The result is of course "You\'re awesome". My question is, how do you safely display the comment without the "\". I could do a string replace, but wouldn't that just undo what the escape_string did?

I'm sure I'm missing something obvious. Any advice is appreciated! Thanks!!
User avatar
flying_circus
Forum Regular
Posts: 732
Joined: Wed Mar 05, 2008 10:23 pm
Location: Sunriver, OR

Re: Having trouble understanding mysql_real_escape_string

Post by flying_circus »

I'm not sure if it's just an example for the question, or how you are actually writing you code, but if its the latter, mysql_real_escape_string() function is the wrong tool for the job. If you want to strip the slashes from a string, you should consider using stripslashes().

Please note that the mysql_real_escape_string function is for escaping data before entry into a mysql database and that if you are working with mysql databases, you should really use the mysqli extension instead.. If you want to escape data before displaying in html, consider using htmlspecialchars() and html_entities().
User avatar
twinedev
Forum Regular
Posts: 984
Joined: Tue Sep 28, 2010 11:41 am
Location: Columbus, Ohio

Re: Having trouble understanding mysql_real_escape_string

Post by twinedev »

Here is how I program: A variable always holds the raw string. I only use mysql_real_escape_string() when using it in a query, and only use htmlspecialchars() when outputting it to the browser.

Code: Select all

$strData = "This isn't left & that isn't \"right\" (correct)';

$SQL = 'UPDATE `tblData` SET `myField` = "' . mysql_real_escape_string($strData) .'" WHERE `key`=33';
SQL statement will be[text]UPDATE `tblData` SET `myField` = "This isn\'t left & that isn't \"right\" (correct)" WHERE `key`=33[/text]

Code: Select all

echo 'Here is Greg's Quote: <br />' , htmlspecialchars($strData);
ECHOs (second line): [text]This isn't left & that isn't "right" (correct)[/text]
weismana81
Forum Newbie
Posts: 20
Joined: Mon Feb 07, 2011 3:36 am

Re: Having trouble understanding mysql_real_escape_string

Post by weismana81 »

Perfect! I think htmlspecialchars is what I was looking for. Thanks so much for the quick replies!!
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Re: Having trouble understanding mysql_real_escape_string

Post by Mordred »

Imagine every function you call like a tender baloon of different material and imagine the parameters you pass to it like objects dumped in the baloon.
Some baloons can be popped by sharp objects, so you need to wrap them with cotton.
Some baloons can be popped by hot objects, so you must first cool them.

To step back from the metaphor:
You must escape every datum you pass to certain functions and different functions have different means to do so.
You must not escape the data twice hoping that it will make it ready for using in two of these functions. Instead, use different copies of the data, escaping each one in its special way before giving it to a dangerous functions.
The two most dangerous functions are database queries, mysql_query in your case and HTML output - echo, print and <?=$var?>
There are others, and the best way to learn is to read the documentation on each function you use and check for possible security concerns.
For mysql_query, you mostly use mysql_real_escape_string. Read the article on SQL injection in my sig on when you need other things to use.
For html output, htmlspecialchars(), but there's a trick to it.

The problem with htmlspecialchars() is that it is insecure by default. There are many things, security-wise, that PHP developers have screwed up and this is among the top.
You must always call it with ENT_QUOTES as a second parameter, and the correct encoding as the third.
User avatar
Apollo
Forum Regular
Posts: 794
Joined: Wed Apr 30, 2008 2:34 am

Re: Having trouble understanding mysql_real_escape_string

Post by Apollo »

Mordred wrote:The problem with htmlspecialchars() is that it is insecure by default. There are many things, security-wise, that PHP developers have screwed up and this is among the top.
You must always call it with ENT_QUOTES as a second parameter, and the correct encoding as the third.
Can you elaborate on this?

The encoding param is obvious (after all, as Joel Spolsky says: There Ain't No Such Thing As Plain Text) but what's the deal with ENT_QUOTES? (assuming I'm correctly quoting any htmlspecialchar'd content with double quotes wherever applicable)
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Re: Having trouble understanding mysql_real_escape_string

Post by Mordred »

If you consistently use double quotes for attribute values then it's okay. People usually don't and the standard allows it, so it's ridiculous not to have this by default.
Post Reply