SQL injection is the FizzBuzz of web security

Posted at 08:00 on 19 January 2017

FizzBuzz is the (in)famous interview question designed to filter out totally unqualified candidates for a programming job at a very early stage in the process. The kind who can't solve even the very simplest programming problems and who would be wasting your time and money if you called them in for an interview after the phone screen.

You can—and should—do something similar for web security. Take a look at this snippet of Python code:

def check_password(username, password):
    db = MySQLdb.connect(passwd=DB_PASSWORD, db=DB_DATABASE)
    c = db.cursor()
    c.execute("SELECT password from tblUsers " +
        "WHERE username = \"" + username + "\"")
    row = c.fetchone()
    if row:
        return row[0] == password
    else:
        return False

Did you spot the problem? If you have any significant experience at all as a web developer, it should stand out to you like a sore thumb. You should be able to spot it in seconds, even if you have never used Python before in your life. Even if you're the kind of .NET-only developer who insists on being spoon-fed by Microsoft and believes that Python is a dangerous heresy, it should still be glaringly obvious to you.

A couple of years ago, I used a similar question to this one on a number of interview candidates—some of them with twenty or more years of experience at a variety of impressive sounding companies. Yet it shocked me just how many of them required very heavy prompting to see it.

If you're interviewing a candidate for a software developer role, show them this snippet of code. If they can't tell you in seconds that it contains a SQL injection vulnerability in line 5, don't hire them. If they can't tell you why it's a SQL injection vulnerability, don't hire them. No exceptions, no excuses.

SQL injection vulnerabilities are quite frankly inexcusable. Out of all the different kinds of security vulnerabilities that you can get, they are the easiest to understand, the easiest to spot, and the easiest to avoid. Anywhere that you see user input being smashed together with any kind of instructions—SQL, SPARQL, LDAP queries, whatever—it should raise a massive red flag. A candidate who can't spot security vulnerabilities will write security vulnerabilities (or more likely, copy and paste them from the Internet)—and if they can't spot the simplest vulnerability of the lot, they're going to have trouble even understanding more complex ones. And that's before you even get started on other aspects of programming such as data integrity or performance.

With the rise of ransomware and other increasingly nasty exploits, you simply can not afford to be careless or blasé about IT security these days. As software developers, we all have a responsibility to make sure our knowledge and skills are sharp and up to date in this area, and as a recruiter, you can't afford to take on anyone who isn't taking this responsibility seriously.

Finally: there is a second glaring security flaw in this snippet, and candidates should be expected to spot it as well. I shall leave that one as an exercise for the reader.