SQLAlchemy case insensitive IN based search query?

Question

How do I do a case insensitive IN search in the SQLAclhemy ORM in a way that is secure? Both myself and others on my project have looked for this, but we cant seem to find anything that fits our needs. In raw SQL I could do: ``` SELECT * FROM TABLENAME WHERE UPPER(FIELDNAME) IN (UPPER('foo'), UPPER('bar')); ``` ..if FOO and BAR were not user input in unknown case. As it is, I am worried about the following: - Security: I don't want a visit from Bobby Tables (http://xkcd.com/327/) in the form of an SQL INjection Attack.and I cant find the documentation that tells me how to escape strings in SQLAlchemy or I would feel safer joining strings (But still feel dirty doing it). - Speed is handled largely by indexing but obviously, doing case corrections in RAM before issuing the query would be faster than telling the DB to do it, so I would not do the UPPER in the query unless I really had to. The above was however the best way to show what I want to do. But sti;;, it shouldn't do anything cr…

Accepted Answer

This should compile exactly...

```
query( models.Object )\
.filter( 
     sqlalchemy.func.upper( models.Object.fieldname )\
     .in_( (sqlalchemy.func.upper(foo) , sqlalchemy.func.upper(bar), ) )
)\
.all()
```

you could also just pass in uppercase text. personally, i would do `in_( foo.uppercase() , bar.uppercase() )`

SqlAlchemy works with the DBAPI to pass bind parameters into your backend datastore. Translation -- values are automatically escaped.

if you want to do a list of strings , something like this should work

```
.in_( [ i.upper() for i in inputs ] )
.in_( [ sqlalchemy.func.upper(i) for i in inputs ] )
```

Just want to add that if you want to optimize these selects for speed, and are on Postgres or Oracle, you can create a 'function index'

```
CREATE INDEX table_fieldname_lower_idx ON table(lower(fieldname))
```

the query planner (in the database) will know to use that `lower(fieldname)` index when searching against a `lower(fieldname)` query.

SQLAlchemy case insensitive IN based search query?

Solution

Problem