Protecting against SQL injection attacks

Ah, another “Updates Tuesday” done although it will be a busy time for a few days yet. We often get questions after we release security bulletins which range from the very simple “Should I install this critical update?” to the horribly complex “Would any OLE streams in the compound document format allow remote code execution given that only PowerPoint and Excel are installed on this system?”

However, today I will be continuing the theme of secure code. By the way, feel free to chip in with any comments or questions unless you are the spammer who tried to add 300 comments to one blog entry linking to his site which appears to be trying to do a “drive by” infection. The comments were NOT published and he or she may be disappointed to learn that the drive by was completely ineffective against my nice 64 bit browser.

So, secure code. With security as with all things, you want the biggest bang for your buck. The easiest type of hack and one of the most common is the SQL injection attack. I have been asked in the past if this is a bug in SQL or Oracle or MySQL or your database of choice. The simple answer is “no”. The database is doing exactly what your application asked it to do. It may not have been what your application wanted it to do but that is another matter.

It is also one that is easy to defend against. Validation and reduction of attack surface are the way.

As I said in a previous post, most developers assume that input data is correct or harmless nonsense. SQL injection attacks work by entering SQL code that will have a malicious effect in to a web browser or a local application – which is often ignored. Hacks can come from within an organization, especially when the workforce has a grievance with the company. Internal hacks often occur when a company has announced staff reductions. The protection is to prevent SQL being entered as much as possible. Before appending a string, validate it as much as possible. Entry fields basically fall into 3 classes:

Numbers – Easy to validate. Some languages even have functions to ask if a string is a pure numeric. Oh, small oddity here. Such functions will normally pass 1e2 and 1d2 as pure numerics. That is because they are valid scientific notation. They are harmless here so let us pass on

Formatted fields – Dates; part numbers in a fixed format such as 3 letters, 2 alpha followed by 4 numbers; 3 letter airport codes. These require specific validation but it is easy enough to do and good hygiene even if security is not your first concern

Free text – This is the tricky one and the most important. You have to pick a reasonable set of restrictions. Double quotes (character 34) are normally a bad thing in text. Brackets may be unreasonable. In a lot of applications, taking out anything that is not A-Z, a-z or 0-9 is acceptable.

If you are lazy *cough* efficient in your coding, you will want to do this with the smallest code change if you are doing this as a maintenance task. The easiest way is normally to write validation routines that take a string and return a string – and return whatever they are passed minus the dodgy characters. You can insert a call to this routine into a function call and thus sanitize the data on the fly. It is a bit sloppy since the query will fail without necessarily giving a terribly informative error but it is cheap and safe.

Reducing the attack surface is always good and I will return to it another time but for now, let us consider the default stored procedures on SQL. Do you need all of those? At the very least you should rename xp_cmdshell. The more obstacles you put in the way of a hacker or malware, the safer you are. It might seem unnecessary to take out stored procedures if you are validating all input but defense in depth is always safest. If you miss only one field, that could be all that is needed. Even if you are sure that you will never miss a trick, are you sure that the placement student will never make a mistake? Belt and Braces for me!

Until next time. Unless I get any questions, I will be talking about trust boundaries.

Mark

Comments

• Anonymous
  May 30, 2007
  The comment has been removed

• Anonymous
  June 04, 2007
  Hey Mark, I want to know more about how to protect SQL Injection, maybe using anomaly detection systems. It will be great if you can throw some light on this too. My email id is sweth75@gmail.com. Thank you. Swetha.

• Anonymous
  June 04, 2007
  Hi Swetha I would be happy to discuss this but I would sooner do it here so that anyone who is interested can read. I covered the basics above but is there is a specific area that you are interested in? Also, what environment would you like to discuss as the details differ a bit Thanks Mark

• Anonymous
  June 04, 2007
  Hey Will, it has been a while. Hope that you are doing well. Yes, I will be addressing buffer overruns in a future blog. There isn't that much to them but it is quite interesting all the same Thanks Mark