Basics of SQL Injection Attacks

This page gives an introduction to SQL Injection Attacks, which are still one of the very most common types of successful cyber attacks.

1. Basic Explanation of How SQL Injection Attacks are Carried Out

SQL injection attacks are carried out by inserting SQL code into a field where user input is accepted. If the user input is not correctly handled, this can result in the code executing by the software processing the user input, with whatever privilege levels that software runs under. The SQL statements are specifically crafted by the threat actors (i.e. hackers) to manipulate databases.

Using automated scripts, it's possible to call a web page (or some other application, or many thousands or millions of them) many thousands of times, each time trying out different variations of malicious SQL code, to see if there is a vulnerability present in the code being attacked.

Below are some basic/rudimentary examples of SQL statements suitable for attack against SQL code in a MySQL database:

SELECT email FROM members WHERE userid = '$ID';

In user input to this statement stored in the variable called $ID, assuming this value isn’t escaped nor checked for any malicious SQL code, this is how the attack would work:

The server would be expecting a value that looks something like james123 for the variable called $ID. Now assume the following input is entered into the field instead, on a page that displays the user’s email address:

james123'; select GROUP_CONCAT(table_name SEPARATOR ', ') FROM information_schema.tables AS email'

Now the “email address” that's returned by this query, if no other code is present to check anything strange is happening, would appear as a comma-separated list of all the table names in the current database, and these would be displayed to the hacker. A destructive attack might then be done to delete any of the entire tables, with a value entered for the user ID something like this:

james123'; DROP TABLE [any of the table names found from the previous query, or a comma-separated list of all of them]'

Note that the closing single quote in both examples above was added so that the final SQL statement as seen by the database server would terminate in ''; which is effectively a blank statement, and less likely to cause a server error than a stray single quote (as would be the case without it there).

The database user which the script being attacked runs under might not have permissions to drop tables — however there are still plenty of other things which could be done once an attacker knows the names of all the tables. And quite often tables are named something generic, like "users", or "posts", etc. So that if an SQL injection attack is carried out by an automated script against many thousands (or more) of websites/systems, there's a fair chance that some of them will have tables with generic names like that.

Most sites (or application programs) now will have some level of defence against basic SQL injection attacks. Yet SQL injection attacks are still being successfully carried out — a lot. Some of this is due to old (i.e. "legacy") code, which was not written to defend against SQL injection. Some of it is due to programmer carelessness (which is really easy to do accidentally) — e.g. where a programmer assumes some variable is safe, because there was code earlier on in the program to sanitise it, but then later on another programmer changes the earlier code (e.g. to fix a bug, or add a new feature to the website/app/product), without realising that this opens up a security hole in another part of the code where this variable is used in an SQL statement.

2. Discussion of A Recent Attack Initiated by SQL Injection

Successful SQL injection attacks appear to be still quite common [1], despite the knowledge of how to prevent them being around for a long time now. Even in 2021, the main vulnerabilities in WordPress, which is by far the most used web Content Management System CMS), are XSS and SQL injection. Furthermore, by far the majority of these vulnerabilities are found in third-party developed plugins rather than the base WordPress code itself [2].

Interestingly, it was a great deal harder to find online examples of recent successful SQL Injection attacks than it was to find theoretical examples, and instructions on how to prevent them, and how they are done, and even scripts (such as in Python) designed to exploit and achieve them. Most easily found examples of successful SQL injection were up to the early 2010s, which is to be expected, considering the widespread knowledge and awareness of them in recent years.

A 2020 report describes a successful SQL injection attack on Freepik and Flaticon. Though the report (and the company attacked) did not reveal the date of attack, that the report is from 2020 suggests it was recent [3].  According to the report, “The data stolen included the email addresses of users along with 3.77 million hashed passwords. Some 3.55 million of those passwords were encrypted with bcrypt, making them highly difficult but not impossible to crack, while 229,000 were salted MD5, an older encryption standard that can be easily decrypted.”

3. Some Possible Defenses Against SQL Injection Attacks

Escaping Variables

Probably the simplest way to defend against SQL injection is to “escape” all variables which are used in SQL queries, especially those which might contain any user input (or any data of not fully known origin and content).

This can be done using functions built into most coding languages, such as the PHP function mysqli_real_escape_string(), which adds extra characters (often the \ character) before special characters, and/or otherwise “fixes” a string variable so that it’s safe from SQL injection, before using it in an SQL query.

Escaping all user data that's sent as part of a query string can prevent the majority of SQL injection attacks, yet not all of them.

Manually Sanitising Variables

A second (and also quite good) way to help prevent SQL injection attacks is to properly (i.e., securely) handle any variables which may contain user input in the code of an application. This can be done in a few ways.

One simple way is just to automatically delete all special characters from a field, leaving only alphanumeric (or even only alphabetic) characters as allowed input. This can be done on fields where there are no special characters required for the function of that field itself.

Some types of data do require at least some special characters though, for example, an email address needs to have at the very least the @ character, and the fullstop, and usually also the underscore and hyphen, and perhaps a few others. These could be allowed for only the email field, and still would prevent most or all SQL injection attacks (most of which rely on at least the semicolon getting through, since that’s the statement termination character in SQL).

On a web application, one implementation of this would be to make one of the first things that every web page on the site does is be to delete all special characters from the entire GET and POST variable arrays, apart from any whitelisted field names where some special characters are needed to be there (and then only allow those needed characters and delete everything else). This can save effort later on if a programmer is careless and ignores other precautions (e.g. escaping fields before using them in database queries, as described further down on this page).

Securely handling any variables can also be done like in the code example below — by generating a hard-coded whitelist of possible values for a variable that's used in an SQL query, and only allowing those values and nothing else to be processed in the actual query that the database server executes. Below is an example of how this could work in PHP and MySQL.

Note that in PHP, $_GET is a global array of all the "GET" (i.e. URL) variables which are in the URL when the page is called. So, calling the page with a URL of https://domainname.com/page.html?type=fb would result in the value of $_GET['type'] being equal to the string 'fb'.

Note also that, because this variable is taken from whatever value is there in the URL (which would normally be called from another page in the website), it would be very easily possible for a hacker to call the page manually and put any value at all in here — including a long string of SQL code.

// Check if the URL variable exists. This will stop an error
// being generated if someone tries to call the page
// in an unusual way, without the variable that the page
// is expecting. The exit function will just stop the
// rest of the script from running and the hacker
// will see nothing but a blank page, and nothing will
// happen.

if (!isset($_GET['type'])) exit;

// Create an array of all the values that the URL variable 
// ever needs to possibly have.

$valid_types = array('fb', 'tw', 'li', 'gp', 'pi', 'rss');
	
// If the URL variable is NOT one of the specific allowed values,
// then the script will just stop running,
// and nothing else happens.

if (!in_array($_GET['type'], $valid_types)) exit;

// Next is the query which will be sent to the database server.
// Note that now the URL variable can only ever be one of the 
// specifically allowed values above.
// This will stop any other values (e.g. those containing 
// SQL injection) from being injected into the query which is 
// then sent to the database server to execute.

$update_query = "UPDATE some_table_name
				 SET ".$_GET['type']." = ".$_GET['type']." + 1
				 WHERE	some_column_name = 'hardcoded_field_value' 
                 AND other_column_name = '".$known_safe_variable."'
				 ;";
                 
// Code to run the query would go next...

This may seem to be a very thorough way of defending against SQL injection attack. And basically, it is, except for risks like that some of this code (e.g. the name of the variable) might later on be edited in an update to the website/app, but the programmer might forget to also change the part of the code that's validating/sanitising the variable against SQL injection attack.

As well as just exiting the script when there is a non-standard input detected, it would also be possible to add code here to log the details of the request, and flag it as a potential intrusion attempt. Since it would not be possible in the normal use of the website (or application, etc.) for the URL variable above to have any other value than the ones in the allowed list. Therefore if the page is called with any other value for this URL variable, then something unusual (e.g. an intrusion attempt) is happening.

Prepared Statements

A third — and even more thorough — defence against SQL injection attack is to always use prepared statements. Prepared statements are a database feature where an SQL query is stored in the database itself, and only the variable data (e.g. field names entered by a user) needed for any particular execution of the query are sent to the SQL database server (rather than the entire query string as an SQL statement).

The server will treat this data as data (e.g. field names) only, rather than as a complete SQL statement to be executed. Which means that even if there is SQL injected into the user-input data, it won't be executed as actual SQL commands by the database server (unlike when not using prepared statements, and the entire query is sent as one string to the database server, which it then executes).

References

[1]          E. Avillez. "2019 Website Threat Research Report." Sucuri. https://sucuri.net/wp-content/uploads/2020/01/20-sucuri-2019-hacked-report-1.pdf (accessed September 26, 2021).

[2]          A. Talalaev. "Website Hacking Statistics You Should Know in 2021." Patchstack. https://patchstack.com/website-hacking-statistics/ (accessed September 26, 2021).

[3]          D. Riley. "8.3M records of Freepik and Flaticon users stolen in SQL injection attack." siliconANGLE. https://siliconangle.com/2020/08/24/8-3m-records-freepik-flaticon-users-stolen-sql-injection-attack/ (accessed September 26, 2021).

Cover image by Shutterstock

Share This Page

If you liked this page, please share it with others! You can use the links to share on Facebook, Twitter, LinkedIn, Pinterest, and Email. Ther is also an RSS feed to get updates for the website.