Hi everyone.
I've made a change which allows queries to be made like this, using the
classic interface:
db.query("insert into x (a,b,c) values ($3,$1,$2)", 'bravo', 3, 'alpha')
db.query("select * from x where a = $1", 'alpha')
The benefit of this is you don't have to quote input strings, you don't have
to worry about SQL injection because you forgot to quote input strings, and
you don't have to worry about the setting of standard_conforming_strings if
your data has back-slashes in it.
The attached patch is against 4.0 and hence is for demonstration purposes
only. If this is of interest to the project, I'll redo it against the head
revision and submit a diff against SVN.
The changes to pgmodule.c and the first two blocks vs pg.py are necessary for
the functionality described above. The changes starting at line 604 are the
changes to DB.update() to use this rather than quoting.
In the final patch, I'd propose to make essentially the same changes I have
here, plus equivalent changes to DB.insert(), DB.delete() and anywhere else I
find calls to _quote in pg.py. I also will do some testing with unicode which
might result in other changes.
I don't propose to touch the dbapi code or to introduce prepared statements at
this time.
--
Patrick TJ McPhee <[email protected]>
--- pgmodule.c.orig 2009-01-01 08:05:02.000000000 -0500
+++ pgmodule.c 2012-09-20 19:24:40.000000000 -0400
@@ -2222,13 +2222,14 @@
/* database query */
static char pg_query__doc__[] =
-"query(sql) -- creates a new query object for this connection,"
-" using sql (string) request.";
+"query(sql, *args) -- creates a new query object for this connection,"
+" using sql (string) request and optinally one or more arguments.";
static PyObject *
pg_query(pgobject * self, PyObject * args)
{
char *query;
+ PyObject *oargs = NULL;
PGresult *result;
pgqueryobject *npgobj;
int status;
@@ -2240,9 +2241,9 @@
}
/* get query args */
- if (!PyArg_ParseTuple(args, "s", &query))
+ if (!PyArg_ParseTuple(args, "s|O:query", &query, &oargs))
{
- PyErr_SetString(PyExc_TypeError, "query(sql), with sql (string).");
+ PyErr_SetString(PyExc_TypeError, "query(sql, *args), with sql (string).");
return NULL;
}
@@ -2254,9 +2255,48 @@
}
/* gets result */
- Py_BEGIN_ALLOW_THREADS
- result = PQexec(self->cnx, query);
- Py_END_ALLOW_THREADS
+ if (!oargs)
+ {
+ Py_BEGIN_ALLOW_THREADS
+ result = PQexec(self->cnx, query);
+ Py_END_ALLOW_THREADS
+ }
+
+ else
+ {
+ int nparms, *parml;
+ char **parms;
+ register int i;
+
+ nparms = PyTuple_Size(oargs);
+ parms = (char **)alloca(nparms * sizeof(*parms));
+ parml = (int *)alloca(nparms * sizeof(*parml));
+
+ /* convert optional args to a list of strings -- this allows
+ * the caller to pass whatever they like, and prevents us
+ * from having to map types to OIDs */
+ for (i = 0; i < nparms; i++)
+ {
+ PyObject * obj = PyTuple_GetItem(oargs, i);
+ PyObject * str;
+
+ if (obj == Py_None)
+ {
+ parml[i] = 0;
+ parms[i] = NULL;
+ }
+ else
+ {
+ str = = PyObject_Str(obj);
+ parml[i] = PyString_Size(str);
+ parms[i] = PyString_AsString(str);
+ }
+ }
+ Py_BEGIN_ALLOW_THREADS
+ result = PQexecParams(self->cnx, query, nparms,
+ NULL, parms, parml, NULL, 0);
+ Py_END_ALLOW_THREADS
+ }
/* checks result validity */
if (!result)
--- pg.py.orig 2009-01-01 08:05:02.000000000 -0500
+++ pg.py 2012-09-20 02:35:34.000000000 -0400
@@ -291,7 +291,7 @@
self.db.close()
self.db = db
- def query(self, qstr):
+ def query(self, qstr, *args):
"""Executes a SQL command string.
This method simply sends a SQL query to the database. If the query is
@@ -310,7 +310,7 @@
if not self.db:
raise InternalError('Connection is not valid')
self._do_debug(qstr)
- return self.db.query(qstr)
+ return self.db.query(qstr, args)
def pkey(self, cl, newpkey=None):
"""This method gets or sets the primary key of a class.
@@ -594,7 +594,8 @@
d.update(kw)
attnames = self.get_attnames(qcl)
if qoid in d:
- where = 'oid = %s' % d[qoid]
+ where = 'oid = $1'
+ params = [d[qoid]]
keyname = ()
else:
try:
@@ -604,14 +605,18 @@
if isinstance(keyname, basestring):
keyname = (keyname,)
try:
- where = ' AND '.join(['%s = %s'
- % (k, self._quote(d[k], attnames[k])) for k in keyname])
+ where = ' AND '.join(['%s = $%s'
+ % (k, i+1) for i,k in enumerate(keyname)])
+ params = [d[k] for k in keyname]
except KeyError:
raise ProgrammingError('Update needs primary key or oid.')
values = []
+ i = len(params)
for n in attnames:
if n in d and n not in keyname:
- values.append('%s = %s' % (n, self._quote(d[n], attnames[n])))
+ i += 1
+ values.append('%s = $%s' % (n, i))
+ params.append(d[n])
if not values:
return d
values = ', '.join(values)
@@ -622,13 +627,12 @@
ret = ''
q = 'UPDATE %s SET %s WHERE %s%s' % (qcl, values, where, ret)
self._do_debug(q)
- res = self.db.query(q)
+ res = self.db.query(q, tuple(params))
if ret:
- res = self.db.query(q).dictresult()
+ res = res.dictresult()
for att, value in res[0].iteritems():
d[att == 'oid' and qoid or att] = value
else:
- self.db.query(q)
if selectable:
if qoid in d:
self.get(qcl, d, 'oid')
_______________________________________________
PyGreSQL mailing list
[email protected]
https://mail.vex.net/mailman/listinfo.cgi/pygresql
