Re: Escaping text for raw SQL?

On 10/12/2010 4:34 PM, Nick Arnett wrote:
>
>
> On Tue, Oct 12, 2010 at 9:00 AM, Javier Guerra Giraldez
> <javier@guerrag.com <mailto:javier@guerrag.com>> wrote:
>
> On Tue, Oct 12, 2010 at 10:46 AM, Nick Arnett <nick.arnett@gmail.com
> <mailto:nick.arnett@gmail.com>> wrote:
> > Anybody know a good way to do this?
>
> Words.objects.filter(foo__in=mylist)
>
>
> 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.
>
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/

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to django-users+unsubscribe@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-users?hl=en.