Re: Prepared Statements

Ben Lamb wrote:
> Exactly.
> 
> I'm writing an app with a web-based interface and I've made myself a load of 
> objects that all inherit from a base class called Field. I can call a method 
> and the Field will return HTML code to edit itself, if it is a DateField that 
> will consist of some listboxes, for booleans a checkbox.
> 
> I then have some business objects which carry a list of fields. When I tell 
> the business object to write itself to the database it constructs a query 
> along of the lines of:
> 
> sql = "UPDATE %s SET " % bizobj.table
> for field in bizobj.fields:
> 	sql += "%s = '%s' " % (field.name, field.value)
> sql += ";"
> 
> When I accept input from the web I don't want someone attempting to inject 
> their own SQL, nor do I want the query to fail if they type a character that 
> needs escaping e.g. 
> 
> I could write a function to check all this myself but I'd rather use what's in 
> pyPgSQL. [...]

It works if you separate these two concerns:

1) Building the SQL string with placeholders
2) Telling the DB-API module which query to execute, and with which 
parameters

The trick with using Python's quoting is that if you double the percent 
sign, it is kept after quoting with the % operator. I confess that the 
mass of % signs below might be confusing at first sight ;-)

Attached is a working example of dynamically constructing a SQL string, 
while still using pyPgSQL's quoting of the various types. Note that 
pyPgSQL can handle a dictionary as the second parameter to cursor.execute.

-- Gerhard

from pyPgSQL import PgSQL

# PART I) Build SQL statement
# Mapping of keys to values, the keys are also the names of the fields in the
# database table:
mapping = {"id": 1, "age": 25, "firstname": "Alice", "lastname": "Muller"}

tablename = "person"

# Build the assignments for the SET clause:
s = []
for k in mapping.keys():
    if k != "id":
        s.append("%s = %%(%s)s" % (k, k))
set_values = ", ".join(s)

sql = "UPDATE %s SET %s WHERE id=%%(id)s" % (tablename, set_values)

# Part II) Execute statement
cx = PgSQL.connect(host="opuslx5", user="gerhard", database="gerhard")
cu = cx.cursor()
print sql
cu.execute(sql, mapping)
cx.close()