Properly escaping strings in raw Django SQL query

Question

Really the root of the problem is poor database design going back to long before I started here, but that problem isn't going to be resolved by the end of the day or even the end of the week. Thus, I need to find a better way to handle the following to get a feature added.

I'm forced to deviate away from the Django ORM because I need to build a raw SQL query due to having to write logic around the FROM <table>. We've elected to go this route instead of updating the models.py a couple times a year with the new tables that are added.

There are numerous areas starting here where the Django documentation says "Do not use string formatting on raw queries or quote placeholders in your SQL strings!"

If I write the query like this:

cursor.execute("""
    SELECT * \
    FROM agg_data_%s \
    WHERE dataview = %s \
    ORDER BY sortorder """, [tbl, data_view])

It adds single quotes around tbl, which obviously causes an issue, but will correctly construct the WHERE clause surrounded in single quotes.

Doing this, will not put single quotes around tbl, but will force you to put single quotes around the WHERE which is bad to say the least (opens it up for SQL injection):

sql = """ \
    SELECT * \
    FROM agg_data_%s \
    WHERE dataview = '%s' \
    ORDER BY sortorder """ % (tbl, data_view)

cursor.execute(sql)

Anyway to make lemonade out of these lemons?

willeM_ Van Onsem · Accepted Answer · 2020-08-24 20:30:22Z

4

The parameters %s can only be used for values. Not for (parts) of identifiers, like table/column names. If you are sure tbl is safe, then you can escape with:

sql = """
    SELECT * \
    FROM agg_data_%s \
    WHERE dataview = %%s \
    ORDER BY sortorder """ % tbl, [data_view])

Here the %% will "fold" to % and thus you yield %s after the first string interpolation.

But it might be better not to use different tables for this, and thus filter the table, for example by a foreign key.

answered Aug 24, 2020 at 20:30

willeM_ Van Onsem

482k33 gold badges483 silver badges624 bronze badges

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

Collectives™ on Stack Overflow

Properly escaping strings in raw Django SQL query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related