SQL Injection: The Hack That Will Never Go Away

[Megalith]

SQLi has been around for nearly two decades, yet it repeatedly rears itself at the top of vulnerability lists. Are web developers solely to blame?

“You could teach a 4-year-old to do it,” Al-Bassam added, summing up how incredibly easy the whole process is. Indeed, Hunt has uploaded a video of him teaching his 3-year-old son how to carry out an SQLi attack with Havij.

 

[atp1916]

When you call anyone who can bang out code a developer, this is what happens.

 

[westrock2000]

Ok, I am not a expert programmer by any means, but something about this has always bothered me.

Why do programmers not put in basic input handling that keeps out a lot of these types of attacks?

for instance if I'm expecting a integer to be inputed I put something like

if ($input =~ /(\d+)/ {
use $1 and do something
}else{
give error
}

So in that example, how would someone "inject" a command? Maybe this doesn't stop anything, I don't know. But I don't even do this to stop hackers, I do it to make the code more reliable. It would seem to me that so called "professionals" would be even better at this stuff.

Even with SQL, which I only have novice level experience with, can the operator of the database not put limits on what calls are allowed? Something like don't return this select statement unless some level of authentication or trigger is active?

 

[Mohonri]

Why do programmers not put in basic input handling that keeps out a lot of these types of attacks?

As a developer (among many other things, I can say it most often comes from ignorance, laziness, hubris and/or workload/deadlines. It takes extra work/time to check every input. atp1916 is right that a large number of coders out there aren't experienced enough. Sometimes it's a case of "nobody will bother trying to hack us", sometimes it's a "we need to get this out the door ASAP".

 

[atp1916]

As a developer (among many other things, I can say it most often comes from ignorance, laziness, hubris and/or workload/deadlines. It takes extra work/time to check every input. atp1916 is right that a large number of coders out there aren't experienced enough. Sometimes it's a case of "nobody will bother trying to hack us", sometimes it's a "we need to get this out the door ASAP".

You'd also imagine that some form of basic input sanitation would happen even in those cases you listed, but nope - some "developers" just like to hook stuff right on up to the back-end straightaway. No prepared statements, no API layer, no nothin' - just straight on back to the good stuff.

"Look ma! I made it go FAST!"

 

[GaryJohnson]

Ok, I am not a expert programmer by any means, but something about this has always bothered me.

Why do programmers not put in basic input handling that keeps out a lot of these types of attacks?

for instance if I'm expecting a integer to be inputed I put something like

if ($input =~ /(\d+)/ {
use $1 and do something
}else{
give error
}

So in that example, how would someone "inject" a command? Maybe this doesn't stop anything, I don't know. But I don't even do this to stop hackers, I do it to make the code more reliable. It would seem to me that so called "professionals" would be even better at this stuff.

Even with SQL, which I only have novice level experience with, can the operator of the database not put limits on what calls are allowed? Something like don't return this select statement unless some level of authentication or trigger is active?

Pattern matching is one way to mitigate sql injection attacks. This is easy enough to do, as you show, when you're talking about an integer. It can be more complicated when you're talking about a string. If your string has to accept something complex, you end up blacklisting bad stuff rather than white listing good stuff such as with the integer example. You have to be really sure you've filtered out all the bad stuff. I would presume most injections happen with strings.

One bad thing I've seen happen alot is that you'll have a user input an integer, and then it will get stored as a string in the database - which is often the correct way to store something that is a number, but isn't a numeric value like a phone number. And you'll have all the "validation" happening client side because your GUI or webform API gives you a convenient number-only text field. One should never trust the client like that as it can be modified or bypassed.

 

[ml_paladin888]

the biggest problem I've seen while working with other people's old (5+ years old) code is that they try to take on the responsibility that should be left to the DB driver/DBMS. my observation is that many people tried to formulate a complete query on their own rather than using prepared statement.

while not 100% fool-proof, prepared statement does a damn good job at preventing a lot of sql injection vulnerabilities

 

[Ordeith]

I blame PHP.
Terrible language begets terrible "developers" film at 11.

 

[westrock2000]

Pattern matching is one way to mitigate sql injection attacks. This is easy enough to do, as you show, when you're talking about an integer. It can be more complicated when you're talking about a string. If your string has to accept something complex, you end up blacklisting bad stuff rather than white listing good stuff such as with the integer example. You have to be really sure you've filtered out all the bad stuff. I would presume most injections happen with strings.

One bad thing I've seen happen alot is that you'll have a user input an integer, and then it will get stored as a string in the database - which is often the correct way to store something that is a number, but isn't a numeric value like a phone number. And you'll have all the "validation" happening client side because your GUI or webform API gives you a convenient number-only text field. One should never trust the client like that as it can be modified or bypassed.

I agree, and I used that for simplicity sake. But taking something like the Shellshock bash vulnerability, it takes something like

env x='() { :;}; echo vulnerable' bash -c "echo this is a test"

What I see there is all kinds of symbols that could cause havoc, single quotes, semicolons, curly braces.

Simple replaces on the input can squash that stuff real fast.

For me, I am always trying to validate the input as much as possible and trying to foresee common ways the user could break my code. For me, breaking code is part of the fun.

 

[ml_paladin888]

I blame PHP.
Terrible language begets terrible "developers" film at 11.

ehhhh if this:

$stmt = $dbh->prepare("SELECT * FROM users WHERE USERNAME = ? AND PASSWORD = ?");
$stmt->execute(array($username, $pass));

works anything like .NET's or Java's prepared statements, it *should* safely handle *most* sql injection attacks

 

[Ordeith]

It's not that you can't avoid Injection attacks in PHP. It's that the whole language is a poorly thought out "bad practices" language. of course that will lead to poorly thought out "bad practices" programs.

http://eev.ee/blog/2012/04/09/php-a-fractal-of-bad-design/

 

[Ordeith]

So I have to fit this in here, because it bears repeating: PHP is a community of amateurs. Very few people designing it, working on it, or writing code in it seem to know what they’re doing. (Oh, dear reader, you are of course a rare exception!) Those who do grow a clue tend to drift away to other platforms, reducing the average competence of the whole. This, right here, is the biggest problem with PHP: it is absolutely the blind leading the blind..

http://eev.ee/blog/2012/04/09/php-a-fractal-of-bad-design/

 

[BunnyKillBot]

Ah its good to see an anti-PHP sentiment echoed here; Unreadable, inefficient and riddled with bad practice - welcome to bad language design 101. Blaming developers for SQL injection attacks is like blaming..... developers for IE6 layout issues. Its not the developers fault that the software was designed with such glaring oversights.

 

[serious]

PHP is not the only database that is vulnerable to SQL injection... All Front end with a back end Database ...

 

[Ordeith]

PHP is not the only database that is vulnerable to SQL injection... All Front end with a back end Database ...

PHP is not a database. It is a god awful programming language that breeds incompetence. SQL injection attacks aren't an issue with competent programmers. Competent programmers tend to use other languages.

 

[Navilor]

Don't forget to look at the hover text on the website.

 

[Mohonri]

You'd also imagine that some form of basic input sanitation would happen even in those cases you listed, but nope - some "developers" just like to hook stuff right on up to the back-end straightaway. No prepared statements, no API layer, no nothin' - just straight on back to the good stuff.

"Look ma! I made it go FAST!"

If the boss says it has to ship by the end of the week or you'll get dinged on your performance review, your priorities tend to shift. Input sanitation doesn't incur any noticeable performance penalty, it just takes a bit more time to code.

 

[heatlesssun]

1. Avoid the use of dynamic SQL
2. Avoid SQL outside of the database layer. Use the code encapsulation capabilities of the database as much as possible, stored procedures, functions, etc. And use strongly type parameters throughout all code as much possible with good type selection.
3. Limit the scope of a single transaction as much as possible.

 

[carnageX]

1. Avoid the use of dynamic SQL
2. Avoid SQL outside of the database layer. Use the code encapsulation capabilities of the database as much as possible, stored procedures, functions, etc. And use strongly type parameters throughout all code as much possible with good type selection.
3. Limit the scope of a single transaction as much as possible.

+++++1

This is what I was taught when I started learning SQL. Never ever use hard-coded (dynamic) queries (even with using a 'prepare' statement, it's just bad practice and not very maintainable). Was always taught to use Stored Procs / Functions that get called from code, and not to keep the connection open longer than necessary.

 

[heatlesssun]

Don't forget to look at the hover text on the website.

This is actually two problems. An application account should pretty much NEVER have DDL permissions on persistent objects.

 

[heatlesssun]

+++++1

This is what I was taught when I started learning SQL. Never ever use hard-coded (dynamic) queries (even with using a 'prepare' statement, it's just bad practice and not very maintainable). Was always taught to use Stored Procs / Functions that get called from code, and not to keep the connection open longer than necessary.

This is pretty standard stuff though I do know a lot of people that don't like code encapsulation inside of the database because it makes code in theory less portable to other database platforms. That's true but migrating significant databases and applications tied to those database isn't a simple task anyway.

 

[serious]

PHP is not a database. It is a god awful programming language that breeds incompetence. SQL injection attacks aren't an issue with competent programmers. Competent programmers tend to use other languages.

My apology. I meant to say PHP isn't the only Server-side scripting language that are vulnerable to SQLi. As far as I know, any Server-side scripting language can be vulnerable to SQLi as it isn't which scripting language or which database, but the design of the software/website. All it takes is one ill-constructed dynamic SQL statement and everything can be compromised. SQLi doesn't have to occur on the login page, and parameters doesn't need to be on the URL either.

 

[carnageX]

This is pretty standard stuff though I do know a lot of people that don't like code encapsulation inside of the database because it makes code in theory less portable to other database platforms. That's true but migrating significant databases and applications tied to those database isn't a simple task anyway.

Yeah it's definitely standard stuff...but I still facepalm even at work here at how often I see people wanting to do hard coded SQL; thankfully we don't do PHP at least.

 

[Mohonri]

PHP is not a database. It is a god awful programming language that breeds incompetence. .... Competent programmers tend to use other languages.

I'm curious--what is it about PHP that you find so objectionable? I've used it a fair bit, and while I can see how things *can* go wrong with PHP, that has more to do with the programmer, not the language.

Never ever use hard-coded (dynamic) queries (even with using a 'prepare' statement, it's just bad practice and not very maintainable).

I won't claim to be the most experienced developer, but from what I've seen, the truth of that statement depends heavily on what type of system you're working on. On a larger team with a more static backend, sure, you have the time, stability, and resources to use functions and stored procedures. But if you're working in a small team on a very dynamic project, the realities of life intrude. Some things lend themselves very well to such abstraction, like "look up item X in our inventory", but there are lots of situations where it's a one-off type query, and it doesn't make sense to put in all the effort.

I've also found that there are a fair number of things which are considered "bad practice," without much justification. In business terms, arguments like "coding like that is 10 years old!" or "it looks messy" are poor arguments when put up against "I can code this, test it, debug it and deploy it in half the time."