SQL Injection (SQLI)

Structured Query Language Injection (SQLI) is a potentially devastating use of a code injection vulnerability specifically targeting the database query language SQL. SQL injection is the result of user-controllable data being passed as a query against a back-end SQL database. Improperly coded forms will allow a hacker to use them as an entry point to a database at which point the data in the database may become visible and access to other databases on the same server or other servers in the network may be possible.

Web site features such as contact forms, logon pages, search functions, and any field that allows user input, are all susceptible to SQL injection attack because the very fields presented for visitor use MUST allow at least some SQL commands to pass through directly to the database..

Using SQL injection, a hacker will try to enter a specifically crafted SQL commands into a form field instead of the expected information. The injection attack relies and the ability of the attacker to prematurely close the argument string of the SQL statement so that only their intended command is executed and interpreted correctly. The goal of the injection is to secure a response from the database that will help the hacker understand the database construction, such as table names. The next step would be to access and view data in important tables or to add data to tables, such as adding new accounts or user names and passwords. The third step, roughly, would be to use access to the database to discover and change security settings on a server that would allow a hacker administrative access.

Any dynamic script language including ASP, ASP.NET, PHP, JSP, and CGI is vulnerable to attack. The only equipment needed is a web browser. There are tools widely available online that will semi-automate the process of searching for weaknesses, and there are many forums in which hackers share exploits and help each other overcome obstacles.

It is important to note that while code injection attacks against databases are usually branded as SQL injection, this does not mean all databases use SQL as their query language, or that SQL is the only language vulnerable to this attack. Any query language ranging from something as ubiquitous as Javascript to something as obscure as SPARQL is vulnerable to this sort of attack.

SQLI Workflow

Non-Technical Example of the SQL Injection

Imagine a fully-automated dish washer that functions based on instructions given by humans through a standard web form. Values in bold are provided by humans and instruct the bus. That form might look like this:

Wash <what should be washed> at <where should we wash> if <when should we wash?>

Sample Populated Form

Wash dishes at the sink if the dishes are dirty.

Imagine a scenario where someone manages to send these instructions:

Wash dishes at the sink and ignore remaining instructions. if the dishes are dirty.

The dishwasher is fully-automated. It does exactly as instructed: it washes dishes at the sink, even if the dishes are already clean. Such an injection is possible because the query structure and the supplied data are not separated correctly. The automated dishwasher 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 Example of SQL Injection

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:

SELECT * FROM users WHERE username = 'steve' AND password = 'mysuperduperpassword';

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:

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:

SELECT * FROM users WHERE username = 'admin'; -- ' AND password = 'anything';

What is happening here? The (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 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.

SQLI Mitigation

Parameterized Statements
A lot of programming languages used to build web servers connect to a SQL database use a database driver. A feature of a lot of these drivers is that they have the ability to accept user input as a parameter, and not use the user data explicitly. In other words, using a paramaterization of the user data tells the driver and in turn the SQL database that the user input is only to be treated as data and not a query command. This ensures that no matter what the user sends as an input, it will not be treated as valid commands. Parameterized statements are the best protection against SQL injection attacks and should always be used when available. To better explain what is meant by parameterization here are two examples written in everyone’s favorite language JavaScript (node-sql package).

connection.query(
 ‘select * from users where email = ?’,
 [email],
 function(err, rows, fields) {
 // Do something with the retrieved data.
 });

As we can see in the above example, the data for email is provided in the query as a ? question mark, and the next parameter given to the connection.query function is [email]. This tells the connection.query function to replace the ? with the variable titled email and to treat the data in email as data only and not to interpret the data as commands.

 connection.query(
 “select * from users where email =’” + email + “‘“,
 function(err, rows, fields) {
 // Do something with the retrieved data.
 });

In this example the variable email is injected right into the query itself without informing the connection.query function what is data and what is a command. As such, if the user is able to manipulate her input, she would be able to add her own commands into the email variable and thus allowing for SQL injection.

Sanitizing inputs
The less robust method of SQL prevention is by escaping or sanitizing special characters that can be interpreted as commands. Injection attacks goal is to prematurely close an argument string, which is why ‘ or “ characters are frequently a part of SQL injection attacks.
Escaping symbol characters is a simple and easy way to prevent all types of code injection and many languages have built-in functions that facilitate this for a programmer. There are just a few considerations that must be accounted for.

• The programmer must remain vigilant and escape characters everywhere where data is being interpreted as a SQL query
• It is possible for an injection attack to occur without quotes being abused. Numeric input fields are a prime example, especially if they do not prevent the user from entering alpha characters rather than just numerics.

The programmer also has the possibility of sanitizing the user inputs holistically. This can involve a series of regular expressions designed to catch suspicious strings of characters or even more simply can be used to restrict allowed characters in a field (allowing only alphanumeric characters for a username and blocking symbols, etc).

Web Application Firewall
Even by following the best practices, new vulnerabilities or unanticipated scenarios may arise that can result in code injection. A web application firewall is a useful tool in filtering out SQLI and other types of code injection attacks. Most modern WAFs are integrated solutions that augment detection and blocking heuristics. For example, if the WAF encounters input that looks suspicious but may not entirely be malicious, the WAF can cross-reference the incoming IP’s reputation/history to make a determination on blocking a request. This helps protect sites against possible zero-day attacks that may normally take internal developers weeks, months, or years to properly address.

An effective solution against all types of online attacks and especially code injection attack is to use a combination of all methods to ensure the robustness of a defense and ensure there is not a single point of failure in the system.