Don’t wait for the police: plugging holes in your website forms to avoid SQL injection

It’s been a while since the last major Florida election controversy but at long last the sunshine state has delivered. A cybersecurity researcher exposed serious vulnerabilities in the Lee County Supervisor of Elections Office website…and was promptly arrested after detailing those vulnerabilities in a YouTube video that bizarrely featured a man running for the supervisor of elections position.

This was a notable occurrence in the world of cybersecurity for a couple of reasons. The first is that this particular cybersecurity researcher, David Levin, is a white hat researcher – typically a good guy. He was arrested because he reportedly went too far in exposing the vulnerabilities, accessing a large volume of data and poking around in the site’s private resources.

The second reason this is a notable cybersecurity-related occurrence is because it was once again an SQL injection (SQLi) vulnerability that was exploited in a data breach. If, as an application developer, you’re wondering how to stop SQL injections, allow us to add our two cents. 

Understanding the threat

SQL injections are the number one threat in the OWASP top 10 and have been a favored tool of hackers for over 15 years. Tried, true, effective, and able to be automated using third party tools. What more could a hacker want? When your application is attacked using SQLi, the attacker sends malformed SQL statements using forms or even querystring values in the hopes that you don’t validate and check them before you execute them on the server. SQLi is unique from other attacks such as XSS because the statements run on the database server and not in the user’s browser.

Take, for instance, the following SQL statement.

select firstname, lastname from customers where firstname = ‘john’

The above statement just gets a list of customers. In an application, the firstname parameter would be dynamic. For instance, you might want to get the customer’s first and last name after they log in. The dynamic firstname would be input from the application either in the form of user input, a query string, a cookie or even a hidden form variable.

What happens if the user input is a malicious SQLi statement? Let’s send the application the following input for firstname.

‘ or 1=1 –

This input makes the SQL statement the following.

select firstname, lastname from customers where firstname = ‘‘ or 1=1 –’

Since 1 is always equal to 1, the logic in the above statement returns every customer record. The application is vulnerable to SQLi and the hacker now has a list of users’ first and last names. As you can tell from the example, the attacker can form any number of statements as long as the input string is terminated and the “leftover” SQL terminating string is commented out. The “—“ characters comment out the rest of the string to ensure that the SQL database doesn’t return an error instead of successfully running.

Battening down the hatches

SQL injections are a known threat. They’re about as known as they get. If you need motivation to secure against these threats, imagine having the following conversation with a client:

Client: Talk to me about this data breach that occurred, resulting in the theft of thousands of customers’ personal data. Is it a new type of threat? Something you haven’t seen before?

You: Actually it’s number one on the list of web application security threats.

Client: So…

There are a few common ways application developers can defend against SQLi. The first one is to avoid using dynamic SQL built within the application to then run against the database server. Stored procedures will run the opening and closing tick marks used for string variables as literals, so they won’t execute the malicious code.

Another method is to validate the SQL code input from the user if dynamic SQL is necessary and can’t be avoided. You can use third-party tools or scrub the input yourself. It’s usually better to find good third-party frameworks to avoid SQLi because your own homegrown version might not catch all variances of SQLi input. Even a combination of tools and your own code is a step forward in protecting your application and users.

Everyone loves a cheat sheet

It may not be possible to catch all potential attacks, but you can use a SQL injection cheat sheet such as the one from application security software provider Checkmarx to view a list of possible attacks and how they work.

You’ll want to keep in mind that attackers usually incorporate third-party tools and scripts designed to automate the attack, so you should be vigilant about finding the right ways to protect your application. Scrubbing data, validating SQL input, and even logging when malicious scripts are found can help protect your application from a successful SQLi attack.

SQLi is one of the most common attacks on the Internet. It’s taken seriously enough that a white hat hacker was arrested. Any input you get from a user must always have validation run against it to avoid the possibility of a data breach.

Remember, the police are only going to be able to step in if your attacker posts a video of himself on YouTube both committing the attack and detailing it, and the chances of this happening are low, especially now that someone’s been arrested for it. You know what they say: better to prevent SQL injections than be associated with Florida elections.