[DB-SIG] Escaping parameter markers
Chris.Clark at actian.com
Thu May 23 22:27:53 CEST 2013
On Thu, 23 May 2013 17:44:35 +0200, M.-A. Lemburg <mal at egenix.com> wrote:
> I've done some research on escaping parameter markers. Here's what
> I came up with:
> ODBC: Supports only qmark ('?'). The standard doesn't specify an
> escape sequence for ? in SQL statements.
> JDBC: Supports qmark ('?') and named (':param'). The standard
> doesn't specify an escape sequence for either of those
> in SQL statements.
Being pedantic; JDBC requires qmark ('?').
The JDBC spec. only documents named (':param') for execute procedure (
). Any JDBC driver that implements named parms for queries, like SELECT,
has added an extension, for example Oracle (or is an ORM feature).
> Oracle: Supports named (':param'). There's no mention of escaping
> the colon in their documentation. Interesting aside: The
> parameters can be bound based on name and based on
> position (if unambiguous), so I guess we'll have to spell
> out that with named we always mean binding by name.
It depends on the interface/api. If using ODBC it is qmark ('?').
If using something like ProC, then yes it is named only.
> Since specifically PostgreSQL uses '?' as operator, there has
> been some discussion on how to resolve the issue, but I haven't
> found their resolution on the net:
> In PostgreSQL, it's possible to avoid using '?' and '::'
> in SQL by simply using appropriate functions or CASTs instead,
> so you don't really need escaping.
> Since neither of those standards defines an escape mechanism for
> the parameter markers, I guess the DB-API should not either and
> instead leave this for the database modules to handle in whatever
> way is appropriate for the database backend.
More information about the DB-SIG