Defending Against SQL Injection

If you’re worried about computer hackers, you should be worried about SQL injection (SQLi). It keeps showing up on the top ten list of the Open Web Application Security Project (OWASP). In 2013, the year of their latest approved list, OWASP put injection at the top of the list. “Injection flaws such as SQL, OS, and LDAP injection,” they write, “occur when untrusted data is sent to an interpreter as part of a command or query.” If you’re concerned about keeping out hackers and their “untrusted data”, read on.

What is SQL Injection?

Have you ever seen a hypnotist doing dirty tricks? The story goes that he puts someone under hypnosis and then gives them a command. Maybe he tells them to start acting like a chicken. Or he asks them to recall past events. When the subject awakens, he knows nothing about what has happened or what he was told to do. With SQL injection, hackers put commands into a screen input that slips right by the computer’s security measures. It’s as if the computer is asleep, in a trance, or not paying attention. But in a short time, a clever hacker and his software tools can harvest a whole lot of information from an unsuspecting organization.

A clever hacker and his software tools can harvest a whole lot of information from an unsuspecting organization.

Here are the basics. SQL (Structured Query Language) is a commonly used database language. In fact, it is probably the most common database application out there. In an online video on SQL injection, one expert put it this way: “Any time you have to enter information or retrieve information using a website, it’s interacting with SQL.”

SQL (often pronounced SEQUEL) is a query language. It’s the way you talk to a database. Suppose you want to ask the database a question. You wouldn’t write a statement with subject and predicate as in normal human speech. But the language is not so far off. SQL queries are commands. And they play a part of most of the input screens on the web.

Now let’s discuss the most common internet input screen: the login window. To access the resources of a web page, you are frequently asked to put in your username and password. Let’s see how that might look as an SQL query:

SELECT * FROM users WHERE username = ‘john’

SQL is made up of databases, table names, and columns. In this case, the query is asking for all records from the table “users” in which the “username” column has a value of “john”. Here are a few sample records so that you can see the data in context:

 

Sample Database
username password occupation city
john ******** accountant Houston
sally ******** manager San Francisco
jim ******** engineer Atlanta

 

The idea behind the use of an ID and password is to limit access. But if you’re not careful, your login page could be an easy target for hackers. How?

Injection. It’s a word that carries the idea of forcefully putting something in that’s not wanted. And what the hackers put in is an unwanted command. Where? It’s at the end or in place of a harmless SQL query. By putting the name “john” (without the quotes) into the username field, the computer will look for John’s record. But by injecting a command in the field, a hacker can break into an unprotected database.

The hack was detected many years ago, but it’s still around. In the article “The History of SQL Injection, the Hack That Will Never Go Away”, author Joseph Cox talks about the internet’s number one threat. He quotes the hacker who calls himself w0rm: “It’s the most easy way to hack”. The hacker w0rm used SQL injection to break into the Wall Street Journal’s network.

If you really want to see the roots of the discovery of SQLi, have a look at an the 1998-12-25 edition Phrack magazine. In particular, go to the section called “ODBC and MS SQL server 6.5”. That’s where the author Jeff Forristal first brings up the strange behavior he found in Microsoft SQL.

– WHAT’S THE PROBLEM? MS SQL server allows batch commands.

– WHAT’S THAT REALLY MEAN? People can possibly piggyback SQL commands into your statements.

But after all these years, SQL injection is still with us. It’s still happening. And many people still don’t know anything about it.

Hacker Tactics

There is no set nomenclature for the types of SQL injection attacks that hackers use. Different names are used in explanations across the internet. So the names we use here are not set in stone.

#1: Unfiltered characters

The website Rapid7 calls this unsanitized input. It’s about using characters that should not get through the authentication process. The first example normally given in explanations about SQL injection usually looks something like this:

john’or’1’=’1

The initial text doesn’t matter. It could be Jim’ or Sally’ or Joe’ or even blank by adding a closing single quote ‘. By closing off the text with a single quotation, the hacker can move on to the next argument. When he writes that one equals one (1=1), the computer recognizes that to be true and let’s him in. It all seems so simple and so stupid. But without protecting your data, it’s all possible. And it happens all the time.

#2: Blind SQL injection

This is where the attacker looks for clues, but he can’t see the results of his injection directly. This tactic may take some time. The OWASP website says that blind SQLi is “a type of SQL Injection attack that asks the database true or false questions and determines the answer based on the application’s response”. They write that the attacker may use this method to run tests to see if a site is vulnerable to SQL injection.

It can get complicated. Obviously, the hackers will spend a lot of time perfecting their craft. A web archive from Hack All The Things will tell you more about blind SQLi. Of course, most of us don’t need to know more than the basics.

#3: Error-based SQL injection

One video referred to error-based SQL injection, which has some similarities to blind injection. The hacker is still working from limited or no information, but he is building his knowledge of the database. A line from an Acunetix article sheds some light on the matter: “Errors are very useful to developers during development, but if enabled on a live site, they can reveal a lot of information to an attacker.”

For example, if an error popped up saying that something is not in the “sales” database, the hacker would then know that a sales database exists. By playing around, the hacker can learn a lot from error messages.

Hacker Gains

So what can a hacker gain from SQL injection? Lots. Acunetix says that the hacker can bypass authentication or even impersonate a user. He can pull some or all of the data from a website’s database. He can change or delete records within the database. And if the configuration is right, he can even attack the server in other ways through the database.

What can the hacker do with this access? A techie who calls himself The Code Curmudgeon has done the work for us. He has put together an SQLi Hall of Shame. His developing list includes dozens of security breaches using SQL injection. The Hall includes all sorts of organizations from government and business.

Who knows what goes through the minds of these criminals? They may be doing it just to prove they can. They may be stealing financial information that can be passed on to others. They may hold the information for ransom. But it’s all illegal, and none of it is good.

Stopping the Hacker

The first order of business in stopping SQL injection is prevention. OWASP has plenty of information on web application vulnerabilities. For this hack, nothing is more helpful than their “SQL Injection Prevention Cheat Sheet”. They cover four main defenses against SQLi, which we will summarize here.

Mitigation is about lessening the force or intensity of something. Mitigating the attacks of a hacker means you are trying to prevent or eliminate them. The defenses below are for developers. If that’s not your job, you may still be able to make sure the developers in your organization are able to implement these strategies.

#1: Prepared Statements

This solution uses parameterized queries. Your developers will know how this works. OWASP says, “Parameterized queries force the developer to first define all the SQL code, and then pass in each parameter to the query later.” Each programming language has its own way of doing this.

#2: Stored Procedures

With this solution, the SQL code for procedures are stored in a database and then called up as needed. Developers should be careful with this one. They are more complicated than prepared statements, and may be more difficult to code.

#3: Whitelist Input Validation

OWASP also has an Input Validation Cheat Sheet. They say that input validation is performed “to ensure only properly formed data is entering the workflow in an information system”. We’ll let OWASP walk us through this one: “White list validation involves defining exactly what IS authorized, and by definition, everything else is not authorized.” That’s pretty clear.

#4: Escaping All User-Supplied Input

This is a last-resort strategy. It is a database-specific method for using escape routines to filter user input. In computing terms, escape is a way to get out of some process, command, or function. You computer keyboard has an escape (ESC) key. Again, the coding can get complicated here. Just know that it’s a way to cancel the effects of any unwanted user input.

Conclusion

SQL injection is about software. Web-based applications are pieces of software that are loaded onto servers. Hackers use SQL injection to penetrate that software. They even have software tools that run standardized SQLi routines to make the job easier for them. To combat these efforts, you need to have your defenses up.

The website Common Weakness Enumeration defines common software weaknesses. Their Common Weakness 89 (CWE-89) is called “Improper Neutralization of Special Elements used in an SQL Command”. The problem is the “special elements”. If you can’t keep hackers from successfully using special characters and commands to attack your network, then you’d better watch out. Those hackers will steal you blind.

Prevent your next outage now!

TRY IT FREE

Other articles you might like to read: