[DB-SIG] Escaping parameter markers

[Chris Clark]

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 ( 
e.g. 
http://docs.oracle.com/javase/7/docs/api/java/sql/CallableStatement.html#setString%28java.lang.String 
). 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:
>
> http://mail.openjdk.java.net/pipermail/jdbc-spec-discuss/2013-February/000053.html
> http://stackoverflow.com/questions/14779896/does-the-jdbc-spec-prevent-from-being-used-as-an-operator-outside-of-quotes
>
> 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.

Agreed.

Chris