how can i use bind parameters with an ilike ?

[Jonathan Vanasco]

i need to search something like this:

select name from users where name ilike '%jonathan%';

i know i could do this:

dbSession.query( models.User )\
.filter( models.User.name.ilike( """%%%s%%""" % 'jonathan' )

but the name is coming from the web, so i want treat it with a bind,
like this...

dbSession.query( models.User )\
.filter( models.User.name.ilike( """%:name%""" )
.params( name = 'jonathan' )

anyone know if something this is possible ? ( the above does not
work )

is this a feature request ?

[Audrius Kažukauskas]

How about using

dbSession.query(models.User).filter(models.User.contains(name))

--
Audrius Kažukauskas
http://neutrino.lt/

[Audrius Kažukauskas]

On Tue, 2013-01-29 at 20:19:45 +0200, Audrius Kažukauskas wrote:
> How about using
>
> dbSession.query(models.User).filter(models.User.contains(name))

Scratch that, this generates LIKE, while you need ILIKE.

[Jonathan Vanasco]

> How about using
>
>   dbSession.query(models.User).filter(models.User.contains(name))

That generates a LIKE statement , not an ILIKE. I need to case-
insensitive match. as a stopgap, i might be able to chain a lower()
in there, but something like this should be support.

also i'm not so sure what's going on with the sql this generates for
postgres:
startswith :
- LIKE 'jonathan' || '%'

contains
- LIKE 'jonathan' || '%%'

[Michael Bayer]

ilike is available using column.ilike("some string"). You can turn it into a "contains" by adding in the appropriate "%" signs manually. If you want to do lower() manually, then you can say func.lower(column).contains('some string'), though ilike() does the lower() logic when used on a backend that doesn't have ILIKE built in.

Also contains() produces this:

LIKE '%%' || <bindparam> || '%%'

why concatenation? in case the value you're passing to startswith()/contains() is a SQL expression itself and not a literal value.

[Jonathan Vanasco]

On Jan 29, 2:04 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:

> ilike is available using column.ilike("some string").   You can turn it into a "contains" by adding in the appropriate "%" signs manually.  If you want to do lower() manually, then you can say func.lower(column).contains('some string'), though ilike() does the lower() logic when used on a backend that doesn't have ILIKE built in.

i knew all that, my concern was passing in a bind parameter to ilike.

i want to do something like:

name = 'Jonathan'

models.User.name.contains( name , case_sensitive=False )
models.User.name.startswith( name , case_sensitive=False )
models.User.name.ilike( """%:name%""" ).params( name = name )

I don't want to do:

models.User.name.ilike( """%%%s%%""" % name )

because without an ability to escape 'name' or bind it as a
placeholder, it becomes a sql injection vulnerability

the workaround to get the same result ( manually doing a lowercase )
works :

sqlalchemy.fun.lower( models.User.name ).contains( name.lower() )

but i'd rather generate the proper sql for my backend ( postgres) and
have the ilike.

maybe i should ticket a feature request (or two?):

- allow `contains` and `startswith` to accept a case_sensitive option
( defaults to True, as that is the current behavior )
- parse strings in ilike for bind params, or give them a params
keyword ( col.ilike( pattern , params={} ))

[Michael Bayer]

On Jan 29, 2013, at 7:01 PM, Jonathan Vanasco wrote:

>
>
> On Jan 29, 2:04 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
>
>> ilike is available using column.ilike("some string"). You can turn it into a "contains" by adding in the appropriate "%" signs manually. If you want to do lower() manually, then you can say func.lower(column).contains('some string'), though ilike() does the lower() logic when used on a backend that doesn't have ILIKE built in.
>
> i knew all that, my concern was passing in a bind parameter to ilike.
>
> i want to do something like:
>
> name = 'Jonathan'
>
> models.User.name.contains( name , case_sensitive=False )
> models.User.name.startswith( name , case_sensitive=False )
> models.User.name.ilike( """%:name%""" ).params( name = name )
>
> I don't want to do:
>
> models.User.name.ilike( """%%%s%%""" % name )
>
> because without an ability to escape 'name' or bind it as a
> placeholder, it becomes a sql injection vulnerability

well in the absence of "icontains()" you can for now do just what contains() does:

User.name.ilike('%%' + literal(name) + '%%')

though even if you are saying 'ilike("""%%%s%%""" % name)', that string value is still converted to a bound parameter, so there's no SQL injection here.

>
> - allow `contains` and `startswith` to accept a case_sensitive option
> ( defaults to True, as that is the current behavior )

I'd just do icontains() and istartswith() here, sure.

> - parse strings in ilike for bind params, or give them a params
> keyword ( col.ilike( pattern , params={} ))

parsing strings for bound params is a feature of the text() construct, so technically that's already available, but is not really needed in this case.

[Jonathan Vanasco]

On Jan 29, 7:25 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
> User.name.ilike('%%' + literal(name) + '%%')
>
> though even if you are saying 'ilike("""%%%s%%""" % name)', that string value is still converted to a bound parameter, so there's no SQL injection here.

i didn't know that was converted to a bound parameter. that's what i
had, and was worried sick about before putting something into
production!

thanks for everything!

[Jonathan Vanasco]

i forgot to add that this all comes from my irrational fear of "Little
Bobby Tables" ( http://xkcd.com/327/ )