[DB-SIG] Escaping parameter markers

M.-A. Lemburg mal at egenix.com
Fri May 24 10:08:13 CEST 2013

On 23.05.2013 22:27, Chris Clark wrote:
> 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).

Thanks for the clarification. Indeed, the prepared statement does not
expose methods for named binding:

>> 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 ('?').

Right. ODBC doesn't have a way of binding by name. It only
supports binding by position - and it doesn't differentiate
between prepared statements and callable ones.

> 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.

Marc-Andre Lemburg

Professional Python Services directly from the Source  (#1, May 24 2013)
>>> Python Projects, Consulting and Support ...   http://www.egenix.com/
>>> mxODBC.Zope/Plone.Database.Adapter ...       http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
2013-07-01: EuroPython 2013, Florence, Italy ...           38 days to go

::::: Try our mxODBC.Connect Python Database Interface for free ! ::::::

   eGenix.com Software, Skills and Services GmbH  Pastor-Loeh-Str.48
    D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
           Registered at Amtsgericht Duesseldorf: HRB 46611

More information about the DB-SIG mailing list