Preventing MySQL Injection Attacks in <textarea>

htmlentities($str, ent_quotes) VS. mysql_real_escape_string($str)

[whoisgregg]

Trying to preserve newlines from textarea blocks where the user may also be including some HTML.

After I strip out "nasty" html tags (<script>,eval, etc.), is running

htmlentities($textarea_clean,ENT_QUOTES)

sufficient to prevent injection attacks?

If I run mysql_real_escape_string() then my output ends up with "\n\r" all over it, stripslashing them just makes them "nr"

I can tell I'm missing something, but not sure what it is. :/

[coopster]

Are you asking what to do after a user submits a form page with a textarea in it that you are going to INSERT into a MySQL database table?

[whoisgregg]

Yes, a user fills out a

<textarea>

that could include HTML. I want to insert their data into a MySQL database.

However, I want to preserve their newlines and other data entry precisely so it can be echoed back into a

<textarea>

for future editing.

Seems that just running

mysql_real_escape_string()

when inserting then

stripslashes()

when echoing ends up turning newlines "\n\r" into the actual text string "\n\r"

What I have working well right now is that I

htmlentities($str, ent_quotes)

when inserting, then I use

html_entity_decode($str,ENT_NOQUOTES)

to display the data inline, and just echo the

$str

when putting it into a

<textarea>

for editing.

What I don't know is this:
A) Secure?
B) Best Practices?

Added: Thanks for fixing my title! :)

[coopster]

Accepting and writing data:

1. strip unwanted tags
   
2. mysql_real_escape_string
   
3. INSERT/UPDATE data in MySQL table

Retrieving and displaying data for editing in a textarea:

1. SELECT FROM table
   
2. print in textarea

Retrieving and displaying data as an HTML page:

1. SELECT FROM table
   
2. print to browser

The only issue you may have is how your server is configured to handle Magic Quotes [php.net]. Both of them off is best.

[whoisgregg]

Magic Quotes was my first suspect, phpinfo() shows Off for magic_quotes_gpc and magic_quotes_runtime.

Here's my test string:

<h5>"Testing"</h5>

<p>This is some text, don't misconstrue it's purpose.</p>

How it looks in my DB after I run mysql_real_escape_string() and INSERT it:

<h5>\"Testing\"</h5>\r\n\r\n<p>This is some text, don\'t misconstrue it\'s purpose.</p>

When I SELECT it then either echo or print it, I get:

<h5>\"Testing\"</h5>\r\n\r\n<p>This is some text, don\'t misconstrue it\'s purpose.</p>

If I stripslashes() then print, I get:

<h5>"Testing"</h5>rnrn<p>This is some text, don't misconstrue it's purpose.</p>

(My red)

So even though magic quotes is Off, it still looks like it's on. :(

[whoisgregg]

Realizing that my setup had all the familiar symptoms of magic_quotes without actually having magic_quotes, then realizing that I've done textareas without this problem before, I looked for other suspects. And oh wow, I feel so stupid.

I had just started using a PHP Class a few days ago to connect to and do all my database work. It runs mysql_real_escape_string() as part of it's default insert() function. Everything is working now that I've adjusted my scripts.

Sorry for wasting your time coopster. :(

[coopster]

I'm volunteering willfully, just like anybody else here, so don't feel bad. Sometimes we just need somebody else to help us step through to figure out where the bugger is ;)

Glad you got it sorted!