Menu
Structured Query Language (SQL) injection remain one of the oldest and most common web application vulnerabilities. Learn what they are, how they work, how to prevent them and more with AppCheck.
An SQL injection is an attack vector that involves inserting malicious code in SQL statements via input data from the client to the application. Hackers generally use SQL injections in an attempt to steal personal information like passwords and card details. But before we go into more detail, let’s take a step back.
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;
Typical website development is split into “front end” and “back end” development, that is to say into:
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 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
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 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.
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.
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):
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.
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.
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.
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
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.
AppCheck performs comprehensive checks for a wide range of injection vulnerabilities including:
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: info@localhost
No software to download or install.
Contact us or call us 0113 887 8380
AppCheck is a software security vendor based in the UK, offering a leading security scanning platform that automates the discovery of security flaws within organisations websites, applications, network and cloud infrastructure. AppCheck are authorized by te Common Vulnerabilities and Exposures (CVE) Program aas a CVE Numbering Authority (CNA)
