This is a guest post by Cigital’s resident songwriter Paco Hope.
Sometimes we just let our hair down and have a good laugh over security stuff. I was telling my wife about some ridiculous code I had recently reviewed. While I didn’t go into details, she got the gist of it (she also gets “Little Bobby Tables,” too). I finished my story by offhandedly saying “there must be 50 ways to bypass their SQL protections.” She responded by singing “there must be 50 ways to inject your SQL” and that’s how the whole thing got started.
So what was it that was so ridiculous that it brought me to sing? Here’s the pseudocode of a routine called “ValidateQuery()”. Its role is to take user input from a .Net application and validate it to remove potential injection.
ValidateQuery():
WordArray = TokenizeString( ‘ ‘ ); /* split on space characters */
For each Word in WordArray
if it is ‘delete’ then return
if it is ‘purge’ then return
if it is ‘drop’ then return
if it is ‘truncate’ then return
End For
That’s it. If your string contained anything else (for example ‘update’), it would be fine. They did do a case-insensitive match, so ‘dElEtE’ would get caught, but that’s hardly praiseworthy. This was the sole protection against user input that was fed—good ole string-concatenation style—into SQL queries.
Note that this is NOT a web application, this was a Windows EXE written in C#.Net. For those of you who think SQL injection is a web only problem, think again. The other classic screw-up in this application was putting all the enforcement of roles and access controls in the client itself. While this may work when your business logic runs on an application server in a 3-tier architecture (that presumably cannot be controlled or modified by the end user), that approach doesn’t work when the business logic runs on the user’s PC. A quick SQL injection to change your roles in the database, and this thick client would start working for you just fine—as admin, or a higher privileged user.
The motherhood and apple pie lessons to take away?
- SQL Injection can happen, even in EXEs
- Don’t roll your own protection, use parameterized queries
- Don’t trust business logic that runs on a user’s PC
Musically, I’m pretty proud of the fact that I managed to use the words “unhandled exception” in a song. They fit the rime and meter perfectly. I’m trying to come up with a tune to parody for cross-site scripting. If you have ideas, I’m open. I think I’ll get someone else to sing next time. A kind of Gilbert & Sullivan arrangement where I’ll just be the librettist. :)
