I'm decidedly old-school in a few of my habits. My morning routine, barring anything out of the ordinary, is one such example. Typically, I'll get up between 6:30 to 7:00 am, grab my first cup of coffee, and then chat with my daughters for a few minutes before seeing them off to school. Next, I make a bowl of oatmeal (a great choice for diabetics like me), pour a second cup of coffee, and browse the local paper, The Tennessean, while I have breakfast. On the morning of August 5, I had the added pleasure of spewing said coffee and oatmeal all over my morning paper when I read the headlines: Russian gang stole 1.2 billion Net passwords.
Scanning the article, midway through the details, I saw that the hackers used good, ol’ fashioned SQL injection attacks. They target approximately 450,000 websites from small mom-and-pop shops to the very largest company websites. A bit later, I read more online from The New York Times article where the story first broke.
Their efforts brought in 4.5 billion records in total, each one containing a user name and password, resulting 1.2 billion unique accounts. And of those unique accounts, a little data processing found that there were about 542 million unique email address, since many people use the same user name and password on multiple accounts.
The person who discovered and publicized the data breach, Alex Holden, is the founder and chief information security officer of Hold Security, an internet security services and consulting firm. “Most of these sites are still vulnerable,” said Holden, pointing out that the hackers continue to exploit an age-old and well-known vulnerability to collect data.
By this time, I’m quite outraged. This is like saying a bunch of robbers rummaged through 450,000 houses because none of the home owners knew about locking doors and windows. It is that serious and that elementary of a mistake. SQL injection can be perpetrated against any website that uses a relational database as a back-end. Oracle and MySQL, PostgreSQL and Microsoft SQL Server are all equally vulnerable when the website developers, the home owners in my analogy, either don’t know or don’t care to lock the door.
Why do SQL injection attacks keep happening - and succeeding?!
As blogger Bill Napier says in his post “Like Shooting 1.2B Fish in a Barrel,” the hackers relied on simple website developer laziness, crappy skills, and/or ignorance. Basically, these web developers designed their websites under the assumption that everyone coming there has good intentions. Not true!
As Napier says, “The problem of SQL injection typically involves software developers not checking the input from the user (or from a bot request), and where SQL requests posted from the Web request is forwarded directly to the database.”
That means that a hacker can “inject” their own SQL code into whatever the website SQL code that is forwarded directly on to the database. With a minimum of effort, a hacker can turn:
SELECT * FROM user_accounts WHERE username=’scott’ AND password=’tiger’;
Into:
SELECT * FROM user_accounts WHERE username=’scott’ AND password=’tiger’ OR 1=1 --;
Since 1 always equals 1, the SELECT statement evaluates as true for ALL records in the database and returns all records in the user_accounts table as a result set. (You can see a nice example of SQL Injection on here on YouTube). As you might be guessing, an evil hacker could just as easily do this instead:
SELECT * FROM user_accounts WHERE username=’scott’ AND password=’tiger’ ; DELETE FROM user_accounts; --;
And, voila!, your website is toast.
The remedy for SQL injection attacks?
The sad part about all of this is that it is literally as easy for website developers to prevent as it is for a home owner to lock their doors at night. Typically, developers will implement one or more security measures, here are a few among many such techniques:
- No SQL queries from the website directly to the database and its source data. Instead, all website calls go to database stored procedures, which check for SQL injection first, then process the request.
- Multiple layers of security, with separate authentication at the front end and back end, each step having the least possible privileges needed to complete the step of work.
- Disabling all features that are not in use. By default, most database systems have a lot of features, such as email integration or execution privileges on the host OS, enabled by default. If the database doesn’t need them, do not enable them.
- Developer your website from an attitude of distrust, rather than trust. So many websites are built expecting everyone to be good girls and boys. The problem is that bots and scripting have now made SQL injection an easy exploit to automate and scale out. After all, as the hackers are teaching us, why hack two or three websites when you can script it out and hack hundreds of thousands?!?
More information about preventing SQL injection attacks, please
I’ve been speaking about SQL injection, and the easy ways to thwart it, for years. I only listed four security techniques above. And I didn’t even cover some of the tools available that will help you to determine whether your website is vulnerable to SQL injection attacks.
To see those details and for additional information on understanding and preventing SQL injection attacks, please take a look at my slides and videos at KevinEKline.com to learn more.
Stay safe and lock your [SQL] doors at night!
Kevin Kline, a longtime Microsoft SQL Server MVP, is a founder and former president of PASS and the author of SQL in a Nutshell. Kline tweets at @kekline and blogs at http://kevinekline.com.
Follow Kevin Kline on Twitter and Google.