[DB-SIG] Re: Probelem with inserting into postgres database using python
paul@boddie.net
paul@boddie.net
1 Mar 2002 11:56:10 -0000
Mubaraka Arif <marif@alvin.stmarytx.edu> wrote:
>
>> Thanks for providing sample code accessing postgres database thru
>> python interface. Earlier , i was using the "pg" module which worked
>> fine , but it would not allow cursor handling. But yr approach thru
>> "pgdb" interface uses the cursor mechanisms , so I shall include it in
>> my development too.
I can recommend the pyPgSql module as being easy to install and robust,
although I've not used it extensively yet.
>> On 2002.02.27 12:31 Kevin Cole wrote:
>> >
>> > bookstore = pgdb.connect("localhost:boox") # Open the "boox" database
>> > newz = bookstore.cursor() # Establish a "cursor"
[...]
>> > inst = replace(title, "'", "''") # Escape all single quotes
>> > insw = replace(author, "'", "''")
>> > insk = replace(keywords, "'", "''")
>> > insa = replace(abstract, "'", "''")
>> > values = (id,yr,issue,page,article,inst,insw,insk,insa,pdf,touched)
>> > insert = "insert into newz values
>> > (%s,%s,%s,%s,%s,'%s','%s','%s','%s','%s','%s');"
>> > newz.execute(insert % (values))
>> > boox.commit()
It's one of my favourite topics, but I really must point out that the way the
values have been "escaped" and then "merged" with the statement should be
avoided - you should use statement parameters (bind variables) instead. For
example, with pyPgSQL:
# Don't bother escaping single quotes.
values = (id,yr,issue,page,article,inst,insw,insk,insa,pdf,touched)
# pyPgSQL uses %s as its parameter marker, but this is *not* the
# same as "merging" values into the statement. We haven't put quotes
# in the statement either because with parameters, they aren't needed.
insert = "insert into newz values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
# We aren't using the % operator here - values is another parameter.
newz.execute(insert, values)
boox.commit()
Other database modules/systems use different parameter markers. With Sybase
databases and apparently throughout JDBC, one would write the following:
insert = "insert into newz values (?,?,?,?,?,?,?,?,?,?,?)"
Parameters save you the effort of encoding your data to work with SQL syntax,
and consequently close one kind of security hole that apparently exists more
frequently than many people might suspect. If your database module doesn't
support them, switch to one that does - PostgreSQL offers enough of a choice to
make this a non-issue.
Paul
P.S. Was a unified parameter marker ever proposed for that compatibility layer
on top of DB-API?
--
Get your firstname@lastname email at http://Nameplanet.com/?su