Opened 2 years ago

Closed 2 years ago

#19883 closed Bug (duplicate)

Inconsistent parameter substitution in cursor wrappers

Reported by: xi@… Owned by: nobody
Component: Database layer (models, ORM) Version: 1.4
Severity: Normal Keywords:
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

The following script produces different results with different values of DEBUG setting and the database backend:

from django.db import connections
from django.db.utils import DEFAULT_DB_ALIAS
connection = connections[DEFAULT_DB_ALIAS]
cursor = connection.cursor()
cursor.execute("SELECT 'test' LIKE '%st'")
print cursor.fetchone()

Correct output with DEBUG=False and ENGINE=postgresql_psycopg2:

(True,)

Correct output with DEBUG=False and ENGINE=mysql:

(1L,)

Incorrect output with DEBUG=False and ENGINE=sqlite3:

(0,)

Traceback with DEBUG=True and ENGINE=postgresql_psycopg2:

Traceback (most recent call last):
  File "bug.py", line 9, in <module>
    cursor.execute("SELECT 'test' LIKE '%st'")
  File "/home/xi/lib/python2.7/site-packages/django/db/backends/util.py", line 40, in execute
    return self.cursor.execute(sql, params)
  File "/home/xi/lib/python2.7/site-packages/django/db/backends/postgresql_psycopg2/base.py", line 52, in execute
    return self.cursor.execute(query, args)
IndexError: tuple index out of range

Traceback with DEBUG=True and ENGINE=mysql:

Traceback (most recent call last):
  File "bug.py", line 9, in <module>
    cursor.execute("SELECT 'test' LIKE '%st'")
  File "/home/xi/lib/python2.7/site-packages/django/db/backends/util.py", line 40, in execute
    return self.cursor.execute(sql, params)
  File "/home/xi/lib/python2.7/site-packages/django/db/backends/mysql/base.py", line 114, in execute
    return self.cursor.execute(query, args)
  File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 159, in execute
    query = query % db.literal(args)
TypeError: not enough arguments for format string

Traceback with DEBUG=True and ENGINE=sqlite3:

Traceback (most recent call last):
  File "bug.py", line 9, in <module>
    cursor.execute("SELECT 'test' LIKE '%st'")
  File "/home/xi/lib/python2.7/site-packages/django/db/backends/util.py", line 44, in execute
    sql = self.db.ops.last_executed_query(self.cursor, sql, params)
  File "/home/xi/lib/python2.7/site-packages/django/db/backends/__init__.py", line 614, in last_executed_query
    return smart_unicode(sql) % u_params
TypeError: not enough arguments for format string

Python DB API does not specify when to perform parameter substitution, but it appears all database drivers that use pyformat parameter style follow the same rules:

  1. If cursor.execute(sql) is called with one parameter, no parameter substitution is performed.
  2. If cursor.execute(sql, params) is called with two parameters, placeholder symbols are replaced with escaped parameter values.

I'd expect Django cursor wrappers to respect these rules, or at least be consistent. Currently, django.db.backends.CursorDebugWrapper and django.db.backends.sqlite3.SQLiteCursorWrapper violate these conventions.

Change History (2)

comment:1 Changed 2 years ago by carljm

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Triage Stage changed from Unreviewed to Accepted

Thanks for the report!

comment:2 Changed 2 years ago by claudep

  • Resolution set to duplicate
  • Status changed from new to closed

I think it's a duplicate of #9055, or at least closely related.

Note: See TracTickets for help on using tickets.
Back to Top