SQL Injection and Prepared Statements
SQL injection happens when an application builds a SQL statement by pasting user input into it, and the input changes the meaning of the query. It is one of the most damaging and still most common web vulnerabilities. This lesson shows exactly why it works, on a demo table, and how prepared statements with parameters stop it completely.
- See how string-built queries change meaning
- Use prepared statements in MySQL with
PREPAREandEXECUTE - Use parameters from application code
- Test an application for injection as a QA engineer
A vulnerable login
A demo app_users table (passwords are stored as hashes, as they always should be):
CREATE TABLE app_users ( user_id INT PRIMARY KEY, username VARCHAR(30) NOT NULL UNIQUE, pw_hash CHAR(64) NOT NULL, role VARCHAR(10) NOT NULL);INSERT INTO app_users VALUES(1, 'admin', SHA2('Adm1n#Demo', 256), 'admin'),(2, 'asha', SHA2('Asha#Demo1', 256), 'user');SELECT user_id, username, role FROM app_users;| user_id | username | role |
|---|---|---|
| 1 | admin | admin |
| 2 | asha | user |
An application checks a login by gluing the typed values into SQL text, like this Python code:
sql = ("SELECT user_id, role FROM app_users " f"WHERE username = '{username}' AND pw_hash = SHA2('{password}', 256)")cursor.execute(sql)With honest input (asha / Asha#Demo1) the query finds Asha. Now an attacker types the username admin' -- and any password. The pasted SQL becomes:
SELECT user_id, role FROM app_usersWHERE username = 'admin' -- ' AND pw_hash = SHA2('anything', 256)| user_id | role |
|---|---|
| 1 | admin |
- The
'in the input closes the username string early. --starts a comment, so the whole password check is ignored.- The query returns the admin row: the attacker is logged in as admin without the password.
Another classic input, ' OR '1'='1, makes the condition true for every row:
SELECT user_id, username, role FROM app_usersWHERE username = '' OR '1'='1' AND pw_hash = SHA2('x', 256) OR '1'='1';| user_id | username | role |
|---|---|---|
| 1 | admin | admin |
| 2 | asha | user |
The same trick can read other tables (UNION SELECT ...), change data or drop tables, depending on the account’s privileges. Only run injection tests against systems you own or are authorised to test.
The fix: prepared statements
A prepared statement sends the SQL with placeholders (?) and the values separately. The values are always treated as data, never as SQL, so quotes and comments in them have no special meaning.
In MySQL itself:
PREPARE login FROM 'SELECT user_id, role FROM app_users WHERE username = ? AND pw_hash = SHA2(?, 256)'; SET @u = 'asha', @p = 'Asha#Demo1';EXECUTE login USING @u, @p; SET @u = 'admin'' -- ', @p = 'anything';EXECUTE login USING @u, @p; DEALLOCATE PREPARE login;| user_id | role |
|---|---|
| 2 | user |
PREPAREcompiles the query once, with two?placeholders.- The honest login passes Asha’s values and finds her row.
- The attack passes the text
admin' --as the username. It is compared as a plain string: no user has that name, so the result is an Empty set. DEALLOCATE PREPAREfrees the statement.
Parameters in application code
Every database library supports parameters. In Python with mysql-connector or PyMySQL:
sql = "SELECT user_id, role FROM app_users WHERE username = %s AND pw_hash = SHA2(%s, 256)"cursor.execute(sql, (username, password)) # values passed separately, never formatted into the stringThe same idea in Java is PreparedStatement with ?, in Node.js connection.execute(sql, [values]), and in ORMs such as SQLAlchemy or Hibernate, bound parameters.
Placeholders work for values only, not for table or column names. If a column name comes from user input (for example a sort option), check it against a fixed allow-list in code.
Defence in depth
- Parameters everywhere: no string concatenation or f-strings for SQL values.
- Least privilege: the application account should not have
DROP,ALTERor access to other databases (GRANT). - Validate input (type, length, format) as an extra layer, not as the main defence.
- Don’t show database errors to users; log them instead. Error messages help attackers.
- Store passwords hashed with a slow algorithm (bcrypt, Argon2) in the application, not reversible.
Testing for SQL injection (QA checklist)
As a tester, try these inputs in every field that reaches the database, on a test environment:
| Input | Looks for |
|---|---|
' | an SQL error or a 500 page: input is not escaped |
' OR '1'='1 | login bypass or extra rows |
admin' -- | comment-based bypass |
1 OR 1=1 | numeric parameters without quotes |
' UNION SELECT NULL -- | data from other tables |
A safe application treats all of them as ordinary text: “invalid username or password”, no errors, no extra data. Automated scanners such as OWASP ZAP and sqlmap can help, but only with permission.
Quick quiz
1. What causes SQL injection?
The input becomes part of the SQL code.
2. Why do prepared statements prevent injection?
Placeholders keep data and code apart.
3. Can a placeholder be used for a table name?
Validate identifiers with an allow-list instead.
4. An app account has only SELECT on its database. What does that limit?
Least privilege is a second layer of defence.
SQL injection FAQ
What is SQL injection?
A vulnerability where an attacker supplies input that changes the structure of a SQL query, because the application builds the query by concatenating that input.
How do you prevent SQL injection?
Use prepared statements or parameterized queries for every value, give the application account the least privileges it needs, and validate input.
Does escaping quotes prevent SQL injection?
Escaping is error-prone and easy to forget or bypass with character-set tricks. Parameters are the reliable fix.
How do testers check for SQL injection?
By entering characters and payloads such as ', ' OR '1'='1 and admin' -- into inputs on a test system and checking for errors, bypassed logins or unexpected data.