I disagree. this SELECT * FROM table where field ILIKE '%|featured|%';
is valid SQL. % does not need to be escaped in SQL and, in fact, it is used for pattern patching: http://www.postgresql.org/docs/7.4/static/functions-matching.html Perhaps new-relic may have a bug. On Dec 14, 5:30 pm, pbreit <pbreitenb...@gmail.com> wrote: > I have been trying out New Relic and have found that any queries that > include '%' are breaking. > > This is what Graham at New Relic advised: > > As far as I can tell: > > ILIKE '%|featured|%' > > would cause a problem because the mogrify() function in psycopg2 cursor > which does variable substitution doesn't care about quoted values and will > try and do a % substitution where ever it sees one. The only time it will > not is when is actually %% meaning a single escaped %, which it would then > replace with just a single %. > > So right now to me looks like Web2Py is generating incorrect SQL. > > In general it is regarded as best practice to never do full SQL > construction yourself and to always rely on DBAPI module to do variable > text interpolation and Web2Py isn't doing that. > > The errors I get look like this: > > Traceback (most recent call last): > File "/opt/web2py/gluon/restricted.py", line 204, in restricted > exec ccode in environment > File "/opt/web2py/applications/init/controllers/default.py" > <https://test.pricetack.com/admin/edit/init/controllers/default.py>, line > 129, in <module> > File "/opt/web2py/gluon/globals.py", line 162, in <lambda> > self._caller = lambda f: f() > File "/opt/web2py/applications/init/controllers/default.py" > <https://test.pricetack.com/admin/edit/init/controllers/default.py>, line 20, > in home > (db.auth_user.id==db.item.seller)).select(limitby=(0,4), > orderby='<random>') > File "/opt/web2py/gluon/dal.py", line 5825, in select > return self.db._adapter.select(self.query,fields,attributes) > File "/opt/web2py/gluon/dal.py", line 1275, in select > rows = response(sql) > File "/opt/web2py/gluon/dal.py", line 1265, in response > self.execute(sql) > File "/opt/web2py/gluon/dal.py", line 1351, in execute > return self.log_execute(*a, **b) > File "/opt/web2py/gluon/dal.py", line 1345, in log_execute > ret = self.cursor.execute(*a, **b) > File > "/usr/local/lib/python2.6/dist-packages/newrelic-1.0.5.156-py2.6.egg/newrel > ic/hooks/database_dbapi2.py", line 38, in execute > return self._nr_cursor.execute(sql, parameters) > IndexError: tuple index out of range > > *File > /usr/local/lib/python2.6/dist-packages/newrelic-1.0.5.156-py2.6.egg/newreli > c/hooks/database_dbapi2.py > in execute at line 38* code argumentsvariables > Function argument list > > (self=<newrelic.hooks.database_dbapi2.CursorWrapper object>, sql='SELECT > item.id, item.title, item.seller, item.s...item.seller)) ORDER BY RANDOM() > LIMIT 4 OFFSET 0;', parameters=()) > Code listing > > 33. > 34. > 35. > 36. > 37. > 38. > > 39. > 40. > 41. > 42. > > transaction = newrelic.api.transaction.transaction() > if not transaction: > return self._nr_cursor.execute(sql, parameters) > with newrelic.api.database_trace.DatabaseTrace( > transaction, sql, module): > return self._nr_cursor.execute(sql, parameters) > > def executemany(self, sql, seq_of_parameters=[]): > transaction = newrelic.api.transaction.transaction() > if not transaction: > > Variablesself._nr_cursor.execute<built-in method execute of > psycopg2._psycopg.cursor > object>self<newrelic.hooks.database_dbapi2.CursorWrapper > object>self._nr_cursor<cursor object at 0x29ffc18; closed: > 0>parameters()sql'SELECT > item.id, item.title, item.seller, item.s...item.seller)) ORDER BY RANDOM() > LIMIT 4 OFFSET 0;'
