SQL injection

02The statement and the value

In SELECT name FROM members WHERE id = 3, SELECT names the information to return, FROM names the table, and WHERE gives the condition used to choose rows. The intended task is to return the name from the row whose id matches the submitted value.

String concatenation means joining pieces of text. In the first construction, the application joins the SQL text and the submitted value into one string. The database then reads that whole string as SQL. Characters supplied by a user can therefore become part of the instructions.

In the second construction, the statement ends with id = ?. The question mark is a placeholder for a value. The application gives the database the statement and the value separately, a process called binding. Changing the supplied ID changes the value being compared, while the statement stays the same.

The tuple ('3',) is Python's way to group the one value supplied to this placeholder. The comma makes it a one-item tuple. The quotation marks show that 3 is a Python string; they are not extra SQL text to paste around the placeholder.

The illustration below uses a name search instead of an ID search. Ada fits into the joined text, but the apostrophe in O'Brien closes its SQL string too early. A legitimate name can expose the same construction problem that an attacker could exploit. The parameterized version keeps that whole name as one value.

SELECT name FROM members WHERE id = 3

Not separately bound in joined mode

SELECT name FROM members WHERE id = 3

SELECT name FROM members WHERE id = ?

('3',)

03Why ordinary input can hide the problem

Both versions can look reasonable when the input is a digit such as 3 or 8. A successful lookup with those values does not show that the application handles other input safely. The important question is how the statement is built.

SQL injection happens when submitted text can change the SQL instructions. Depending on the query and the database account's permissions, that can expose or change data the application did not intend to allow. An error caused by a quote is a clue to investigate; an error by itself does not establish an attack or its impact.

For the value in this example, use the database library's placeholder support and pass the value separately. A form label such as numbers only cannot enforce this on the server. Checking the value's expected type can help the application, and binding keeps it out of the SQL syntax.

04Read the Python call

Python's sqlite3 module provides tools for working with SQLite databases. In its examples, cur represents a database cursor: the object used here to run a statement. Its execute method receives the SQL statement as the first argument and the values to bind as the second.

The line cur.execute("SELECT name FROM members WHERE id = ?", (value,)) has one placeholder and a tuple containing one value. The question mark stays in the SQL string. Python passes value through that method instead of joining it onto the string.

The code for the checks also reads form['id'], the submitted ID, and uses .strip() to remove whitespace from its ends. It shows a joined statement stored in q alongside the execute call. Creating q builds a string; it does not run that string. fetchone reads one returned row, and print displays it.

This approach binds data values such as an ID or a name. A placeholder is not a substitute for an entire SQL instruction or a table name. This lesson keeps the table, column, and comparison fixed.

05Trace the activity value

The activity changes the submitted ID from 3 to 8. Use the files below to trace where that new value appears. query-demo.py prints the query constructions; it does not connect to a database or return a member's name.

The recorded trace contains the script's printed lines. Its separate table note describes a different database read. Keep those two operations separate as you work. You can complete the activity entirely from the printed text.

CREATE TABLE members (id INTEGER, name TEXT);
INSERT INTO members (id, name) VALUES (3, 'Ada');
INSERT INTO members (id, name) VALUES (8, 'Lane');

"""Print two ways of constructing a lookup for a fictional member ID.

This demonstration only prints text. It does not open a database,
execute SQL, or return a member's name. Read the printed statement
and the separate tuple to see where the submitted value appears.
"""


def show(label, value):
    pasted = "SELECT name FROM members WHERE id = " + value
    print(label)
    print("pasted text: " + pasted)
    print("bound statement: SELECT name FROM members WHERE id = ?")
    print("bound tuple: " + repr((value,)))


def main():
    show("worked", "3")
    show("activity", "8")


if __name__ == "__main__":
    main()

Query construction trace
Recorded output from query-demo.py using Python 3.12.3.
The script prints query text and tuples; it does not open a database.

Script output
worked
pasted text: SELECT name FROM members WHERE id = 3
bound statement: SELECT name FROM members WHERE id = ?
bound tuple: ('3',)

activity
pasted text: SELECT name FROM members WHERE id = 8
bound statement: SELECT name FROM members WHERE id = ?
bound tuple: ('8',)

Reading notes
The joined statement contains the submitted digit inside its SQL text.
The statement with a placeholder stays the same for both values.
The separate tuple changes from ('3',) to ('8',).

Separate table lookup
An existing SQLite 3.45.1 in-memory read of members.sql returned:
3|Ada
8|Lane
Those rows are not output from query-demo.py.

1. Find the two rows in members.sql. Identify the id and name columns, then note which name belongs to activity ID 8.
2. In query-demo.py, find show("activity", "8"). The second argument is the value used for this activity. Follow it into the function's value variable.
3. Find the line that builds pasted using +. In the activity trace, record the complete statement it produces. The submitted digit appears inside the SQL statement text.
4. Compare that with the bound statement and bound tuple. The statement still ends in id = ?, while ('8',) carries the activity value separately.
5. Check your explanation: the joined statement changed from id = 3 to id = 8. The statement with a placeholder stayed the same; only its separate value changed. The table associates ID 8 with Lane, but the demonstration script prints query text rather than that name.
6. Finish by explaining which construction treats the submitted ID as data. No script run or database setup is required for this activity.

06Why hiding errors does not fix the query

An application may replace a detailed database error with a generic message for visitors. This changes what the visitor sees. It does not change the statement the application sent to the database.

If user input is still joined into SQL, the construction problem remains. For the lookup value taught here, the fix is to use a statement with a placeholder and bind the value. Error handling and parameter binding solve different problems.

07Optional challenge

Allow about ten minutes. Explain to another learner why removing the apostrophe from O'Brien is a poor replacement for binding the name. Your explanation should describe both the legitimate input that would change and the way the query should receive that input.

Check your reasoning: the name contains an ordinary punctuation mark. A bound parameter allows it to remain part of the name while keeping the SQL statement fixed.

08What to remember

Follow user input to the point where a query is constructed. Joining that input into SQL allows data to become instructions. Binding a value through a placeholder preserves the separation.

Use that same distinction when reviewing a proposed fix: does it change how the query receives input, or only how the page displays a result?

09Cards for the checks

The checks below use these cards. They are original fictional text, safe to read anywhere.

1value = form['id'].strip()
2q = "SELECT name FROM members WHERE id = " + value
3cur.execute("SELECT name FROM members WHERE id = ?", (value,))
4print(cur.fetchone())

10Fresh cases for the checks

Use these fictional cases for the checks below. They have different inputs from the worked examples. Read them here or make notes; no installation or external service is needed.

1 | value = form['id'].strip()
2 | prefix = "SELECT name FROM members WHERE id = "
3 | statement = prefix + value
4 | cur.execute(statement)
5 | print(cur.fetchone())
 
As in the lesson, + joins strings. These lines have not been run.

value = form['id'].strip()
cur.execute("SELECT name FROM members WHERE id = " + value)
 
Keep the table, selected column, and comparison unchanged.
Only replace the execute call. No database result is supplied.

Before: submitted ID text is joined into SQL; visitors see detailed database errors.
After: the same submitted ID text is joined into SQL; visitors see only 'Lookup unavailable'.
The statement construction, execution call, and database permissions are unchanged.
No request trace, returned rows, or evidence of an attack is supplied.

statement = "SELECT name FROM members WHERE name = ?"
value = form['name']
cur.execute(statement, (value,))
 
Change the submitted name from Mira to D'Arcy, preserving the apostrophe.
The statement and the code stay unchanged. No table contents are provided.

11Optional explanation: follow the value

Spend up to five minutes explaining a submitted value's route through one joined lookup and one bound lookup. Do not run a database or invent an attack outcome.

Self-check: distinguish reading input, constructing a statement, executing a statement, and observing a result. Explain how the placeholder and separate tuple preserve a value, why hiding an error changes a different mechanism, and why source alone does not establish returned rows or impact. This explanation is self-reviewed, not automatically graded.