SQL formatting/escaping injection with raw(...)

I am using a query in django. While I normally do this directly in the sql connector, sometimes I use the raw(...) method, for example as follows:

items_from_master = list(ItemMaster.objects.raw(
    """SELECT * FROM main_itemmaster WHERE 
       (content_type_id != 'TV Season') AND (MATCH(name) against('%s')) LIMIT 20
       UNION 
       SELECT * FROM main_itemmaster WHERE name='%s'
       ORDER BY imdb_votes DESC LIMIT 30""" % (q,q)))

Is there a way to escape the string here? For example, something like cursor.execute(sql, params)?

1 answer

  • answered 2018-11-08 02:27 kungphu

    raw(...) supports a params argument for exactly this case.

    Note the removal of the quotes from your original query:

    items_from_master = list(
        ItemMaster.objects.raw(
            """
            SELECT * FROM main_itemmaster WHERE 
            (content_type_id != 'TV Season') AND (MATCH(name) against(%s)) LIMIT 20
            UNION 
            SELECT * FROM main_itemmaster WHERE name=%s
            ORDER BY imdb_votes DESC LIMIT 30
            """,
            [q, q],
        ),
    )
    

    This can also take a dictionary, which is a bit cleaner in cases like yours where one value is used multiple times:

    items_from_master = list(
        ItemMaster.objects.raw(
            """
            SELECT * FROM main_itemmaster WHERE 
            (content_type_id != 'TV Season') AND (MATCH(name) against(%(q)s)) LIMIT 20
            UNION 
            SELECT * FROM main_itemmaster WHERE name=%(q)s
            ORDER BY imdb_votes DESC LIMIT 30
            """,
            {"q": q},
        ),
    )