Author: tmckay
Date: 2012-09-26 11:52:54 +0000 (Wed, 26 Sep 2012)
New Revision: 5473
Modified:
trunk/cumin/python/cumin/admin.py
trunk/cumin/python/cumin/model.py
trunk/rosemary/python/rosemary/model.py
trunk/rosemary/python/rosemary/sqlfilter.py
Log:
Properly quote values in constructed SQL queries
BZ840112
Modified: trunk/cumin/python/cumin/admin.py
===================================================================
--- trunk/cumin/python/cumin/admin.py 2012-09-26 11:45:28 UTC (rev 5472)
+++ trunk/cumin/python/cumin/admin.py 2012-09-26 11:52:54 UTC (rev 5473)
@@ -155,7 +155,8 @@
mapping_cls.user.sql_column)
#TODO: one column ?
cols = [ role_cls.name.sql_column ]
- filt = SqlComparisonFilter(user_cls._id.sql_column, user._id, '=')
+ filt = SqlComparisonFilter(user_cls._id.sql_column, user._id,
+ gen_cursor = self.app.database.get_read_cursor)
query.add_filter(filt)
sql = query.emit(cols)
cursor.execute(sql)
Modified: trunk/cumin/python/cumin/model.py
===================================================================
--- trunk/cumin/python/cumin/model.py 2012-09-26 11:45:28 UTC (rev 5472)
+++ trunk/cumin/python/cumin/model.py 2012-09-26 11:52:54 UTC (rev 5473)
@@ -321,7 +321,8 @@
def __init__(self, app, cls, sig, session, extra_filters=None):
super(SamplesSqlAdapter, self).__init__(app, cls.sql_samples_table)
- filters = cls.get_sample_filters_by_signature(sig)
+ filters = cls.get_sample_filters_by_signature(sig,
+ self.app.database.get_read_cursor)
for f in filters:
self.query.add_filter(f)
Modified: trunk/rosemary/python/rosemary/model.py
===================================================================
--- trunk/rosemary/python/rosemary/model.py 2012-09-26 11:45:28 UTC (rev 5472)
+++ trunk/rosemary/python/rosemary/model.py 2012-09-26 11:52:54 UTC (rev 5473)
@@ -629,8 +629,9 @@
self.sql_samples_insert.execute(cursor, obj.__dict__, columns=columns)
- def get_sample_filters_by_signature(self, sig):
- # by default, there are no sample_filters, if you need them, please override
this
+ def get_sample_filters_by_signature(self, sig, gen_cursor):
+ # by default, there are no sample_filters, if you need them,
+ # please override this
return []
def delete_object(self, cursor, obj):
@@ -751,16 +752,18 @@
agent_id = agent_id.replace("|", ":")
return self.get_object_by_qmf_id(cursor, agent_id, obj_id)
- def get_sample_filters_by_signature(self, signature):
+ def get_sample_filters_by_signature(self, signature, gen_cursor):
agent_id, obj_id = signature
agent_id = agent_id.replace("|", ":")
filters = []
col = self.sql_samples_table._qmf_agent_id
- filters.append(SqlComparisonFilter(col, "'%s'" % agent_id))
+ filters.append(SqlComparisonFilter(col, agent_id,
+ gen_cursor=gen_cursor))
col = self.sql_samples_table._qmf_object_id
- filters.append(SqlComparisonFilter(col, "'%s'" % obj_id))
+ filters.append(SqlComparisonFilter(col, obj_id,
+ gen_cursor=gen_cursor))
return filters
Modified: trunk/rosemary/python/rosemary/sqlfilter.py
===================================================================
--- trunk/rosemary/python/rosemary/sqlfilter.py 2012-09-26 11:45:28 UTC (rev 5472)
+++ trunk/rosemary/python/rosemary/sqlfilter.py 2012-09-26 11:52:54 UTC (rev 5473)
@@ -6,18 +6,68 @@
pass
class SqlComparisonFilter(SqlFilter):
- def __init__(self, this, that, operator="="):
+ '''
+ Emit a comparison filter for a SQL query.
+
+ The filter will be of the form 'this op that'.
+
+ If a cursor generation callback is provided and
+ 'mogrify' indicates that any of the three arguments
+ may need special quoting, the psycopg2.cursor.mogrify()
+ routine will be used to produce a filter with
+ correctly quoted values.
+
+ If 'gen_cursor' is None or 'mogrify' does not specify
+ any values to be quoted, simple concatenation will be done.
+
+ gen_cursor -- callback to retreive a pyscopg2
+ cursor, invoked as gen_cursor()
+
+ mogrify -- which values need to be quoted. This is an
+ ordered tuple of booleans corresponding to 'this', 'operator',
+ and 'that'. The default is False, False, True since 'this'
+ and 'operator' typically come directly from code but 'that'
+ may originate outside of the program.
+ '''
+ def __init__(self, this, that,
+ operator="=",
+ gen_cursor=None,
+ mogrify=(False,False,True)):
+
super(SqlComparisonFilter, self).__init__()
assert isinstance(operator, str)
+ assert len(mogrify) == 3
self.this = getattr(this, "identifier", this)
self.that = getattr(that, "identifier", that)
self.operator = operator
+ self.gen_cursor = gen_cursor
+ self.mogrify = mogrify
+
+ def _mogrify(self):
+ pattern = []
+ vals = []
+ for elem in zip((self.this, self.operator, self.that), self.mogrify):
+ if elem[1]:
+ # We want to mogrify this
+ pattern.append("%s")
+ vals.append(elem[0])
+ else:
+ # make this a literal
+ pattern.append(elem[0])
+ pattern = " ".join(pattern)
+ cursor = self.gen_cursor()
+ return cursor.mogrify(pattern, vals)
def emit(self):
- return "%s %s %s" % (self.this, self.operator, self.that)
+ if self.gen_cursor and True in self.mogrify:
+ return self._mogrify()
+ return "%s %s %s" % (self.this,
+ self.operator,
+ self.that)
+
class SqlValueFilter(SqlComparisonFilter):
def __init__(self, this, operator="="):
that = "%%(%s)s" % this.name