An Introduction to SQL Injection (SQLi)
Research / Posted March 12, 2020
What is SQL?
All computers are fundamentally devices that take input, process or transform it somehow, and then return an output. A web application performs exactly this basic process – taking input from users in the form of requests over HTTP, processing them, and then returning an output that the user’s browser displays. A simple website may be static in that it returns exactly the same output in return to a request for a given resource or URL, but modern web applications offer functionality that are dynamic and permit parametrization of requests – that is, taking in variables such as a username, and transforming the returned output based on that input.
Web applications will generally store these received parameters as persistent data on the server. It is perfectly possible to store all the data for a web application or other systems in hierarchical data storage systems such as a directory structure on a disk containing plain text files known as flat files, and indeed this is what was done in the early days of computing. However, by far the most common form of data storage for over 30 years has remained the relational database, a storage system that organises data into one or more tables containing rows and columns, a little like an Excel spreadsheet. Relational databases offer the ability to link information between multiple tables, as well as to rigidly describe constraints for the permitted format of given data records.
The power of relational databases, and the reason for their longevity, is that they provide flexibility in how the data in various tables can be sorted, organised, linked, combined and analysed. This power is leveraged through code written in SQL or Structured Query Language. SQL consists of a set of primitive commands for data definition, query and manipulation (insert, update, and delete) – these primitive commands or clauses can be built up into complex queries known as statements that return or update exactly the data needed from across an entire data set. Here’s a somewhat complicated example:
SELECT TO_CHAR(e.salary, 'X1234', 'NLS_NUMERIC_CHARACTERS = ''.,'' NLS_CURRENCY = ''£''') AS "Salary" FROM employees e JOIN job j e.jon_id = job_id LEFT JOIN employees m ON e.manager_id = m.employee_id ORDER BY e.eployee_id;
How does SQL relate to websites specifically?
Typical website development is split into “front end” and “back end” development, that is to say into:
- The User Interface (UI) design, consisting of the design and layout of pages (eg one to show your profile, one to login, one to show a product) and elements (eg navigation bar, logout button, settings link) of your web application; and
- The data that is used to populate different elements on the webpages.
These elements are separate enough that often a development team will consist of specialists in each area. With just a UI in place, but no data, any dynamic elements will only show up as empty boxes or blank fields. The data is stored in and retrieved from databases. The web server needs to make sure that whichever user is accessing the site sees their own information only – for that to happen it needs a way of retrieving the correct information from the database for a given user, which is where SQL is used, executed as code on the database server to retrieve or update the information using the parameters (values) that it gathered from the user. These values provided as input to the query can be either explicitly entered by the user (eg entering your name in an online form) or implicitly passed in as part of the link a user clicks on without you explicitly typing it.
A Non-Technical Explanation of SQL Injection
A web page or web application is said to have an SQL Injection vulnerability if it is possible for a user to “inject” code into the SQL query via the data that they submit, typically by using special characters. The injected code subverts the intended SQL to be executed and either alters, expands or replaces it in order to change application behaviour.
The first public discussions of SQL Injection or SQLi appeared in December 1998 when a security researcher named Jeff Forristal published an article in Phrack Magazine describing the flaw. Over 20 years later Injection Vulnerabilities are at the top of the OWASP Top 10 Vulnerability list where they have sat for almost 10 years.
To see why this is dangerous, imagine a login system that has a simple job – to login a user. That functionality might look like this pseudo-code example:
Login user <user>
It’s a simple instruction to log the user in. When a user provides their username, the system takes that username and populates the user variable in the SQL. If we pass in the username “daniel”, then the server executes:
Login user daniel
In SQL injection attacks, however, instead of passing in the username “daniel”, a malicious user exploits the fact that certain characters have special meaning within SQL. The malicious user “injects” these special characters into their input string to trick the SQL language into executing part of the input as code. In our pseudo-code example, they send in their username as “daniel’ and delete all other user records”. Now the server sees:
Login user daniel and delete all other user records
The server duly complies, and the database is wiped – the query structure and the supplied data were not separated correctly in the code. SQL injection vulnerabilities are based on this concept. Attackers are able to inject malicious instructions into benign ones, all of which are then sent to the database server through the front end web application.
A Technical Explanation of SQL Injection
Let’s take a look at a real world example rather than pseudo-code above. We’ll assume that – extending our pseudo-code example above – our web application is trying to check if a user login is valid with the credentials we just provided it. Whenever someone logs in, the following SQL statement is executed on the database:
$users = "SELECT * FROM users WHERE username = 'daniel' AND password = '1234'";
In written English, that translates roughly to “Select all users from the database where the username is daniel and the password is 1234” – we’d expect one result to be returned if the credentials are valid. But imagine that a malicious attacker is trying to find if our web application is vulnerable to SQL injection: they might change the values of ‘user’ and ‘password’ in our login form: instead of entering the username daniel, they enter admin’; —
This new username contains three special characters – characters that have unique meaning within SQL if they are interpreted as code. The first, and the most critical, is the ‘ character, which acts to indicate the start and stop points bracketing a variable. The second special character is ; which indicates that the end of the statement has been reached. And the third special character sequence — indicates that any text after that point should be interpreted as a comment rather than code to be executed.
The server now sees the SQL statement
$users = "SELECT * FROM users WHERE username = 'admin'; -- ' AND password = 'anything'";
It discards the “comment”, leaving:
$users = "SELECT * FROM users WHERE username = 'admin';
and interprets this as “Select all users where the username is admin” then stops processing any further. Since the requirement to provide a valid password has been commented out, the SQL returns that one record (since the user “admin” does indeed exist), and the user is logged in as admin to your site, without providing a password.
Potential impact of SQL injection
The three primary exploits that an attacker can perform using SQL injection to modify SQL statements are, in typical ascending order of severity (depending on the system):
- To access some or all data without authorisation, for example by tricking the database into providing too many results for a query;
- To subvert intended application behaviour that’s based on data in the database, for example by tricking an application into allowing a login without a valid password (this is the example we ran through above). No data is modified in the database, but the SQL commands are subverted to modify the application’s behaviour.;
- To actually alter data in the database without authorisation, for example by creating fraudulent records, adding users,“promoting” users to higher access levels, or deleting data;
- To execute commands on the host OS, escalating the attack to a form of Remote Code Execution and potentially subverting the entire host. This relies on the existence of features within the implementation of SQL on a case by case basis – eg Windows Server has a xp_cmdshell command that can be used to execute commands on the host OS.
Types of SQL Injection
There are many different forms of SQL, which we will cover in a later article in this series, including: Blind SQL Injection, Error Based SQL Injection, In-band SQLi, Union-based SQL, Boolean-based (content based), Time-based Blind SQLi, Out of band, and NoSQL injection
High Profile Example – “LulzSec”
One high profile example is a group calling themselves ‘Lulz Security’ also dubbed ‘LulzSec’ for short who spent a period of time hacking multiple well-known targets. This was covered in detail in our Educational Seminar Series at The Kia Oval Cricket Ground in March this year. On the 7th May 2011 LulzSec targeted the US version of the ‘talent’ show X-Factor. This attack resulted in the publishing of names, birthdates, phone numbers and email addresses of an estimated 250,000 contestants.
Following this on the 10th May Fox News became a target with over 400 sales contacts (usernames, email addresses, passwords and other sensitive data). On May 30th PBS website was defaced and a database of staff, authors and press room employees was leaked.
Then on the 2nd of June Lulsec began naming their targets, starting with operation ‘Sownage’ aimed at electronics giants Sony. This began with the disclosure of several Sony user databases, including thousands of usernames and passwords. The next day the FBI got involved with investigating the hacks, which led LulzSec to name their next target. The FBI. An FBI affiliate companies, Infraguard, became the targets resulting in the disclosure of email addresses, usernames and passwords of 180 clients.
British members Ryan Cleary (20) and Jake Davis (19) pleaded guilty to the hacks admitting to using automated tools such as Havij and SQLMap, and demonstrating that these attacks don’t require highly sophisticated technology or expert adversaries to be exploited on vulnerable systems.
Am I vulnerable to SQLi?
A recent report from Akamai discovered that 65% of web application attacks are SQLi based and states it is the fastest growing vulnerability. One of the dangers of Injection Attacks is that they can be completely automated to scan thousands of websites programatically – even smaller businesses that may not feel that they are sufficiently well known to be deliberately targeted may find themselves attacked purely by having an online discoverable presence, making most companies potential targets.
How to Detect SQL Injection
The Open Web Application Security Project (OWASP) suggest a mix of measures to ensure that you detect any SQL injection vulnerabilities in your code – including code review, vulnerability scanning (such as AppCheck) and static analysis together offer the best protection. They state that source code review is the best method of detecting if applications are vulnerable to injections, closely followed by thorough automated testing of all parameters, headers, URL, cookies, JSON, SOAP, and XML data inputs. Organisations can include static source (SAST) and dynamic application test (DAST) tools into the CI/CD pipeline to identify newly introduced injection flaws prior to production deployment.
SQL Injection Prevention
Prepared statements / Paramerised queries
SQLi vulnerabilities can be entirely eliminated in most instances by using parameterised queries or prepared statements . These methods of structuring SQL queries specify placeholders for parameters, rather than compiling the query as a string mixing both code and user-provided data. The database will always treat data within the placeholders as data and refuse to recognise any special characters within them, so that they do not become part of the executing SQL command. Prepared statements and object relational mappers (ORMs) make this easy for developers. If used correctly parameterised queries can prevent SQL injection and this approach is supported by all major application frameworks.
Where this is not supported, or it is thought that rework is not possible for resource reasons, then input validation can be considered, although it should be recognised that it is not nearly as robust a measure.
Input Validation involves adding code to your web application to filter any input provided through forms or URL parameters and attempt to identify known attack signatures of meta characters that are known to be problematic since they have special meaning as escape sequences etc in underlying languages such as SQL.
Unfortunately, global filters often do not work and can breed a false sense of security since it is possible to double-encode characters, or to encode characters using a different character set to pass validation checks. A more robust input validation alternative is to instead try to permit only known good characters, validate the structure of the input, impose length restrictions server side, and check input validation against a whitelist of permitted characters.
SQL Injection Mitigation
How can AppCheck Help?
AppCheck performs comprehensive checks for a wide range of injection vulnerabilities including:
- SQL Injection
- NoSQL Injection
- XPath Injection
- Code Injection
- Command Injection
- LDAP Injection
- Expression Language Injection
The AppCheck Vulnerability Analysis Engine provides detailed rationale behind each finding including a custom narrative to explain the detection methodology, verbose technical detail and proof of concept evidence through safe exploitation.
As always, if you require any more information on this topic or want to see what Injection Vulnerabilities AppCheck can pick up in your website and applications then please get in contact with us: firstname.lastname@example.org
Get started with Appcheck
No software to download or install.
Contact us or call us 0113 887 8380