[DB-SIG] Escaping parameter markers
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:
>> 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.
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