Bound SQL parameters don't make LIKE searches literal

Bound parameters preserve SQL syntax boundaries, but literal substring searches also need LIKE escaping and tests that reject unwanted matches.

OpenThunder Editorial · 2026-11-13

Summary slide: Bound parameters don't make searches literal Keep binding. Escape in order. Reject unwanted matches.

Bound parameters don't make searches literal. Keep parameter binding, escape literal search input, and test the complete set of returned IDs. Save the slide (PNG)

For example, a search for 10% should return discount 10%, but not discount 100. A correctly bound parameter still treats the user's percent character as a wildcard in this query:

connection.execute(
    "SELECT id FROM items WHERE label LIKE ?",
    ("%" + search_text + "%",),
)

The resulting pattern is %10%%. Its percent characters mean “any sequence of characters,” including the percent supplied by the user.

Binding protects the SQL boundary, not the search contract.

If you are reviewing an AI-generated search helper, keep parameter binding and add a behavioral test. Replacing binding with string-built SQL would solve neither problem safely. Add pattern escaping when the requirement is literal search, not when users are meant to supply wildcards.

Define which characters must stay literal

For this check, the contract is a substring search where user-supplied %, _, and ! are literal characters. The implementation reserves ! as SQLite’s explicit escape character. SQLite’s LIKE and ESCAPE documentation defines the relevant syntax:

Escape the user input first, then add the outer % characters that implement substring matching. Otherwise, you would escape the wildcards the application deliberately needs.

Escape the escape character first.

That ordering matters because later replacements introduce new exclamation marks. Escaping those newly introduced marks would change the pattern’s meaning.

The fixture makes the distinction visible:

Search input Required row IDs Faulty bound-query row IDs
% 1, 7 1–9
_ 3, 7 1–9
!%_ 7 7, 8

The near-matching rows expose the defect. A fixture containing only the desired matches would let the faulty query pass.

Save this isolated regression check

Save the following as test_like_escape.py. It uses Python’s standard-library sqlite3 and unittest modules and creates only an in-memory database.

Assert the complete expected ID set so unwanted matches fail too. Checking that one desired row appears would miss the overmatching shown above.

import sqlite3
import unittest


ROWS = [
    (1, "discount 10%"),
    (2, "discount 100"),
    (3, "file_name"),
    (4, "fileXname"),
    (5, "bang!done"),
    (6, "bangdone"),
    (7, "mix!%_end"),
    (8, "mix!ABZend"),
    (9, "ordinary"),
]


def bound_like(connection, text):
    # Deliberately faulty: binding does not make wildcards literal.
    rows = connection.execute(
        "SELECT id FROM items WHERE label LIKE ? ORDER BY id",
        ("%" + text + "%",),
    )
    return [row[0] for row in rows]


def escape_like_literal(text):
    return (
        text.replace("!", "!!")
        .replace("%", "!%")
        .replace("_", "!_")
    )


def literal_like(connection, text):
    pattern = "%" + escape_like_literal(text) + "%"
    rows = connection.execute(
        "SELECT id FROM items "
        "WHERE label LIKE ? ESCAPE '!' ORDER BY id",
        (pattern,),
    )
    return [row[0] for row in rows]


class LiteralLikeTests(unittest.TestCase):
    def setUp(self):
        self.connection = sqlite3.connect(":memory:")
        self.connection.execute(
            "CREATE TABLE items "
            "(id INTEGER PRIMARY KEY, label TEXT NOT NULL)"
        )
        self.connection.executemany(
            "INSERT INTO items (id, label) VALUES (?, ?)",
            ROWS,
        )

    def tearDown(self):
        self.connection.close()

    def test_faulty_helper_exposes_wildcard_expansion(self):
        all_ids = [row_id for row_id, _ in ROWS]
        self.assertEqual(bound_like(self.connection, "%"), all_ids)
        self.assertEqual(bound_like(self.connection, "_"), all_ids)
        self.assertEqual(bound_like(self.connection, "!%_"), [7, 8])

    def test_literal_substring_contract(self):
        cases = [
            ("%", [1, 7]),
            ("_", [3, 7]),
            ("!", [5, 7, 8]),
            ("!%_", [7]),
            ("10%", [1]),
            ("file_", [3]),
            ("bang!", [5]),
            ("ordinary", [9]),
            ("missing", []),
            ("", [row_id for row_id, _ in ROWS]),
        ]
        for text, expected_ids in cases:
            with self.subTest(text=text):
                self.assertEqual(
                    literal_like(self.connection, text),
                    expected_ids,
                )


if __name__ == "__main__":
    unittest.main()

Run it locally or as a CI step:

python -m unittest -v test_like_escape.py

The first test documents the deliberately faulty helper’s overmatching. The second enforces the desired behavior.

Assert unwanted matches are absent too

A check that merely looks for placeholders would accept both helpers. Both bind their values, but only one implements the literal-search requirement.

Exact result sets catch both missing matches and unwanted matches.

The fixture deliberately exercises four distinct boundaries:

Boundary What catches a mistake
Literal percent % and 10% reject wildcard expansion.
Literal underscore _ and file_ reject single-character substitution.
Escape character ! and bang! catch mishandling of the chosen escape character.
Combined characters !%_ catches interactions that isolated inputs can miss.

The empty-input case also records a product decision: here, an empty search returns every row. If your application rejects empty searches or returns no results, change that expectation and implement the policy explicitly.

“Literal” here concerns pattern metacharacters, not case sensitivity. The lowercase fixture deliberately avoids making a broader promise about text comparison.

Test the actual query path before relying on CI

This file demonstrates SQLite behavior. For an application regression test, I would call the production search helper against the target database engine, reaching the ORM wrapper and request validation where present. Testing a separately copied implementation will not catch changes to the actual query path.

When adapting the test, revalidate these assumptions:

Try it today: run the file, then change the contract test’s call from literal_like to bound_like and rerun it. Inspect the % failure: all nine IDs appear instead of [1, 7]. Restore literal_like, then add one application-specific input and its exact expected IDs using synthetic data.

Save the field guide

Field guide: Make literal LIKE searches behave literally

A field guide distilled from this article. The text version follows below.

Save the infographic (PNG)

Text version

Fixture example: a literal percent: In the article's fixture, searching for % requires IDs 1 and 7. The faulty bound query returns all nine IDs instead.

This check demonstrates SQLite behavior, not every database engine. Literal pattern matching does not establish case, Unicode, or collation behavior.