On Mon, 28 Feb 2005 12:50:47 +0300, Oleg Broytmann <phd@...> wrote:
> On Mon, Feb 28, 2005 at 11:57:36AM +1100, Andy Todd wrote:
> > On Fri, 25 Feb 2005 18:45:29 +0300, Oleg Broytmann <phd@...> wrote:
> > > How does Oracle quote (escape) special characters in queries?
> > Because Oracle (and the associated DB
> > drivers) use proper bind variables you or I shouldn't need to do any
> > escaping in our application code.
> By "proper bind variables" do you mean that SQLObject must pass
> parametrized query string along with parameters? E.g.:
> cursor.execute("SELECT * FROM atable WHERE afield = ':1'", bind_var1)
> The problem is that SQLObject canot do it yet - SQLObject currently
> generates entire query string by itself:
> cursor.execute("SELECT * FROM atable WHERE afield = %s" % sqlrepr(bind_var1))
> And if there are special characters in bind_var1 they can cause all sort
> of problems.
> SQLObject's sqlrepr() currently handles some cases:
> sqlrepr("a string w'th apostrophe") => 'a string w''th apostrophe', for
> example, but what are other rules to quote special characters in Oracle?
> Oleg Broytmann http://phd.pp.ru/ phd@...
> Programmers don't die, they just GOSUB without RETURN.
Then it could be tricky implementing a SQLObject connector for Oracle.
cx_Oracle uses bind variables kind of like you indicated;
cursor.execute("SELECT * FROM my_table WHERE date_column = :dateCol",
If you try and handle this yourself, from my experience you'll be in a
world of, errrr, bugs. Take date values for instance, the example I
used above is fine as long as you use the default data format mask,
but to do this properly yourself you'd need to do something like;
cursor.execute("SELECT * FROM my_table WHERE date_column =
to_date('%s', 'DD-MON-YY')" % sqlrepr(bind_var1))
and ditto for numeric columns. Which is my roundabout way of saying
that unless you know the exact form of the data in your client it's
going to be tricky to generate the correct query strings.
Although having written those paragraphs it's beginning to sound a bit
more reasonable than I initially thought.
From the desk of Andrew J Todd esq