Page 1 of 1

Escaping for input to mySQL DB

Posted: Mon Jul 21, 2008 3:59 pm
by fluidbyte
I've seen a lot of different methods through searching on this topic, but was curious if there was a best practice for escaping content for input into a mySQL database.

I have a few fields, let's just say "Title", "Description", and "Content". The first two are just plain text, but Content is input with HTML formatting.

Any suggestions on a good way to approach this?

Re: Escaping for input to mySQL DB

Posted: Mon Jul 21, 2008 6:41 pm
by dyluck
Yeah, I will have to do this too...
Inserting objects with HTML code in the database.
I heard it is not best practice because it can leave the database vulnerable.
a lot of html has characters that could easily be identified as "malicious" by an escape string.
The only thing I can think of is using a function to pull and match pre determined strings out of the code like obvious and dangerous SQL injection attacks... before it gets inserted, selected or updated etc.
I am still curious as to an easy way to do this without leaving the database open to the vultures!

Re: Escaping for input to mySQL DB

Posted: Mon Jul 21, 2008 7:44 pm
by fluidbyte
For my use I'm not too concerned about SQL Injections; the users are authenticated and it's a CMS running their site, so I don't think they're going to be too malicious in their usage of the system. I'm just wondering if there are any characters that will cause an error.

I'm formerly an ASP developer and apostrophes always had to be replaced, just curious if there was a similar issue with PHP/MySQL.

Re: Escaping for input to mySQL DB

Posted: Mon Jul 21, 2008 7:53 pm
by dyluck
Well I have a personal forms for html database entry with Mysql and PHP and I did not get any character error issues. I set my database column as text.

I have not tried java though.

Re: Escaping for input to mySQL DB

Posted: Mon Jul 21, 2008 8:02 pm
by manixrock
The database should contain plain data, not escaped data.

If you have a string to enter in your database, use mysql_real_escape_string(), and sprintf() to ease your job:

Code: Select all

$query = sprintf('SELECT * FROM users WHERE user="%s" AND id=%d LIMIT 1', mysql_real_escape_string($user), $id);
Please note that I used ' to quote the string, as it is faster than " and you should use it always like that in mysql calls when using sprintf.

Also, when printing out the string don't forget to escape it if necessary. For example in html always use htmlspecialchars() if you want to display the source as a string (to avoid sql injections).