KAHIBARO
Discord Login Register ☰
☰

15.3 SQL Injection

Table of Contents

Understanding SQL Injection

SQL injection is one of the most dangerous and common attacks against backend applications that use relational databases. It happens when user input ends up inside an SQL query in an unsafe way, so the attacker can change what the query does.

This chapter focuses on how SQL injection works, how it is exploited, and how to defend against it in the context of backend development.

Core idea of SQL injection
User-controlled data must never be directly concatenated into SQL strings.
You must use parameterized queries or safe query builders / ORMs for all dynamic values.

A Simple Example Of SQL Injection

Vulnerable login query

Imagine a simple login endpoint:

python 
username = request.form["username"]
password = request.form["password"]
query = (
    "SELECT * FROM users "
    "WHERE username = '" + username + "' "
    "AND password = '" + password + "';"
)
cursor.execute(query)
user = cursor.fetchone()

If the user submits:

The query becomes:

sql 
SELECT * FROM users
WHERE username = 'alice'
  AND password = 'secret';

That looks fine. But an attacker can send:

Then the query becomes:

sql 
SELECT * FROM users
WHERE username = 'alice' --'
  AND password = 'anything';

In SQL, -- starts a comment to the end of the line. Everything after -- is ignored:

sql 
SELECT * FROM users
WHERE username = 'alice'

Now the password check is removed. The attacker logs in as alice without knowing her password.

How SQL Injection Works

Where the vulnerability comes from

The problem is this pattern:

text 
"SELECT ... WHERE column = '" + user_input + "';"

The application builds SQL by mixing code and data in a single string. The database cannot tell which characters are part of the data and which are part of the SQL syntax.

If the user input contains characters like ', ", ;, --, /, /, or keywords like OR, AND, UNION, DROP, it can change how the SQL is interpreted.

Typical attack payloads

Some classic malicious inputs:

GoalExample inputEffect
Bypass login check' OR 1=1 --Makes condition always true
Bypass numeric check0 OR 1=1Makes id = 0 OR 1=1, returns many rows
Terminate and add query1; DROP TABLE users; --Ends original query, runs DROP TABLE
Read more data' UNION SELECT * FROM credit_cards --Combines results from another table
Detect vulnerability' or " or ')Often causes syntax errors, reveals stack traces

Example of a login bypass payload:

text 
username: ' OR 1=1 --
password: anything

Query becomes:

sql 
SELECT * FROM users
WHERE username = '' OR 1=1 --'
  AND password = 'anything';

OR 1=1 is always true, the password condition is commented out, so the attacker usually gets access as the first user in the table.

Types Of SQL Injection

Some useful categories for understanding how attacks work.

1. In-band SQL injection

The attacker both sends the malicious input and receives results using the same channel (the normal HTTP response).

Two common subtypes:

TypeDescriptionExample effect
Error-basedForces database errors to reveal informationLeaks table/column names, DB version
Union-basedUses UNION SELECT to combine attacker-chosen dataShows data from other tables in page

Example, UNION-based:

Suppose the app runs:

sql 
SELECT id, title, body
FROM posts
WHERE id = <user_input>;

If the attacker controls id and the app prints titles and bodies directly, they can send:

text 
id = 1 UNION SELECT 1, username, password FROM users

Final query:

sql 
SELECT id, title, body
FROM posts
WHERE id = 1 UNION
      SELECT 1, username, password FROM users;

The page might now display usernames and password hashes instead of blog posts.

2. Blind SQL injection

The application does not show database errors or query results directly. But the attacker can still infer information from behavior changes, like:

Two main forms:

TypeDescriptionExample
Boolean-basedUses true/false conditions and observes differencesAND 1=1 vs AND 1=2
Time-basedUses SLEEP() or similar functions and measures timeAND IF(condition, SLEEP(5), 0)

Boolean-based example:

The app runs:

sql 
SELECT * FROM products WHERE id = <user_input>;

Attacker tests:

text 
id = 1 AND 1=1
id = 1 AND 1=2

If the first returns a normal page and the second returns an empty or error page, the attacker knows the input is inside an SQL condition and can build more complex conditions to extract data bit by bit.

3. Out-of-band SQL injection

The attacker cannot use the normal HTTP response or timing easily, but the database can reach out to external services.

For example, some databases can:

The attacker crafts SQL that triggers such behavior and then monitors their own server logs to see which data was exfiltrated.

This is rarer in typical backend apps for beginners, but it exists, especially when database features like LOAD_FILE or external functions are enabled.

Where SQL Injection Shows Up In Backends

Common vulnerable places

SQL injection can happen anywhere user input ends up in a query:

  1. Authentication
    Login forms, "remember me" tokens, password reset tokens.
  2. Search
    Search boxes with queries like:
sql 
   WHERE title LIKE '%<user_input>%'
  1. Filtering, sorting, pagination
    • WHERE clauses built from filters.
    • ORDER BY built from sort fields.
    • LIMIT and OFFSET from user input.
  2. Admin panels
    "Export CSV" features, reporting dashboards, advanced filters.
  3. APIs
    Query parameters and JSON bodies used directly in SQL.
  4. Hidden input sources
    • HTTP headers (User-Agent, Referer).
    • Cookies.
    • URL path segments.

Any place where developers think "users cannot modify this" is dangerous. Attackers often can.

Example: search endpoint

Vulnerable code:

python 
search = request.args.get("q", "")
query = f"SELECT * FROM products WHERE name LIKE '%{search}%';"
cursor.execute(query)

Attacker sends:

text 
?q=' OR 1=1 --

Query becomes:

sql 
SELECT * FROM products
WHERE name LIKE '%' OR 1=1 -- %';

The OR 1=1 makes the condition always true, so the attacker sees every product, possibly including unpublished ones.

Real-World Impact Of SQL Injection

Depending on how severe the vulnerability is, SQL injection can allow attackers to:

Impact levelPossible attacker actions
Read dataDump users, emails, password hashes, secrets, logs, etc.
Modify dataChange balances, approve orders, alter permissions
Delete dataDrop tables, truncate logs, erase users
Escalate privilegesPromote self to admin, create backdoor accounts
Execute system commandsOn some setups, run OS commands through DB functions
Persist further accessInstall malicious triggers, store webshells, change config

For a backend developer, even "just" reading the users table can be catastrophic, because passwords, tokens, etc, may be leaked.

Defending Against SQL Injection

1. Use parameterized queries

This is the most important protection.

With parameterized queries (also called prepared statements), you write SQL with placeholders, and pass user values separately:

python 
# Python with psycopg2 (PostgreSQL)
username = request.form["username"]
password = request.form["password"]
query = """
    SELECT * FROM users
    WHERE username = %s
      AND password_hash = crypt(%s, password_hash);
"""
cursor.execute(query, (username, password))
user = cursor.fetchone()

The driver:

  1. Sends the SQL structure with placeholders to the database.
  2. Sends the values separately.
  3. The database always treats values as data, not SQL code.

Even if username is:

text 
alice' OR 1=1 --

The driver will escape it correctly and the query will still have the same structure:

sql 
WHERE username = 'alice'' OR 1=1 --'

The attacker cannot break out of the string literal.

Examples in different languages / libraries

EnvironmentPlaceholder styleExample
psycopg2 (Python)%scursor.execute("... WHERE id = %s", (id_val,))
sqlite3 (Python)?cursor.execute("... WHERE id = ?", (id_val,))
Node, pg$1, $2, ...client.query("... WHERE id = $1", [idVal])
Java, JDBC?PreparedStatement ps = con.prepareStatement("...")
Go, database/sql?db.Query("... WHERE id = ?", idVal)

Rule: Always use parameterized queries
Any time you include user-controllable values in SQL, you must use parameters, never string concatenation or manual interpolation.

2. Use an ORM or safe query builder

ORMs and query builders usually parameterize values for you.

Example with SQLAlchemy (Python):

python 
user = db.query(User).filter(User.username == username).first()

Example with SQLAlchemy Core:

python 
from sqlalchemy import select
stmt = select(User).where(User.id == user_id)
result = session.execute(stmt)

The ORM converts this to parameterized SQL under the hood.

However:

Unsafe:

python 
stmt = text(f"SELECT * FROM users WHERE username = '{username}'")
session.execute(stmt)

Safe:

python 
stmt = text("SELECT * FROM users WHERE username = :username")
session.execute(stmt, {"username": username})

3. Never build queries by concatenating strings

Avoid this pattern:

python 
query = "SELECT * FROM users WHERE username = '" + username + "';"

Or:

python 
query = f"SELECT * FROM users WHERE username = '{username}';"

Or:

python 
query = "SELECT * FROM users WHERE username = '%s';" % username

Even if you "sanitize" the input, this pattern is hard to make safe and easy to get wrong.

A few exceptions may exist, for example when constructing dynamic parts that cannot be parameterized, like column names or sort directions. We will cover that later.

4. Whitelist identifiers for dynamic SQL

Sometimes you need to build queries with dynamic:

Most database drivers do not let you parameterize these, only values. In these cases, use whitelisting.

Example: safe sorting

python 
# User provides sort field and direction
sort_field = request.args.get("sort", "created_at")
sort_dir = request.args.get("dir", "desc")
allowed_fields = {
    "created_at": "created_at",
    "price": "price",
    "name": "name",
}
allowed_dirs = {"asc": "ASC", "desc": "DESC"}
field = allowed_fields.get(sort_field, "created_at")
direction = allowed_dirs.get(sort_dir, "DESC")
query = f"SELECT * FROM products ORDER BY {field} {direction} LIMIT %s OFFSET %s"
cursor.execute(query, (limit, offset))

Attacker inputs like price; DROP TABLE users; -- will not be found in allowed_fields, so they are ignored.

5. Validate and constrain input

Input validation is not a complete defense, but it helps:

Examples:

python 
# Numeric ID from path
try:
    user_id = int(request.path_params["user_id"])
except ValueError:
    raise HTTPException(status_code=400, detail="Invalid user id")
# Limit search length and characters
search = request.query_params.get("q", "")
if len(search) > 100:
    raise HTTPException(status_code=400, detail="Search too long")

Even with validation, you still must use parameterized queries.

6. Principle of least privilege for database users

Use database accounts with the minimum required permissions for the application.

For example:

Then, even if SQL injection happens, the impact is reduced.

Example table of roles:

RoleExample permissionsUsed for
app_read_writeSELECT, INSERT, UPDATE, limited DELETENormal app behavior
app_read_onlySELECT onlyAnalytics pages, public APIs
db_adminFull privilegesMigrations, maintenance only

7. Avoid exposing raw error messages

Detailed SQL error messages can help attackers craft injection payloads:

In production:

Example:

python 
try:
    cursor.execute(query, params)
except Exception:
    logger.exception("Database query failed")
    raise HTTPException(status_code=500, detail="Internal server error")

Examples Of Vulnerable And Safe Code

Example 1: User profile endpoint

Vulnerable

python 
# GET /users?username=<username>
username = request.args["username"]
query = f"SELECT id, username, email FROM users WHERE username = '{username}';"
cursor.execute(query)
user = cursor.fetchone()

Payload:

text 
username = alice' OR '1'='1

Query:

sql 
SELECT id, username, email FROM users
WHERE username = 'alice' OR '1'='1';

Attacker gets all users.

Safe

python 
username = request.args["username"]
query = "SELECT id, username, email FROM users WHERE username = %s;"
cursor.execute(query, (username,))
user = cursor.fetchone()

Payload is safely treated as a string value.

Example 2: Search with LIKE

Vulnerable

python 
search = request.args.get("q", "")
query = "SELECT * FROM articles WHERE title LIKE '%" + search + "%';"
cursor.execute(query)

Safe

python 
search = request.args.get("q", "")
pattern = "%" + search + "%"
query = "SELECT * FROM articles WHERE title LIKE %s;"
cursor.execute(query, (pattern,))

Example 3: Numeric ID

Assume an endpoint:

python 
# GET /product/<id>
product_id = request.path_params["id"]
query = f"SELECT * FROM products WHERE id = {product_id};"
cursor.execute(query)
product = cursor.fetchone()

Even if id is supposed to be an integer, the application might receive:

text 
id = 1; DROP TABLE products; --

Query becomes:

sql 
SELECT * FROM products WHERE id = 1; DROP TABLE products; --;

Safe version

python 
product_id = int(request.path_params["id"])  # may raise ValueError
query = "SELECT * FROM products WHERE id = %s;"
cursor.execute(query, (product_id,))

Or, in an ORM:

python 
product_id = int(request.path_params["id"])
product = db.query(Product).filter(Product.id == product_id).first()

Testing For SQL Injection Vulnerabilities

As a backend developer, you should learn to spot and test for potential SQL injection issues.

Basic manual tests

  1. Try special characters

Fields likely used in SQL:

  1. Try OR conditions
    • abc' OR '1'='1
    • Check if you suddenly get more results than expected.
  2. Numeric fields
    • Replace 123 with 123 OR 1=1
    • If the response changes, it may be vulnerable.
  3. Time-based tests

If the database supports SLEEP, try:

Automated tools

Security professionals often use tools like:

For learning purposes, you can:

Never run such tools against systems you do not own or do not have explicit permission to test. That is illegal.

Special Cases And Tricky Spots

Dynamic WHERE clauses

Sometimes you build complex filters based on many optional parameters:

python 
conditions = []
if "status" in filters:
    conditions.append(f"status = '{filters['status']}'")
if "category" in filters:
    conditions.append(f"category = '{filters['category']}'")
where_clause = " AND ".join(conditions) if conditions else "1=1"
query = "SELECT * FROM orders WHERE " + where_clause
cursor.execute(query)

This is deeply vulnerable, because multiple user-controlled values are inserted directly.

Safe approach

Build conditions and parameters together:

python 
conditions = []
params = []
if "status" in filters:
    conditions.append("status = %s")
    params.append(filters["status"])
if "category" in filters:
    conditions.append("category = %s")
    params.append(filters["category"])
where_clause = " AND ".join(conditions) if conditions else "1=1"
query = "SELECT * FROM orders WHERE " + where_clause
cursor.execute(query, params)

The SQL structure stays fixed, only values are parameterized.

Stored procedures and SQL injection

Using stored procedures does not automatically prevent SQL injection.

Example of vulnerable stored procedure:

sql 
CREATE PROCEDURE GetUser(IN p_username VARCHAR(50))
BEGIN
  SET @sql = CONCAT('SELECT * FROM users WHERE username = ''', p_username, '''');
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END;

If p_username contains ' OR 1=1 --, the procedure becomes vulnerable.

Summary

Key points to remember as a backend developer:

  • Never build SQL by concatenating or interpolating user input into query strings.
  • Always use parameterized queries or safe ORMs / query builders for all dynamic values.
  • When you must build dynamic SQL for identifiers like column names, use strict whitelisting.
  • Validate input, limit privileges of your database users, and avoid exposing raw SQL errors.

If you consistently follow these rules, you will prevent almost all SQL injection vulnerabilities in your backend applications.

Views: 11

Comments

Please login to add a comment.

Don't have an account? Register now!

© 2025 Kahibaro
Visitors: 3162 / 24h
Impressum / Imprint
Datenschutz / Data Policy