Thoughts On SQL Injection Attacks

While this post doesn't cover how to prevent SQL Injection Attacks (there are plenty of well written articles on the subject already) This is more a response to a blog post from Cameron Pitt that started as a comment on his blog and turned into a little more. You will probably want to read the blog post or none of this will make any sense.

This was one of my beefs with PHP back in the day, and I'd say even still: How easy it was (is) for amateur developers to put themselves into a really bad place and not even know it. A lot of these SQL Injection issues have changed with the introduction of mysqli bringing in the concept of prepared statements, but there is just so much legacy code out there its insane.

Even without legacy code a quick Google Search and then scan of the results for php mysql tutorial shows that all of these tutorials are using mysql as opposed to mysqli, and if they are using mysqli most are not using the Object Oriented Style and they are not taking advantage of prepared statements. Therefore you have new people coming in learning the language from Tutorials on the web that frankly in my opinion are giving out what is now considered Bad Practice.

As far as detecting SQL Injection attacks, I cannot think of a way for a web-host to implement this without becoming cumbersome to the developer. At some point the developer has to take responsibility for what code is executing and follow good practices. The following apply to just about any modern programming language: 

1. Use Prepared Statements
• This really is the answer, in this day and age there is no reason not to use them. Most modern languages have support for this (PHP-mysqli, .NET/C#-SqlCommand.Prepare())
2. If the above is not possible for some reason, Make sure you have some way to Sanitize all inputs.
• One of the 10 Commandments of C (specifically 5 in the linked version) states that you should ALWAYS validate user input, therefore 'Never Trust The User'
3. Use the Rule of Least Privilege
• Don't assign more privileges to the service account being used to insert data. If they don't need write permissions, don't give them write permissions. On numerous occasions I have seen developers simply set the GRANT ALL permission or running as the sa user when doing data access. While its debatable if this is even 'OK' for development work, it is absolutely unacceptable in any production code.
4. If you are at a large enough development shop don't allow your UI programmers to write Data Access Logic.
•  This goes back to my Core Belief of Specialization, the UI Programmers should do one thing and do it well: Program amazing front ends for your customers to deal with. The Database guys know their stuff and know all the little ins and outs of SQL Injection and can combat against it in their Data Access Layer.
• In the very same breath I say while you don't need to be an expert if you are not a DBA you should at least be aware of the issue.
5.  If you are a Mom and Pop Shop perhaps you should be looking at using Frameworks for your data access needs.
• It has been awhile since I have done any serious PHP work, but back in the day I seem to remember PEAR being all the rage, and that the days of writing your own Data Access Layer are dead.
• While I have some strong opinions on statements such as that above (which I'll save for another blog post) for a development shop that cannot afford to have a DBA this might prevent you from completely shooting your foot off.

Following best practices in general will stave off most unsophisticated attacks, but its hard to teach best practices to someone just learning the language now is it not?