Opinion: No more excuses for SQL injection attacks

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.

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:

<!--begin code--> select * from customers where last_name='Smith'</<!--end code-->

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:

<!--begin code--> String query = "Select * from customers where last_name='" + req.getParameter("lastName") + "'";</<!--end code-->

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:

<!--begin code--> select * from customers where last_name='' or '1'='1'</<!--end code-->

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.

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

<!--begin code--> 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( ); }</<!--end code-->

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.

With more than 20 years in the information security field, Kenneth van Wyk has worked at Carnegie Mellon University's CERT/CC, the U.S. Deptartment of Defense, Para-Protect and others. He has published two books on information security and is working on a third. He is the president and principal consultant at KRvW Associates LLC in Alexandria, Va.


Copyright © 2009 IDG Communications, Inc.

It’s time to break the ChatGPT habit
Shop Tech Products at Amazon