Opinion: No more excuses for SQL injection attacks

Computerworld - 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:

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.