0

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?

Eamonn
  • 1,338
  • 2
  • 21
  • 53

2 Answers2

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.

Jonathon Reinhart
  • 132,704
  • 33
  • 254
  • 328
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

a1ex07
  • 36,826
  • 12
  • 90
  • 103
  • Yes, parameterisation seems to be the way to go. I was wondering, however, how necessary it would be for someone to rewrite so many queries for that purpose when those queries had already been escaped... – Eamonn Jan 08 '12 at 18:30
  • @Eamonn: In my opinion, that all depends on the project. Sometimes it's not possible to implement all the best practices because of limited time/human resources... Also, the cost of such refactoring is tied with overall application design. Keeping all sql queries in separate module, and accessing db only through dedicated data access layer significantly reduces amount of work. – a1ex07 Jan 08 '12 at 18:39
  • It certainly seems very efficient for larger applications - as a futher query, do you know of a good resource to guide me in building such a framework, or is it very much a case of whatever suits the moment? – Eamonn Jan 08 '12 at 18:48
  • I can recommend some books that might be useful (in my subjective opinion). They cover different aspects of software engineering and help building robust systems. Domain Driven Design by Eric Evans, Working Effectively with Legacy Code by Michael Feather, Refactoring to Patterns by Joshua Kerievsky, Clean Code: A Handbook of Agile Software Craftsmanship by Robert C. Martin. – a1ex07 Jan 08 '12 at 18:59
  • Thats great, thanks for that - very helpful! I will certainly look all of those up. I'm going to hold off on selecting the answer for a bit longer, as I'm curious to see what flaws people will point out on a purely real_escaped system. But thanks again! – Eamonn Jan 08 '12 at 19:18