MySQL escaped_strings VS. Parameterised Queries

Question

Just reading up on Parameterised queries, which seem to be the last word in database defence, and was wondering the following:

I have an existing PHP/MySQL self-built CMS where ALL inputs (bar checkboxes and radio buttons) are subject to real_escape_string. There is an admin section accessible via a sha1 encrypted password and a matching username where a small (3) group of trusted people can update content (tinyMCE), upload photos, etc. Strictly smalltime. None of my queries are parameterised, but are only executed after escaping.

There are no inputs taken from the general public, but I do want to open it up to user-submitted forms later.

My host is a private one, with a very good record.

All else being equal, how secure am I?

Answer 1

I'd rather use parametrized / prepared statements. Compared to just escaping input, they let you specify type which is handy (you don't have, for instance, to convert datetime values to server-specific format), and it also resolves ambiguity in handling conversion errors by different RDMS. For example, query SELECT * FROM table1 WHERE int_field='aaa' (int_field is integer) returns records with int_field equals 0 in Mysql, raises error in Oracle and SQLServer, and returns empty set in SQlite

Answer 2

mysql_real_escape_string does not immediately protect you from SQL injection attacks. I recently helped the developer of a small site who thought he was safe my calling mysql_real_escape_string on all of the inputs, but still got pwn'd. In his case, he was expecting a variable (via GET string) to be an integer, but was not verifying it as such. The attackers then utilized that ID field to craft custom queries and gain access to his entire database.

The moral of the story? Validate, validate, validate. You need to assume every possible piece of data coming from the outside (even if it's an <input type="hidden"> that you populate) is an attempt to bypass security measures. This is where using frameworks like Codeigniter, etc. are helpful, because they have validation components built right in. If you want to do everything yourself, just be careful, and check all input variables.