Jan Mikoláš
Glossary
SQL injection
SQL injection arises when user input becomes part of SQL code. Parameters protect values; column names and sort order need a predefined allowlist.
Short definition
A value must not change the structure of a query.
SQL injection occurs when an SQL string is assembled from a command and untrusted input. The database can no longer distinguish which part was intended as a value and which is SQL syntax. Depending on the rights of the application database account, the flaw may allow unexpected reading, modification, or deletion of data.
A prepared statement with a placeholder passes the query structure and the value to the database separately. An email address, order number, or date therefore cannot change the SQL command. Input validation still supports business rules and clear errors, but does not replace parameterisation.
Use cases
Where protection matters
The risk is not limited to public forms; input can come from an API, import, administration interface, or internal script.
- filtering orders by email, status, or date
- product search and administration exports
- API endpoints with parameters, pagination, and sorting
- imports of external identifiers and integration reports
- handwritten SQL, DQL fragments, and dynamic query-builder expressions
Practical example
Filtering and safely sorting orders
The first line below demonstrates the flaw: the email would be appended to the SQL text. The safe version sends the email as a parameter. Sort direction and the column name cannot be parameterised, so the application uses a map of predefined options and a default.
The example uses Doctrine DBAL for illustration only. The same principle applies to PDO and ORM: separate data with parameters, define query structure in code, and never insert free-form client text into it.
// Nedělat: SQL kód a data jsou v jednom řetězci.
$sql = "SELECT * FROM orders WHERE customer_email = '$email'";
$sortColumns = ['date' => 'created_at', 'number' => 'order_number'];
$sort = $sortColumns[$requestedSort] ?? 'created_at';
$rows = $connection->executeQuery(
'SELECT id, order_number, created_at
FROM orders
WHERE customer_email = :email
ORDER BY ' . $sort . ' DESC',
['email' => $email],
);How it works
From input to a safe query
A safe flow distinguishes values that can be parameterised from SQL structure that the application itself must define.
- The application receives input An email address, filter, or chosen sort direction is untrusted even when it comes from an internal administration interface.
- It separates values and identifiers Values go into parameters; a column or table name and an SQL keyword are not ordinary parameters.
- It validates permitted choices Dynamic sorting is mapped through an allowlist, such as created_at or order_number, and never appended directly from the request.
- It executes a prepared statement DBAL, PDO, or ORM passes a typed value through a placeholder, and the database preserves the query structure.
- It limits the consequences of a flaw The database account has only the required rights, and the client receives a safe error response without SQL details.
Key concepts
Security lies at the boundary between SQL and data.
Each technique protects a different part of the problem; none addresses every business rule alone.
Prepared statements and placeholders
A placeholder such as :email or ? stands for a value. The driver sends it to the database separately from the SQL text and handles it according to its type. Manual string escaping is more fragile and not a replacement.
Identifier allowlists
A column or table name and ASC/DESC generally cannot be passed as a parameter. The application chooses from a fixed map of known options, not free-form request text.
ORM and QueryBuilder
Doctrine ORM and DBAL reduce risk when parameters are used, but manually assembled SQL or DQL fragments and expressions can reintroduce the flaw. A QueryBuilder is not automatically a safe string.
Least database privilege
The application account should have only the rights required for operation. This does not remove the query flaw, but limits what exploitation can do.
Benefits and limitations
The right mechanism is simple, but query boundaries need careful design.
Benefits
- parameters unambiguously separate data from SQL syntax
- an allowlist provides a safe, readable choice of sorting or columns
- DBAL, PDO, and ORM support a standard safe path
- limited database rights reduce the impact of another flaw
Risks and mistakes
- manual concatenation, even after escaping, breaking easily when the context changes
- an ORM not automatically protecting native SQL or dynamic fragments
- a parameter being unsuitable for a table name, column, or SQL keyword
- a WAF detecting some attacks but not replacing a source-code fix
Scope
Parameterisation is the default, not an optional detail.
Every value originating outside a fixed SQL statement belongs in a parameter. This also applies to numbers, dates, API payloads, and data from earlier imports: an internal value today may become untrusted input tomorrow. A consistent database interface and code review help catch dangerous concatenation early.
Dynamic sorting, a column name, or sort direction are different. If they need to vary, the design should provide a map of permitted values and select only from it. The goal is never to let a client write SQL, but to offer a small, clearly defined set of application features.
What to consider
Verify protection in both code and database permissions.
Safe queries should be the standard path, not a decision left to every individual developer.
- parameterise every dynamic value in SQL, DQL, and query builders
- use a fixed allowlist for sorting, columns, and tables
- do not return database errors, SQL text, or a stack trace to the client
- grant the application only the minimum required database rights
- test filtering and sorting with unexpected inputs and review handwritten SQL
Common questions
What parameterisation protects and what it does not
Is validating an email address or number enough?
No. Validation is useful for business rules, but a parameterised query safely separates the value from SQL code.
Can I parameterise a column name in ORDER BY?
Usually not. A placeholder represents a value, not an SQL identifier. Use a map of allowed names and select only from it.
Am I safe if I use an ORM?
Routine work with entities and parameters significantly reduces the risk. Manually constructed native SQL, DQL fragments, or unsafe expressions can reintroduce SQL injection.
Will a web application firewall solve the problem?
It may detect some attempts, but cannot reliably replace parameterisation in the application. The fix belongs in the code and database permissions.
How I work with data in practice
I assess database queries together with integrity and permissions.
When developing backends, I address explicit SQL, ORM, transactions, constraints, and the secure boundary between HTTP input and the database.