Escaping text for raw SQL?

[Nick Arnett]

I can't figure out if there is a way to escape text for raw SQL queries.  I can't use substitution (I think) because I'm building a query like this:

SELECT foo, bar FROM proj_words WHERE foo IN ("bat", "bug", "snip", "snap")

The list of terms for the IN operator can be quite long... I suppose I could dynamically generate this:

SELECT foo, bar FROM proj_words WHERE foo IN (%s, %s, %s, %s)

... but I was hoping for the much simpler list comprehension that MySQLdb would do:

my_list = [connection.escape(x) for x in my_list]

However, I don't see an escape function exposed in Django.

Anybody know a good way to do this?

TIA,

Nick

[Javier Guerra Giraldez]

On Tue, Oct 12, 2010 at 10:46 AM, Nick Arnett <nick....@gmail.com> wrote:
> Anybody know a good way to do this?

Words.objects.filter(foo__in=mylist)

--
Javier

[Nick Arnett]

Didn't even occur to me to not use raw SQL for this, but I could... trouble is, I wanted this to be reusable in a way that will be clumsy in the ORM.  But maybe I'll go that route.  I'm using raw SQL for a lot of this because the ORM is way too slow for what I'm doing.

Nick

[Steve Holden]

In which case be *extremely* careful not to introduce SQL injection
vulnerabilities into your code. The safest way is to use parameterized
queries. Some back-ends will let you write (untested):

cursor.execute("SELECT foo, bar FROM proj_words WHERE foo IN %s",
my_list)

but I can't off-hand remember which ones. If the back-end doesn't allow
that then you have little option but to generate your own SQL. The
required escape function is extremely simple:

def sqlesc(s):
return replace("'", "''")

and the SQL generation would read something like (again, untested):

sql = "SELECT foo, bar FROM proj_words WHERE foo IN (%s)" % \
", ".join("'%s'" % sqlesc(s) for s in my_list)

Personally I would do everything I could to avoid this construct, however.

regards
Steve
--
DjangoCon US 2010 September 7-9 http://djangocon.us/

[Nick Arnett]

On Tue, Oct 12, 2010 at 9:53 AM, Steve Holden <hold...@gmail.com> wrote:

...

but I can't off-hand remember which ones. If the back-end doesn't allow
that then you have little option but to generate your own SQL. The
required escape function is extremely simple:

 def sqlesc(s):
     return replace("'", "''")

Am I going brain dead, or isn't there more than just quotation marks that need to be replaced?

This code is a back-end processing script, so there is no danger of SQL injection by anybody (except me).

Nick