Security 101: SQL Injection

A carefully crafted attack can convince a database to reveal all its secrets. Understanding the basics of what the attack looks like and how to protect against it can go a long way toward limiting the threat.

Many web-facing enterprise applications have databases sitting behind them. For many of those, the application itself is little more than a snazzy user interface sitting on top of a database. And in 2020, it’s a near certainty that the database speaks Structured Query Language, or SQL. That’s great news for the developers who need maximum flexibility in creating applications. It’s also pretty great for criminals who want to convince the database to give up far more information than any single user should see.

SQL Injection is a hacking technique that’s been around since at least 1998. It takes advantage of two factors for success: First, web applications often ask users for data; and second, those applications tend to take the user-supplied data and pass it to the database as part of an instruction. Put them together with no code-based guard-rails, and the possibility exists for a criminal to run the application far off into the weeds.

Structure of a query

In a common application fragment, a user might be asked for their user name in order to see the information the enterprise holds on their account. When they type their user name into the application and hit “Enter”, the code that results could look something like this:

statement = “SELECT * FROM users WHERE name = ‘” + userName + “‘;”

This tells the database to select everything (“*”) in a database called “users” in which there’s a record with a username that matches what the user just typed in. So far, so good.

But if the user types in a username that looks like this:

‘ OR ‘1’=’1

Then the code that’s generated will tell the database to return all the information for every record in the database, because “1=1” is true no matter which record is being examined.

The attack can get even more complex because most databases accept what’s called “batched” SQL commands, in which multiple commands can be entered at once and separated by a semi-colon. In such a case, an attacker can command the victim database to do a great deal of work to select and arrange data in such a way that it’s most useful to the hacker (and perhaps a bit less noticeable to the victim’s security team).

All of this is possible because the most basic web application programming takes input from the user and simply places it inside a pre-built database query string before passing it to the database. So what’s an enterprise to do if it would rather not give its entire database to anyone who asks?

Structure of a defense

Defense against SQL injection can happen at two separate points in the application development and execution process: the first is during code development, the second at the time of execution.

Defending against SQL injection during development means writing code that doesn’t allow commands to be passed to the database as part of queries from the application. Part of this might be input validation — if you’re asking for a name, don’t allow numbers or special characters, for example.

Some of the development-based protection might be code recognition, in which anything that is SQL code (or a SQL code fragment) is recognized and deleted from the query before being passed to the database. In an ideal case, both of these techniques will be used to make sure that no query can be created that doesn’t provide the results intended by the application designer.

Protecting at execution typically means deploying a web application firewall (WAF) or similar product that scans input for illicit queries and commands, and then scans output for results that go beyond the intended contents. As with email filtering systems, security staff will need to set up the WAF in a way that doesn’t interfere with legitimate uses, but the occasional rejected legitimate input may be a price administrators are willing to pay for greater security.

Just like cross-site scripting, SQL injection has been around longer than most of the professionals trying to defend against it. It’s still a danger because it’s still so very fast to build web application code that doesn’t block the attack, and so inexpensive to simply not buy a firewall that protects against the technique. The cost benefits of not acting, though, can be wiped out in a single incident of successful attack. The real question is just how much data an enterprise is willing to risk.

Related content:

 

 

Curtis Franklin Jr. is Senior Editor at Dark Reading. In this role he focuses on product and technology coverage for the publication. In addition he works on audio and video programming for Dark Reading and contributes to activities at Interop ITX, Black Hat, INsecurity, and … View Full Bio

Recommended Reading:

More Insights

Read More HERE