Topic: Declare War on SQL Injection Attacks (SQL - Structured Query Language)

[Samker]

We should have eradicated SQL injection attacks by now. SQL injection should be the Internet generation's smallpox or polio -- gone for good. Countermeasures are readily available and understood. They're easy to implement. And yet, I keep seeing headlines like, "Huge Web hack attack infects 500,000 pages."

SQL injection attacks continue to be among the most fruitful against Web sites and applications. And why not? From an attacker's perspective, the database behind many Web applications is where the really juicy targets live. That's where you'll find customer records, credit card numbers and other good stuff.

And now attackers have started using SQL injection to plant malware on Web sites, so that visitors to those sites get their computers infected with the malware. The databases aren't just where the juicy targets are; they're ripe for planting malicious data that infects other people's computers.

This raises the stakes and makes an even more compelling argument for eradicating every single SQL injection defect in existence. We should ensure that no future applications have SQL injection defects. Let's take a closer look to see what is involved.

SQL, or Structured Query Language, is an interpreted database language that is commonly used by application servers in talking to database servers. The databases are where, as I said, we store things like customer records. A record might consist of a customer name, address, e-mail address, phone number, and credit card payment information.

How it Works

To view one customer's data in a database, an SQL query is constructed in the application server and sent to the database, which should return that customer's information and nothing more. The query could look something like:

select * from customers where last_name='Smith'</

So what's the big deal? Well, in application servers, the customer name is generally represented as a variable with a value that enters the application via a Web form -- with data that comes from the application's user. So, in Java, the query might be constructed something like the following:

String query = "Select * from customers where last_name='" + req.getParameter("lastName") + "'";</

See the problem yet? Well, in the normal functional case, a user enters a last name such as "Smith" into that field, but an attacker could just as easily enter a value of "' or '1'='1'-". That would result in a query like the following being sent to the server:

select * from customers where last_name='' or '1'='1'</

That Boolean expression (last_name='' or '1'='1') is always going to be true, which will result in the database server responding with the entire contents of the customer data table. That is, our attacker has likely just succeeded in extracting the customer data of every one of our customers. Not good.

But I did say the problem is easy to fix. You see, the underlying issue in the above scenario is that the intent of the SQL logic can be altered by maliciously formed user data. But Java and most Web-related languages provide us a simple solution called parameterized queries.

In a parameterized query, the data is passed to the SQL query as actual data, not as part of the SQL syntax itself. It takes only a little bit more effort to set up the query, but then it's done, and any data provided by an attacker will do nothing to alter the intent of our query.

How to Fix it

Here's what our query above would look like if we used Java's parameterized query mechanism, known as prepared statements:

String lastName = req.getParameter("lastName"); String query = "Select * from customers where last_name = ?" PreparedStatement pstmt = connection.prepareStatement( query ); pstmt.setString(1, lastName ); try { ResultSet results = pstmt.execute( ); }</

OK, so it's a couple more lines of code, but it's a simple pattern to follow. And the results are functionally identical to the vulnerable form -- but without the vulnerability.

If we simply go through our Web applications and change all of our SQL queries into the form of a prepared statement, we can completely obliterate every single SQL injection weakness in the world. Seriously.

We'll still have plenty of other issues to deal with, like cross-site scripting (also known as "XSS"), but we'll have eliminated this one issue of SQL injection entirely.

I know what you must be thinking: It can't be this simple, otherwise we would have done away with SQL injection years ago. That would be only partially true. At a technical level, it is this simple. But at a human level, not so much.

To fix the problem of SQL injection, several pre-conditions have to exist. One, we need to have access to the application source code itself. If our application has been outsourced, or if we're relying on third-party code, this may not be such a simple thing at all.

And that's just one of the human hurdles I've heard as excuses for not fixing a security defect. There are many others.

But come on, folks, we deserve better than the status quo. Our companies deserve better; our users deserve better; indeed, our customers deserve better. Let's stop making excuses and start making reasons. Make it a goal to go through your Web applications now and eradicate every single SQL injection weakness. And while you're at it, be sure to teach each and every one of your software developers how to do safe SQL calls.

No more SQL injection. No more excuses.

(PC World)