SQL injection: how a stray quote mark turns data into instructions

Imagine a website with a search box: type a username, see that user's role. Behind the box, a program asks a database a question. The question is written in SQL (the language databases understand), and the program builds it as text. That small detail, building a question out of text, is the source of one of the most famous bugs in software.

Building a query by gluing text together

Here is the obvious way to write it in Python. The program takes whatever the visitor typed and pastes it between two quote marks:

def find_unsafe(name):
    sql = "SELECT name, role FROM users WHERE name = '" + name + "'"
    return db.execute(sql).fetchall()

With the input bob the text becomes ... WHERE name = 'bob', and everything works. The quote marks tell the database "what's between these is just a value, a piece of data". Now ask yourself: what if the visitor types a quote mark too?

Try it: where does the data end?

The playground below builds the query exactly like the code above and then colours what the database sees. Orange parts are data (inside quotes). Blue parts are instructions. Try the three preset inputs, then type your own.

What went wrong with O'Brien

Nobody was attacking anything. A person named O'Brien simply has a quote mark in their name, and the glued-together text became 'O'Brien'. The database read 'O' as a complete piece of data, and then found the leftover word Brien where it expected an instruction. It gave up with a syntax error. That is already a bug: real people with real names break your page.

When the input becomes instructions

Now the third preset. Text that was meant to be a name contains a quote mark followed by the word OR. The database cannot tell which characters came from the programmer and which came from the visitor, because by the time the query arrives, it is one flat piece of text. So it follows the visitor's words as instructions: OR '1'='1' is always true, and every row matches. This is called SQL injection: input smuggling its own instructions into your query. Here it only leaked a three-row practice table, but on a real site the same mistake can expose or change data the visitor should never touch.

The fix: send the question and the data separately

Every mainstream database library lets you write the query with a placeholder and hand over the values on the side. In Python's built-in sqlite3 the placeholder is a ?:

def find_safe(name):
    return db.execute(
        "SELECT name, role FROM users WHERE name = ?", (name,)
    ).fetchall()

These are called parameterized queries (or prepared statements). The query text is fixed and never contains the visitor's words, so there is nothing for them to hijack; the value always arrives as plain data, quote marks and all. Switch the playground to "Use a placeholder" and try the third preset again.

The real output

This is the same toy table run in actual Python 3.13 with its built-in SQLite, in the same three cases:

--- input: bob
unsafe -> [('bob', 'member')]
safe   -> [('bob', 'member')]
--- input: O'Brien
unsafe -> error: near "Brien": syntax error
safe   -> []
--- input: x' OR '1'='1
unsafe -> [('ada', 'admin'), ('bob', 'member'), ('cy', 'member')]
safe   -> []

Notice that the safe version returns an empty list for O'Brien. That is correct: there is no user with that name in the table. It looks the name up as a name, and does not crash. With a user called O'Brien in the table, it would find them.

What to take into your own code

Never build a SQL query by joining strings with anything a user typed, including values from forms, URLs, cookies and files. Use placeholders whenever your library offers them (?, %s or :name, depending on the library). One honest limit: placeholders stand in for values, not for table or column names, so if you ever need those to vary, choose from a fixed list you wrote yourself. Many web frameworks and ORMs (tools that write SQL for you) use parameters under the hood, which is one reason to use them, but they usually have a "raw query" escape hatch where the same rule applies.