Newbie: Database technique

[Gerhard Häring]

Mel Wilson wrote:
> [...]
> def quote (s):
>     return '"' + s + '"'
> [...]
>     mc.execute ('INSERT INTO my_users (%s) values (%s,%s)'
>                  % (','.join (org_fields)
>                    , ','.join ([quote(x) for x in rec[1:9]])
>                    , quote(role_id) ))
>     print "%5d %-20s %-20s transferred" % (i, rec[1], rec[3])
>     id = mc.insert_id()
> 
> You could get rid of the quote function [...]

Yes, it's a good idea of getting rid of the quote function. And of the
idea of constructing SQL manually, too while we're at it. *sigh*

Consider this:

#v+
# Username and password entered from a dialog box

sql = "SELECT COUNT(*) FROM USERTABLE WHERE UT_NAME=%s AND UT_PASSWORD=%s" \
    % (quote(username), quote(password))

cursor.execute(sql)
row = cursor.fetchone()
login_ok = row[0] != 0
#v-

Hah! Now somebody evil (like me ;-) wants to break in and enters the
following as a password:

    ' OR 1=1; --

Voilà. And I didn't delete your whole database yet, which would not
have been a problem at all (just throw in a few DELETEs).

The lesson is that you should use the DB-API rather than working
around it - use bound parameters:

#v+
cursor.execute(
    "SELECT COUNT(*) FROM USERTABLE WHERE UT_NAME=%s AND UT_PASSWORD=%s",
    username, password)
# ...
#v-

There are some cases, say for overly complex APIs like ADO.NET where
you don't want to manually bind 10 parameters and where you need the
*exact* *database* type of the field to do so :-((

For this or other reasons you sometimes want to use your own quote
function. It should be safe if you also escape the apostrophes, then:

#v+
import re

def quote(s):
    return "'%s'" % re.sub("'", "''", s)
#v-

Of course you'll still have to verify all user input, for example
numeric ones which don't run thru quote.

Gerhard
-- 
Favourite database:             http://www.postgresql.org/
Favourite programming language: http://www.python.org/
Combine the two:                http://pypgsql.sf.net/
Embedded database for Python:   http://pysqlite.sf.net/