Introduction
SQL injection happens when an application treats untrusted input as part of a database command instead of as data. A vulnerable query can expose records or change application behavior because the database receives a different instruction than the developer intended. The problem is preventable: parameterized queries keep SQL structure separate from the values supplied by users.
This tutorial explains SQL injection with a fictional local application, shows vulnerable and safe Python examples, and covers input handling, least privilege, and why manually escaping strings is not a dependable general defense.
Key takeaways
- SQL injection occurs when input changes the meaning or structure of a SQL statement.
- Use parameterized queries for values; do not build SQL by concatenating user input.
- Validate input for business rules, but do not treat validation or escaping as a replacement for parameterization.
- Give the application database account only the permissions it needs.
- Parameters generally cannot stand in for SQL identifiers such as table names or sort directions; use strict allowlists for those cases.
How SQL injection works
Imagine a fictional local application that looks up a user account by the username provided in a request. Its intended query is:
SELECT id, username FROM users WHERE username = 'alice';
The application should ask the database to find the row whose username equals the supplied value. If it instead inserts that value directly into a SQL string, an input containing SQL syntax can change the condition itself.
For example, a test value such as ' OR '1'='1' -- could turn a concatenated query into something equivalent to:
SELECT id, username FROM users WHERE username = '' OR '1'='1' -- ';
In SQL dialects where -- begins a comment, the remainder of the line is ignored. Since '1'='1' is true, the condition may match more rows than intended. The exact behavior depends on the database and query context; the important point is that the input has become part of the SQL program rather than remaining a username value.
A vulnerable example
This illustrative Python example uses SQLite and is intentionally unsafe. Do not use this query-building pattern in an application:
import sqlite3
connection = sqlite3.connect("app.db")
username = request.args.get("username", "")
query = f"SELECT id, username FROM users WHERE username = '{username}'"
row = connection.execute(query).fetchone()
The f-string places username directly inside SQL syntax. If the value contains quotes or SQL operators, it may alter the query. The issue is not that the input is inherently malicious; the issue is that the application gives the input control over the SQL statement's structure.
Stop SQL injection with parameterized queries
A parameterized query defines the SQL separately from its values. The database driver sends the statement and the username as distinct pieces, so the value is treated as data rather than parsed as SQL syntax.
import sqlite3
connection = sqlite3.connect("app.db")
username = request.args.get("username", "")
row = connection.execute(
"SELECT id, username FROM users WHERE username = ?",
(username,),
).fetchone()
In this SQLite example, ? is a parameter placeholder, and (username,) supplies one value. The trailing comma matters: it makes the value a one-item tuple. Other database drivers use their own placeholder formats, so follow the driver documentation rather than copying placeholder syntax blindly.
Parameterized queries are not limited to lookups. Use them for values in inserts, updates, and deletes as well:
connection.execute(
"UPDATE users SET display_name = ? WHERE id = ?",
(display_name, user_id),
)
The placeholders represent values, not fragments of SQL. For example, a parameter generally cannot safely replace a table name, column name, or ASC/DESC keyword. If a query must vary by one of those elements, choose from a fixed allowlist of valid options and keep user-provided values parameterized.
sort_columns = {
"name": "username",
"created": "created_at",
}
sort_column = sort_columns.get(request.args.get("sort"), "username")
rows = connection.execute(
f"SELECT id, username FROM users ORDER BY {sort_column} LIMIT ?",
(page_size,),
).fetchall()
Here, the interpolated identifier comes only from a fixed application-controlled mapping, not directly from the request. The limit remains a bound value. Keep the allowlist narrow and review any query fragments assembled this way.
Input handling: validate for meaning, bind for safety
Input validation and parameterization solve different problems:
- Parameterization protects SQL structure. It prevents a supplied value from being interpreted as SQL syntax in that query.
- Validation enforces application rules. It can ensure a page size is within an allowed range, a username meets length limits, or an identifier has the expected format.
Apply validation when the application has a meaningful rule to enforce. For example, reject an invalid page size rather than silently allowing an extreme value. But do not rely on validation alone to make SQL concatenation safe: valid-looking text can still contain characters that matter to a particular SQL context.
Avoid trying to create a universal “safe SQL string” by manually escaping quotes. Escaping rules vary by database, character encoding, and query context, and can be applied inconsistently. A string escaped for one context may not be safe in another. Use the database driver's parameter-binding feature instead. If a library or ORM supports parameterized queries, use its documented safe APIs; raw SQL and dynamically assembled fragments still require careful review.
Least privilege limits the impact
Parameterization is the primary defense against SQL injection, but database permissions provide an additional security boundary. The account used by an application should have only the permissions required for its tasks. A service that reads account profiles, for example, generally should not use an account with permission to alter database schemas or manage other database users.
Use separate accounts or roles when different components need different access. Keep credentials out of source control, restrict access to them, and rotate them according to your organization's practices. Least privilege cannot make an unsafe query safe, but it can reduce what an attacker could do if another weakness is present.
Common mistakes and troubleshooting
- Concatenating input because it was validated. Validation is useful, but values should still be bound as parameters.
- Escaping quotes manually. This is fragile across database systems and SQL contexts. Prefer the database driver's parameter API.
- Using parameters for identifiers. Placeholders generally represent values, not table or column names. Use a fixed allowlist for dynamic identifiers.
- Assuming an ORM makes every query safe. ORM query builders often bind values, but raw-query methods and string-built conditions can reintroduce injection risks.
- Assuming stored procedures are automatically safe. A procedure that constructs and executes dynamic SQL from untrusted strings can still be vulnerable.
- Testing only the happy path. Add tests for quotes, unexpected operators, empty values, and boundary cases. Confirm that inputs remain data and cannot change the intended query structure.
- Granting broad database permissions. Use narrowly scoped application roles rather than relying on permissions that are unnecessary for normal operation.
If a parameterized query fails, check that the placeholder syntax matches the database driver, that each placeholder has a corresponding value, and that values are supplied in the expected order and type. For dynamic ordering or filtering, confirm that the chosen SQL fragment comes from a strict allowlist.
Defensive workflow
- Find queries that include request data, form values, cookies, or other externally controlled input.
- Replace string concatenation and formatting with the database driver's parameterized API.
- Validate values against application requirements, independently of query safety.
- For dynamic SQL identifiers or keywords, map accepted choices to fixed, reviewed SQL fragments.
- Review raw SQL, ORM escape hatches, and stored procedures for string-built statements.
- Restrict the database account's permissions and test expected behavior using a local or authorized test environment.
Conclusion
SQL injection is a query-construction flaw: untrusted input is allowed to change SQL structure. Keep that structure separate from values with parameterized queries, validate inputs for application-specific rules, and use least-privilege database accounts to limit exposure. Manual escaping is not a dependable substitute for binding parameters.