How to prevent SQL injection

How SQL injection attacks work

Structured Query Language injection (SQLi) is a code injection attack that allows attackers to retrieve, manipulate, or destroy sensitive information located in SQL databases. These attacks work by inserting specialized commands into SQL query fields; when executed, the commands may enable attackers to spoof the identity of legitimate users, view or retrieve protected data, and even gain root access to servers.

Often, attackers carry out SQLi by exploiting vulnerabilities in application programming interfaces (APIs) that cannot properly differentiate between legitimate and untrusted code. Without the ability to detect altered commands or queries, these APIs can be used to execute malicious requests, such as bypassing web application firewalls (WAF) or authentication measures.

Typically, SQLi is performed using one of three methods:

1. In-band SQL injection uses a single communication channel to initiate and complete an attack. Common types of in-band SQLi include error-based SQLi (when error messages help attackers identify critical information about the underlying database) and union-based SQLi (when attackers use UNION SQL operators to uncover vulnerabilities in the database). This is the simplest and most common form of SQLi.
2. Out-of-band SQL injection, by contrast, does not allow attackers to use the same communication channel to initiate and complete an attack. Instead, the compromised application must be able to exfiltrate data to a remote endpoint within the attacker’s control, often via DNS or HTTP request. This is the most difficult and least common form of SQLi.
3. Inferential SQL injection, also called blind SQLi, requires attackers to send payloads to the targeted server in order to learn how to exploit it. These usually take one of two forms: blind Boolean-based SQLi (when attackers use true-false queries to force a server to produce different responses) or blind time-based SQLi (when attackers can infer the same information via variations in the server’s response times). This often takes more time to complete than in-band SQLi, but can be equally damaging.

To see real-world examples of benign and malicious SQL queries, read What is SQL injection?

How to prevent SQL injection

While SQL injection is one of the most prevalent API threats, it can be effectively avoided with the right prevention strategies. Helpful approaches for preventing SQL injection include restricting database procedures, sanitizing database inputs, and enforcing least-privilege access.

Restrict database procedures and code

SQL injection largely depends on an attacker’s ability to manipulate data inputs and database functions. By restricting these inputs and limiting the type of database procedures that can be performed, organizations can minimize the risk of unauthorized or malicious queries. Ways of doing so include:

• Enforcing prepared statements and parameterized queries: Prepared statements define acceptable SQL code, then set specific parameters for incoming queries. Any malicious SQL statements are classified as invalid data inputs, rather than executable commands.
• Using stored procedures: Like prepared statements, stored procedures are prepared and reusable SQL statements that can be retrieved from a database — and prevent malicious parties from executing code directly on the database itself.

Validate and sanitize database inputs

User inputs into any SQL database should be regularly monitored, validated, and sanitized to eliminate malicious code. Input validation ensures that data is properly inspected and formatted according to predetermined criteria, while input sanitization modifies (or “sanitizes”) the input by removing invalid or unsafe characters and reformatting it as necessary. Ways of ensuring input validation include:

• Establishing an allowlist: An allowlist can help define valid user inputs, against which the database can check (and reject) incoming queries that appear abnormal. For instance, special characters and extended URLs are two types of user inputs that can be exploited by attackers to gather information about a database (before running malicious queries). Limiting the use of these inputs can help minimize the likelihood of an attack.
• Escaping user-supplied input: Organizations may also choose to escape (i.e. treat as input, rather than commands or conditionals) all user-supplied input, so that specific characters or words cannot be used to form malicious requests.

Enforce least-privilege access

Least-privilege access is the principle of giving users only as much access to protected resources as their role requires. For example, this may mean limiting the number of users who are granted administrator-level privileges to a database, or even giving users temporary admin-level access that can later be revoked.

Restricting user access on a role-based level also helps minimize the impact of a breach, as attackers who breach a database using stolen credentials will be similarly limited in their ability to view, modify, steal, or destroy protected data. For the same reason, organizations should limit shared access to databases across multiple websites and applications.

How Cloudflare helps prevent SQL injection

Cloudflare helps organizations improve their resilience against SQLi attacks with a powerful application and API security portfolio:

• Cloudflare WAF monitors traffic patterns for potential SQL exploits, detects bypasses and variations in attack types, and uses advanced machine learning technologies to adapt WAF rulesets to evolving attack methods
• Cloudflare D1 is a serverless SQL database that natively integrates with Workers to implement prepared statements and prevent users from modifying or deleting databases