ORM Escape Hatches, Dynamic SQL, and Second-Order Injection
Follow a city saved safely in a profile into a later report query, and fix the point where stored text becomes SQL.
You’re reading a preview. Premium includes the full lesson and specialist practice. View the full curriculum.
Mia saves her preferred city in a customer profile. The profile update uses a prepared statement, so saving the city is safe. That night, a report worker loads the city and pastes it into a new SQL statement.
The same text has now crossed two different database calls. The first treated it as a value; the second treats it as part of the instructions. This delayed use is called second-order SQL injection.
Profile request → city saved as data → nightly worker loads city → city becomes SQL source
Follow that one value first. After fixing the report query, we'll look at the related problem of selecting sort columns and using an ORM's raw-query methods.
The first request is correctly parameterized
The city preference belongs to an authenticated customer. The write route derives the account from the session, checks that city is a string with a reasonable length, and passes it as a bound value. These controls are useful. They prevent a caller from replacing the customer identifier and stop structured or unbounded input from entering this field.
# profile_service.py
def save_preferred_city(db, principal, incoming):
city = incoming.get("city")
if not isinstance(city, str):
raise ValueError("city must be text")
city = city.strip()
if not city or len(city) > 120:
raise ValueError("city must contain 1–120 characters")
db.write(
"UPDATE customers SET preferred_city = ? WHERE id = ?",
(city, principal.customer_id),
)
Nothing in that contract promises that a city contains no apostrophe, SQL-looking text, or punctuation. Nor should a normal city field need such a promise to be safely stored. Real place names contain punctuation and international characters. Parameter binding lets the database store those characters as data without asking them to become SQL instructions.
Do not “fix” this correctly bound write by removing quotes from every saved city. That loses information, damages legitimate values, and creates an undocumented dependency for every consumer. Even if one filter happened to reject a particular demonstration string, a later import, migration, partner integration, or older database row could still supply a value outside that filter.
The security label you should carry forward is customer-controlled city text, not sanitized database text. Its origin remains relevant after storage, caching, queueing, and retrieval. Recording that origin in your review notes makes the second part of the trace much easier to find.