String substitution VS proper mysql escaping

[Nik Gr]

  Στις 18/8/2010 7:31 πμ, ο/η Cameron Simpson έγραψε:
> On 17Aug2010 20:15, Νίκος<nikos.the.gr33k at gmail.com>  wrote:
> | ===============================
> | cursor.execute( ''' SELECT host, hits, date FROM visitors WHERE page =
> | '%s' ORDER BY date DESC ''' % (page) )
> | ===============================
> |
> | Someone told me NOT to do string substitution ("%") on SQL statements
> | and to let MySQLdb do it
> | for me, with proper escaping like the following
> |
> | ===============================
> | cursor.execute('''SELECT host, hits, date FROM visitors WHERE page=%s
> |         ORDER BY date DESC''', (page,))
> | ===============================
> |
> | The difference is that if some external source can control "page",
> | and
> | they put in a value like
> |         100 ; DELETE FROM visitors; SELECT * FROM visitors
> | i will be losing my database table data.
>
> That other difference is that the mysql dialect support knows how to
> correctly escape a string for insertion into an SQL statement. You may
> not, or may forget to pre-escape the string, etc. Using the MySQLdb
> stuff do it for you is reliable and robust.

Can you please tell me what escaping means by giving me an example of 
what is escaped and whats isn't?

Also hwo can i delete my data for testing purposes as?

http://webville.gr/index.html?page="100 ; DELETE FROM visitors; SELECT * 
FROM visitors"

I get an error...
> | a) I wanted to ask what is proper escaping mean and why after variable
> | page syntax has a comma
>
> Because this:
>
>    (page)
>
> means the same thing as:
>
>    page
>
> i.e. the argument to the "%" operator is just the string in page.
>
> This:
>
>    (page,)
>
> is a _tuple_ containing a single element, the page variable.
> A bit like:
>
>    [page]
>
> which is a list containing a single element. The trailing comma is
> needed to tell python you want to use a tuple, not the bare string.
>
> The "%" operator has special knowledge that is it is passed as string instead
> of a list or tuple or other sequence then it should act _as_ _if_ it had been
> passed a single element tuple containing the string.
>
%s and %d is behaving the same due to % expecting a string instead of an 
integer?

> Otherwise, because a string _is_ a sequence the "%" might want to treat
> the string "foo" as the sequence:
>
>    ("f", "o", "o")
cursor.execute('''SELECT host, hits, date FROM visitors WHERE page=%s 
ORDER BY date DESC''',  page)

But it alss might treat it an entity, i mean since 'page' is a variable containing a string why not just 'page' as it is expecting 'page' variable to give its value when asked?


> Run these three loops to see the difference:
>
>    for s in "foo":
>      print s
>    for s in ("foo"):
>      print s
>    for s in ("foo",):
>      print s
>
> Cheers,
 >>> for s in "nikos":
     print s


n
i
k
o
s

# this handles the string "nikos" as a series of chars right?

 >>> for s in ("nikos"):
     print s


n
i
k
o
s

# this handles the string "nikos" as a series of chars too but what si 
the difference with the above in htis with the parentheses? is "nikos" 
is handles still as string here?

 >>> for s in ("nikos",):
     print s


nikos

# Here yes it handles "nikos" as the 1st item of a tuple

nikos
 >>> for s in ["nikos"]:
     print s


nikos

# Here? why is it behaving fifferent than the above ("nikos") and is 
proccessign it all chars in one?

 >>> for s in ["nikos",]:
     print s


nikos

# Here it handles "nikos" as the 1st item of a list right?