View Full Version : mysql_real_escape_string()

02-28-2007, 09:10 AM
Is it safe to use the php-function mysql_real_escape_string() on all data I put in a mysql db, nomatter what datatype it is?

Or should it just be used on varchar, char and text columns?

Since I have generic getter- and settermethods it whould be easy to escape and unescape everything whitout checking the datatype first...

02-28-2007, 03:47 PM
Yes, Yes, Yes!

Escaping data is not just about avoiding single or double quotes that might break you statement or corrupt your table data. It is also about avoiding SQL injection:

$public_posted_data = "'; SHOW TABLES;"
$sql = "SELECT * FROM tbl WHERE key='$public_posted_data'"

So, if you are just avoiding corrupt data in your db, don't use escaping on every datatype. Then again, if you don't want somebody to screw with your data and hack you site, USE it.

I'd also suggest using a database abstractor like Pear::DB as well that has prepared statements.

02-28-2007, 05:59 PM
Or you can do what I've been doing lately and use the "ctype_" functions in PHP to validate that the data is of a certain kind (alphanumeric, digits, etc.). For strings that shouldn't have anything other than letters and numbers (no ' or " or anything else), I do an if statement like this:

if (!ctype_alnum($variable)) {
//hold the presses! don't insert into mysql because this string is not just letters and numbers!

I actually built these into functions, which check user data for empty(), strlen() is too long or short, and for the correct ctype_. Pretty cool.