QuoteSQL

[Lawrence D'Oliveiro]

Why doesn't MySQLdb provide a function like this:

def QuoteSQL(Str, DoWild) :
"""returns a MySQL string literal which evaluates to Str. Needed
for those times when MySQLdb's automatic quoting isn't good enough."""
Result = []
for Ch in str(Str) :
if Ch == "\0" :
Ch = "\\0"
elif Ch == "\010" :
Ch = "\\b"
elif Ch == "\011" :
Ch = "\\t"
elif Ch == "\012" :
Ch = "\\n"
elif Ch == "\015" :
Ch = "\\r"
elif Ch == "\032" :
Ch = "\\z"
elif Ch == "'" or Ch == "\"" or Ch == "\\" :
Ch = "\\" + Ch
elif DoWild and (Ch == "%" or Ch == "_") :
Ch = "\\" + Ch
#end if
Result.append(C h)
#end for
return "\"" + "".join(Res ult) + "\""
#end QuoteSQL

[John Machin]

Re: QuoteSQL


Lawrence D'Oliveiro wrote:

Why doesn't MySQLdb provide a function like this:

Because the author has read PEP 8?

[Anders J. Munch]

Re: QuoteSQL

Lawrence D'Oliveiro wrote:

Why doesn't MySQLdb provide a function like this:
>
def QuoteSQL(Str, DoWild) :
"""returns a MySQL string literal which evaluates to Str. Needed
for those times when MySQLdb's automatic quoting isn't good enough."""

Presumably because you're expected to use placeholders. When is that
not good enough?

elif Ch == "'" or Ch == "\"" or Ch == "\\" :
Ch = "\\" + Ch

Always sad to see an SQL DBMS willfully violate the SQL standard.

- Anders

[Lawrence D'Oliveiro]

Re: QuoteSQL

In message <451541db$0$417 1$ba624c82@nntp 02.dk.telia.net >, Anders J. Munch
wrote:

Lawrence D'Oliveiro wrote:

>Why doesn't MySQLdb provide a function like this:
>>
>def QuoteSQL(Str, DoWild) :
> """returns a MySQL string literal which evaluates to Str. Needed
> for those times when MySQLdb's automatic quoting isn't good
> enough."""

>
Presumably because you're expected to use placeholders. When is that
not good enough?

Here's an example:

def QuoteSQLList(Th eList) :
"""returns a MySQL list containing the items of TheList, suitable
for use in an "in" clause."""
return \
"(" + ", ".join([QuoteSQL(Str, False) for Str in TheList]) + ")"
#end QuoteSQLList

> elif Ch == "'" or Ch == "\"" or Ch == "\\" :
> Ch = "\\" + Ch

>
Always sad to see an SQL DBMS willfully violate the SQL standard.

Why is that a violation of SQL?

[Robert Kern]

Re: QuoteSQL

Anders J. Munch wrote:

Always sad to see an SQL DBMS willfully violate the SQL standard.

You must be a constantly depressed person, then. :-)

--
Robert Kern

"I have come to believe that the whole world is an enigma, a harmless enigma
that is made terrible by our own mad attempt to interpret it as though it had
an underlying truth."
-- Umberto Eco

[Sybren Stuvel]

Re: QuoteSQL

Lawrence D'Oliveiro enlightened us with:

Why doesn't MySQLdb provide a function like this:

Because generally you're able to pass newlines and the like just fine.
You can even store binary data into a BLOB column.

Sybren
--
Sybren Stüvel
Stüvel IT - http://www.stuvel.eu/

[Lawrence D'Oliveiro]

Re: QuoteSQL

In message <slrneha9ng.k6. sybrenUSE@schui mige.stuvel.eu> , Sybren Stuvel
wrote:

Lawrence D'Oliveiro enlightened us with:

>Why doesn't MySQLdb provide a function like this:

>
Because generally you're able to pass newlines and the like just fine.
You can even store binary data into a BLOB column.

Yes, I have done blobs. Still need a quoting function for the specials,
though.

[Sybren Stuvel]

Re: QuoteSQL

Lawrence D'Oliveiro enlightened us with:

Yes, I have done blobs. Still need a quoting function for the
specials, though.

Why? What makes your data so different from mine? I can store newlines
and the likes just fine in a regular text field.

Sybren
--
Sybren Stüvel
Stüvel IT - http://www.stuvel.eu/

[Anders J. Munch]

Re: QuoteSQL

Robert Kern wrote:

Anders J. Munch wrote:
>

>Always sad to see an SQL DBMS willfully violate the SQL standard.

>
You must be a constantly depressed person, then. :-)

Nah, I just look the other way most of the time *g*

- Anders

[Anders J. Munch]

Re: QuoteSQL

Lawrence D'Oliveiro wrote:

>> elif Ch == "'" or Ch == "\"" or Ch == "\\" :
>> Ch = "\\" + Ch

>Always sad to see an SQL DBMS willfully violate the SQL standard.

>
Why is that a violation of SQL?

Taking another look, I might be wrong: Your code uses double quotes, and
since SQL uses single quotes for string literals, it just might be a
compatible extension.

Otherwise I would have taken note of the backslash escapes. E.g. '\\'
is a two-character SQL string literal.

- Anders

[Steve Holden]

Re: QuoteSQL

Lawrence D'Oliveiro wrote:

In message <slrneha9ng.k6. sybrenUSE@schui mige.stuvel.eu> , Sybren Stuvel
wrote:
>
>

>>Lawrence D'Oliveiro enlightened us with:
>>

>>>Why doesn't MySQLdb provide a function like this:

>>
>>Because generally you're able to pass newlines and the like just fine.
>>You can even store binary data into a BLOB column.

>
>
Yes, I have done blobs. Still need a quoting function for the specials,
though.

No, actually you need to use the DB API as it was intended to be used.

You think Booleans' invert behaviour is strange, you think that
cgi.escape is broken, and you think Python needs a SQLquote function.

It might be a good idea to lurk for a bit longer and get more idea of
how Python is used in practice before starting to suggest spurious
improvements.

regards
Steve
--
Steve Holden +44 150 684 7255 +1 800 494 3119
Holden Web LLC/Ltd http://www.holdenweb.com
Skype: holdenweb http://holdenweb.blogspot.com
Recent Ramblings http://del.icio.us/steve.holden

[Lawrence D'Oliveiro]

Re: QuoteSQL

In message <slrnehd2vg.7nu .sybrenUSE@schu imige.stuvel.eu >, Sybren Stuvel
wrote:

Lawrence D'Oliveiro enlightened us with:

>Yes, I have done blobs. Still need a quoting function for the
>specials, though.

>
Why? What makes your data so different from mine?

"select * from details where person_name like"
" concat(\"%%\", %s, \"%%\")" \
% \
QuoteSQL(name, True)

[Sybren Stuvel]

Re: QuoteSQL

Lawrence D'Oliveiro enlightened us with:

"select * from details where person_name like"
" concat(\"%%\", %s, \"%%\")" \
% \
QuoteSQL(name, True)

Wouldn't this be a whole lot better?

cursor.execute(
"select * from details where person_name like ?",
'%' + name + '%'
)

Sybren
--
Sybren Stüvel
Stüvel IT - http://www.stuvel.eu/

[Lawrence D'Oliveiro]

Re: QuoteSQL

In message <slrnehf19i.ejf .sybrenUSE@schu imige.stuvel.eu >, Sybren Stuvel
wrote:

Lawrence D'Oliveiro enlightened us with:

> "select * from details where person_name like"
> " concat(\"%%\", %s, \"%%\")" \
> % \
> QuoteSQL(name, True)

>
Wouldn't this be a whole lot better?
>
cursor.execute(
"select * from details where person_name like ?",
'%' + name + '%'
)

No. Can you figure out why?

[Sybren Stuvel]

Re: QuoteSQL

Lawrence D'Oliveiro enlightened us with:

>Wouldn't this be a whole lot better?
>>
>cursor.execute (
> "select * from details where person_name like ?",
> '%' + name + '%'
>)

>
No. Can you figure out why?

Ok, should have tested it better. This works fine on my machine,
though:

curs.execute(
"select * from details where person_name like ?",
('%' + name + '%', )
)

Including all sorts of quotes, newlines, backslashes etc. in the name.

Sybren
--
Sybren Stüvel
Stüvel IT - http://www.stuvel.eu/