Lawrence D'Oliveiro [EMAIL PROTECTED] wrote:
In message [EMAIL PROTECTED], LI wrote:
execfile(QuoteSQL.py)
EscapeSQLWild(r\%)
'%'
SQLString(% + EscapeSQLWild(r\%) + %)
'%%%'
EscapeSQLWild(r\%) == r\\%
True
SQLString(% + EscapeSQLWild(r\%) + %) == r'%%%'
True
With
Lawrence D'Oliveiro [EMAIL PROTECTED] wrote:
In message [EMAIL PROTECTED], Duncan Booth wrote:
Deary me. Did you actually test out that bit of code before you
posted it?
execfile(QuoteSQL.py)
EscapeSQLWild(r\%)
'%'
SQLString(% + EscapeSQLWild(r\%) + %)
'%%%'
In message [EMAIL PROTECTED], I wrote:
def EscapeSQLWild(Str) :
escapes MySQL pattern wildcards in Str.
Result = []
for Ch in str(Str) :
if Ch == % or Ch == _ :
Result.append(\\)
#end if
Result.append(Ch)
In message [EMAIL PROTECTED], LI wrote:
execfile(QuoteSQL.py)
EscapeSQLWild(r\%)
'%'
SQLString(% + EscapeSQLWild(r\%) + %)
'%%%'
EscapeSQLWild(r\%) == r\\%
True
SQLString(% + EscapeSQLWild(r\%) + %) == r'%%%'
True
With the correction to EscapeSQLWild, this becomes:
Lawrence D'Oliveiro [EMAIL PROTECTED] wrote:
def EscapeSQLWild(Str) :
escapes MySQL pattern wildcards in Str.
Result = []
for Ch in str(Str) :
if Ch == % or Ch == _ :
Result.append(\\)
#end if
In message [EMAIL PROTECTED], Duncan Booth wrote:
Lawrence D'Oliveiro [EMAIL PROTECTED] wrote:
def EscapeSQLWild(Str) :
escapes MySQL pattern wildcards in Str.
Result = []
for Ch in str(Str) :
if Ch == % or Ch == _ :
Lawrence D'Oliveiro [EMAIL PROTECTED] wrote:
In message [EMAIL PROTECTED], Duncan Booth
wrote:
Lawrence D'Oliveiro [EMAIL PROTECTED] wrote:
def EscapeSQLWild(Str) :
escapes MySQL pattern wildcards in Str.
Result = []
for Ch in str(Str) :
if Ch
In message [EMAIL PROTECTED], Duncan Booth wrote:
Lawrence D'Oliveiro [EMAIL PROTECTED] wrote:
In message [EMAIL PROTECTED], Duncan Booth
wrote:
Lawrence D'Oliveiro [EMAIL PROTECTED] wrote:
def EscapeSQLWild(Str) :
escapes MySQL pattern wildcards in Str.
Result =
Lawrence D'Oliveiro wrote:
In message [EMAIL PROTECTED], Duncan Booth wrote:
Lawrence D'Oliveiro [EMAIL PROTECTED] wrote:
In message [EMAIL PROTECTED], Duncan Booth
wrote:
Lawrence D'Oliveiro [EMAIL PROTECTED] wrote:
def EscapeSQLWild(Str) :
escapes MySQL pattern
Lawrence D'Oliveiro [EMAIL PROTECTED] wrote:
You are still missing the point. I'm not talking about generating a
MySQL string literal, I'm talking about preventing wildcards
characters having their special meaning when using the string as a
parameter in cursor.execute.
But that's what
In message [EMAIL PROTECTED], Duncan Booth wrote:
Lawrence D'Oliveiro [EMAIL PROTECTED] wrote:
You are still missing the point. I'm not talking about generating a
MySQL string literal, I'm talking about preventing wildcards
characters having their special meaning when using the string as a
Lawrence D'Oliveiro [EMAIL PROTECTED] wrote:
I'm assuming you mean, how would you get from a Python expression to a
MySQL clause that looks like
name like %%%
(wildcard % followed by literal backslash \\ followed by literal
percent \% followed by wildcard %.) That's easy:
In message [EMAIL PROTECTED], Duncan Booth wrote:
Lawrence D'Oliveiro [EMAIL PROTECTED] wrote:
I'm assuming you mean, how would you get from a Python expression to a
MySQL clause that looks like
name like %%%
(wildcard % followed by literal backslash \\ followed by literal
Lawrence D'Oliveiro wrote:
In message [EMAIL PROTECTED], Steve
Holden wrote:
When you use the DB API correctly and paramterise your queries you still
need to quote wildcards in search arguments, but you absolutely
shouldn't quote the other SQL specials.
That's what parameterised queries are
In message [EMAIL PROTECTED], Steve
Holden wrote:
Lawrence D'Oliveiro wrote:
In message [EMAIL PROTECTED], Steve
Holden wrote:
When you use the DB API correctly and paramterise your queries you still
need to quote wildcards in search arguments, but you absolutely
shouldn't quote the other
Steve Holden wrote:
Ah, so your quoting function will deduce the context in which arguments
intended for parameter substitution in the query will be used? Or are
you suggesting that it's unwise to rely on autoquoted parameters? That
could have a serious impact on the efficiency of some
In message [EMAIL PROTECTED], Duncan Booth wrote:
The spurious escaping of the apostrophe does no harm, but spuriously
escaping a newline makes the select match the letter 'n' insteal of
matching a newline.
And how would you get my QuoteSQL routine, as written, to make the same
mistake you
Sybren Stuvel wrote:
I'm suggesting functions based on the role of the string they need to
escape, not the characters in that string.
1) Quoting of wildcard strings for a query using LIKE etc.
2) Quoting of values for putting into queries.
it's actually quite amusing that some
Lawrence D'Oliveiro enlightened us with:
The trouble with this is that, instead of offering extra functionality, it
leaves the door open to making two stupid mistakes:
2) quoting of wildcards BEFORE quoting of non-wildcards
Why is this a stupid mistake in your view? Please explain this in
In message [EMAIL PROTECTED], Sybren Stuvel
wrote:
Lawrence D'Oliveiro enlightened us with:
The trouble with this is that, instead of offering extra functionality,
it leaves the door open to making two stupid mistakes:
2) quoting of wildcards BEFORE quoting of non-wildcards
Why is
Lawrence D'Oliveiro enlightened us with:
You're proposing two separate functions:
1) quoting of non-wildcard specials
2) quoting of wildcard specials
I'm suggesting functions based on the role of the string they need to
escape, not the characters in that string.
1) Quoting of
Lawrence D'Oliveiro enlightened us with:
Because quoting the wildcards introduces backslash specials before
each wildcard. Quoting non-wildcards then causes those backslashes
to be doubled, which means they escape themselves instead of the
wildcards.
I don't know about other DBMSes, but in
In message [EMAIL PROTECTED], Duncan Booth wrote:
However, your QuoteSQL messes up every time because it wraps double
quotes round the whole string, so it isn't suitable for use with
parameterised queries at all. If you care to modify it to work in that
situation I think you'll find
On Tue, 2006-09-26 at 07:08, Lawrence D'Oliveiro wrote:
So yes, there should be two separate functions, one for escaping
non-wildcard specials, and one for escaping wildcards.
You only need the first one, since every database interface that
follows PEP 249.
You still need the second
In message [EMAIL PROTECTED], Sybren Stuvel
wrote:
Lawrence D'Oliveiro enlightened us with:
You're proposing two separate functions:
1) quoting of non-wildcard specials
2) quoting of wildcard specials
I'm suggesting functions based on the role of the string they need to
escape,
Lawrence D'Oliveiro wrote:
In message [EMAIL PROTECTED], Duncan Booth wrote:
However, your QuoteSQL messes up every time because it wraps double
quotes round the whole string, so it isn't suitable for use with
parameterised queries at all. If you care to modify it to work in that
situation I
Sybren Stuvel wrote:
cur.execute(select * from people where last_name in (%s) %
','.join('%s' for i in t), t)
and now we're waiting for the ['%s']*len(t) vs. '%s' for i in t benchmarks
(and the
consistency is more important than efficiency and creating extra objects is
conceptually
wrong
Lawrence D'Oliveiro wrote:
In message [EMAIL PROTECTED], Steve
Holden wrote:
Lawrence D'Oliveiro wrote:
In message [EMAIL PROTECTED], Steve
Holden wrote:
When you use the DB API correctly and paramterise your queries you still
need to quote wildcards in search arguments, but you
Need is a strong word unless something like the
following doesn't work for some reason:
cur.execute(select * from people where last_name in
(%s,%s,%s), (name1, name2, name3) )
Which could be nicely generalized to something like
t = (name1, name2, name3)
cur.execute(select * from
Tim Chase enlightened us with:
cur.execute(select * from people where last_name in (%s) %
','.join('%s' for i in xrange(len(t))),
t)
But since the value of 'i' isn't used at all, it can be abbreviated
to:
cur.execute(select * from people where last_name in (%s) %
Lawrence D'Oliveiro wrote:
In message [EMAIL PROTECTED], Duncan Booth wrote:
However, your QuoteSQL messes up every time because it wraps double
quotes round the whole string, so it isn't suitable for use with
parameterised queries at all. If you care to modify it to work in that
situation I
Fredrik Lundh enlightened us with:
and now we're waiting for the ['%s']*len(t) vs. '%s' for i in t
benchmarks (and the consistency is more important than efficiency
and creating extra objects is conceptually wrong followups, and
the it depends on what you mean by followups to the followups)
I
Sybren Stuvel wrote:
I didn't care anything about all that. I just found the way I wrote it
somewhat easier to read.
absolutely.
/F
--
http://mail.python.org/mailman/listinfo/python-list
Lawrence D'Oliveiro [EMAIL PROTECTED] wrote:
In message [EMAIL PROTECTED], Duncan Booth wrote:
The spurious escaping of the apostrophe does no harm, but spuriously
escaping a newline makes the select match the letter 'n' insteal of
matching a newline.
And how would you get my QuoteSQL
Dennis Lee Bieber wrote:
So.. In the case of MySQLdb, one can modify the dictionary of quote
converters... Seems like one could create a class for LIKE wildcard
strings, rather than just pass plain strings, and add that type to the
list of quote converters. That way, one's custom quote
In message [EMAIL PROTECTED], Sybren Stuvel
wrote:
Tim Chase enlightened us with:
cur.execute(select * from people where last_name in (%s) %
','.join('%s' for i in xrange(len(t))),
t)
But since the value of 'i' isn't used at all, it can be abbreviated
to:
cur.execute(select * from
In message [EMAIL PROTECTED], Steve
Holden wrote:
Lawrence D'Oliveiro wrote:
In message [EMAIL PROTECTED], Steve
Holden wrote:
Lawrence D'Oliveiro wrote:
In message [EMAIL PROTECTED], Steve
Holden wrote:
When you use the DB API correctly and paramterise your queries you
still need to
In message [EMAIL PROTECTED], Steve
Holden wrote:
Lawrence D'Oliveiro wrote:
In message [EMAIL PROTECTED], Duncan Booth wrote:
In particular it currently turns newlines in backslash followed by n
which (since MySQL ignores the extra backslash escape) is equivalent to
turning newlines into
Lawrence D'Oliveiro wrote:
In message [EMAIL PROTECTED], Steve
Holden wrote:
[...]
Sadly your assertions alone fail to convince. Perhaps you could provide
a concrete example?
Sorry, that turned out to be wrong. You do in fact need to escape the
escapes on wildcards.
Thank you.
--
Steve
(\%%\, %s, \%%\) \
% \
QuoteSQL(name, True)
--
http://mail.python.org/mailman/listinfo/python-list
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
In message [EMAIL PROTECTED], 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
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(
his point was that any '%' characters inside name act like
wildcards whereas his version looked for literal percents.
This could be an argument for having a utility function to escape the
wildcards for this sort of situation, but certainly not an argument for his
proposed QuoteSQL.
--
http
not an argument for
his proposed QuoteSQL.
Why not? That is exactly one of the options my QuoteSQL offers.
--
http://mail.python.org/mailman/listinfo/python-list
Lawrence D'Oliveiro [EMAIL PROTECTED] wrote:
This could be an argument for having a utility function to escape the
wildcards for this sort of situation, but certainly not an argument for
his proposed QuoteSQL.
Why not? That is exactly one of the options my QuoteSQL offers.
Yes, but your
, but certainly not an
argument for his proposed QuoteSQL.
Indeed. An escaping function should be small and not do all kinds of
escaping for different situations at once.
Sybren
--
Sybren Stüvel
Stüvel IT - http://www.stuvel.eu/
--
http://mail.python.org/mailman/listinfo/python-list
In message [EMAIL PROTECTED], Duncan Booth wrote:
Lawrence D'Oliveiro [EMAIL PROTECTED] wrote:
This could be an argument for having a utility function to escape the
wildcards for this sort of situation, but certainly not an argument for
his proposed QuoteSQL.
Why not? That is exactly one
to escape
the wildcards for this sort of situation, but certainly not an
argument for his proposed QuoteSQL.
Indeed. An escaping function should be small and not do all kinds of
escaping for different situations at once.
Even when the two situations are related?
--
http://mail.python.org/mailman
to escape
the wildcards for this sort of situation, but certainly not an
argument for his proposed QuoteSQL.
Indeed. An escaping function should be small and not do all kinds of
escaping for different situations at once.
Look at it this way: there is _no_ case where you need escaping of wildcards
for having a utility function to escape
the wildcards for this sort of situation, but certainly not an
argument for his proposed QuoteSQL.
Indeed. An escaping function should be small and not do all kinds of
escaping for different situations at once.
Look at it this way: there is _no_ case where you
Lawrence D'Oliveiro [EMAIL PROTECTED] wrote:
Indeed. An escaping function should be small and not do all kinds of
escaping for different situations at once.
Look at it this way: there is _no_ case where you need escaping of
wildcards without also escaping other specials.
You need to engage
Lawrence D'Oliveiro enlightened us with:
An escaping function should be small and not do all kinds of
escaping for different situations at once.
Even when the two situations are related?
Yup, even then. Different situations need different escaping
functions.
Sybren
--
Sybren Stüvel
Stüvel
and wildcard specials
Or:
* A single function that performs either of the above two operations,
depending on a boolean flag--which is what QuoteSQL does.
I guess I don't have a strong preference for which way we do it, but I would
suggest confining our discussion to non-stupid solutions.
--
http
In message [EMAIL PROTECTED], Steve
Holden wrote:
When you use the DB API correctly and paramterise your queries you still
need to quote wildcards in search arguments, but you absolutely
shouldn't quote the other SQL specials.
That's what parameterised queries are for on the first place...
In article [EMAIL PROTECTED], Lawrence D'Oliveiro wrote:
You're proposing two separate functions:
1) quoting of non-wildcard specials
2) quoting of wildcard specials
Lawrence, you're wrong in this thread for the same reason you were
right in the cgi.escape thread. Escaping general
In message [EMAIL PROTECTED], Jon Ribbens wrote:
In article [EMAIL PROTECTED], Lawrence D'Oliveiro wrote:
You're proposing two separate functions:
1) quoting of non-wildcard specials
2) quoting of wildcard specials
Lawrence, you're wrong...
Did I misunderstand what Sybren Stuvel
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/
--
In message [EMAIL PROTECTED], 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.
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 -
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
--
http://mail.python.org/mailman/listinfo/python-list
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
Lawrence D'Oliveiro wrote:
In message [EMAIL PROTECTED], 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.
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
Lawrence D'Oliveiro wrote:
Why doesn't MySQLdb provide a function like this:
Because the author has read PEP 8?
--
http://mail.python.org/mailman/listinfo/python-list
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
In message [EMAIL PROTECTED], 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
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
68 matches
Mail list logo