SQL injection and safe parameters

How does an app turn a search box into SQL?

Your app's code builds a SQL query as a piece of text and sends that text to the database. When a visitor types a name into the bird club's member search, the code has to get that name into the query somehow. The quick way, and the one many first drafts use, is to join strings together: the start of the query, then whatever was typed, then the closing quote.

If someone types Priya Shah, the result is a perfectly normal query. The database reads the text, sees a WHERE condition comparing name with one text value, and returns Priya's row. Nothing looks wrong, and in testing nothing goes wrong, because testers type names.

The query below is what that joined text looks like when the input is ordinary. Run it and you get one member.

SELECT name, email, joined_on
FROM members
WHERE name = 'Priya Shah';

What goes wrong when the typed text contains a quote?

The database can't tell which parts of the text came from your code and which parts came from the visitor. It only sees one string of SQL. A quote typed by the visitor closes the string your code opened, and everything after it is read as SQL.

The textbook example is a visitor typing ' OR '1'='1 into the name box. Glued between your quotes, the condition becomes name = '' OR '1'='1'. The first half looks for an empty name. The second half compares the text '1' with itself, which is true for every row. Because OR needs only one side to be true, every member in the table matches.

Run the starter query in the editor and open the Rows view. The WHERE step keeps all nine rows, admins and the treasurer included. On a real site, that is a search box that hands out the whole membership list with emails. The same trick works on login forms, filters and any other place that glues input into SQL, and it can do worse than read data when the query is an UPDATE or DELETE.

This is called SQL injection: input that changes the meaning of the query instead of being treated as data. It has been near the top of the OWASP list of web risks for over twenty years because the pattern is so easy to write by accident.

SELECT name, email, role
FROM members
WHERE name = '' OR '1'='1';

How do placeholders keep the input as data?

The fix is a parameterised query. You write the SQL with a placeholder where the value goes, usually ? or a named one like :name, and pass the value separately. The database first reads and plans the SQL text, which has a hole of a known shape in it. Then it drops the value into that hole as one complete piece of data. The value never passes through the SQL reader, so a quote in it is just a quote character and OR is just two letters.

In JavaScript with better-sqlite3 it looks like db.prepare('SELECT name, email FROM members WHERE name = ?').get(name). With a named placeholder you write WHERE name = :name and call .get({ name }). In Python's built-in sqlite3 module it is cur.execute('SELECT name, email FROM members WHERE name = ?', (name,)). The comma after name matters there: the values must be a tuple, and (name) without it is just the string.

Most libraries use the same idea with different spellings. PostgreSQL drivers such as node-postgres use $1, $2; Python's psycopg uses %s, which looks like string formatting but is a placeholder when you pass the values as the second argument to execute. Using Python's own % or f-strings to build the text puts you right back at the unsafe version.

SQLite's own query language has no way to type a placeholder value into the editor, so the code below shows the idea with comments. The next section shows what the database effectively compares against.

-- not runnable
-- JavaScript (better-sqlite3)
-- const row = db.prepare('SELECT name, email FROM members WHERE name = ?').get(name);
-- const row2 = db.prepare('SELECT name, email FROM members WHERE name = :name').get({ name });
-- Python (sqlite3)
-- cur.execute('SELECT name, email FROM members WHERE name = ?', (name,))

What does the database see when the value goes in safely?

With a placeholder, the typed text ' OR '1'='1 is compared with name as one value, all ten characters of it. No member is called that, so the query returns nothing, which is exactly what should happen.

You can write the same comparison by hand in plain SQL to see the effect. Inside a SQL string, a quote character is written as two quotes. The value ' OR '1'='1 therefore becomes ''' OR ''1''=''1' once it is wrapped in quotes. That is the task in the editor. It shows what the placeholder achieves; in app code you let the library pass the value and never build this text yourself.

The same rule lets real names with apostrophes work. Niamh O'Hara's row is stored with a doubled quote in the seed, and the query below finds her. Use the Tables view to confirm the stored name has one apostrophe.

SELECT name, email
FROM members
WHERE name = 'Niamh O''Hara';

Why isn't escaping by hand or blocking bad words enough?

Doubling quotes yourself works in a simple case, and it is tempting to write a small helper that does it. The trouble is all the cases it has to get right: different databases escape differently (MySQL also treats a backslash as an escape by default), numbers are often glued in without quotes at all, and one forgotten call anywhere in the app reopens the hole. Placeholders handle every type in every place, and the library authors have already tested them.

Blocking words such as OR, DROP or -- fails in two directions. It blocks honest input (a member named Orla, a note that says "drop-off at 9") and it misses tricks written with other spacing, capitals or comment styles. Stripping quotes out, as in this page's Break It, changes what people searched for without telling them and makes O'Hara impossible to find.

Checking input is still useful for its own reasons. Rejecting an email field with no @ or a date that isn't a date gives people better feedback. Treat that as tidiness and keep placeholders as the protection.

What about table names, column names and sort order?

Placeholders stand for values only. You can't write SELECT * FROM ? or ORDER BY ? and pass a column name; the database either refuses or, for ORDER BY, sorts by a constant and ignores your choice. Yet apps often need to let people pick a sort column or a direction.

Use an allow-list. Your code keeps a fixed list of the columns it accepts, for example name, joined_on and role, checks the requested one against the list, and puts the matching name from your own list into the SQL. Anything not on the list falls back to a default. The visitor's text never reaches the query, only your own known-good strings do. Direction works the same way: accept asc or desc, map them to ASC and DESC, and ignore anything else.

The query below is what the app sends after its allow-list picked joined_on and DESC. Every part of it came from the code.

SELECT name, joined_on
FROM members
ORDER BY joined_on DESC;

What else limits the damage if something slips through?

None of these replaces placeholders. They decide how bad a mistake gets when one happens.

• Least privilege: the database account your web app uses should have only the rights it needs. A member search needs SELECT on a few tables. It doesn't need to drop tables or read the payments table. In PostgreSQL and MySQL you create a separate user and GRANT just those rights; SQLite has no users, so protect the file itself and keep admin tools away from the public app.
• Hide raw errors: a database error message often includes part of the query, table names or column names. Show visitors a plain "Something went wrong" and write the full error to your server log. A stream of syntax errors in the log after someone types a quote is also your cue that a query is being glued together.
• Separate accounts for separate jobs: the code that runs migrations or imports can use a stronger account than the code that answers web requests.
• Keep libraries updated: drivers and ORMs fix rare escaping bugs, and updates bring those fixes.

What should I look for in SQL an AI tool wrote?

AI assistants often produce code that builds queries with template strings, because it reads naturally and the example works. In JavaScript watch for backticks with ${...} inside the SQL, such as ` SELECT * FROM members WHERE name = '${name}' . In Python watch for f-strings, .format(), % formatting or + inside execute`. Any of these with a user's value is the unsafe pattern, even when the surrounding code is tidy.

Ask for the placeholder version directly, and check that the value appears as a separate argument after the SQL string. Also check the less obvious spots: LIKE searches (the % signs belong in the value, as in .all('%' + term + '%') with WHERE name LIKE ?), IN lists (one ? per item), LIMIT and OFFSET (placeholders work there too) and ORDER BY (allow-list). An ORM's raw-query escape hatch, such as a method with "raw" or "unsafe" in its name, deserves the same look.

A quick test helps too. Type a name with an apostrophe, like O'Hara, into every search and form field. If anything errors or behaves oddly, that field is likely glued into SQL.

Field notes

• When an app pastes typed text into SQL, a quote in that text can change what the query does.
• A placeholder like ? sends the value separately, so it is always treated as data.
• Inside a SQL string, a quote character is written as two quotes.
• Placeholders stand for values only, so table and column names need an allow-list.

Easy to get wrong

A template string puts the typed text into the SQL before the database sees it, so a quote can rewrite the query. Calling prepare does not help if the value is already inside the text; the value has to go in as an argument.

An f-string builds the full SQL text in Python first. Pass the values as a tuple in the second argument, and remember the trailing comma for a single value.

Placeholders hold values, never column names, so ORDER BY ? sorts by a fixed value and changes nothing. Pick the column from your own allow-list instead.

Deleting quotes from input changes what people searched for. Keep the value whole and let a placeholder (or a doubled quote in hand-written SQL) carry it.

Questions people ask

Does an ORM protect me automatically?

Its normal query methods use placeholders under the hood, so yes for those. Raw SQL methods and string-built fragments inside ORM calls are not protected, so check those by hand.

Is a number field safe to glue in, since it has no quotes?

No. Without quotes, typed text like 1 OR 1=1 changes the query even more easily. Pass numbers as parameters too, and convert them to numbers in your code first.

Are stored procedures a fix?

Only if they use parameters inside. A stored procedure that builds SQL from its arguments by joining strings has the same problem, one layer down.

Can I use a placeholder for a whole list in IN (...)?

Not with one ? in most libraries. Generate one ? per item, like IN (?, ?, ?), and pass the items as separate values. The count of question marks comes from your code, which is safe.

Is SQLite in the browser at risk too?

The same rule applies anywhere SQL is built from input. The risk is lower when the database only holds the visitor's own data, but placeholders cost nothing, so use them everywhere.