Security 101: SQL Injection

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; 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 guardrails, 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 semicolon. 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:

Learn from industry experts in a setting that is conducive to interaction and conversation about how to prepare for that "really  bad day" in cybersecurity. Click for more information and to register.