Basics: Avoiding SQL Injection

SQL injection is a pretty big deal.  The attack is easy to carry out, the vulnerabilities are prevalent and the payoff is potentially large.  Many of the password breaches reported over the last year or two are known or thought to have been carried out via SQL injection. 

SQL injection attacks allow attackers to execute arbitrary queries or commands against a database.  Developers introduce the vulnerabilities into their code when they concatenate or substitute user input into the elements of a SQL query.  In the following Python example, the program will accept any input as the "user_id" variable (returned as a part of login_data) and tack it onto the end of a string that is subsequently executed as a sql query:  

login_data = web.input()
query_string = "SELECT * FROM USERS WHERE ID = '%s'" %

login_data.user_id

cursor.execute(query_string)

Lessons from the S.C. breach

In October, the South Carolina Department of Revenue discovered that it had been breached and contacted Mandiant to assist in the investigation and response.  All told, millions of social security numbers and hundreds of thousands of bank/credit card numbers had been stolen.

In November, Mandiant published their findings.  This is exciting.  All we usually get is a news article lacking in technical detail.  This we can actually learn from.

My goal in this blog post is to explore what, in hindsight, the S.C. Department of Revenue could or should have done better. Please read the Mandiant report before you move on.

Lessons from the CCSF debacle

In January 2012, some fairly sensational news stories were published about a major data breach at City College of San Francisco.  According to the early reports, tens of thousands of student records may have been compromised.  Even more interesting, the reports said that some systems may have been infected for over a decade and that there were connections to China and Russia.  While the reports were interesting, they were short on details and I hoped to eventually read more after the school had some time to sort things out.

In May, the CTO of CCSF was suspended at least in part for his reaction to the breach.  The Guardsman, CCSF's newspaper, published a series of articles that described controversy within CCSF over the handling of the breach, the CTO's management and accusations that the breach was a false alarm.

The CTO's tenure sounds like it was a disaster.  It's also full of lessons for IT and security managers.

Wrapping up 2012

I've been really busy lately so I haven't blogged much.  Things are coming together pretty well here at the end of 2012.  Here's what's happening with me.

This summer, I won a free trip to Fishnet Security's iSWAT training event in Las Vegas through The Ethical Hacker Network.  I decided to take the CISSP review course.  I've been meaning to take the CISSP exam for a while, but it's been hard to find time to study since I'm working and in school full-time.  There were only three of us in class, but it worked out really well.  Instead of sitting in rows and listening to the instructor drone on for hours, we sat around a conference table and actually discussed things as we went over them.  Many of the discussions went well past what we needed for the exam, but I enjoyed the hell out of it.  It's not often that I get to spend an entire day talking about security.

My only complaint is that Fishnet was supposed to reimburse me for the CISSP exam (it was part of the package).  I was told a month ago that my reimbursement was being processed, but I haven't heard back and I haven't received anything.

Password Expiration

One common bit of advice with respect to security is to require frequent password changes.  This "best practice" has persisted for decades despite some prominent criticism.  But, is password expiration actually helpful or not?

Are there benefits?

Password expiration has a negligible effect on limiting or preventing malicious behavior.  The ability to steal passwords often implies privileged access to your systems or network.  If the attacker has administrator rights, access to the password database or the ability to sniff traffic on your network, he can install a backdoor or continuously steal passwords in order to avoid the expiration window.  That’s assuming he even needs continued access to accomplish his goal.  If the attacker only needs short-term access, which is often the case, password expiration is irrelevant.

A note on password math

The number of possible passwords with a character set of size C and a password length x is C^x.  For instance, with mixed case alphanumeric passwords we have a character set that has 62 possible characters: 26 lower case letters, 26 upper case letters and 10 numbers (26 + 26 + 10 = 62).  If a password is 8 characters long, there are 62⁸ = 62 ∙ 62 ∙ 62 ∙ 62 ∙ 62 ∙ 62 ∙ 62 ∙ 62 possible combinations.

If there are P possible passwords and we can guess G passwords per second, then it will take us P ÷ G seconds to guess all possible passwords.  Since there are 86,400 seconds in a day, the number of days that it will take us is P ÷ (G ∙ 86,400) and the number of years is P ÷ (G ∙ 86,400 ∙ 365).

The number of guesses that an attacker can make per second depends mostly on the password hashing algorithm.  For a fast algorithm like MD5, a reasonable cracking speed is several billion guesses per second.  For bcrypt or scrypt, a reasonable speed might be from a few hundred to a few thousand guesses per second. 

Example:

Number of possible 8 character mixed-case alphanumeric passwords = 62⁸ = 218,340,105,584,896

Seconds to guess all possible passwords (1 billion per second) = 62⁸ ÷ 1,000,000,000 = 218,340 seconds.

Days to guess all possible passwords = 218,340 ÷  86400 = 2.523 days.

Years to guess all possible = 2.523 ÷ 365 = .007 years

Salted vs Unsalted

A lot of people seem to think that it's okay to use something like salted SHA-1, without any key stretching, as a password hash.  The following graphic shows how many guesses an attacker would be able to make per user on a daily or monthly basis assuming that he can make either one thousand or one billion guesses per second.  One thousand guesses per second indicates a password hash such as bcrypt or PBKDF2 that includes stretching to slow down the hash.  One billion guesses per second is a reasonable estimate for a single iteration of MD5 or SHA-1 (depending on your hardware) .

Click for full-size

It should be obvious that salting is not enough.  Even with a site that has 10 million users, an attacker can make millions of guesses per user per day against salted SHA-1* or MD5. A strong password hash literally makes password cracking a million times harder.  If an attacker can only guess a handful of passwords per day, per user, then any user with a password that isn't his name, username, or on one of the worst passwords lists is probably going to be okay.  There is some safety in numbers.

If an attacker targets a single account, he can still make millions of guesses per day, even with a strong password hash.  There is no safety in numbers once the attacker is focused on you.  Pick good passwords.

* I used SHA-1 as an example because it's common.  The SHA-2 family are stronger cryptographic hashes, but they don't provide any significant benefit beyond SHA-1 for password hashing.

Edit: I'd like to point out that, for simplicity, these numbers do not factor in the number of passwords that are actually cracked along the way.

Edit #2: I expanded the graphic to include user counts of 10k, 100k, and 10M.  Thank you Solar Designer for the suggestion.