String substitution VS proper mysql escaping

[Íßêïò]

===============================
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.


a) I wanted to ask what is proper escaping mean and why after variable
page syntax has a comma

and as i have the script now

why don't my code as i have it now for string reproduction

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

don't reproduce the problem of actual deleting my data. I don't care
losing it!

I just want to see that happening with my own eyes!

 

[Cameron Simpson]

| ===============================
| 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.

| 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.

Otherwise, because a string _is_ a sequence the "%" might want to treat
the string "foo" as the sequence:

("f", "o", "o")

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,

 

[Nik Gr]

Στις 18/8/2010 7:31 πμ, ο/η Cameron Simpson έγÏαψε:

| ===============================
| 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,

print s


n
i
k
o
s

# this handles the string "nikos" as a series of chars right?
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?
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?