SQL Injection: How It Works, and How to Prevent It
Can hackers take control of an entire website with just a few strings of code? An SQL injection could let someone ransack your database, stealing everything from administrative credentials to customer card details.
If your site or application has been left with just a few weak-spots in its programming, a hacker can extract all kinds of information you never intended to be made public.
So what is an SQL injection attack, and how can you prevent it?
What is SQL?
SQL is a coding language, used primarily to retrieve information from online databases. It’s easy to work with because it’s intuitive and incorporates basic English words as commands.
For example, imagine a customer is shopping online and types the word “shoes” into a store’s search bar. When they initiate the search function, a simple process begins behind the scenes.
The website will be using a database management system (DBMS) which, in turn, will run some form of SQL. When the customer searches for “shoes”, a string of code is created using that language. This is the SQL query.
That query will contain some specifications; where to look, for example, and what to retrieve. It should look like this:
SELECT name, description FROM products WHERE category = “shoes”
When this query reaches the database, the DBMS system will read the SQL query and know to look in the “products” table. It can then retrieve the names and descriptions of any items in the “shoes” category, and send that data back to the customer. If it finds ten items, it will send ten names and descriptions.
It’s a simple process. That’s where the trouble starts.
What is SQL Injection?
To carry out an injection, a hacker will try to slip some additional coding into the SQL string. That’s the injection.
This doesn't always work, of course. The site's DBMS reads some characters as coding commands (SELECT, for example) and others as normal words and characters (“shoes”, in this scenario).
If a site has been programmed correctly, searching for a coding command won't result in an injection. Instead, the system will read the command as a set of characters it doesn’t recognise, and send back a message saying something like “no items found.”
However, if the system hasn’t been secured, it will read the injected characters as a genuine SQL command. This is where things can go badly wrong.
Instead of using the keyword “shoes”, the hacker could search for specific SQL commands. When the string is formed and sent to the database, the DBMS will read the searched keyword as a command, and carry it out accordingly.
Using a technique called blind SQL injection, a hacker can quickly work out exactly what DBMS is being operated behind the scenes. Knowing this, they will be able to start using the appropriate SQL language.
If they search for the right coding terms, they can compel the DBMS to return a full list of all the different data tables it contains. Armed with this insight, they can access each table and extract any information they want. If they find a “user” table, they can really cause some trouble.
UNION is a coding command that allows someone to add an extra query to their main one. The results of this subquery will appear below the results of the primary query.
To do this, a hacker could write something like this in the search bar:
“shoes” UNION (SELECT username, password FROM users);--"
Behind the scenes, an SQL string would be generated, which might look like this:
SELECT name, description FROM products WHERE category = “shoes” UNION (SELECT username, password FROM users);--
Now, as well as returning the results for the keyword “shoes”, the hacker will also be able to view all usernames and passwords from the “users” table.
The passwords will probably be hashed, but it wouldn't take long for a hacker to crack them. If they can work out which usernames belong to the admins, they can use brute-forcing software to quickly break into the relevant account and gain administrative access to the entire application.
How to prevent SQL injection
A successful SQL injection can lead to colossal damages. A hacker can steal passwords and payment information, leak user details online, and delete essential data. An event like this can irreversibly destroy consumer trust. How can you prevent it?
- Input Validation
If you build an input validation process into the backend coding of your site, this can go a long way to cutting down the threat. You could create a white-list of accepted characters, and programme the DBMS to recognise if a keyword is not listed. If a hacker “searches” for a coding command, your system will check it against the white-list. When it doesn’t get a match, it won’t run the code.
- Prepared statements
Creating prepared statements is probably the best strategy. A vulnerable site creates a new SQL string every time the hacker searches for something, but with a prepared statement, that's not the case. When programming the backend of your site, create your SQL templates in advance, with a question mark in place of the keyword. The DBMS can be programmed to read that question mark as whatever data is in the search bar, but the query itself is premade. This drastically reduces the risk of a malicious SQL command reaching the database.
- Data segregation
The more your data is segregated, the less there is for a hacker to seize in one attack. In the example of the online store we discussed earlier, the problem of the SQL weakness is made far worse by the fact that user data is being kept on the same database as basic product lists. Separating different kinds of information across separate databases and servers limits the amount of damage an SQL attack can cause.