Syntax error using rest_framework.authtoken.models with postgres

Using Django Mystery Errors
1

I’m trying to use Token Authentication:

REST_FRAMEWORK = {
    'DEFAULT_AUTHENTICATION_CLASSES': [
       'rest_framework.authentication.TokenAuthentication', ... ]

Calling the authenticate method from django.contrib.auth results in the following SQL syntax error:

django.db.utils.ProgrammingError: syntax error at or near "" LINE 1: select AT.key` from authtoken_token AT join auth_user AU on …

Apparently the backtick quotes typically used for mysql are not allowed in postgres.
Using django.db.backends.postgresql as my ENGINE, and have installed psycopg3[binary,pool] (latest versions)

Is this a known bug in django.contrib.auth authenticate? (or wherever the package is creating the SQL statement?) Is there a fix for this (short of ditching postgres?) Thanks for any help on this!

2

Hi sloughin,

You can find out whether it’s Django or DRF creating erroneous SQL by looking further up the stacktrace :+1: Or share it in this thread so we can take a look.

For us to determine an issue with Django we’d need to know more details on how to isolate & reproduce the issue.

3

Here’s the complete stacktrace:

Traceback (most recent call last):
  File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/django/core/handlers/exception.py", line 55, in inner
    response = get_response(request)
               ^^^^^^^^^^^^^^^^^^^^^
  File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/django/core/handlers/base.py", line 197, in _get_response
    response = wrapped_callback(request, *callback_args, **callback_kwargs)
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/django/views/decorators/csrf.py", line 56, in wrapper_view
    return view_func(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/stephenloughin/dev/cuddleworks/cwrk/cuddle/views.py", line 40, in cuddle_authenticate
    cursor.execute(sql, [username, ])  # name and profile_id are retrieved for debugging
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/django/db/backends/utils.py", line 102, in execute
    return super().execute(sql, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/django/db/backends/utils.py", line 80, in _execute_with_wrappers
    return executor(sql, params, many, context)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/django/db/backends/utils.py", line 84, in _execute
    with self.db.wrap_database_errors:
  File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/psycopg/cursor.py", line 737, in execute
    raise ex.with_traceback(None)
django.db.utils.ProgrammingError: syntax error at or near "`"
LINE 1: select AT.`key` from authtoken_token AT join auth_user AU on...
4

As to the code, without putting all of the source code in here, the source of the error in my code is as follows:

Postman call to api/login → handled by this url,py entry:

from django.urls import path
from .views import my_authenticate, ...

urlpatterns = [
    path('login/', my_authenticate), ...

then in views.py

from django.contrib.auth import authenticate
from django.http import HttpRequest, JsonResponse
...

@csrf_exempt
def my_authenticate(request: HttpRequest, **kwargs):
    """allows a user to login and returns a token for future authentication of requests"""
   
    if request.method == 'POST':  # post only!
        username = request.POST.get('username').strip()
        password = request.POST.get('password').strip()

        user = authenticate(username=username, password=password)  #  <-- this line causes the error
        ...
5

The stack trace says it’s this line that’s causing the issue:

File "/Users/stephenloughin/dev/cuddleworks/cwrk/cuddle/views.py", line 40, in cuddle_authenticate
    cursor.execute(sql, [username, ])  # name and profile_id are retrieved for debugging
6

Thanks, that was it!
{“success”: true, “key”: “509b54ccdafd332a39ea53b70ddbd92553fa18e4”, “message”: “”}

1 Like