What is SQL injection?

What is SQL injection (SQi)?

Structured Query Language (SQL*) Injection is a code injection technique used to modify or retrieve data from SQL databases. By inserting specialized SQL statements into an entry field, an attacker is able to execute commands that allow for the retrieval of data from the database, the destruction of sensitive data, or other manipulative behaviors.

With the proper SQL command execution, the unauthorized user is able to spoof the identity of a more privileged user, make themselves or others database administrators, tamper with existing data, modify transactions and balances, and retrieve and/or destroy all server data.

In modern computing, SQL injection typically occurs over the Internet by sending malicious SQL queries to an API endpoint provided by a website or service (more on this later). In its most severe form, SQL injection can allow an attacker to gain root access to a machine, giving them complete control.

*SQL is a programming language used to maintain most databases.

How does a SQL injection attack work?

Imagine a courtroom in which a man named Bob is on trial, and is about to appear before a judge. When filling out paperwork before the trial, Bob writes his name as “Bob is free to go”. When the judge reaches his case and reads aloud “Now calling Bob is free to go”, the bailiff lets Bob go because the judge said so.

While there are slightly different varieties of SQLi, the core vulnerability is essentially the same: a SQL query field that is supposed to be reserved for a particular type of data, such as a number is instead passed unexpected information, such as a command. The command, when run, escapes beyond the intended confines, allowing for potentially nefarious behavior. A query field is commonly populated from data entered into a form on a webpage.

Let’s look at at a simple comparison between normal and malicious SQL statements:

Normal SQL query:

In this normal SQL query, the studentId string is passed into a SQL statement. The goal is to look through the list of students for a student that matches the studentId entered. Once found, that student’s record will be returned. Put simply, the command says “go find this user and give me their data”.

The code might look something like this:

studentId = getRequestString("studentId");
lookupStudent  = "SELECT * FROM students WHERE studentId = " + studentId
   

If a student enters a student ID of 117 inside a webpage form labelled 'Please enter your student ID number'

the resulting SQL query will look like:

SELECT * FROM students WHERE studentId = 117;

This command will return the record for the particular student with a studentId, which is what the developer who wrote the API expects to have happen.

SQL Injection query:

In this example, an attacker instead enters a SQL command or conditional logic into the input field, he enters a student ID number of:

Where normally the query would search the database table for the matching ID, it now looks for an ID or tests to see if 1 is equal to 1. As you might expect, the statement is always true for every student in the column, and as a result, the database will return all data from the students table back to the attacker making the query.

SELECT * FROM students WHERE studentId = 117 OR 1=1;

SQLi works by targeting a vulnerable Application Programming Interface or API. An API in this case is the software interface through which a server receives and responds to requests.

Commonly used tools exist that allow a malicious actor to automatically search through a website looking for forms, and then attempt to input various SQL queries that may generate a response that the website’s software developers did not intend in order to exploit the database.

SQL injections are easy to implement, and interestingly, also fairly easy to prevent given the proper development practices. The reality is more murky, as tight deadlines, inexperienced developers, and legacy code often result in variable code quality and security practices. A single vulnerable field on any form or API endpoint across a website that has access to a database may be sufficient to expose a vulnerability.

How is a SQL Injection attack prevented?

There are number of methods for reducing the risk of a data breach due to SQL injection. As a best practice, several strategies should be utilized. Let’s explore a few of the more common implementations:

• Use of Prepared Statements (with Parameterized Queries) - This method of sanitizing database inputs involves forcing the developers to first define all the SQL code, and then to pass only specific parameters to the SQL query; data entered is explicitly given a limited scope that it can not expand beyond. This allows the database to make the distinction between data that’s being input and code that’s to be run, regardless of the type of data supplied in the input field. Some object-relational mapping (ORM) libraries are commonly utilized for this purpose, as some versions will sanitize database inputs automatically.
• Escape All User Supplied Input - When writing SQL, specific characters or words have particular meaning. For example, the ‘*’ character means “any” and the words “OR” is a conditional. To circumvent users who enter these characters either accidentally or maliciously into an API request to the database, user supplied input can be escaped. Escaping a character is the way of telling the database not to parse it as a command or conditional but instead treat it as literal input.
• Use of Stored Procedures - Although not a robust security strategy by itself, stored procedures can help limit the risk associated with SQL injection. By properly limiting the permissions of the database account running SQL queries, even non-robust application code that is vulnerable to SQL injection will lack the permissions necessary to manipulate unrelated database tables. Stored procedures may also check the type of input parameters, preventing data to be input that violates the type the field is designed to receive. In instances where static queries are insufficient, stored procedures are typically to be avoided.
• Enforce Least Privilege - As a general rule, in all instances where a website needs to use dynamic SQL, it is important to reduce the exposure to SQL injection by limiting permissions to the narrowest scope required to execute the relevant query. In its most obvious form, this means that an administrative account should in no instance be executing SQL commands as a result of a API call from an unauthorized request. While stored procedures are best utilized for static queries, enforcing least privilege can help reduce the risks of dynamic SQL queries.

What is a compound SQL injection attack?

In order to circumvent security measures, clever attackers will sometimes implement multi-vector attacks against a targeted website. While a single attack may be mitigated, it can also become the focus of attention for database administrators and information security teams. DDoS attacks, DNS hijacking and other methods of disruption are sometimes used as a distraction to implement sweeping SQL injection attacks. As a result, a comprehensive threat mitigation strategy provides the widest range of protection. Cloudflare’s web application firewall, DDoS mitigation and DNS security comprise core elements of a holistic security strategy.