backend-specific escaping

[jws]

My ticket was closed - http://code.djangoproject.com/ticket/470

I'd like to clarify the reasoning, since I think it pertains to a
larger issue. My understanding of Adrian's comment is that there
currently is no infrastructure in Django to escape special characters
in strings in a way that is specific to each backend. Admittedly, I
haven't dug in very far yet, but that seems like an odd omission.

The question is:

Does this reflect a policy- 'we do not concern ourselves with quoting
and leave that task to the backend' or is it rather a simple statement
of reality, which might change in the future?

[Maniac]

jws wrote:

>My ticket was closed - http://code.djangoproject.com/ticket/470
>
>I'd like to clarify the reasoning, since I think it pertains to a
>larger issue. My understanding of Adrian's comment is that there
>currently is no infrastructure in Django to escape special characters
>in strings in a way that is specific to each backend. Admittedly, I
>haven't dug in very far yet, but that seems like an odd omission.
>
>

Django uses bound parameters in queries. It means that a query that fed
to database never contain any actual strings or numbers but instead
placeholders:

SELECT name FROM some_table WHERE id=%s

%s is a placeholder (though looking similar to Python's specifier). When
database executes query and encounter a placeholder it only then asks
client for data for this placeholder. Data itself provided separately in
native format.

This serves two purposes:
- you don't need escaping since database has already parsed and prepared
query and just picks data in a known format
- similar queries which only differs in data become abslutely identical
which means that database may cache its parsed state and just use
different parameters. This GREATLY improves performance.

[Adrian Holovaty]

On 11/29/05, jws <jsack...@gmail.com> wrote:
> I'd like to clarify the reasoning, since I think it pertains to a
> larger issue. My understanding of Adrian's comment is that there
> currently is no infrastructure in Django to escape special characters
> in strings in a way that is specific to each backend. Admittedly, I
> haven't dug in very far yet, but that seems like an odd omission.

Sure, I'd be happy to clear up the reasoning --

You're correct that there's no infrastructure in Django to escape
special characters for a given database backend. That's because the
underlying Python database libraries -- psycopg, MySQLdb and
py-sqllite -- handle this automatically when you pass them a database
query with parameters.

The problem is that those libraries don't expose that functionality.
(I'd love to be proved wrong on this!)

Adrian

--
Adrian Holovaty
holovaty.com | djangoproject.com | chicagocrime.org

[jws]

OK, so this is not a problem when we are working with the databases
'live', since they are using parameterized queries. It IS a problem
when using the 'install' and 'sql' sub-commands, since those are built
up using simple string concatenation.

So if all the following were true, would this be acceptable?

1. Each backend class grew a escapeChars() method that worked
correctly.
2. My original change used the appropriate method when composing the
sql statements.
3. We comment the function to state it is only to be used for sql
string composition.

[Amit Upadhyay]

On 11/29/05, jws <jsack...@gmail.com> wrote:

How about:

1. Each backend class grew escapeChars() method.
   
2. It raises NotImplemented if its not implemented/NotPossible if escaping can not be done without an open connection.
3. Your original change tries to use escapeChars and if no exception is thrown, but resorts to current way on exception.
4. On exception a warning is printed along with SQL about the problem.
   

?

--
Amit Upadhyay
Blog: http://www.rootshell.be/~upadhyay
+91-9867-359-701

[Adrian Holovaty]

On 11/29/05, Amit Upadhyay <upad...@gmail.com> wrote:
> Each backend class grew escapeChars() method.
> It raises NotImplemented if its not implemented/NotPossible if escaping can
> not be done without an open connection.
> Your original change tries to use escapeChars and if no exception is thrown,
> but resorts to current way on exception.
> On exception a warning is printed along with SQL about the problem.

That sounds good to me. I believe one of the backend db libraries
(MySQLdb, maybe?) does indeed expose the parameter-quoting logic, so
we could just wrap it in that case. In the other cases, we'll have to
write the code. Would one of you guys volunteer to write a patch?

[jws]

If this sounds reasonable, I'll attach my diff to the original ticket,
re-open it and continue the discussion there.

[Adrian Holovaty]

On 11/29/05, jws <jsack...@gmail.com> wrote:

> If this sounds reasonable, I'll attach my diff to the original ticket,
> re-open it and continue the discussion there.

Sounds good!