SQL ? vs $1 Parameters: Convert Placeholders Without Reordering Values
Understand anonymous and numbered SQL parameters, repeated bindings and why string replacement is unsafe around literals and comments.
SQL parameter markers are part of the interface between a query and a database driver. Some drivers use anonymous question marks; PostgreSQL commonly uses numbered markers such as $1. Changing marker syntax can appear trivial until a query contains repeated parameters, a question mark in a string or a database-specific operator. Treat conversion as a template transformation and keep value binding separate from the SQL text.
Count occurrences and parameter identities separately
In a question-mark template, each marker normally consumes the next bound value. In a numbered template, the same parameter can appear more than once. Converting WHERE a = $2 OR b = $2 to anonymous markers creates two occurrences that both need the original second value. The converter therefore returns an occurrence-based parameter-order list in the numbered-to-anonymous direction. Use that list to build the destination bindings rather than copying the original array unchanged.
Preserve quoted SQL and comments
A question mark inside a quoted string is data, not a parameter. Marker-like text may also appear in quoted identifiers, line comments, block comments or PostgreSQL dollar-quoted strings. A lexical converter must skip those regions rather than applying a global replacement. Our tool recognizes these common forms and rejects unclosed quoted regions. It is still not a full parser for every SQL dialect. Database-specific quoting extensions and ambiguous operator syntax require review before the transformed query is used.
Do not confuse placeholders with escaped values
The generated SQL should still be passed to a database driver together with a separate binding collection. This tool never inserts values, executes a query or establishes that a query is valid for the destination server. PostgreSQL question-mark JSON operators are particularly ambiguous when interpreting question marks as source placeholders; provide a prepared-statement template without those operators. After conversion, test a query with representative values through the actual driver and verify both the result and the binding count.
Worked example
Input: SELECT * FROM t WHERE a = $2 OR b = $2 AND c = $1;
Output: SELECT * FROM t WHERE a = ? OR b = ? AND c = ?;
Parameter order: [2, 2, 1]Can I replace every question mark with an incrementing parameter number?
Only for a restricted template where every unquoted question mark is a bind marker. Literals, comments and JSON operators make unrestricted replacement unreliable.
Reference and further reading
KEEP EXPLORING
Another useful idea.
Bulk URL Slug Migration: Build a Safe Redirect Map
Generate slugs for a content migration, identify collisions, and preserve existing URLs with explicit redirects.
How to Prevent Duplicate URL Slugs in a CMS
Find slug collisions, enforce uniqueness, and distinguish duplicate paths from duplicate content.
SQL IN and NOT IN: NULL, Empty Lists and Parameters
Understand three-valued SQL logic, why NOT IN with null can remove expected rows, and how to handle an empty lookup list.