Product case study · November 19, 2026

Ask Claude Code to fix a SQLite lookup that breaks on apostrophes

Give Claude Code a reproducible SQLite lookup bug, then verify a parameterized repair with apostrophes, Unicode, duplicate names and SQL-shaped input.

Reading time
6 min
Checked
Nov 19, 2026
A blue paper token fits into a separate slot beside a charcoal database cylinder, with small evidence cards on warm paper.
A search value belongs in its own slot, not inside the query's instructions.
Bottom line

Keep the query structure fixed and bind the search value separately; removing punctuation repairs the demo by corrupting the requirement.

A contact lookup works for Ada and crashes for O’Neil. Asking an agent to “sanitize the name” can make things worse: punctuation is part of the name, and deleting it changes what you are searching for.

Give Claude Code a more useful job: preserve the search string exactly while keeping it separate from the SQL instructions. The repair is small. The interesting part is proving that it still returns the right rows when the input becomes awkward.

You need Bash on Linux, macOS or WSL, Python 3 with sqlite3, and an installed, signed-in Claude Code. All contacts below are synthetic. The Python baseline and reference repair were tested locally; the Claude prompt is an authored exercise, not a recorded agent run. Official sources were checked November 19, 2026.

Create the broken lookup

Use a fresh practice folder, away from any real database:

sql_lab=$(mktemp -d /tmp/claude-sql-binding.XXXXXX)
cd "$sql_lab"
cat > lookup.py <<'PY'
def find_contacts(db, name):
    query = f"SELECT id, name FROM contacts WHERE name = '{name}' ORDER BY id"
    return db.execute(query).fetchall()
PY

This is deliberately broken code. The f-string puts the name directly into SQL text. An apostrophe can end the quoted value early. Python’s sqlite3 documentation recommends placeholders and separately supplied parameters instead of string formatting.

Save this as test_lookup.py:

import sqlite3
import unittest
from lookup import find_contacts


class LookupTests(unittest.TestCase):
    def setUp(self):
        self.db = sqlite3.connect(":memory:")
        self.addCleanup(self.db.close)
        self.db.execute(
            "CREATE TABLE contacts (id INTEGER PRIMARY KEY, name TEXT NOT NULL)"
        )
        self.rows = [(1, "Ada"), (2, "O'Neil"), (3, "Zoë"), (4, "Ada")]
        self.db.executemany("INSERT INTO contacts VALUES (?, ?)", self.rows)

    def tearDown(self):
        actual = self.db.execute("SELECT id, name FROM contacts ORDER BY id").fetchall()
        self.assertEqual(actual, self.rows)

    def test_duplicates_in_id_order(self):
        self.assertEqual(find_contacts(self.db, "Ada"), [(1, "Ada"), (4, "Ada")])

    def test_apostrophe(self):
        self.assertEqual(find_contacts(self.db, "O'Neil"), [(2, "O'Neil")])

    def test_unicode(self):
        self.assertEqual(find_contacts(self.db, "Zoë"), [(3, "Zoë")])

    def test_missing_name(self):
        self.assertEqual(find_contacts(self.db, "Nobody"), [])

    def test_sql_shaped_input(self):
        self.assertEqual(find_contacts(self.db, "' OR 1=1 --"), [])

    def test_no_trimming(self):
        self.assertEqual(find_contacts(self.db, " Ada "), [])

Every test gets a new in-memory database. The final check also compares the stored rows with the original fixture, so a repair that rewrites names cannot quietly pass.

Run:

python3 -m unittest -v test_lookup

The local baseline ran six tests: four passed, the apostrophe case raised sqlite3.OperationalError, and the SQL-shaped input case failed because it returned all four contacts. The latter example changes the meaning of a single query; it does not need to execute a second SQL statement. Use it only in this synthetic local fixture.

If your baseline differs, check the literal quotes and filenames before continuing. If Python cannot import sqlite3, consult your Python distributor’s installation instructions for that optional module.

Set the contract before asking for a patch

Save this as SPEC.md:

find_contacts(db, name) receives a SQLite connection and a Python string.
Return all contacts whose name equals that string, ordered by numeric id.
Return a list of (id, name) tuples; an unknown name returns an empty list.
Preserve punctuation, Unicode, whitespace and duplicate matching rows.
Do not modify stored data, the schema, tests or this specification.
Change only lookup.py. Use Python's standard library.
Keep the SQL structure fixed and bind the value separately.
Do not build SQL with f-strings, concatenation or manual escaping.

Start claude from the practice folder and paste:

Read SPEC.md, lookup.py and test_lookup.py.
Run python3 -m unittest -v test_lookup and report the actual baseline.
Explain why an ordinary apostrophe and the SQL-shaped input fail differently.
Repair only lookup.py to satisfy SPEC.md. Keep all tests fixed.
Do not install packages, commit, delegate or access external databases.
Run the same tests again and report the result, including any remaining failures.
Show where the query text ends and the bound value is supplied.

This follows Anthropic’s debugging workflow: give the agent the failure, ask for a focused repair, and verify it. The prompt is a scope instruction, not a filesystem sandbox; review the session’s permissions and commands.

I would reject a patch that strips apostrophes even if it stops the crash. It has made O’Neil impossible to find under the original contract. Likewise, changing = to LIKE, trimming the input or returning only the first matching row would quietly change the product behavior.

Review the reference repair

Compare the agent’s proposal with this implementation:

def find_contacts(db, name):
    query = "SELECT id, name FROM contacts WHERE name = ? ORDER BY id"
    return db.execute(query, (name,)).fetchall()

The SQL text remains constant across searches. The second argument supplies one value for the one placeholder. (name,) is a one-element tuple; the comma matters. Do not quote the placeholder as '?', because that makes it a string literal instead.

SQLite’s expression reference defines parameters as placeholders for values supplied at runtime. That distinction explains the repair: the apostrophe stays part of the value instead of becoming query syntax.

This reference passed all six tests locally. After leaving Claude’s session, run the same command yourself:

python3 -m unittest -v test_lookup

A binding-count error usually means the parameter sequence or placeholder count is wrong. Inspect both arguments to execute. If the apostrophe still produces a syntax error, look for a remaining string interpolation. If the tests pass only after changing their expected results, restore the original tests before judging the patch.

Passing tests alone is not enough here: manual escaping could satisfy this small sample while violating the implementation contract. Read the two-line function and confirm that the value reaches execute separately.

Keep the claim narrow

This exercise verifies one exact-name lookup. It does not audit the rest of an application, establish authorization rules or prove every query is safe. Parameters stand for values; a user-selected table name or sort column needs a separately designed allowlist, not substitution into the same value slot.

Keep the fixture and the final test output with your repair. When another lookup fails, reuse the method: preserve the real input, write down the intended match behavior, and make the agent show both the patch and the evidence.

Sources

Checked November 19, 2026:

Put this to work

How to review an agent's database repair by separating query structure from values.

Try

Ask Claude Code to repair one Python lookup function without changing its tests.

Prove it worked

Run six regression tests and inspect the query for bound parameters.

Keep in view

  • Reproduce an apostrophe failure in a disposable in-memory database.
  • Give Claude Code a fixed exact-match contract and independent tests.
  • Check that SQL-shaped input stays data without changing stored names.
Learn the workflow: Set up your first Claude Code project session