SQL injections are among the most common and basic types of cyberattack. Unfortunately, an SQL injection is also one of the most devastating threats an application can face. These attacks regularly lead to data losses and are especially dangerous to infrastructures with shared databases.
This article teaches how to prevent SQL injections. Read on to learn what SQL injections are, how these attacks work, and what steps companies take to protect their databases from malicious injections.
What Is SQL Injection Attack?
SQL injection (SQLi) is a cyberattack in which a hacker runs malicious SQL statements through the application to manipulate the database. These attacks can affect any website or web application that relies on an SQL database (MySQL, Oracle, Sybase, Microsoft SQL Server, Access, Ingres, etc.).
Our MySQL commands cheat sheet offers an overview of the most important commands you need to master this RDBMS.
The consequences of SQLi range from mild to severe. Following an injection, a hacker can:
- Corrupt, steal or delete data.
- Gain root access to the system.
- Create new records to open the door for more advanced breaches, such as an APT attack.
- Elevate privileges to reach other applications and systems on the network.
- Compromise the server or other backend infrastructure.
- Launch a DDoS (denial-of-service) attack.
- Access the operating system through the database server.
The extent of damage depends on the attacker’s ability. The victim can experience anything from a few database errors to a complete takeover of the web server.
How Does SQL Injection Work?
Most web applications require users to provide credentials to prove their identity and level of access. If a verified user requests data, the application sends an SQL statement to the database in the form of a query and returns the requested data.
When an app has an SQLi vulnerability, a hacker can skip the authentication process and manually inject SQL statements (or malicious payload) into the database. The database does not recognize the threat and executes the statement as if the application is sending the request.
Unlike some types of cyberattacks, an SQL injection requires the target system to have an exploitable flaw. Most weaknesses arise from a lack of strict separation between program code and user-provided input.
Hackers typically target databases through an application or a website. Some more sophisticated attacks go directly after the database, though.
Learn the difference between SQL and NoSQL to find the perfect database type for your application.
SQL Injection Types
There are two kinds of SQL injections:
- A classic SQLi: Attacks in which a hacker sends commands to the database and gathers results from the output.
- A blind SQLi: Breaches in which a hacker sends commands to the database but does not gather results directly from the output.
Below are the seven most common types of SQL injections a business can face.
In-Band SQLi (Classic SQLi)
An in-band SQLi occurs when a hacker uses the same communication channel to launch the attack and gather results. Here is an example of an in-band injection vulnerability in WordPress code:
global $wpdb; $title = $wpdb->get_var("select post_title from " . $wpdb->posts . " where ID=" . $_GET['id']); echo $title;
This code has an SQLi weakness because the user input in
$_GET[‘id’] goes directly to the database. There is no sanitization or escaping, so an attacker can send commands directly to the database and receive the output back to the browser. For example, a hacker can send:
SELECTcommands that retrieve records from the database.
INSERTcommands that create new user accounts.
UPDATEcommands that modify existing records.
In-band SQLi is the most common type of SQL injection.
Error-based SQLi is an in-band injection technique that relies on error messages. Hackers repeatedly probe the application for errors to gather information about database structure.
Error-based SQL injections allow an attacker to retrieve data such as table names and content from visible errors. In some cases, error-based SQL injection is enough for a hacker to enumerate an entire database.
Union-based SQLi is an in-band injection that leverages the vulnerabilities of the
UNION SQL operator.
UNION command executes one or more additional
SELECT queries and appends the result to the original query. An attacker can leverage extended results to retrieve data from other tables within the database.
The Union-based SQLi only works if the original and new queries have the same number and data type of columns.
Inferential SQLi (Blind SQLi)
In an inferential SQLi, the web application does not transfer data via direct output. Instead, an attacker must gather information by sending payloads and observing:
- Web application’s response.
- The behavior of the database server.
- Differences in the web page.
Here is an example of a blind SQLi:
global $wpdb; $title = $wpdb->get_var("select post_title from " . $wpdb->posts . " where ID=" . $_GET['id']);
User input goes directly to the database by concatenating the
$_GET[‘id’] variable to the query. The browser never displays the output, but an attacker can gather information about the database by analyzing the server’s reactions.
This injection type takes more time to exploit than in-band SQLi, but the consequences are just as dangerous.
Content-Based Blind SQLi
Content-based SQLi is an inferential technique in which the attacker forces the database to return different results depending on whether the query returns a
Here is an example of a content-based SQLi:
select post_status from wp_posts where ID=1 and (select 1 from wp_users where substring(user_pass,1,1) = 'a' and ID=1)
This query checks if the first letter of the hashed password for the user with ID 1 is an A. While the attacker does not see the output, the web page behavior reveals whether the query is true or not. A hacker can go through every character with this technique and extract the admin password.
Content-based SQLi attacks are slow, especially on large databases. An attacker must enumerate the database character by character. Another name for this attack type is the Boolean-based blind SQL injection.
Time-Based Blind SQLi
Time-based SQLi is another inferential injection technique. An attacker sends queries that force the database to wait (sleep) for a specific number of seconds before responding.
For example, a hacker can ask the database if the first letter of the admin account starts with an A. If the first letter is A, the hacker instructs the database to sleep for 10 seconds. Here is how that code would look like:
select post_title from wp_posts where ID=1 union select IF( substring(wp_users.user_login,1,1)='a', BENCHMARK(10000000,ENCODE('blah','asdf')), null) from wp_users where ID=1
The hacker may not see the output, but the response time to generate a web page reveals the answer.
Like content-based SQLi, time-based injections are slow. Unfortunately, these attacks are often fully automated, so incorrect guesses do not slow down the process.
Out-of-band SQLi is the least common injection type that typically happens when a hacker cannot launch a direct query-response attack. Instead, a hacker crafts SQL statements that trigger the database to create a connection to an external server under the attacker’s control.
The database server must have the ability to make DNS or HTTP requests to deliver data to an attacker. Otherwise, out-of-band SQLi does not work.
Attackers typically choose an out-of-band approach as an alternative to time-based techniques when server responses are unstable.
How Can You Detect SQL Injection?
SQLi attempts often appear as standard database errors, so injections are hard to detect without tools. However, your security team should monitor for signs of SQL injections.
Every SQLi involves trial and error. Hackers typically set up a worm or a bot to repeatedly probe a website for flaws. Set up your scanning tools to monitor for failed logins and bad syntax errors.
Here are additional ways to detect SQL injections:
- Examine error_reported event for odd errors.
- Search the database for common HTML tags, such as
- Monitor the traffic for suspicious changes in behavior, especially changes in permissions and passwords.
- Set up a network-based intrusion detection system (IDS) to monitor connections to the database server.
- Set up a host-based IDS to monitor web server logs.
Use vulnerability assessment tools to ensure your application is safe from SQLi and similar cyberattacks.
How to Prevent SQL Injection Attacks?
There are several effective measures a business can take to prevent SQLi attacks.
Input sanitization (or validation) is the practice of checking and filtering user inputs. This technique ensures the application can identify illegitimate user inputs and dangerous executables.
Developers can sanitize input in three ways:
- Whitelist sanitizing: Only pre-approved characters and code strings can reach the database.
- Blacklist sanitizing: Developers forbid certain characters from reaching the database (line breaks, extra white spaces, tags, tabs, etc.).
- Escape sanitizing: All data in queries require an SQL-escape before the query execution.
Typically, whitelisting is a simpler and more secure option than blacklisting. A clever hacker can find a way around a blacklist, so verify user input with whitelists where possible.
Another good practice is to validate user input with drop-down menus and radio buttons. These input fields prevent users from typing in input and stop the injection of executable code.
Parameterized SQL Code
Parameterized queries require the developers to define all the SQL code first and pass each parameter to the query later. This coding style enables the database to differentiate between code and data, a feature that is not possible with dynamic SQL queries.
Parameterized SQL code prevents attackers from changing the intent of a query even if they insert commands. For example, if a hacker entered the user ID of ‘1’=’1, the query would look for a username that matches the entire ‘1’=’1 string. The database would not accept the query as a command.
Limit User Permissions
Employ Zero Trust security and limit users to the bare minimum permissions they require to perform their roles. The fewer accounts with read-write-execute permissions, the better.
For example, if a website only returns database content with the
SELECT statement, do not grant the connection other privileges, such as
Another good practice is to distrust all user input. Treat input from internal users the same way as input coming from external and third-party sources. Also, never allow the web application to connect to the database with admin privileges.
Set Up a Firewall
Use a Web Application Firewall (WAF) to filter all traffic between web applications and your database. A firewall protects all web-facing apps, so set up the defenses to stop SQLi and similar cyberattacks.
Read about the different types of firewalls and find the one that offers ideal protection to your business.
Updates and Patches
Keep all systems and defenses up to date with the latest patches and updates. Doing so helps:
- Catch new bugs that allow an SQL injection.
- Prevent attackers from exploiting weaknesses and bugs present in older software versions.
Regularly patch all web application software components, including libraries, plugins, frameworks, web server, and database server.
Run security testing to check the resilience of your database and associated applications. Regular tests are vital to discovering vulnerabilities for all cyberattacks. Consider running:
- Dynamic analysis tests (DAST) that look at the app from the attacker’s point of view.
- Static analysis tests (SAST) that look for vulnerabilities at the code level.
Manual tests on potential entry points in the application also help detect SQLi vulnerabilities. Here are several tests to consider:
- Submit single quote characters and inspect database responses.
- Run Boolean conditions (OR 1=1, OR 1=2, etc.) and look for differences in the responses.
- Submit payloads that trigger time delays upon execution. Measure the differences in the response times.
- Run OAST payloads that trigger an out-of-band network interaction. Monitor the resulting interactions for potential exploits.
Learn about penetration testing, the most well-rounded and realistic test form on the market.
Never Store Sensitive Data in Plain Text
Encrypt all private and confidential data in your database. Encryption also provides an additional level of protection if an attacker manages to exfiltrate sensitive data.
Do Not Display Database Errors to Users
Database error messages must never show up on the client web browser. An attacker can use technical details from error messages to adjust queries for successful injection.
Instead of displaying error messages with useful information, set up simple word-based error messages that apologize for the inconvenience.
Train and Maintain SQLi Awareness
Preventing SQL injections is a team effort, and all staff members must know the importance of cybersecurity. Provide suitable security training to all developers, QA staff, SysAdmins, and DevOps.
While Dangerous, SQLi Attacks Are Easy to Prevent
Although SQL injections can be damaging, a well-organized company should never fall victim to these attacks. Set up proper precautions and ensure your databases are resistant to SQL injections.