SQL error when using format() function with pyodbc in Django

Question

I want to execute a command using pyodbc in my Django app. When I do simple update with one column it works great:

cursor.execute("UPDATE dbo.Table SET attr = 1 WHERE id = {}".format(id))

However when I try to use a string as a column value it throws error:

cursor.execute("UPDATE dbo.Table SET attr = 1, user = '{}' WHERE id = {}".format(id, str(request.user.username)))

Here's error message:

('42S22', "[42S22] [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'Admin'. (207) (SQLExecDirectW)")

Suprisingly this method works:

cursor.execute("UPDATE dbo.Table SET attr = 1, user = 'Admin' WHERE id = {}".format(id))

What seems to be the problem? Why is sql mistaking column value for its name?

highly discourage you using raw SQL like this in your django app, you're vunerable to sql injections, a bad actor could truncate and delete your entire database or worse gain access to your system without you knowing. At the least you should sanitise your input. Why aren't you using the ORM ? — Umar.H
– Umar.H, Commented May 28, 2021 at 14:34
@Umar.H I am using ORM. Raw SQL I use is necessary because database that is part of different system provides updates to one of my tables. I need to check something every time button is used and update this remote db. Also- there is no danger of someone messing with my data, this website will operate only inside my network, I will host it locally. — Jorhanc
– Jorhanc, Commented May 31, 2021 at 6:59

FlipperPA · Accepted Answer · 2021-05-29 14:51:15Z

4

As mentioned above, you have your arguments backwards, but if you're going to use cursor.execute(), the far more important thing to do is use positional parameters (%s). This will pass the SQL and values separately to the database backend, and protect you from SQL injection:

from django.db import connection

cursor = connection.cursor()

cursor.execute("""
    UPDATE dbo.Table
    SET attr = 1,
        user = %s
    WHERE id = %s
""", [
    request.user.username,
    id,
])

edited May 29, 2021 at 14:51

answered May 28, 2021 at 15:10

FlipperPA

14.4k5 gold badges46 silver badges76 bronze badges

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

2 Comments

Gord Thompson Over a year ago

Shouldn't the %s placeholders be ? …?

FlipperPA Over a year ago

@GordThompson When using a pyodbc cursor, that is true. Using it through Django, however, the placeholders are %s when using a Django cursor. Each DB engine then replaces %s to the underlying driver's placeholder: github.com/michiya/django-pyodbc-azure/blob/…

Tim Nyborg · Accepted Answer · 2021-05-28 14:28:20Z

2

You've got your format arguments backwards. You're passing id to user, and username to the id WHERE clause.

answered May 28, 2021 at 14:28

Tim Nyborg

1,6491 gold badge11 silver badges16 bronze badges

Collectives™ on Stack Overflow

SQL error when using format() function with pyodbc in Django

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related