SQL Injection Attacks Increasing

An anonymous reader writes "Help Net Security has a story that covers the dramatic increase in the number of hacker attacks attempted against its banking, credit union and utility clients in the past three months using SQL Injection." Article follows up on press release with a little more information. Not a lot here shockingly surprising, but it's worth mentioning that SQL injection is a real pain for web developers. You have to be very careful about checking user input.

Qualifications

[Chris Graham]

Perhaps all programmers working on professional database systems should have to get a professional qualification to show that they can write secure code. I wouldn't say the same should be manditory for things like usability or stability (except for special sensitive areas), but being able to write code that actually allows serious danger without qualification is pretty weird. Builders need qualifications, electricians do, gas installers do, ...

A pain for who exactly?

[MosesJones]

but it's worth mentioning that SQL injection is a real pain for web developers

Which web developers would these be? MuppetsR'US ? SQL injection is a pain if you take the input and lob it directly to the database without doing any sort of validation that the information is sensible.

Its a great example of all those people who scream "THIS IS SO MUCH QUICKER TO DEVELOP IN THAN THE OLD WAY" and then bite it after the system goes live.

SQL injection isn't a pain, except for those who think they've found a new quick magic bullet that solves all the problems and the old fuddy duddy practices are now all redundant.

Re:Hard for Devs?

[Goaway]

You're glad that you use pretty much the only langauge where this is not done automatically for you, but which instead forces you to use a function with a name like mysql_real_escape_string()? And that actually has a similarly-named function without the "_real_" that doesn't do the job right? Just kidding with that other one, here's the real one!

Input checking is a half-assed solution.

[cduffy]

Checking input for escape attempts is error-prone. Passing in parameters as bind variables *isn't* error-prone (with regard to blocking SQL injection attacks); makes string quoting completely moot; and can result in a massive performance increase (particularly against Oracle) to boot.

I continue to be in disbelief that anyone doing professional database work can *not* follow this widely accepted best practice and continue to be employed.

Re:Hooray for PHP!

[Goaway]

Of course it's the job of the language to make it as easy as possible to write secure code, and as hard as possible to write insecure code. That should be blindingly obvious, especially for a language that's pretty much aimed at people with little programming experience who are likely to have no idea what they're doing.

Re:serious question

[cnettel]

There are some possibilities if some part of your stack is using UTF-8, for example. What one portion doesn't interpret as a ' will effectively hide or be translated into ' at a later point. You can come up with more variations of the basic idea.

Re:Checking input is a "pain in the ass"?!?

[gutnor]

The other category of people that consider that it is a pain in the ass are people that start working on an already existing project containing thousand of webpages developed in a time when security was no concern or when the application was not supposed to be made available on internet or when the application was supposed to be your team little private quick and dirty monitoring tools done by the boss kid during his internship.

There is a lot of legacy code and lot of code that was never meant to see a production server, not every developer has the opportunity to work only on new applications. For any webdeveloper nowadays, it is trivial to make a *new* website safe from web injection, however securing an old crappy one is non-trivial.

this doesn't match my anecdotal evidence...

[JeanBaptiste]

Often when I am on a page that looks SQL-injectionable, I'll try a few things just for giggles. I've been doing this for a few years now. I'd say that there are much, much fewer injectionable sites then there used to be...

Re:How difficult is it.

[eggoeater]

In SQL Server you can do something like this (there's a way to do this in Oracle but I forget. Not sure about sybase.)

create procedure myTestProc @someDateTime datetime = '1/1/2050' as
--put insert, delete, update here....
select * from someTable
where (@someDateTime >= someTable.someDate or @someDateTime = '1/1/2050')

The where clause basically says, if the optional parameter is not the default then check it, otherwise ignore it.
I've never had 30 optional parameters but I've had quite a few and this
trick has allowed me to condense many statements into one.

If this trick doesn't work, you can also use IF statements and keep everything in one stored proc instead of multiple.

How to make SQL injection impossible

[hypersql]

Many developers write code like execute("SELECT ... WHERE NAME='"+name+"' ...) because it's so easy, they are lazy, or because they are clueless. Many know that they should use bind variables, but not all (and peer reviews are not very common).

There is a way to solve SQL injection problems: Disallow text literals in the database engine. Or even, disallow literals (including numbers) at all. This could be a setting in the database that is on by default, and only off for certain applications (ad hoc query tools). What do you think about that?

I'm thinking about implementing this feature in the database I write (http://www.h2database.com/ [h2database.com]):

SET ALLOW_LITERALS 0 (no literals allowed)
SET ALLOW_LITERALS 1 (only numbers, text not)
SET ALLOW_LITERALS 2 (everything allowed)

This would be a persistent setting, and only an admin can change it.

(Of course there are other security risks, like using 'customer id' in URL or hidden fields in a web application. Or relying on Javascript data validation. I don't know what to do about those problems.)

Re:Checking input is a "pain in the ass"?!?

[ubergenius]

Hell, there are some PHP books out there that do this. I have been primarily a PHP developer for a while now (not because it's the only language I know, I just find it to be the fastest and easiest for me over my experiences with Perl, ASP, and *shudder* ColdFusion, but that's personal preference), and I have found that almost ALL the knowledge I learned from books from the beginning of my experience with PHP is virtually useless nowadays (with the exception of the basics, obviously, like printf() and such). I had to relearn the language as I went along, and I didn't develop a proper security sense of the language for quite a while. This is funny considering the books I read for learning other non-web languages, such as C, C++ and Java, were written with a much more solid foundation, and I still find the knowledge learned in those books useful today. Maybe it has something to do with the web language publishers.

But ... but ... IT'S CHEAP!

[Opportunist]

You get what you pay for. A lot of people already suggested easy solutions to the problem that are just as easy to implement and that would immediately make the problem disappear. So why is it not done?

Simple: The people who write those insecure databases don't even know that those functions and features exist. Some ages ago, they learned a bit about SQL, maybe did a course about it (so they have a sheet of paper saying "Look, I can do it!") and that's it.

HR managers tend to go by papers, and by price. Now, who do you think is cheaper to hire? A person with a well rounded education concerning computers, programs and the fallacies, pitfalls and security issues around them, or someone who learned his SQL statements by heart and has no clue what exactly is going down inside the server?

Sure, both of them will create code that does what the specs say. As long as you only enter data according to spec (which is, interestingly enough, ALL that is checked, even under the SOA). The true quality of code is revealed as soon as you pit something unexpected and malicious against it.

Re:How difficult is it.

[mdboyd]

Agreed
I definitely don't think PHP is to blame for SQL vulnerabilities. Using it as a scapegoat most likely means you have no idea what you're talking about.
If I had modpoints, I'd mod your post up for all PHP haters to see.

Perl DBI is pretty good, actually

[Anonymous Coward]

Considering you have "completely skipped" Perl, it surprises me to read your comments on how bad its database interface is.

In fact, Perl's DBI is not only fast, but when used properly (variable substitutions, binding variables, etc) it works extremely well. Also the fact that everytime you change your data source (CSV, XLS, MySQL, SQLite, MSSQL Server, Oracle, PostGres, etc) all your functions don't change. You can always count on:

my $dbh = DBI->connect(@DSN, \%flags);
my $sth = $dbh->prepare($sql);
$sth->execute( @vals );
while( my $rec = $sth->fetchrow_hashref )
{
# Do stuff with $rec
}
$sth->finish();
$dbh->disconnect();

If you're not doing some kind of column binding or type-casting on your form-derived query arguments, you are always leaving yourself open to sql injection.

== IT competence decreasing

[runcible]

With the help of a whiteboard (!) I explained to about half a dozen ( okay, mostly junior ) developers and -- here's the real kicker for me -- *the three most senior members of out QA department, including the department head* that you could use the password

' or 1 = 1 --

for many, many sites on the Internet, regardless of user name.

The whiteboard came in when I had to explain *why it worked*...

_shakes head_

Its a pity...

[dcam]

.. because avoiding SQL injection is relatively easy to do.

1. Use only prepared statements or stored procedures (Note even without concerned of SQL injection this is a good idea).

2. If you use stored procedures do not use any of the passed in values to generate dynamic SQL (otherwise you have just moved the problem from the app to the database).