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 can execute commands allowing for data retrieval from the database, destroying sensitive data, or other manipulative behaviors.
With the proper SQL command execution, the unauthorized user can 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 later). In its most severe form, SQL injection can allow attackers 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 where a man named Bob is on trial and 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 a simple comparison between normal and malicious SQL statements:
Normal SQL query:
The studentId string is passed into an SQL statement in this normal SQL query. 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 the command that 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 labeled ‘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 the query would usually search the database table for the matching ID, it now looks for an ID or tests to see if 1 equals 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 student’s table 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. In this case, an API 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 to exploit the database.
The proper development practices make SQL injections easy to implement and prevent. The reality is 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 with access to a database may be sufficient to expose a vulnerability.
How is an SQL Injection attack prevented?
There are several 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 first to 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. Object-relational mapping (ORM) libraries are commonly utilized for this purpose, as some versions will sanitize database inputs automatically.
- Escape All User Supplied Input – Specific characters or words have a particular meaning when writing SQL. For example, the ‘*’ character means “any,” and the word “OR” is a conditional. User-supplied input can be escaped to circumvent users who enter these characters accidentally or maliciously into an API request to the database. Escaping a character tells 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, stored procedures can help limit the risk associated with SQL injection. By adequately limiting the permissions of the database account running SQL queries, even non-robust application code 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 from being 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 due to an API call from an unauthorized request. While stored procedures are best utilized for static queries, enforcing the least privilege can help reduce the risks of dynamic SQL queries.
What is a compound SQL injection attack?
Clever attackers sometimes implement multi-vector attacks against a targeted website to circumvent security measures. 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 disruption methods are sometimes used as a distraction to implement sweeping SQL injection attacks. As a result, a comprehensive threat mitigation strategy provides the broadest range of protection.
