Are you perhaps introspecting the type of the cursor object in some way and changing behaviour based on that? We wrap the cursor object returned by DBAPI2 module and so you wouldn't see original psycopg2 cursor type.
Graham On Dec 16, 10:02 am, Graham Dumpleton <graham.dumple...@gmail.com> wrote: > On Dec 16, 7:13 am, Massimo Di Pierro <massimo.dipie...@gmail.com> > wrote: > > > 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. > > It may be valid SQL as eventually received by database, but you are > ignoring that the pscyopg2 adapter will do variable substitution prior > to it getting passed to the database. > > The pscyop2 adapter is interpreting the %| as trigger to look for > variable to substitute and thus the index error when it finds > parameter tuple is empty. > > The substitution looks for: > > %% - replace with % > %(name) - believe this expects parameters to be a dictionary and > does name based lookup > %s - in sequence as finds these gets next value from parameter list/ > tuple. > > For the latter people usually use %s, but psycopg2 will actually > ignore the character if got through above checks and thus why %| is > also triggering variable substitution. > > All DBAPI modules support parameter substitution in some way by the > placeholder differs. > > SQLite for example uses ? instead of % variants above. > > So, any SQL you provide has to for the specific database adapter > escape placeholders where they aren't actually meant to be used for > variable substitution. > > The New Relic agent passes through exactly what above layer provides > and so not doing this in framework/application code would lead to the > problems where substitution not occurring. > > Graham > > > > > > > > > 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;'
