Correct way to pass parameters containing string into Django Raw sql

Question

I have bunch of complicated sql queries that I need to execute on Django and Raw SQL seems to be the only option. My parameters are strings and can be empty. The reason for being empty is that I have conditional statements above, and depending on that the correct sql needs to be executed. But when the ra sql is run, django actually puts quotes (which I use to denote strings) in the sql and so it throws an error.

I have simplified the query to show the problem I am facing. The following query when executed throws an error.

select_cond = ''
where_cond = 'id = 109'

qraw = Book.objects.raw(
        "\
        SELECT  id %s\
        FROM book\
        WHERE %s\
        ",
        [select_cond, where_cond]
)

The error is due to it being translated as follows. The quotes actually get in the sql and so it won't execute. Any idea on how I can fix this?

 SELECT  id ''
 FROM book
 WHERE 'id = 109'
 ORDER BY id DESC;

Brad · Accepted Answer · 2016-06-21 21:54:56Z

4

Django provides a 'params' argument for raw that avoids the potential for SQL injection attacks. As @alecxe's example shows, triple quotes in python are a great way to define a multi-line variable and avoid any backslash line escapes. Here's a proposed safe solution the "Django way":

select_cond = condition
where_cond = condition

qraw = Book.objects.raw("""
    SELECT
        id, %(select_cond)s
    FROM
        book
    WHERE
        %(where_cond)s
    """, params={'select_cond': select_cond, 'where_cond': where_cond})

Just replace condition placeholders with appropriate values.

answered Jun 21, 2016 at 21:54

Brad

491 silver badge4 bronze badges

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

Comments

alecxe · Accepted Answer · 2015-02-27 22:39:32Z

3

This is the default behavior of the underlying database driver used by Django.

Note: I'm not sure the motivation behind trying to construct the query this way.

Only if you trust the source of the where conditions you are using in the queries, put them into the query via string formatting:

qraw = Book.objects.raw("""
    SELECT 
        id, {select}
    FROM 
        book
    WHERE 
        {where}
    """.format(select=select_cond, where=where_cond)
)

Again, this is not safe to do since you are actually making it vulnerable to SQL injections.

To make it safer you can pre-parse the where conditions you'll be using in the query and call MySQLdb.escape_string() manually on the field values, example:

key, value = where_cond.split(' = ')
value = MySQLdb.escape_string(value)
where_cond = ' = '.join(key, value)

edited Feb 27, 2015 at 22:39

answered Feb 27, 2015 at 22:21

alecxe

476k127 gold badges1.1k silver badges1.2k bronze badges

1 Comment

alecxe Over a year ago

I guess noting about it being not safe 3 times is not enough to avoid downvotes.

Collectives™ on Stack Overflow

Correct way to pass parameters containing string into Django Raw sql

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related