A dedup lookup that fails reads as "not found": urllib.parse.quote() is not query escaping

·4 mins

An OCR’d invoice number with an apostrophe in it, say O'BRIEN-1, went into a duplicate check like this one (trimmed):

flt = urllib.parse.quote(f"firma='{firma}' and cisDosle='{cis_dosle}'", safe="")
try:
    r = self.get(f"faktura-prijata/({flt}).json?detail=custom:id,kod&limit=1")
    entries = r.get("winstrom", {}).get("faktura-prijata", [])
    return entries[0] if entries else None
except FlexiBeeError:
    return None

The server rejected the filter with HTTP 400 (“Špatný formát WQL”, bad WQL format). The except turned that into None, the caller read None as “no such invoice yet”, and pushed it again: a second copy of the invoice in the books, and no error anywhere.

If your client puts a filter expression into a URL and wraps it in quote(), the apostrophe problem is probably yours too.

Setup

The API is the REST interface of ABRA Flexi, a Czech ERP, called from Python’s standard library. Its filters are a small query language (WQL) carried in the URL path: evidence/(field='value').json. The ?filter= query-string form is silently ignored, so the path it is used instead.

The values come from OCR and a language model. The invoice number was normalized for dashes and whitespace only, so an apostrophe made it into the filter intact.

What went wrong

quote() did its job. It turned the expression into something safe to put in a URL:

>>> urllib.parse.quote("cisDosle='O'BRIEN-1'", safe="")
'cisDosle%3D%27O%27BRIEN-1%27'

The server URL-decodes the path before it parses the filter, so the parser gets back cisDosle='O'BRIEN-1'. The literal ends after O; the rest is garbage, and the query fails.

Percent-encoding protects the URL, and the server undoes it before the filter language ever sees the value.

Every client in the codebase built its filters this way, including the MCP server. Two call sites even disagreed on safe= ("'=" versus ""), which made no difference, since the server decodes either form back to the same bytes.

The failure stayed hidden because of the except. A lookup that can’t answer reported “not found”, and for a duplicate check, “not found” means “write it”.

Fix

First, find out what the grammar’s escape actually is. No client library for this API escapes it (the PHP one interpolates values raw), so there was nothing to copy. A wrong guess would cause the very duplicate the lookup is there to prevent, so it was settled by asking the server, read-only, in a sandbox company, with values that can’t match any record:

cisDosle='ZZZTEST'      balanced          -> 200
cisDosle='ZZZ'TEST'     raw (the bug)     -> 400  Špatný formát WQL … pozice 15
cisDosle='ZZZ\'TEST'    backslash         -> 200
cisDosle='ZZZ''TEST'    SQL-style doubling -> 400
cisDosle='A\\B'         doubled backslash -> 400
cisDosle='A\B'          lone backslash    -> 400

So this grammar escapes an apostrophe with a backslash, the way C does, not by doubling it, the way SQL and OData do. A backslash escapes nothing else, which means a literal backslash can’t be written at all. That’s acceptable for invoice numbers, codes, company IDs, and dates, which never carry one.

Second, build every filter in one module, so the escape exists exactly once:

import urllib.parse

def escape_literal(value: object) -> str:
    return str(value).replace("'", "\\'")

def eq(field: str, value: object) -> str:
    return f"{field}='{escape_literal(value)}'"

def and_(*clauses: str) -> str:
    return " and ".join(c for c in clauses if c)

def evidence_path(evidence: str, expr: str) -> str:
    return f"{evidence}/({urllib.parse.quote(expr, safe='')}).json"

path = evidence_path("faktura-prijata", and_(eq("firma", firma), eq("cisDosle", cis_dosle)))

escape_literal handles the query language and quote handles the URL, each in one place. Against the sandbox, the real client with this filter now gets a 200 for an invoice number with an apostrophe.

Before the fix, the gap was parked as an expectedFailure test. It checks that the quotes in the generated filter balance once either escape form is removed, so it would pass whichever escape the probes picked, and it flipped to an unexpected pass the moment the fix landed.

Why

Any value that goes into a query language needs that language’s escape, and then the transport’s. Percent-encoding is the transport’s. OData $filter and most search APIs set the same trap with their own escape rules, so probe the server instead of guessing.

The other half is the except. The finder in this codebase still maps an error to None, and that remains on its list of known issues. If you write a duplicate check, give it three outcomes: found, not found, and couldn’t tell, and make the caller stop on the third.

An earlier bug in the same codebase took the other route to the same end: a bank-movement duplicate check used LIKE, which this API doesn’t support. The server didn’t reject it; it returned an empty list, so every movement was pushed a second time. The fix was the API’s BEGINS operator, plus a one-off script to delete the copies.

Takeaway

Escape a value for the query language first and for the URL second; quote() only does the second. A duplicate check that turns errors into “not found” creates a duplicate every time it breaks.

Search your code for quote(f" and check each hit for a quoted literal built from user or OCR input.