Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Python > Re: String substitution VS proper mysql escaping

Reply
Thread Tools

Re: String substitution VS proper mysql escaping

 
 
Nik Gr
Guest
Posts: n/a
 
      08-20-2010
Στις 20/8/2010 8:22 πμ, ο/η Cameron Simpson *γραψε:
> [...snip...]
> | Why does the page variable which is actually a string needs to be a
> | tuple or a list and not just as a string which is what it actually
> | is?
>
> With regard to the "%" operator, it considers the string on the left to
> be a format string with multiple %blah things in it to replace. The
> thing on the right is a sequence of items to place into the format
> string.
>

I didn't undersatnd.

> So the thing on the right is_supposed_ to
> | I have a strong desire to use it like this:
> | cursor.execute( '''SELECT hits FROM counters WHERE page = %s''' , page )
> | opposed to tuple.
>
> Hmm. This isn't the python "%" format operator at all.
> This is the database API's .execute() method.
> If it expects its second argument to be a sequence of parameters
> (which is does) then you need to supply a sequence of parameters.
> It is that simple!
>
> In you usage above you're supplying "page" instead of "(page,)".
> The latter matches the .execute() method's requirements.

I don't follow either.
 
Reply With Quote
 
 
 
 
Νίκος
Guest
Posts: n/a
 
      08-28-2010
On 20 Αύγ, 09:04, Nik Gr <(E-Mail Removed)> wrote:
> With regard to the "%" operator, it considers the string on the left to
> be a format string with multiple %blah things in it to replace. The
> thing on the right is a sequence of items to place into the format
> string.


Can you please clarify what you mean by that?

> In you usage above you're supplying "page" instead of "(page,)".
> The latter matches the .execute() method's requirements.


I tried it and "page" as a string and not a as a single element tuple
works ok.
 
Reply With Quote
 
 
 
 
MRAB
Guest
Posts: n/a
 
      08-28-2010
On 28/08/2010 20:10, Νίκος wrote:
> On 20 Αύγ, 09:04, Nik Gr<(E-Mail Removed)> wrote:
>> With regard to the "%" operator, it considers the string on the left to
>> be a format string with multiple %blah things in it to replace. The
>> thing on the right is a sequence of items to place into the format
>> string.

>
> Can you please clarify what you mean by that?
>

Basically:

format_string % (item_1, item_2, item_3)

>> In you usage above you're supplying "page" instead of "(page,)".
>> The latter matches the .execute() method's requirements.

>
> I tried it and "page" as a string and not a as a single element tuple
> works ok.


Although the .execute() method might accept a single string:

cursor.execute(sql_query, page)

as well as a tuple containing the string:

cursor.execute(sql_query, (page, ))

try to be consistent. As I said before:

"""When there's more than one value you provide a tuple. It's makes sense
from the point of view of consistency that you also provide a tuple when
there's only one value."""
 
Reply With Quote
 
Νίκος
Guest
Posts: n/a
 
      08-28-2010
On 28 Αύγ, 22:35, MRAB <(E-Mail Removed)> wrote:
> On 28/08/2010 20:10, Νίκος wrote:> On 20 Αύγ, 09:04, Nik Gr<(E-Mail Removed)> *wrote:
> >> With regard to the "%" operator, it considers the string on the left to
> >> be a format string with multiple %blah things in it to replace. The
> >> thing on the right is a sequence of items to place into the format
> >> string.

>
> > Can you please clarify what you mean by that?

>
> Basically:
>
> * * *format_string % (item_1, item_2, item_3)


I still don't follow by means that i dotn see the point here...

>
> >> In you usage above you're supplying "page" instead of "(page,)".
> >> The latter matches the .execute() method's requirements.

>
> > I tried it and "page" as a string and not a as a single element tuple
> > works ok.

>
> Although the .execute() method might accept a single string:
>
> * * *cursor.execute(sql_query, page)
>
> as well as a tuple containing the string:
>
> * * *cursor.execute(sql_query, (page, ))
>
> try to be consistent. As I said before:
>
> """When there's more than one value you provide a tuple. It's makes sense
> from the point of view of consistency that you also provide a tuple when
> there's only one value."""


cursor.execute(sql_query, (page, ))

is different than?

cursor.execute(sql_query, page, )

?

===========================
Why in mysql string substitution example i have to use page='%s' and
in the comma way(automatic mysql convertion i dont need the single
quotes and use it as page=%s ?
What is the diff?
===========================
 
Reply With Quote
 
Νίκος
Guest
Posts: n/a
 
      08-28-2010
On 28 Αύγ, 22:35, MRAB <(E-Mail Removed)> wrote:

> """When there's more than one value you provide a tuple. It's makes sense
> from the point of view of consistency that you also provide a tuple when
> there's only one value."""


Can you write something that make use of more than one value?


Perhaps you mena somethign like?

cursor.execute( '''SELECT hits FROM counters WHERE page = %s and date
= %s and host = %s''' , (page,) )

Is this what you mean?

All those special format strign identifiers will grab their values out
of the tuple?
 
Reply With Quote
 
Rami Chowdhury
Guest
Posts: n/a
 
      08-28-2010
2010/8/29 Νίκος <(E-Mail Removed)>:
> On 28 Αύγ, 22:35, MRAB <(E-Mail Removed)> wrote:
>
>> """When there's more than one value you provide a tuple. It's makes sense
>> from the point of view of consistency that you also provide a tuple when
>> there's only one value."""

>
> Can you write something that make use of more than one value?
>
>
> Perhaps you mena somethign like?
>
> cursor.execute( '''SELECT hits FROM counters WHERE page = %s and date
> = %s and host = %s''' , (page,) )
>
> Is this what you mean?
>
> All those special format strign identifiers will grab their values out
> of the tuple?


Yes, that's exactly right -- they'll try to grab values out of the
tuple, and since in that particular code snippet the tuple doesn't
contain enough items, you'll get an error

HTH,
Rami

--
Rami Chowdhury
"Never assume malice when stupidity will suffice." -- Hanlon's Razor
408-597-7068 (US) / 07875-841-046 (UK) / 0189-245544 (BD)
 
Reply With Quote
 
MRAB
Guest
Posts: n/a
 
      08-28-2010
On 28/08/2010 20:48, Νίκος wrote:
> On 28 Αύγ, 22:35, MRAB<(E-Mail Removed)> wrote:
>> On 28/08/2010 20:10, Νίκος wrote:> On 20 Αύγ, 09:04, Nik Gr<(E-Mail Removed)> wrote:
>>>> With regard to the "%" operator, it considers the string on the left to
>>>> be a format string with multiple %blah things in it to replace. The
>>>> thing on the right is a sequence of items to place into the format
>>>> string.

>>
>>> Can you please clarify what you mean by that?

>>
>> Basically:
>>
>> format_string % (item_1, item_2, item_3)

>
> I still don't follow by means that i dotn see the point here...
>
>>
>>>> In you usage above you're supplying "page" instead of "(page,)".
>>>> The latter matches the .execute() method's requirements.

>>
>>> I tried it and "page" as a string and not a as a single element tuple
>>> works ok.

>>
>> Although the .execute() method might accept a single string:
>>
>> cursor.execute(sql_query, page)
>>
>> as well as a tuple containing the string:
>>
>> cursor.execute(sql_query, (page, ))
>>
>> try to be consistent. As I said before:
>>
>> """When there's more than one value you provide a tuple. It's makes sense
>> from the point of view of consistency that you also provide a tuple when
>> there's only one value."""

>
> cursor.execute(sql_query, (page, ))
>
> is different than?
>
> cursor.execute(sql_query, page, )
>
> ?
>

Yes.

The first has 2 arguments: a string and a tuple containing the value of
'page'.

The second has 2 arguments: a string and the value of 'page'.

> ===========================
> Why in mysql string substitution example i have to use page='%s' and
> in the comma way(automatic mysql convertion i dont need the single
> quotes and use it as page=%s ?
> What is the diff?
> ===========================


In the first case you're doing the substitution yourself, but you might
not get it right, leaving your website open an SQL injection attacks.

In the second case you're letting the .execute method do the
substitution. It will have been written to do it correctly and safely.
 
Reply With Quote
 
MRAB
Guest
Posts: n/a
 
      08-28-2010
On 28/08/2010 20:51, Νίκος wrote:
> On 28 Αύγ, 22:35, MRAB<(E-Mail Removed)> wrote:
>
>> """When there's more than one value you provide a tuple. It's makes sense
>> from the point of view of consistency that you also provide a tuple when
>> there's only one value."""

>
> Can you write something that make use of more than one value?
>
>
> Perhaps you mena somethign like?
>
> cursor.execute( '''SELECT hits FROM counters WHERE page = %s and date
> = %s and host = %s''' , (page,) )
>
> Is this what you mean?
>
> All those special format strign identifiers will grab their values out
> of the tuple?


Your example contains 3 placeholders, so it needs 3 values:

cursor.execute('''SELECT hits FROM counters WHERE page = %s and
date = %s and host = %s''', (page, date, host))

This will be safe. Any quoting that's needed will be done by .execute().
 
Reply With Quote
 
Νίκος
Guest
Posts: n/a
 
      08-29-2010
On 28 Αύγ, 23:12, MRAB <(E-Mail Removed)> wrote:
> On 28/08/2010 20:51, Νίκος wrote:
>
>
>
>
>
>
>
>
>
> > On 28 Αύγ, 22:35, MRAB<(E-Mail Removed)> *wrote:

>
> >> """When there's more than one value you provide a tuple. It's makes sense
> >> from the point of view of consistency that you also provide a tuple when
> >> there's only one value."""

>
> > Can you write something that make use of more than one value?

>
> > Perhaps you mena somethign like?

>
> > cursor.execute( '''SELECT hits FROM counters WHERE page = %s and date
> > = %s and host = %s''' , (page,) )

>
> > Is this what you mean?

>
> > All those special format strign identifiers will grab their values out
> > of the tuple?

>
> Your example contains 3 placeholders, so it needs 3 values:
>
> * * *cursor.execute('''SELECT hits FROM counters WHERE page = %s and
> date = %s and host = %s''', (page, date, host))
>
> This will be safe. Any quoting that's needed will be done by .execute().


Will this also work without the parentheses?

> cursor.execute('''SELECT hits FROM counters WHERE page = %s and
> date = %s and host = %s''', page, date, host)


or python will not allow it cause it might think there are 4 args
isntead of two?


> cursor.execute(''' SELECT hits FROM counters WHERE page = '%s' and
> date = '%s' and host = '%s' ''', (page, date, host))


Whats happens if i attempt to also quote by single or double quoting
the above although now i'm aware that .execute method does the quoting
for me?
 
Reply With Quote
 
MRAB
Guest
Posts: n/a
 
      08-29-2010
On 29/08/2010 06:13, Νίκος wrote:
> On 28 Αύγ, 23:12, MRAB<(E-Mail Removed)> wrote:
>> On 28/08/2010 20:51, Νίκος wrote:
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>> On 28 Αύγ, 22:35, MRAB<(E-Mail Removed)> wrote:

>>
>>>> """When there's more than one value you provide a tuple. It's makes sense
>>>> from the point of view of consistency that you also provide a tuple when
>>>> there's only one value."""

>>
>>> Can you write something that make use of more than one value?

>>
>>> Perhaps you mena somethign like?

>>
>>> cursor.execute( '''SELECT hits FROM counters WHERE page = %s and date
>>> = %s and host = %s''' , (page,) )

>>
>>> Is this what you mean?

>>
>>> All those special format strign identifiers will grab their values out
>>> of the tuple?

>>
>> Your example contains 3 placeholders, so it needs 3 values:
>>
>> cursor.execute('''SELECT hits FROM counters WHERE page = %s and
>> date = %s and host = %s''', (page, date, host))
>>
>> This will be safe. Any quoting that's needed will be done by .execute().

>
> Will this also work without the parentheses?
>

Have you tried it?

I did. It didn't like it!

It likes the values to be in a tuple. If there's one value, that's a
1-tuple: (page, ).

>> cursor.execute('''SELECT hits FROM counters WHERE page = %s and
>> date = %s and host = %s''', page, date, host)

>
> or python will not allow it cause it might think there are 4 args
> isntead of two?
>

Not Python (the language) as such, but the method. As I said, it
expects the value(s) to be in a tuple.
>
>> cursor.execute(''' SELECT hits FROM counters WHERE page = '%s' and
>> date = '%s' and host = '%s' ''', (page, date, host))

>
> Whats happens if i attempt to also quote by single or double quoting
> the above although now i'm aware that .execute method does the quoting
> for me?


The method will put in any quoting that's needed. If you also put in
quotes then that'll result in 2 sets of quoting, one inside the other
(or something like that).

Why make more work for yourself? Let the method do it for you, safely
and correctly!
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Re: String substitution VS proper mysql escaping Cameron Simpson Python 10 09-04-2010 01:10 PM
String substitution VS proper mysql escaping Python 2 08-18-2010 09:07 AM
Proper Technique for DBD::mysql install sunckell Perl Misc 0 04-07-2009 12:27 PM
Safe string escaping? Grant Olson Python 2 03-08-2005 02:53 AM
escaping % in a string??? Amy G Python 3 02-27-2004 12:40 PM



Advertisments