Injection and Parameterised Queries

Data that becomes code

Injection happens when untrusted input is concatenated into something an interpreter parses: SQL, shell commands, LDAP filters or templates. The interpreter cannot tell where your code ends and the attacker's text begins. If a login query is built as "... WHERE name = '" + name + "'" and the attacker types ' OR '1'='1, the condition becomes always true. The fix is to keep code and data in separate channels. With a parameterised query (prepared statement), the SQL text contains placeholders such as ? and the driver sends the values separately, so they are always treated as data, never as SQL syntax. Escaping by hand is fragile; ORMs and query builders parameterise for you as long as you avoid their raw-string escape hatches. The same principle applies to shells: pass an argument list to subprocess.run instead of building a command string with shell=True.

Tracing an injection, then the fix

Follow how the same malicious input behaves in a string-built query and in a parameterised one.

import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE users (name TEXT, role TEXT)")
db.executemany("INSERT INTO users VALUES (?, ?)", [("asha", "admin"), ("ravi", "user")])
evil = "x' OR '1'='1"
unsafe = f"SELECT name FROM users WHERE name = '{evil}'"
print("unsafe:", db.execute(unsafe).fetchall())
safe = db.execute("SELECT name FROM users WHERE name = ?", (evil,)).fetchall()
print("safe:", safe)

Output:

unsafe: [('asha',), ('ravi',)]
safe: []

1. Line 2: An in-memory SQLite database is created, so the demo needs no server.
2. Line 4: Two users are inserted. Even the setup uses placeholders, a good habit.
3. Line 5: The attacker's input contains a quote that will close the string literal early.
4. Line 6: The f-string pastes the input into the SQL text, producing WHERE name = 'x' OR '1'='1', which is true for every row.
5. Line 7: SQLite parses the injected OR clause as code and returns all users.
6. Line 8: Here the SQL text only has a placeholder. The whole input is bound as one string value.
7. Line 9: No user is literally named x' OR '1'='1, so the safe query returns nothing.

Filling a printed form

A bank form has a box labelled 'Name'. Whatever you write in the box stays a name; writing 'and also transfer ten lakh' in it does not become an instruction. A parameterised query is the printed form; string concatenation is letting the customer rewrite the form itself.

त्वरित जाँच: Why does a parameterised query prevent SQL injection?

• It removes all quote characters from input
• It encrypts the query before sending it
• The values are sent separately from the SQL text and are always treated as data
• It limits input length to 255 characters