How to Build Safe Dynamic WHERE Clauses in PostgreSQL with pg8000: Parameters Are for Values

Learn why pg8000 parameters can't hold SQL, how to fix 'syntax error at $2', and build safe dynamic WHERE clauses in PostgreSQL with proper parameterization.

Dynamic SQL filters with pg8000: why parameters aren’t SQL and how to fix it

Building a conditional WHERE clause is a common need, but mixing SQL fragments into parameter placeholders will break execution. If you’ve seen a PostgreSQL error like this when adding an optional filter, you’re likely passing SQL code as a parameter value:

"errorMessage": "{'S': 'ERROR', 'V': 'ERROR', 'C': '42601', 'M': 'syntax error at or near \"$2\"', 'P': '3793', 'F': 'scan.l', 'L': '1146', 'R': 'scanner_yyerror'}"

The core idea: SQL parameters are for values only. SQL syntax must live in the statement text.

Minimal working baseline

The basic case with a single value parameter works as expected:

import pg8000

acct_id = 1234

stmt = """
    SELECT *
    FROM samples
    WHERE account_id = %s
    AND delete_date IS NULL
    ORDER BY date DESC
"""

cur.execute(stmt, (acct_id,))

Problematic dynamic filter

Trouble starts when a dynamic SQL fragment is pushed into the parameter list. Consider an optional date filter coming from a query string, formatted like '2025-02-04':

import pg8000

acct_id = 1234

from_date = qs_args['start-date'] if 'start-date' in qs_args else None

# from_date format is: '2025-02-04'

filter_clause = ""
if from_date is not None:
    filter_clause = filter_clause + f" AND DATE(sample_date) >= '{from_date}'"

stmt = """
    SELECT *
    FROM samples
    WHERE account_id = %s
    AND delete_date IS NULL
    %s
    ORDER BY date DESC
"""

cur.execute(stmt, (acct_id, filter_clause))

This fails because the second placeholder is treated as a value. The driver will try to bind a string literal where actual SQL syntax is expected, yielding a syntax error at or near "$2".

What’s really going on

Placeholders like %s represent literal values, not pieces of SQL. The database parses the statement before binding values. When the parser reaches %s in a position where SQL is expected, it can’t replace it with a value and still produce valid syntax. That’s why a free-form filter string cannot be passed as a parameter.

There’s a second pitfall to watch for: using “fancy” quotation marks in your code. Non-ASCII quotes can break both Python and SQL parsing. Use plain ASCII quotes everywhere.

Two approaches that work

If you really need a dynamic SQL fragment, inject only the SQL syntax into the statement text and keep values parameterized. The simplest form—string-interpolating the filter fragment—works, but it increases the risk of an SQL injection vulnerability because the date value is embedded into the SQL text and won’t be escaped by the driver:

filter_clause = ""
if from_date is not None:
    filter_clause = filter_clause + f" AND DATE(sample_date) >= '{from_date}'"

stmt = f"""
    SELECT *
    FROM samples
    WHERE account_id = %s
    AND delete_date IS NULL
    {filter_clause}
    ORDER BY date DESC
"""

cur.execute(stmt, (acct_id,))

A safer pattern is to keep the SQL fragment static while parameterizing the value. Convert the incoming date to the same type as sample_date if needed, then bind it via %s. This avoids the injection risk and lets the driver handle escaping:

params = (acct_id,)
filter_clause = ""
if from_date is not None:
    filter_clause = filter_clause + " AND sample_date >= %s"
    params = (acct_id, from_date)

stmt = f"""
    SELECT *
    FROM samples
    WHERE account_id = %s
    AND delete_date IS NULL
    {filter_clause}
    ORDER BY date DESC
"""

cur.execute(stmt, params)

Using DATE(sample_date) >= %s could work too, but calling a function on a column may introduce a performance penalty depending on the count of inspected records.

Why this matters

Separating SQL from values is essential for correctness, security, and maintainability. Passing SQL fragments as parameters leads to parser errors like the one above. Interpolating raw user input into SQL text increases the risk of an SQL injection attack. Being careful with functions in predicates can also help avoid unnecessary performance costs.

Takeaways

Keep placeholders for data, not for SQL. Build dynamic SQL fragments in the statement text only when necessary, and bind user-controlled values with parameters. Prefer type-consistent comparisons such as sample_date >= %s. Use ASCII quotes to avoid hidden syntax pitfalls. With these small adjustments, your dynamic filtering stays robust, readable, and safe.