What is SQL injection and how to prevent it? SQL injection is a weakness where an application builds a database query by pasting in text from a user, and that text changes what the query does. The fix is to keep the query and the user's data apart, so the database never mistakes data for commands. It is one of the weak spots that whitehatstoic, a software, security and AI safety practice, looks for in web apps.
What is SQL injection and how to prevent it
SQL is the language most relational databases use to read and change stored data. A web app sends SQL to its database whenever it logs someone in, loads a profile, searches a catalog or saves a form. The trouble starts when the app creates that SQL by combining fixed text with values the user typed.
A database cannot tell which part of the string the developer wrote and which part a stranger wrote. It reads the whole thing as one command. If the stranger's text contains SQL syntax, the database runs it. That is SQL injection, a code injection technique in which malicious SQL statements are inserted into an entry field for execution, for example to dump the contents of a database.
How a SQL injection attack works
Take a login form. The app takes the name and password from the form and builds a query in code from three pieces: the text SELECT * FROM users WHERE name = ', then the typed name, then the rest of the check on the password.
A normal user types alice and the query looks for a row with that name and password. Now someone types ' OR '1'='1 as the password. The single quote closes the string early. The text after it becomes part of the query. The condition '1'='1' is always true, so the check on the password no longer filters anything. The query returns a row, and the app logs the attacker in.
The attacker did not break the database. The database did what it was told. The app let outside text become part of the instruction.
The same shape appears wherever a value reaches a query as text: search boxes, URL parameters, cookies and form fields of every kind.
Types of SQL injection: blind, out-of-band and second-order
Blind
Blind SQL injection is used when an app is vulnerable but the results of the injection are not visible to the attacker. The page may not display data at all, but it displays differently depending on the result of a logical statement injected into the query. One form adds a condition that is true and one that is false, such as 1=1 and 1=2 in a URL, and compares what comes back. If the pages differ, the input reaches the query.
Out-of-band
Out-of-band SQL injection occurs when the results can only be retrieved through a separate outbound HTTP or DNS channel from the target to a server the attacker is listening on.
Second-order
Second-order SQL injection occurs when an application guards its SQL against immediate user input but is less strict with data already stored in the system. The hostile text is saved safely at first and does its harm later, when another query uses it. Checking only the form where the text was entered misses it.
Why SQL injection still appears in web apps
The cause is old and simple: it is faster to build a query by concatenating strings than to use placeholders. Old code stays in production. Quick scripts become real services. Developers use a framework with a safe default, then write one raw query by hand for a hard report and concatenate strings there.
The weakness is still common. In the 2021 edition of the OWASP Top 10, injection was detected in 94% of the applications analyzed, and the definition of injection was widened to cover other kinds of attack as well, according to the Wikipedia article on SQL injection.
How to prevent SQL injection with parameterized queries
The main defense is the parameterized query, also called a prepared statement. The app sends a template with placeholders, such as SELECT * FROM users WHERE name = ?, and sends the values separately. The database fixes the structure of the query first and substitutes the values afterwards as plain data of a defined type.
Run the login attack again. The attacker still types ' OR '1'='1. The database now compares the password with that exact odd string. It finds no match. The quote has no power to end anything, because the structure was set before the value arrived.
Two habits make this work in practice:
- Use a placeholder for every value, including ones that look harmless, such as numbers and IDs.
- Keep the query text fixed. If part of the text is built from user input first and parameters are added after, the weakness is still there.
Input validation, least privilege and other defenses
Parameterized queries come first. OWASP, as the Wikipedia article reports, lists parameterized queries, input validation and least privilege as the core defenses.
Input validation
Check that a value has the expected type, length and format before it reaches the database. An age should be a whole number. A country code should match a known list. Validation catches mistakes and shrinks the attack surface. It does not replace parameters, because some fields must accept odd characters. A person named O'Brien needs the apostrophe.
Escaping special characters by hand is weaker still. OWASP describes this approach as "frail compared to other defenses".
Least privilege
The principle of least privilege says an account should have only the permissions it needs. For a database, that means the account the app uses holds the minimum rights for its job, and a read-only account has no rights to modify anything. If an injection slips through, the attacker gets a small account.
Finding SQL injection weaknesses in a codebase
Start with a search. Look for query strings built with concatenation or template strings near words such as SELECT, INSERT, UPDATE and DELETE. Each hit is a place to trace backward: where does each value come from?
Trace all the sources, not only form fields. Check URL parameters, cookies, uploaded file contents, values read from other tables, and responses from outside services. Follow stored values to the queries that read them later, to catch the second-order case.
Then probe the running app, only where you own the system or have written permission. Send a condition that is always true and one that is always false, and compare the responses. A difference suggests the input reaches the query.
After a fix, repeat the same probes, and keep a search for concatenated queries in every change that is merged so the problem does not return.
SQL injection and its link to prompt injection
SQL injection belongs to the wider group of injection weaknesses, and prompt injection in language model apps shares its root cause. In both, text from outside is read as an instruction. In indirect prompt injection, the instruction sits in external data such as emails and documents, and the model mistakes it as coming from the user or the developer. Researchers showed that real-world applications built around language models can be compromised this way.
The database has a firm answer in the parameterized query. A language model reads everything as text, so no placeholder of that kind exists for it.
Frequently asked questions
Is escaping user input enough to stop SQL injection?
No. OWASP calls escaping frail compared to other defenses. Parameterized queries keep data out of the command path entirely, which is a stronger fix.
What is blind SQL injection?
It is SQL injection where the attacker cannot see the results directly. The page behaves differently depending on a true or false condition in the injected text, and that difference gives the answer.
What is second-order SQL injection?
Hostile text is stored safely at first and used later in a query that is less strictly guarded. Checking only the form where the text was entered misses it.
What is the difference between SQL injection and prompt injection?
SQL injection puts attacker text into a database query. Prompt injection puts attacker instructions into the text a language model reads. Both succeed because the system treats outside data as instructions.
How can SQL injection be found in an existing app?
Search the code for queries built by concatenating strings, trace each value back to its source, and probe the running app with always-true and always-false conditions, on systems you are authorized to examine.