Django sqlite3 custom sql operational error when passing more than one parameter

Question

I am trying to run a custom sql query in django on an sqlite3 database and get an operational error when I try and pass more than one parameter into my sql statement. I'm not sure why, I've tried using cursor.fetchall() instead of namedtuplefetchall but that didn't work. My database is named cardholders.sqlite3 and I have a table also name cardholders that I'm trying to pull data out of.

below is the relevant code

from django.db import connections
from collections import namedtuple
def namedtuplefetchall(cursor):
    "Return all rows from a cursor as a namedtuple"
    desc = cursor.description
    nt_result = namedtuple('Result', [col[0] for col in desc])
    return [nt_result(*row) for row in cursor.fetchall()]

then some views in between

@login_required
def databaseTest(request):
    if request.method == 'POST':
        postid = request.POST.get("id")
        with connections['cardholders'].cursor() as cursor:
            cursor.execute("SELECT * FROM %s WHERE ID = %s",['cardholders',postid])
            row = namedtuplefetchall(cursor)
            cursor.close()
        return render(request, 'LibreBadge/databaseTest.html',
        context = {"AlertMessage":AlertMessage.objects.all, "row":row})
        row = "none"
    else:
        return render(request, 'LibreBadge/databaseTest.html',
        context = {"AlertMessage":AlertMessage.objects.all})

and the traceback

Environment:


Request Method: POST
Request URL: http://localhost:8000/databaseTest/

Django Version: 3.0.5
Python Version: 3.8.2
Installed Applications:
['LibreBadge',
 'django.contrib.admin',
 'django.contrib.auth',
 'django.contrib.contenttypes',
 'django.contrib.sessions',
 'django.contrib.messages',
 'django.contrib.staticfiles']
Installed Middleware:
['django.middleware.security.SecurityMiddleware',
 'django.contrib.sessions.middleware.SessionMiddleware',
 'django.middleware.common.CommonMiddleware',
 'django.middleware.csrf.CsrfViewMiddleware',
 'django.contrib.auth.middleware.AuthenticationMiddleware',
 'django.contrib.messages.middleware.MessageMiddleware',
 'django.middleware.clickjacking.XFrameOptionsMiddleware']



Traceback (most recent call last):
  File "/home/micah/.local/lib/python3.8/site-packages/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
  File "/home/micah/.local/lib/python3.8/site-packages/django/db/backends/sqlite3/base.py", line 396, in execute
    return Database.Cursor.execute(self, query, params)

The above exception (near "?": syntax error) was the direct cause of the following exception:
  File "/home/micah/.local/lib/python3.8/site-packages/django/core/handlers/exception.py", line 34, in inner
    response = get_response(request)
  File "/home/micah/.local/lib/python3.8/site-packages/django/core/handlers/base.py", line 115, in _get_response
    response = self.process_exception_by_middleware(e, request)
  File "/home/micah/.local/lib/python3.8/site-packages/django/core/handlers/base.py", line 113, in _get_response
    response = wrapped_callback(request, *callback_args, **callback_kwargs)
  File "/home/micah/.local/lib/python3.8/site-packages/django/contrib/auth/decorators.py", line 21, in _wrapped_view
    return view_func(request, *args, **kwargs)
  File "/home/micah/Documents/GitHub/LibreBadge/mysite/LibreBadge/views/views.py", line 27, in databaseTest
    cursor.execute("SELECT * FROM %s WHERE ID = %s",['cardholders',postid])
  File "/home/micah/.local/lib/python3.8/site-packages/django/db/backends/utils.py", line 100, in execute
    return super().execute(sql, params)
  File "/home/micah/.local/lib/python3.8/site-packages/django/db/backends/utils.py", line 68, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/home/micah/.local/lib/python3.8/site-packages/django/db/backends/utils.py", line 77, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/home/micah/.local/lib/python3.8/site-packages/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
  File "/home/micah/.local/lib/python3.8/site-packages/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/home/micah/.local/lib/python3.8/site-packages/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
  File "/home/micah/.local/lib/python3.8/site-packages/django/db/backends/sqlite3/base.py", line 396, in execute
    return Database.Cursor.execute(self, query, params)

Exception Type: OperationalError at /databaseTest/
Exception Value: near "?": syntax error

mechanical_meat · Accepted Answer · 2020-05-01 03:40:38Z

1

Instead do:

            qry = "SELECT * FROM {} WHERE ID = ?".format('cardholders')
            cursor.execute(qry,(postid,))

Query parametrization doesn't work for table names thus the need for two separate operations with the query string.

Also the with statement is going to close the cursor itself so you do not need this line:

            cursor.close()

edited May 1, 2020 at 3:40

answered May 1, 2020 at 3:33

mechanical_meat

170k25 gold badges237 silver badges231 bronze badges

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

11 Comments

memtech3 Over a year ago

would it be a bad idea to do qry = "SELECT * FROM" + table + " WHERE" + field + " =" +value ? with all table field and value as variables

memtech3 Over a year ago

Thank you so much this was driving me nuts

memtech3 Over a year ago

is there a good way to prevent sql injection here by escaping the variables?

mechanical_meat Over a year ago

I suggest making a whitelist of tablenames, and check for membership in that list.

mechanical_meat Over a year ago

Oh for the values they should be substituted in with the database module's facility for that; in this case with the ? like is shown in my answer.

|

memtech3 · Accepted Answer · 2020-05-01 18:02:21Z

1

Thank you mechanical_meat for helping me this is the code that I wrote as a result of your answer

from django.db import connections
from collections import namedtuple

def namedtuplefetchall(cursor):
    "Return all rows from a cursor as a namedtuple"
    desc = cursor.description
    nt_result = namedtuple('Result', [col[0] for col in desc])
    return [nt_result(*row) for row in cursor.fetchall()]

def select(db, table, field, value):
    with connections['cardholders'].cursor() as cursor:
                qry = "SELECT * FROM " + table + " WHERE " + field + " = " + value
                cursor.execute(qry,[])
                return namedtuplefetchall(cursor)
                cursor.close()

answered May 1, 2020 at 18:02

memtech3

1471 gold badge1 silver badge11 bronze badges

Collectives™ on Stack Overflow

Django sqlite3 custom sql operational error when passing more than one parameter

2 Answers 2

11 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related