Separate query syntax from input values
Concatenating input into SQL lets quotation marks and other syntax in the data affect parsing. A parameterized query defines the structure first, then passes values through the database interface’s binding mechanism. Use the placeholder syntax supported by the actual driver.
Parameters generally represent values, not arbitrary table names, column names or sort directions. If structure must vary, map from explicitly allowed fixed choices instead of accepting arbitrary strings.
Further reading: OWASP: SQL Injection Prevention Cheat Sheet
Other boundaries remain after parameterization
Binding parameters does not determine whether a user may view a row or choose the database account’s privileges. Authorization, least privilege and error handling remain separate responsibilities.
The exercise uses an in-memory SQLite database and one fictional name to observe correct handling of ordinary text containing an apostrophe. It verifies value binding in this query, not every path in an application.
Further reading: Python: sqlite3 parameter substitution
Example: looking up O’Neil
With string concatenation, an ordinary ASCII apostrophe in a name can disrupt SQL structure. The program below binds the value using ?.
The SQL structure stays name = ?. The name is passed as the second argument to execute, so the database compares the entire name as a value.
Conclusion: Expect exactly one synthetic row without manually removing apostrophes.
Offline exercise: query an in-memory database
Use local Python 3 with its sqlite3 module. No disk database, networking or real personal data is used.
- Read the program and locate fixed SQL and bound values.
- Run it locally and check the output.
- Change the lookup value to an absent name and expect an empty list.
import sqlite3
con = sqlite3.connect(":memory:")
try:
con.execute("CREATE TABLE people (name TEXT)")
con.execute("INSERT INTO people VALUES (?)", ("O'Neil",))
print(con.execute("SELECT name FROM people WHERE name = ?", ("O'Neil",)).fetchall())
finally:
con.close()Expected result
The first output is [("O'Neil",)]; an absent name produces [].
Check your work
Why is substituting a table name a different problem from binding a value?
Finish and clean up
The in-memory database closes when the program ends and leaves no data file.