Little Bobby Tables: SQL Injection, and the One Habit That Stops It

Everything so far has treated SQL as something you write. In a real application, most SQL is assembled at runtime from values a user typed: a search box, a login form, a URL parameter. The moment you build a query by pasting user input into a string, you have opened the door to SQL injection. The attacker stops supplying data and starts supplying SQL. It is the oldest serious bug in web software, it still tops the vulnerability lists decades on, and it has one correct fix that has never changed. This chapter shows the attack working against the bookshop, then shows the fix making the exact same attack do nothing.

How a query becomes a weapon

Picture the naive way to look a customer up by email. The application has the address as a string and builds the query by concatenation: "... WHERE email = '" + input + "'". To make that concrete in the database itself, here is a function that does precisely that gluing — it is standing in for careless application code:

CREATE FUNCTION find_customer_unsafe(input text)
RETURNS TABLE(customer_id int, name text, email text) AS $$
BEGIN
    RETURN QUERY EXECUTE
        'SELECT customer_id, name, email FROM customers WHERE email = ''' || input || '''';
END;
$$ LANGUAGE plpgsql;

Used honestly, it does what you’d expect — one address, one customer:

SELECT count(*) FROM find_customer_unsafe('customer1@example.com');

 count
-------
     1

Now the attack. Instead of an email, the “input” is the string x' OR '1'='1. Watch what happens when that gets glued into the query:

SELECT count(*) FROM find_customer_unsafe($$x' OR '1'='1$$);

 count
-------
  5000

Every customer in the table. The $$...$$ is just psql’s way of quoting the literal input so we can type it; the input itself is x' OR '1'='1. Glued into the template, the query the database actually ran was:

SELECT customer_id, name, email FROM customers WHERE email = 'x' OR '1'='1'

The attacker’s single quote closed the string early, and everything after it became SQL, not data. WHERE email = 'x' OR '1'='1' is true for every row, because '1'='1' always is. The filter is gone. A login form built this way lets anyone in; a “show my orders” page built this way shows everyone’s.

From bypass to breach

Returning all rows of the same table is bad. Reading a different table is worse, and injection allows it through UNION, which staples a second query’s rows onto the first. Here is an equally naive book-title search:

CREATE FUNCTION search_books_unsafe(term text)
RETURNS TABLE(a text, b text) AS $$
BEGIN
    RETURN QUERY EXECUTE
        'SELECT title, genre FROM books WHERE title ILIKE ''%' || term || '%''';
END;
$$ LANGUAGE plpgsql;

It returns two text columns. An attacker who notices that can supply a term that closes the string. It adds a UNION selecting two columns from a table they were never meant to touch, then comments out the rest of the query with --:

SELECT * FROM search_books_unsafe($$zzz%' UNION SELECT name, email FROM customers WHERE customer_id <= 3 --$$);

     a      |           b
------------+-----------------------
 Customer 1 | customer1@example.com
 Customer 2 | customer2@example.com
 Customer 3 | customer3@example.com

A book search just returned customer names and email addresses. Nothing about the search page mentioned customers; the attacker reached them by writing SQL through the input box. Swap customers for a table of password hashes or payment tokens and you have the breach that makes the news. The database did nothing wrong — it faithfully ran the query it was handed. The bug is that the query was built out of a string.

The fix: keep data as data

The cure is not to escape quotes by hand, or to blocklist the word UNION, or to strip apostrophes. Those are leaky and endless. The cure is structural: never let user input become part of the query text. Send the query and the data to the database separately, with a placeholder marking where the value goes. Then the database parses the query first, with the placeholder still empty, and only afterward drops the value into the already-parsed plan. The value can no longer change the query’s structure, because the structure was decided before the value ever arrived. This is a parameterized query (also called a prepared statement).

In raw SQL, PREPARE names a statement with typed placeholders ($1, $2, …), and EXECUTE supplies the values:

PREPARE find_customer(text) AS
    SELECT customer_id, name, email FROM customers WHERE email = $1;

EXECUTE find_customer('customer1@example.com');

 customer_id |    name    |         email
-------------+------------+-----------------------
           1 | Customer 1 | customer1@example.com

Honest input, honest result. Now feed it the very same attack string that returned all 5,000 rows a moment ago:

EXECUTE find_customer($$x' OR '1'='1$$);

 customer_id | name | email
-------------+------+-------
(0 rows)

Zero rows. The attack string was treated as one thing: a literal email address that happens to contain punctuation. The database looked for a customer whose email is exactly x' OR '1'='1, found none, and returned nothing. The quotes never closed a string, because the string was never being reparsed. Same input, same database, opposite outcome — and the only change was moving the value out of the query text and into a parameter.

Every driver does this for you

You will almost never write PREPARE by hand. Every real database driver exposes parameters through placeholders, and using them is the fix. In Python’s psycopg, you pass the SQL with %s placeholders and the values as a separate tuple, and the driver keeps them apart:

# Application code — psycopg 3. Shown for illustration; not run in psql.
cur.execute(
    "SELECT customer_id, name, email FROM customers WHERE email = %s",
    ("x' OR '1'='1",),          # the malicious input, passed as a parameter
)
# → 0 rows. The value is bound as data; it can never become SQL.

The rule is one sentence and it is the whole chapter: the query string must be a constant in your code; every value goes through a placeholder. JDBC’s PreparedStatement with ?, Go’s database/sql with $1, Ruby, PHP, .NET — every one of them has this, and the vulnerability exists only when a programmer bypasses it to build SQL with string formatting. The critical distinction: WHERE email = %s with a parameter is safe; WHERE email = '{email}' built with an f-string or + is the find_customer_unsafe we started with, just in another language.

The rare exception, done safely

Sometimes you genuinely must build SQL dynamically — a table or column name chosen at runtime, which cannot be a parameter (placeholders stand in for values, not identifiers). For that narrow case Postgres gives you format() with %L (quote a literal) and %I (quote an identifier), and the standalone quote_literal(). These escape correctly so the value can’t break out:

SELECT format('WHERE email = %L', $$x' OR '1'='1$$);

             format
----------------------------------
 WHERE email = 'x'' OR ''1''=''1'

%L doubled every quote, so the entire attack string is trapped inside one literal — a function using it returns the same zero rows. Use format('%L')/quote_literal() only when a parameter genuinely cannot do the job, and reach for a real parameter every other time. Building the string yourself is the risk; these functions are how you take responsibility for it when you have no choice.

Final thoughts

SQL injection is not exotic and it is not hard to prevent. It happens for exactly one reason: user input was concatenated into query text, so the input could redefine the query. We watched that turn a customer lookup into a full-table dump and a book search into an email leak. The fix is not vigilance or escaping or clever filters; it is a structural habit. Keep the query text constant and pass every value as a parameter, and the attacker’s cleverest string is just a value that matches nothing. Your driver already supports this. The only way to be vulnerable is to go out of your way to build the query as a string. Don’t, and Little Bobby Tables goes home empty-handed. The application is now talking to the database safely; the last chapter is about talking to it efficiently, across many requests at once.

Next: The app talks to the database — connection pooling, transactions that span a request, and what a web application actually holds open.