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.

Comments

How difficult is it.

[El_Muerte_TDS]

Simply forcing request variables to the correct type and escaping all strings is pretty much the only thing you need to do.
Most languages provide the functionality to do that (in php: intval() for all integer request vars, and _escape_string() for string data.).
It's just a small amouth of work, yet a lot of people are way to lazy.

Re:How difficult is it.

[eggoeater]

Simply forcing request variables to the correct type and escaping all strings is pretty much the only thing you need to do.

Or you could just use stored procedures.
I've been doing that for years without any problems.
I've also never had any issue with "business logic". I can keep my business logic
seperate with stored procs. (I never understood that argument against them.)

Re:How difficult is it.

[aymanh]

PHP doesn't force you to do that by hand, you can make use of the numerous database abstraction layers for PHP, like PDO or PEAR::DB.

Here is an example, taken straight from PDO's page:

$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':value', $value);
 
$name = 'one';
$value = 1;
$stmt->execute();

The framework is there, PHP developers need to make use of it, but sadly things like the following are still common:

mysql_query('SELECT value FROM REGISTRY WHERE name = "' . $name . '"');

Re:How difficult is it.

[beavis88]

1) Use a sensible naming convention. eg P_User_Create, P_User_Delete, etc. Use the naming conventions to effectively categorize your stored procs. This takes a little planning and discipline, but what "best practices" don't? The "intellisense issue" is a red herring IMHO - if anything, you're worse off in this regard without sprocs.

2) USE SOURCE CONTROL. Without trying to be nasty, you're insane (or a one man operation) if you use the database as your authoritative source for stored procs. If you have any environments beyond a production server, the ability to script installation/alteration of procs is essential.

Re:How difficult is it.

[CaptainZapp]

Sure you could have files with all your stored procedures in them

Bingo!

but then you have to have 2 copies of everything.

Stored procedures (like any DDL statements to set up your database schemas) should be handled like any other source code and treated as such. This includes version control

There seems this "but I can pull it out of the database with my super GUI tool, so why should I keep it on file too?" attitude. Well, duh; it's mighty hard to pull anything of a database whoms disk just crashed.

For recoverability reasons database objects (including stored procedures) should be scripted and version controlled. Period.

Re:How difficult is it.

[Goaway]

You've never used Perl's or Python's database interfaces, have you? You use placeholders, and pass in values separately, and the interface itself takes care of proper escaping. You use a constant string for your query and don't build the SQL query by hand.

It is both easier, and much more secure.

Re:How difficult is it.

[Aceticon]

Or you could just use stored procedures.
I've been doing that for years without any problems.
I've also never had any issue with "business logic". I can keep my business logic
seperate with stored procs. (I never understood that argument against them.)

If your stored procedures are only very thin layers encapsulating low level database access operations (thus not much more than pre-packaged selects, updates, inserts and deletes) you should have no business logic in the database problems.

On the other hand there's a couple of downsides to such a design:
- It requires developers with a good level of expertise in both the language used to develop the core of the application and the one used for the stored procedures. This is true both for initial development and for maintenance.
- It makes an application tightly couple to the database. If you want to port to another database, at the very least you will have to redo all the stored procedures.
- It increases the likellyhood of having version conflict problems between the core application and the database application components. More specifically, the data-model is usually more stable across versions of the application than the actions executed on data in that model (eg "find all employees in more than Y departments and whose manager is level X"), and thus if you store in the database code which is tightly couple to the actions that the application executes on the data then previous versions of the database (for example, those restored from a backup) are not likelly to work with the lastest version of the application (nor are they likelly to be easilly "fixed" by a DBA).
- It's harder to debug code that crosses platforms and languages

More in general, the problem of SQL injection can be avoided simply by using prepared statements or any other type of SQL query that takes input parameters instead of using string concatenation to make SQL queries that include the input values.

Using stored procedures to solve this specific problem is very much overkill.

Beyond this, the only good reason i can see for using stored procedures like this is for performance reasons if you do some level of post-processing on the results or some sort of "smart" block updating of data. In this case, stored procedures should only be used in a very small number places (to solve high-impact IO bottlenecks between the application and the database) and not in a generic way.

The last couple of reasons i see for such a design are:
- A "job protection" measure by locking the application to the specific skillset combination of a specific developer
- Because the developer prefers-to/is-more-confortable-with developing code in the database that in the core application
Hardly good reasons IMHO

No no No no No no NO

[IPFreely]

You don't need to escape strings.

Just don't build your query on the fly.
Bind ALL parameters to placeholders in a prebuilt query. Binding is an instant kill for any SQL injection attack. It is also much more effecient on many databases.

Re:Use PreparedStatements with Java

[IPFreely]

PreparedStatements can be immune to SQL Injection based attacks.

You should stipulate that you must bind all parameters to placeholders. You could use PreparedStatement the same way as Statement and have the same problem. Bind all parameters, no matter what language you are using.

Re:Use PreparedStatements with Java

[(trb001)]

Additionally, make sure you use PreparedStatements/CallableStatements correctly. I've seen people mark up a PreparedStatement like this:

String SQL = new String("select * from user where username = '" + username + "'");
PreparedStatement statemnet = connection.prepareStatement(SQL);

That does *nothing* for you, and is just as insecure. Instead, make sure you use parameterized statements:

String SQL = new String("select * from user where username = ?");
CallableStatement cs = connection.prepareCall(SQL, ...);
cs.setString(1, username);

Most databases treat the two very differently. In the second case, the database compiles the statement and then compares the username field with your value. In the first, your value is inserted and then compiled, allowing injection.

--trb

Re:Hard for Devs?

[Bogtha]

I am curious what language automatically checks your users input for any attempt at SQL Injection.

You're approaching it with the wrong mindset. A database API shouldn't check for SQL injection attempts, it should encode the input appropriately. Avoiding SQL injection attacks is just a subset of correct operation, as anybody with an Irish surname could tell you.

As for an example, well with Python's DB-API 2.0, you write code like this:

cursor.execute("select foo from bar where baz = %s;", (quux,))

It doesn't matter whether quux has apostrophes, it gets automatically escaped because the API is designed as an interface to input data, not an interface that accepts data that has been specially prepared and cannot be distinguished from data that hasn't been specially prepared.

Dynamic 'WHERE' clauses

[TheRealBurKaZoiD]

I think one thing everyone is overlooking, and I didn't see it mentioned before I posted, is that alot of newbies, and even intermediate SQL developers either can't use stored procedures because they're using some old version of MySQL, or they have problems writing stored procedures that include dynamic WHERE clauses, or they just don't know that you can do that. It's been my miserable privilege to have seen some pretty goddamn bad SQL code in my life, code that was so bad it would make you physically ill, simply because the developer didn't know any better. Remember kids:

1. Stored Procedures
2. Parameterized Queries
3. Learn the SQL-92 Specification (so that you're familar with the language beyond just SELECT, INSERT, UPDATE, and DELETE. There are all kinds of things out there to help you get rid of that dynamic code, like COALESCE, and CASE WHEN, etc.)

Here's the SQL-92 Specification (pops in a new window)

Re:Injection preventation doesn't need input check

[julesh]

are there web application frameworks which don't support parameterized SQL statements?

that would be PHP.

Quit spreading FUD. PHP supports parameterized SQL just as well as any other language I've worked with. See, for example this doc page (search for "Example 2"). Even for databases whose native C APIs don't support the feature (i.e. MySQL), the database abstraction layer PEAR::DB that is distributed with PHP provides emulation.