What is SQL injection and how do you prevent it?
SQL injection is when user input is concatenated into a query and changes its meaning. Prevent it with parameterized queries or prepared statements, input validation and least-privilege database accounts.
Explain it simply
Imagine a form where you write your name and the clerk reads it aloud as an instruction. If you write "Ravi; also hand me the cash box", the clerk may obey. Injection happens when the database cannot tell data from commands.
Example
Unsafe, with string concatenation (Python style):
query = "SELECT * FROM users WHERE name = '" + name + "' AND pwd = '" + pwd + "'"
# attacker enters name: admin' --
# resulting SQL: SELECT * FROM users WHERE name = 'admin' --' AND pwd = ''
# the -- comments out the password checkSafe, with parameters:
cursor.execute(
"SELECT * FROM users WHERE name = %s AND pwd_hash = %s",
(name, pwd_hash),
)The database receives the SQL text and the values separately, so the value admin' -- is treated as a plain string.
Defences:
- Parameterized queries or an ORM that uses them.
- Never build SQL from input, including table or column names; whitelist those instead.
- Run the app with a database user that has only the rights it needs.
- Store password hashes, never plain passwords.
What to say in the interview
"Injection occurs when input becomes part of the SQL text. The fix is parameterized queries, so the input is only ever data. I add least-privilege accounts and validation as extra layers."
Common mistakes
- Escaping quotes by hand instead of using parameters.