SQL Injection Attacks: What Are They and How to Detect Them (with Examples!)

In honor of Cybersecurity Awareness Month, we're kicking off a series on network attacks, so you can better understand how they work and how to stop them.

One of the most devastating database attack techniques is also the most prevalent. SQL injection (SQLi) gives attackers an alarming amount of access to a website. With a small piece of code, an attacker can steal data, delete data, change a website, or compromise a server to launch more attacks.

Code injection flaws are the most critical web application security risk according to the OWASP foundation. A key factor that makes SQLi such a common, dangerous technique is the prevalence of injection vulnerabilities in web applications.

How Does SQLi Work?

Let's start with a short, general background on how web applications relate to Structured Query Language (SQL). Web applications are programs that are supported by a web server. Applications can also be linked to a database, which usually holds important website or application data. Relational databases, the most common database type, contain structured data within tables. That data can be managed by writing queries in SQL. For example, a query for retrieving data stored in an "accounts" table begins with the following SQL statement.

SELECT * FROM accounts

User interactions with a web application affect which data is retrieved from a linked database—e-commerce shopping carts, search filters, or login forms. When a user enters information (such as username and password) into a login form, the web application processes that information as parameter data in the SQL query, which then retrieves account data for the user from the database.

SELECT * FROM accounts WHERE username = 'sea monster' AND password = 'kraken'

SQLi occurs when the attacker injects a piece of SQL code, or fragment, into the web page that generates a malformed SQL query and has unintended results. Returning to the login form example, the attacker might enter a single quote and comment delimiter SQL fragment ('--) after the username (sea monster'--). The comment delimiter cuts the SQL query short, instructing the database to ignore the password field when retrieving account data. The unintended result is that the attacker can view account data without submitting a legitimate password.

SELECT * FROM accounts WHERE username = 'sea monster'--' and password ' '

Many injection techniques exist, varying by the vulnerability and the database management system (DBMS) that they exploit. Attack techniques can be generally grouped into these categories:

• In-band SQLi: The web application includes specific error messages for SQL syntax errors in HTTP responses. The web application also includes query results in HTTP responses. After an injection attempt, the attacker can refine their injection technique based on error messages and results.
• Blind (inferential) SQLi: The web application does not include specific error messages or query results in HTTP responses. The attacker must make several injection attempts—with conditional true/false or time-based statements—to evaluate HTTP responses and refine their injection technique.
• Out-of-band SQLi: The web application does not include specific error messages or query results in HTTP responses. The attacker injects DBMS commands for the database to send DNS or HTTP requests with information to an attacker-controlled server, providing an indirect method for refining their injection technique.

How to Detect SQLi Attacks

Detection methods range from checking server logs to monitoring database errors. Most network intrusion detection systems (IDS) and network perimeter firewalls are not configured to review HTTP traffic for malicious SQL fragments, making it possible for an attacker to bypass network security boundaries. Web application firewalls (WAF) can be integrated into security solutions to filter HTTP requests that match SQLi attempts. But a WAF must be continuously updated to filter new techniques.

ExtraHop automatically detects unusual HTTP traffic crossing the network that could result in malformed SQL queries.

From the detection card, defenders can investigate records of HTTP transactions containing the URL-encoded SQL fragments that can alter SQL queries. URL-encoded refers to the escaped reserved characters, such as spaces and single quotes, within the URL of the HTTP request. Encoding non-ASCII characters into the URL ensures that the HTTP request successfully crosses the internet. When the HTTP request arrives at the web server, the web application decodes the URL and processes the data.

Identifying URL-encoded SQL fragments can help you create SQLi match conditions in your WAF or escape these input character values in web application code.

In the example below, an attacker injected a single quote and space introducing the UNION SQL operator (' UNION all SELECT) into the name field of a login form, generating an HTTP request with this URL-encoded fragment: %27%20UNION%20ALL%20SELECT. The UNION operator combines the results of two or more SELECT statements into one HTTP response.

The Potential SQLi field shows an unusual parameter value for the "name" field. Instead of a username, there is a URL-encoded SQL fragment with a UNION operator.

You can find additional SQL injection examples at the OWASP SQL Injection website and verify escaped characters in this URL encoding reference.

How to Prevent SQLi

The best way to mitigate SQLi is to reduce the number of vulnerabilities an attacker can exploit. Check out the OWASP SQLi Cheat Sheet for tips on how to prevent SQLi attacks, including the following best practices:

• Validate user input. Reject SQL fragments submitted with user input so they are not processed into SQL statements.
• Escape input. Specific characters such as a single quote (') have a specific meaning in SQL statements. But blocking these characters from user input might not be feasible because they're also needed for valid input (for a username such as O'Brien, for example). These values can be escaped, or placed into quotes, so that the data can still be incorporated into the SQL statement in the proper context.
• Implement parameterized queries and prepared statements. These can be hard-coded into the application to keep user-submitted input separate from queries and commands.
• Implement stored procedures. These can be hard-coded into the database to keep user input separate from commands.
• Enforce the principle of least privilege. Prevent unauthorized users from making database changes.

Despite these mitigation options, attackers can still catch web and database teams by surprise. Well-known, publicly-available tools can help attackers perform vulnerability scans, fuzzing (the process of discovering new, unknown vulnerabilities), and SQLi attacks. Stay vigilant for unusual HTTP requests to prevent attackers from accessing web servers and valuable data.

To see how ExtraHop Reveal(x) detects SQL injection and provides the contextual information needed to stop an attack, take a look at our demo.