SQL Injection is a web security vulnerabity.
It is used by hackers to access and manipulate the database.
This is done by entering malicious data in input fields or on URLs.
SQL Injection is one of the most common attack vectors against web applications.
SQL Injection is based on the intruder's understanding how web applications are written, specifically, how dynamic SQL queries are built using string concatenation.
Suppose we have an input field where employees are asked to verify their identity with a unique employee number, like so:
And this is what the intruder enters:
The query will now be changed to this.
SELECT FirstName, LastName, Salary FROM Employee WHERE EmployeeNumber = 78234 OR 1=1
The results are rather dramatic.
This query returns all employee records with their salaries.
The query is designed to retrieve a single employee record.
Adding OR 1=1
in the input field will change the SQL query.
Since 1=1
is always true, the query returns all employees and their salaries.
The same can be done with URLs.
Suppose that employees receive a link via email that they can click to
quickly access their personal data. This is the link:
https://www.company.com/secure/employeenumber=78234
A malicious employee copies the link and appends OR 1=1
, like so:
https://www.company.com/secure/employeenumber=78234+OR+1=1
You can already guess what happens when this URL is placed in the browser. The same malicious query is created, returning all employees with their salaries.
SELECT FirstName, LastName, Salary FROM Employee WHERE EmployeeNumber = 78234 OR 1=1
SQL injection is a web security vulnerability.
This vurnerability allows the intruder to penetrate the database.
SQL injection refers to the act of injecting malicious code into an SQL query.
The injection can be done from an input field or with a URL alteration.
If successful, an intruder may access, modify, or delete data from the database.
An intruder often begins by probing the site to find vulnerabilities. This is done by submitting incorrect data, or any other unusual payload, forcing the site to create error messages.
By observing the response, the intruder may discover certain technical details about the server or the database. For example, if the site returns database errors to the user, this may reveal insights into the database structure, such as the database vendor, table names, columns names, data types, and more.
Next, we'll review some attack vectors.
SQL Injection can be used to retrieve data that is normally inaccessible to users.
Suppose we have an e-commerce website.
In this site, when the user selects a category, the browser requests this URL:
https://company.com/products?category=Computer
This link leads to the following SQL query.
SELECT * FROM Product WHERE Category = 'Computer' AND Discontinued = 0
It returns all computer products that have not been discontinued.
Next, the intruder alters the URL by appending this: '--
, like so.
https://company.com/products?category=Computer'--
Assuming the site does not implement any defense against SQL injection the altered link leads to the following SQL query.
SELECT * FROM Product WHERE Category = 'Computer'--' AND Discontinued = 0
The double-dash sequence --
is an SQL comment which means that the
remainder of the query is ignored.
This removes the Discontinued = 0
part. The query will return all
products whether they are discontinued or not.
After that, the intruder may decide to display products
from any category, including those categories that the user normally does not see, using this link:
https://yourstore.com/products?category=Computer'+OR+1=1--
This leads to the following query:
SELECT * FROM Product WHERE Category = 'Computer' OR 1=1--' AND Discontinued = 0
Since 1=1
is always true, this query will return a complete
list of all products from any category, discountinued or not.
Most web applications have a login form that accept an email and password. When submitting credentials, for example, "jimmy@gmail.com" and "mypassword", the user will be validated with this query.
SELECT * FROM Customer WHERE Email = 'jimmy@gmail.com' AND password = 'mypassword'
If the query returns a customer record, the login is successful, otherwise it fails.
Using SQL injection, an intruder may be able to login without a password by appending
an SQL comment to the email. They will try to submit "jimmy@gmail.com'--'" with an arbitrary
password, which leads to this query:
SELECT * FROM Customer WHERE Username = 'jimmy@gmail.com'--' AND password = 'mysecret123'
The password clause in this query is ignored because of the comment --
that follows the email value.
This query returns a record of customer "jimmy@gmail.com" and the intruder is successfully logged in.
All the intruder needs is a valid email.
This SQL injection technique uses the UNION
keyword.
The UNION
keyword allows an intruder to execute an
additional SELECT
query and have the results appended to an existing query.
Say, an application perform this query.
SELECT Email, Name FROM Customer WHERE Department = 'Marketing'
An intruder may submit the necessary input that effectively appends this string:
UNION SELECT Email, Password FROM [User]--
This is the resulting query.
SELECT Email, Name FROM Customer WHERE Department = 'Marketing' UNION SELECT Email, Password FROM [User]--
Executing this query will return a list of customers together with a list of login credentials of all users. Quite devastating.
An intruder may try to get information about the database structure from the database.
The following string can be appended to an input value with a UNION
keyword.
SELECT * FROM INFORMATION_SCHEMA.TABLES
This would return a list of all tables in the database. Since this is a UNION the intruder will need to ensure the columns match of the first and second query.
SQL Injection is possible because of how SQL queries are constructed in the code. If raw user input is used to simply string together an SQL query then this opens the site to SQL Injection attacks. So, how can this be avoided?
Most instances of SQL injection can be prevented by using parameterized queries (also known as prepared statements). Parameterized queries are SQL queries which accept user input in a more controlled manner by using SQL parameters. A SQL parameter is a placeholder in a query that accepts a user-entered data value.
Here is a SELECT statement with SQL parameters:
var id = GetRequestString("ProductId"); var sql = "SELECT * FROM Product WHERE ProductId = @0"; db.Execute(sql, id);
Parameters are specified in the SQL statement by an @.
The SQL engine checks each parameter value and ensures that it is a valid literal
and does not contain executable SQL code.
Depending on the language and platform, writing prepared statements will be somewhat different.
Here is another prepared statement using Entity Framework Core.
var customer = db.Customers.FromSqlRaw( "SELECT * FROM Customer WHERE Id = {0}", id).FirstOrDefault();
SQL parameters can be used in any sql statement that contains user input.
Here is an INSERT statement.
context.Database.ExecuteSqlCommand( "INSERT INTO Customer (FirstName, LastName) VALUES(@firstName, @lastName)", new SqlParameter("@firstName", firstName), new SqlParameter("@lastName", lastName) );
Applications that accept user input, but cannot use
SQL parameters, need to take a different approach.
White-listing permitted input values is one possible way.
Another way to prevent SQL Injection is by studying
the possible vulnerabilities first and then remove these one by one with custom code.
Clearly, this requires great security skills and experience from the development team.
This risk sheet lists SQL techniques that are commonly used during an SQL Injection attack. The code examples are specific to SQL Server. For other databases the syntax will be somewhat different but the fundamentals are the same.
Comments can be used to remove a portion of the original query. SQL Server supports two types of comments.
--
and
/* comment */
Multiple strings can be combined to create a single string using concatenation. Intruders use this to their advantage by concatenating strings in unexpected ways.
'value1'+'value2'
The SUBSTRING function extracts part of a string with a specified length. SUBSTRING can hide system functions and be part of SELECT, INSERT, or UPDATE statements. This example will reveal the database and its version.
SELECT SUBSTRING(@@version,1,90)
This will return something like this: 'Microsoft SQL Server 2016 (SP2-GDR) (KB4583460) - 13.0.5103.6 (X64) Nov 1 2020 00:13:28'. Armed with this information the intruder can more precisely attack this database.
INFORMATION_SCHEMA is an ANSI standard that returns metadata about a database. This may include information about tables, columns, constraints, indexes, and more. For example, an intruder can obtain table information with this query.
SELECT * FROM INFORMATION_SCHEMA.TABLES
Our sample database would return this list with table names:
TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE |
---|---|---|---|
MySandbox | dbo | Customer | BASE_TABLE |
MySandbox | dbo | Order | BASE_TABLE |
MySandbox | dbo | OrderItem | BASE_TABLE |
MySandbox | dbo | Product | BASE_TABLE |
MySandbox | dbo | Supplier | BASE_TABLE |
The intruder may use conditional errors to test certain database conditions. If the condition is true, a divide-by-zero error is triggered, validating the test. Here is the SQL.
SELECT CASE WHEN (CONDITION-HERE) THEN 1/0 ELSE NULL END
Batched queries is the execution of multiple queries in a single operation (they are batched together).
In SQL Server, multiple queries can be batched by placing a ;
(semicolon)
between the individual queries, like so.
QUERY1; QUERY2; QUERY3