[DB-SIG] Escaping parameter markers

M.-A. Lemburg mal at egenix.com
Thu May 23 17:44:35 CEST 2013


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.

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.

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.

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, May 23 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 ...           39 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
               http://www.egenix.com/company/contact/


More information about the DB-SIG mailing list