Treat Every Input as Hostile
The assistant added a way to edit an expense's note. Ravi changes a note to Ravi's milk, and Kitty crashes: the apostrophe broke a database query. That crash is the friendly version of the problem. The unfriendly version is someone typing a note designed to change what the query does. Anything that arrives from outside your code, such as form fields, web addresses and uploaded files, can contain anything at all.
The rule
Check input on the server, and never paste it into queries or pages.
Three habits cover most of the risk.
Keep data out of the query text. Building SQL by pasting text into it lets the text become part of the command. This is called SQL injection:
# Dangerous: the values are pasted into the SQL itself
db.execute(f"UPDATE expenses SET note = '{note}' WHERE id = {expense_id}")
# Safe: the values travel separately, as parameters
db.execute("UPDATE expenses SET note = ? WHERE id = ?", (note, expense_id))With ? placeholders, the database treats each value purely as data, apostrophes and all. Assistants usually write the safe form, but not always, so check every query in the diff.
Escape input when you show it on a page. If Kitty inserted a note into the page as raw HTML, a note containing a <script> tag would run as code in every flatmate's browser. This is cross-site scripting. Many template systems escape text by default, turning < into harmless characters; the danger comes from code that switches that escaping off.
Validate on the server. Browser checks, such as a minimum value on a form field, help honest users but are easy to skip: anyone can send a request directly. The server must check for itself that an amount is a number, is positive, has at most two decimal places and is under the limit, and that a note is not absurdly long.
The shortcut
Follow the input. For each field, trace it from where it enters to every place it lands: the database, a page, a file, an email. At each landing spot ask one question: checked, parameterised, escaped?
Where it enters
A text box on the expense form. It can hold anything: apostrophes, HTML, ten thousand characters.
The trap is trusting the browser. If a check only exists in the form, it does not exist.
Drills
Answer fast — trust the rule.
Which of these queries is safe from SQL injection?
Kitty's amount field has a minimum of 0.01 set in the form. Does the server still need to check?
A note contains a <script> tag, and Kitty inserts it into the page as raw HTML. What can happen?
What is the shortcut for checking an input field?
Remember
- Rule: check input on the server, and never paste it into queries or pages.
- Shortcut: follow the input to every landing spot and ask "checked, parameterised, escaped?"
- Trap: trusting checks that only live in the browser.