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. :)

IEEE S&P: Securing Online Games (vol.7, no.3)

IEEE Security & Privacy magazine remains the most important trade periodical on security published today. Though the content is on rare occasion esoteric, the magazine is always technically accurate and detailed. Only a peer reviewed publication can offer readers a look at computer security as a science. Think of it as applied academics.

I am pleased to announce that IEEE Security & Privacy magazine’s May/June 2009 edition was recently released. The issue (volume 7: number 3) covers Securing Online Games in a series of four peer reviewed articles that help define the state of the practice.

For more about the issue, see: http://www.computer.org/portal/site/security

Also in the same issue is a print transcript of Silver Bullet 36. That’s the episode where James McGovern turned the tables and interviewed me. That transcript is on the web here.

Beautiful Security

Also of note is a book of thoughtful essays on security put together by John Viega (once a Cigitalite) and Andy Oram. Here’s my original blurb from the back cover:

“This collection of thoughtful essays catapults the reader well beyond deceptively shiny security FUD (the drum major of the bug parade) toward the more subtle beauty of building security in. Security is an essential emergent property for all modern systems—something that most people implicitly expect and few people explicitly receive. This book demonstrates the yin and the yang of security, and the fundamental creative tension between the spectacularly destructive and the brilliantly constructive. Read. Learn. Emulate.”

I’m still working my way a second time through the assembled essays from security stars including mudge, Betsy Nichols, Phil Zimmermann, Mark Curphy, and Jim Routh. This is a must read for 2009.