SQL injection and parameterised queries

The cause, in one idea

SQL injection is not really a database problem. It is a confusion of code and data.

When you build a query by pasting a value into a string, the database receives one flat piece of text and has to parse it. It cannot tell which characters you wrote and which came from a stranger. If the stranger's text contains SQL syntax, the parser treats it as syntax, because that is the only thing it has been given.

Understand that sentence and the fix is obvious: send the code and the data as separate things, so the parser never has to guess.

What the broken pattern looks like

name = "Anu"   # in a real app this comes straight from the request
query = "SELECT id, marks FROM student WHERE name = '" + name + "'"
print(query)

That is the bug. Note it and move on. The value of knowing this pattern is being able to spot it in review — in your own code, in a teammate's pull request, in a Stack Overflow answer someone is about to copy. It is the + and the quotes that should make you stop reading and comment.

The same shape appears as an f-string, as % formatting, as .format(), and as string concatenation in Java or PHP. The language does not matter. Any query assembled out of pieces of text is the same bug.

The fix: parameterised queries

You write the SQL with placeholders. You pass the values separately. The driver sends them apart, and the database plans the query before it ever sees your values. A value can then only ever be a value — never a keyword, never a quote, never a second statement.

This full example runs as it stands.

import sqlite3

conn = sqlite3.connect(":memory:")
cur = conn.cursor()
cur.execute(
    "CREATE TABLE student (id INTEGER PRIMARY KEY, name TEXT, marks INTEGER)"
)
cur.executemany(
    "INSERT INTO student (name, marks) VALUES (?, ?)",
    [("Anu", 78), ("Bharat", 65), ("Chitra", 91)],
)
conn.commit()

def find_student(name):
    cur.execute(
        "SELECT id, name, marks FROM student WHERE name = ?", (name,)
    )
    return cur.fetchall()

def students_in_band(low, high):
    cur.execute(
        "SELECT name, marks FROM student "
        "WHERE marks >= :low AND marks <= :high ORDER BY marks DESC",
        {"low": low, "high": high},
    )
    return cur.fetchall()

print(find_student("Anu"))
print(find_student("Anu' OR '1'='1"))
print(students_in_band(70, 100))

The three lines print [(1, 'Anu', 78)], then [], then [('Chitra', 91), ('Anu', 78)].

Read the second result carefully, because it is the whole lesson. The odd input did not become SQL. It was looked up as a student whose name is literally those 17 characters, no such student exists, and the result is an empty list. Nothing had to be stripped, escaped or blocked.

Both styles are in that program. ? is positional and the values go in a tuple. :low is named and the values go in a dictionary, which reads much better once a query has more than two values.

The part everyone gets wrong: identifiers

Placeholders work for values only. You cannot parameterise a table name, a column name, or the direction of an ORDER BY, because those are part of the query's structure and the database needs them at planning time.

So when the sort column comes from a dropdown, map it through an allow-list. Never paste it in.

SORT_COLUMNS = {"name": "name", "marks": "marks", "id": "id"}

def build_sorted_query(sort_key, descending=False):
    column = SORT_COLUMNS.get(sort_key)
    if column is None:
        raise ValueError("unknown sort column: " + str(sort_key))
    direction = "DESC" if descending else "ASC"
    return f"SELECT name, marks FROM student ORDER BY {column} {direction}"

print(build_sorted_query("marks", descending=True))
try:
    build_sorted_query("email")
except ValueError as e:
    print("rejected:", e)

The f-string here is safe because column can only ever be one of three strings that you wrote, and direction comes from a boolean. The user's text never reaches the query. This is the only acceptable way to build SQL structure dynamically.

Layers behind the parameters

Parameterised queries are the fix. These make the day after a mistake less bad.

• Least privilege on the database account. The web application's user needs SELECT, INSERT, UPDATE on its own tables. It does not need DROP, CREATE USER, or access to other schemas.
• An ORM used correctly. SQLAlchemy, Django ORM and peewee parameterise by default. They also all offer a raw-SQL escape hatch, and that hatch is where injection comes back. If you write .raw() or text(), bind the parameters.
• Return only what is needed. Selecting * and sending it as JSON leaks columns like password hashes and internal flags.
• Migrations and schema changes run under a separate admin account, not the application's account.

Checklist for review

• No +, no f-string, no % and no .format() producing SQL text.
• Every user value arrives through a placeholder.
• Every dynamic identifier comes from a dictionary or set you wrote.
• The application's database user cannot drop or alter tables.