SQL Injection: How It Works and How to Fix It
Author: Domeny.ForSale editorial team Published
In brief: SQL Injection is a vulnerability that allows specially crafted input to change what a database query does. It primarily arises when an application concatenates SQL commands with user-supplied text. The main defense is parameterized queries, which keep the command separate from its data.
How user input becomes a command
SQL is a language used to read and modify data in relational databases. An application can use it to look up an account, retrieve an order or save a customer’s new address. SQL Injection occurs when input is treated as part of the syntax of a SQL command.
Suppose an application looks up an account by username. The developer writes the start of the query, appends the value from a form and closes it with a single quote. An ordinary username produces the expected result, but text containing a single quote and an additional condition can change the meaning of the entire query.
A login form is not the only possible source of a dangerous value. It could also come from a URL parameter, a product filter, a request header or data previously stored in the database. What matters is how the application uses the value, not where it came from.
A simple experiment on Linux
The following example runs entirely on a temporary database held in the process’s memory. It does not connect to any website or external server. It demonstrates retrieving more records than intended, not a complete login mechanism.
You need Python 3 with the sqlite3 module available. Check it by running this command in a terminal:
python3 -c "import sqlite3; print(sqlite3.sqlite_version)"
If the command fails, consult your distribution’s documentation for instructions on installing Python 3 or the missing module. You do not need to run a separate database server.
Run the entire block in a terminal:
python3 - <<'PY'
import sqlite3
connection = sqlite3.connect(":memory:")
polaczenie.execute(
"CREATE TABLE accounts (id INTEGER PRIMARY KEY, login TEXT)"
)
polaczenie.executemany(
"INSERT INTO accounts (login) VALUES (?)",
[("anna",), ("peter",)]
)
value = "' OR 1=1 -- "
query = "SELECT id, login FROM accounts WHERE login = '" + value + "'"
print("Query:", query)
print("Result:", polaczenie.execute(query).fetchall())
polaczenie.close()
PY
The result contains both accounts, even though neither has a username matching the supplied value. The single quote closes the username string, OR 1=1 adds a condition that is always true, and -- starts a comment, so the final single quote no longer affects the query.
This code is deliberately vulnerable for a local demonstration. Do not test similar inputs on other people’s services without explicit permission covering the scope of the tests.
How to fix the same code
A parameterized query contains placeholders for data, while the values themselves are passed separately to the database library. In the sqlite3 module, ? can serve as a placeholder. The library treats the supplied value as data, not as additional SQL syntax.
In the previous example, replace the query construction and execution with this snippet, keeping it before polaczenie.close():
query = "SELECT id, login FROM accounts WHERE login = ?"
result = polaczenie.execute(query, (value,)).fetchall()
print("Result:", result)
You will now get an empty list: the database looks for a literal username containing the entire supplied string. If you change value to "anna", the query will return only Anna’s account. The comma in (value,) creates the single-element tuple required by this way of passing parameters.
Do not replace parameters with manually removing single quotes or formatting strings, for example with an f-string. A username containing a single quote may be valid data; the application is responsible for handling it safely. Parameter syntax depends on the library, so do not assume that the ? placeholder works with every driver without checking.
What the vulnerability can lead to
SQL Injection may allow someone to read other people’s data, bypass some access controls, modify records or delete them. The actual impact depends on the query, the database engine and the permissions of the application’s database account. Finding a vulnerability does not automatically mean that the entire server has been compromised.
The absence of a visible error does not prove that an application is secure, either. The application may hide database messages, with the altered behavior apparent only through differences in its responses. Conversely, a single error after entering a single quote is a reason to investigate, not proof of SQL Injection on its own.
What to check in your own application
Start your review wherever SQL queries are constructed:
- Replace string concatenation with parameters for all values supplied from outside the query.
- Check raw queries even if you use an ORM, a layer that maps application objects to data in the database.
- Choose column names and sort directions from a fixed list of allowed options. Value parameters generally cannot replace these parts of the syntax.
- Restrict the database account’s permissions to the operations the application needs.
- Do not show users detailed database errors or full queries containing sensitive data.
To get started, choose one lookup that uses user input. Add tests with an ordinary value and with text containing a single quote, and check that both inputs remain data rather than changing the query’s structure.
See also: What is an ETF?
Read also
