SQL Injection: When Data Changes a Database Command

SQL injection happens when untrusted input becomes part of executable query syntax.

The database cannot know which characters came from a user and which the developer intended as a command if both are joined into one string.

Keep query structure fixed and pass untrusted values separately as parameters.

Trying to escape dangerous-looking characters manually is fragile and context dependent.

Unsafe code constructs:

SELECT * FROM users
WHERE email = 'USER_INPUT'
AND password = 'USER_INPUT';

If raw input closes a quote and adds SQL syntax, it can alter the WHERE clause.

A parameterized query sends the SQL template and values separately, so the database treats the input as data.

Query structure and values

Parameterized SQL looks conceptually like:

SELECT * FROM users
WHERE email = ?
AND password_hash = ?;

The driver binds values without turning their contents into operators, keywords, or clauses.

Use the parameter API provided by the database library.

Prepared statements

A prepared statement parses or plans the query structure separately from execution values.

Some drivers emulate preparation, but safe parameter binding can still preserve separation. Review framework behavior rather than assuming a method name guarantees safety.

Prepared statements may also improve repeated query performance.

String concatenation

Unsafe patterns include:

direct concatenation,
string interpolation,
template replacement,
homemade escaping,
and building clauses from request fields.

Even input that appears numeric should be bound as a value. Validation rules change, and another code path may reuse the function.

Dynamic identifiers

Parameters normally represent values, not table names, column names, or sort direction.

For dynamic identifiers, use a strict allow-list mapping:

"newest" -> created_at DESC
"price_low" -> price ASC

Never insert an arbitrary request value as an identifier.

`IN` lists

Dynamic value lists need one placeholder per value or a driver-supported array parameter.

Do not join raw IDs into:

WHERE id IN (...)

Validate list size to prevent enormous queries.

Object-relational mappers

ORMs usually parameterize ordinary field comparisons.

Risk returns when developers use:

raw query methods,
string fragments,
custom ordering,
or unsafe expression APIs.

Abstraction reduces common mistakes but does not eliminate the underlying boundary.

Stored procedures

Stored procedures can be safe when they bind parameters and avoid dynamic SQL.

A procedure that concatenates values into a command is still injectable. Moving unsafe construction into the database does not change the problem.

Review dynamic execution carefully.

Second-order injection

Malicious text may be stored safely as data, then used unsafely later to build another query.

For example, an imported report name is stored in a table and later concatenated as a column identifier.

Treat data according to the destination context every time it is used.

Impact

Depending on permissions and engine features, injection can allow:

reading data,
bypassing authentication,
modifying or deleting records,
invoking database functions,
writing files,
or reaching the operating system.

Least-privilege database roles limit the possible damage.

Database permissions

The application should not connect as database administrator.

Separate roles by function:

read-only reporting,
application read/write,
migration administration,
and backup.

Injection in one component should not automatically control every database.

Error handling

Detailed SQL errors can reveal:

table names,
query structure,
engine version,
and syntax behavior.

Return generic safe errors to users and capture detailed diagnostics in protected logs.

Do not hide errors so completely that operators cannot investigate.

Input validation

Validate domain rules:

identifier shape,
range,
allowed enum,
maximum length.

Validation reduces malformed input and abuse but does not replace parameters. A valid surname can contain an apostrophe; the query must remain safe.

Escaping

Correct escaping depends on:

database engine,
character set,
SQL mode,
and context.

Use parameters rather than implementing escaping. If a specialized API requires escaping, use the database's exact supported function and test it.

Batch and bulk APIs

Bulk inserts should still bind values.

Libraries often provide structured batch methods. Avoid creating one giant SQL string from uploaded content.

Apply row and total-size limits to prevent resource exhaustion.

Detection

Possible signals include:

unusual query errors,
unexpected query shapes,
large result exports,
authentication anomalies,
and web-application firewall detections.

Detection is secondary to safe construction and can produce false positives.

Query telemetry

Monitor normalized query fingerprints rather than logging full SQL with sensitive values.

Unexpected new query shapes, sudden full-table scans, large exports, and permission errors can reveal exploitation or a defective release. Protect database audit logs and correlate them with application identity so investigators can distinguish one workload from another.

Static analysis

Tools can flag query strings containing request variables.

They may miss custom wrappers or report safe code. Configure known sources and sinks and review raw-query usage.

Automated analysis is especially useful for enforcing a no-concatenation standard.

Testing

Tests should verify:

ordinary punctuation remains data,
malicious-looking values do not alter results,
dynamic sorting uses allowed mappings,
database role lacks unnecessary rights,
and errors reveal no internal SQL.

Do not test destructive payloads against real production data.

Knowledge check

Why can the database not distinguish input from syntax after concatenation?
What do prepared parameters separate?
Why can dynamic column names not use ordinary value placeholders?
How can an ORM application still be injectable?
How does least privilege reduce injection impact?

The one idea to remember

SQL injection occurs when untrusted data is allowed to shape executable query syntax. Use parameterized queries for values, strict mappings for identifiers, narrow database roles, safe errors, and testing of every raw-query boundary.

A concrete example: a login query