[DB-SIG] Re: Probelem with inserting into postgres database using python

[paul@boddie.net]

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