SQL injection: how a web application is tested and protected

SQL injection is a vulnerability in which text entered by a user becomes part of a database query. It is one of the oldest and best documented web application vulnerabilities and appears in every edition of the OWASP Top 10. It is still common, mostly in legacy systems, custom software and hastily written integrations.

Example

A login form checks the user with a query assembled from text:

SELECT * FROM users WHERE email = '<entered email>' AND password_hash = '<hash>'

If admin@example.com' -- is entered in the email field, the quote closes the text and -- turns the rest of the query into a comment. The password check disappears and the application logs the attacker into the administrator's account.

What an attacker can reach

That depends on the privileges of the database user the application connects with:

reading entire tables: customers, orders, password hashes;
changing or deleting data;
with excessive privileges and a misconfigured server: reading files or running commands on the server.

A customer database leaked this way is a personal data breach. In most cases it has to be reported to the data protection authority within 72 hours.

Protection

Use parameterised queries (prepared statements) everywhere. The value is passed separately from the query text, and the database never executes it as code:
```
db.QueryRow("SELECT id FROM users WHERE email = $1", email)
```
ORM libraries do this by default, but most also allow hand-written queries, where the vulnerability comes back.
Where part of a query cannot be a parameter, such as a column name for sorting, accept only values from a predefined list.
The application connects as a user with access only to its own tables and only to the operations it needs. The database administrator account is never used by the application.
Do not show database errors to visitors. They reveal the table structure and make the attack easier.
A web application firewall (WAF) blocks known attack patterns, but it is only an extra layer. Vulnerable code remains vulnerable.
Hash passwords with Argon2id or bcrypt so they cannot be used straight away if the table leaks. See the article on password cracking for details.

How it is tested

An application test covers every input point: form fields, URL parameters, cookies, HTTP headers and API requests. Automated tools such as sqlmap find the obvious cases. Blind SQL injection, where the application returns no data and only responds differently to queries, is often found through manual testing and code review.