On Thu, 23 May 2013 17:44:35 +0200, M.-A. Lemburg <m...@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
_______________________________________________
DB-SIG maillist - DB-SIG@python.org
http://mail.python.org/mailman/listinfo/db-sig
