1

I am using Django 2.0.5 and my (unchangable) legacy sqlite database has % symbol in a few column names.

CREATE TABLE tbl("name" text, "col1(%)" float);

When I use django model filters, the filter value is applied as a param to the sql string with % operator as:

'SELECT "col1(%)" from tbl where name LIKE "%s" ' % 'John%'

Django is unfortunately not escaping the % symbol(in the column name) in such a scenario and this is causing an error at the string formatting option.

Any suggestion on how to get around this?

EDIT: I think my question pertains to https://code.djangoproject.com/ticket/9055, the problem is only in Django debug mode.

Here is the traceback:

Traceback (most recent call last):
  File "C:\svn\br_sqlite\camp2\venv\lib\site-packages\django\core\handlers\exception.py", line 35, in inner
    response = get_response(request)
  File "C:\svn\br_sqlite\camp2\venv\lib\site-packages\django\core\handlers\base.py", line 128, in _get_response
    response = self.process_exception_by_middleware(e, request)
  File "C:\svn\br_sqlite\camp2\venv\lib\site-packages\django\core\handlers\base.py", line 126, in _get_response
    response = wrapped_callback(request, *callback_args, **callback_kwargs)
  File "C:\svn\br_sqlite\camp2\main\views.py", line 72, in showData
    qs = validateInput(model,batch,version)
  File "C:\svn\br_sqlite\camp2\main\views.py", line 36, in validateInput
    return qs if len(qs) > 0 else None
  File "C:\svn\br_sqlite\camp2\venv\lib\site-packages\django\db\models\query.py", line 254, in __len__
    self._fetch_all()
  File "C:\svn\br_sqlite\camp2\venv\lib\site-packages\django\db\models\query.py", line 1179, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "C:\svn\br_sqlite\camp2\venv\lib\site-packages\django\db\models\query.py", line 53, in __iter__
    results = compiler.execute_sql(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)
  File "C:\svn\br_sqlite\camp2\venv\lib\site-packages\django\db\models\sql\compiler.py", line 1066, in execute_sql
    cursor.execute(sql, params)
  File "C:\svn\br_sqlite\camp2\venv\lib\site-packages\django\db\backends\utils.py", line 104, in execute
    sql = self.db.ops.last_executed_query(self.cursor, sql, params)
  File "C:\svn\br_sqlite\camp2\venv\lib\site-packages\django\db\backends\sqlite3\operations.py", line 143, in last_executed_query
    print(sql % params)
ValueError: unsupported format character ')' (0x29) at index 3252

A sample model:

class tbl(models.Model):
    name=models.TextField()
    col1=models.TextField(db_column="col1(%)")

and a view:

def test(request):
    a = tbl.objects.filter(name__contains='ab')
    return ""
1
  • Presumably you've tried col1=models.TextField(db_column="col1(%%)") Commented May 25, 2018 at 8:00

1 Answer 1

1

Try using

'SELECT "col1(%%)" from tbl where name LIKE "%s" ' % 'John%'

Double %% is escaped to a single one.

Sign up to request clarification or add additional context in comments.

1 Comment

But I am using the django model filter and want to continue using that.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.