Is matchExp case insensitive or not?

[Øyvind Harboe <oy...@zylin.com>]

I need a matchExp which is case insensitve. When I run against MS
Access, then matchExp is indeed case insensitive, whereas when I run
against Derby, it is not.

likeIgnoreCaseExp is *almost* what I'm looking for, but it allows wildcards.

In my app, I do a matchExp(Users.PASSWORD_PROPERTY, password), where
using likeIgnoreCaseExp would work much too well :-) Don't even ask
why the passwords are stored in cleartext in the database.

If I could find a way to safely escape the password, then I could use
likeIgnoreCaseExp, i.e . "%" and other special chars for
likeIgnoreCaseExp should be escaped so they are treated as literal
chars.

Problems with writing an escape fn from scratch:

- how do I know that I've written a complete and correct escape function?
- how do I know that the code I wrote won't break with improvements to
likeExp() and likeIgnoreCaseExp()?


-- 
Øyvind Harboe
http://www.zylin.com

Re: Is matchExp case insensitive or not?

[Øyvind Harboe <oy...@zylin.com>]

> Again, if it were me, and I was knowledgable about the specific
> database being used, I'd try to go through and first replace all
> "match everything" wildcard symbols with "match one" wildcard symbols
> to decrease the amount of returned results.

This only complicates the code when I don't care about passwords or
usernames that contain wildcard symbols.

> I don't understand your comment about comparing against only the first
> result.   You'd have to compare until you found a match or until you
> were sure that nothing matched.

I don't need to worry about multiple results due to wildcards. I need
to worry about duplicate records in the user database(bug-by-bug
compatibility).


-- 
Øyvind Harboe
http://www.zylin.com

Re: Is matchExp case insensitive or not?

[Mike Kienenberger <mk...@gmail.com>]

On 8/2/06, Øyvind Harboe <oy...@zylin.com> wrote:
> > Another possiblity is to use likeIgnoreCaseExp to pull in results, but
> > then go in and manually filter out anything that's not an exact match
> > in your code.   That's probably the safest bet and the most portable.
> >  Of course, you then have to deal with the possiblity that someone's
> > password is "%"
>
> After good nights sleep I arrived at the same conclusion. I pass the
> umodified password to likeIgnoreCaseExp and then I do a String compare
> against the password in the *first* record that matched.
>
> I don't care about the case where escape chars used in passwords would
> cause likeIgnoreCaseExp to not include the record in the query result.
>
> The only thing I assume here is that it is safe to pass a string from
> an attacker to likeIgnoreCaseExp().

I don't see any security issues.    The issue I see is that you may
get back every single record if their password is the wild card
symbol.

Again, if it were me, and I was knowledgable about the specific
database being used, I'd try to go through and first replace all
"match everything" wildcard symbols with "match one" wildcard symbols
to decrease the amount of returned results.

Ie, for Oracle, if the password specified is "this%one", I would
change the string to "this_one" before calling likeIgnoreCase.

I don't understand your comment about comparing against only the first
result.   You'd have to compare until you found a match or until you
were sure that nothing matched.

Do you have only a password or also a username?   A username/password
combination match would be far better as you'd likely get far less
matches.

Not sure about the possiblity of an escape character making a match
impossible, but it sounds like you don't care.

Re: Is matchExp case insensitive or not?

[Øyvind Harboe <oy...@zylin.com>]

On 8/2/06, Tore Halset <ha...@pvv.ntnu.no> wrote:
> On Aug 2, 2006, at 8:09, Øyvind Harboe wrote:
>
> > The only thing I assume here is that it is safe to pass a string from
> > an attacker to likeIgnoreCaseExp().
>
> It should be safe as cayenne uses prepared statement, but some jdbc-
> drivers have had security holes even for prepared statement. Typicaly
> drivers that expand the prepared statement on the client side and
> pass it on as a non-prepared statement.
>
> Storing clear text password in the database is almost never a good
> solution. I mostly store a sha-1 of the password.

This is used code that needs to be bug by bug compatible. Fun isn't it? :-)


-- 
Øyvind Harboe
http://www.zylin.com

Re: Is matchExp case insensitive or not?

[Tore Halset <ha...@pvv.ntnu.no>]

On Aug 2, 2006, at 8:09, Øyvind Harboe wrote:

> The only thing I assume here is that it is safe to pass a string from
> an attacker to likeIgnoreCaseExp().

It should be safe as cayenne uses prepared statement, but some jdbc- 
drivers have had security holes even for prepared statement. Typicaly  
drivers that expand the prepared statement on the client side and  
pass it on as a non-prepared statement.

Storing clear text password in the database is almost never a good  
solution. I mostly store a sha-1 of the password.

  - Tore.

Re: Is matchExp case insensitive or not?

[Øyvind Harboe <oy...@zylin.com>]

> Another possiblity is to use likeIgnoreCaseExp to pull in results, but
> then go in and manually filter out anything that's not an exact match
> in your code.   That's probably the safest bet and the most portable.
>  Of course, you then have to deal with the possiblity that someone's
> password is "%"

After good nights sleep I arrived at the same conclusion. I pass the
umodified password to likeIgnoreCaseExp and then I do a String compare
against the password in the *first* record that matched.

I don't care about the case where escape chars used in passwords would
cause likeIgnoreCaseExp to not include the record in the query result.

The only thing I assume here is that it is safe to pass a string from
an attacker to likeIgnoreCaseExp().


-- 
Øyvind Harboe
http://www.zylin.com

Re: Is matchExp case insensitive or not?

[Joshua Pyle <jo...@gmail.com>]

I Oracle you cam also do

UPPER(parameter) = UPPER(value)

then there is no % issue!

Cayene does not have a built in way to do this.  An
equalsIgnoreCaseExp would be nice but must have database independant
issues.



-- 
Joshua T. Pyle
Go has always existed.

On 8/1/06, Mike Kienenberger <mk...@gmail.com> wrote:
> matchExp case sensitivity behavior is database dependent, as you've found.
>
> I don't think there's any database-independent way to do
> case-sensitivity-specific queries, which is why likeIgnoreCaseExp() is
> used.
>
> You're probably better off writing custom sql to handle this if you
> can't be sure you've dealt with escaping.
>
> For example, Oracle handles it by using "UPPER(parameter) like
> UPPER(value)" (if I remember offhand).
>
> Another possiblity is to use likeIgnoreCaseExp to pull in results, but
> then go in and manually filter out anything that's not an exact match
> in your code.   That's probably the safest bet and the most portable.
>  Of course, you then have to deal with the possiblity that someone's
> password is "%"
>
> On 8/1/06, Øyvind Harboe <oy...@zylin.com> wrote:
> > I need a matchExp which is case insensitve. When I run against MS
> > Access, then matchExp is indeed case insensitive, whereas when I run
> > against Derby, it is not.
> >
> > likeIgnoreCaseExp is *almost* what I'm looking for, but it allows wildcards.
> >
> > In my app, I do a matchExp(Users.PASSWORD_PROPERTY, password), where
> > using likeIgnoreCaseExp would work much too well :-) Don't even ask
> > why the passwords are stored in cleartext in the database.
> >
> > If I could find a way to safely escape the password, then I could use
> > likeIgnoreCaseExp, i.e . "%" and other special chars for
> > likeIgnoreCaseExp should be escaped so they are treated as literal
> > chars.
> >
> > Problems with writing an escape fn from scratch:
> >
> > - how do I know that I've written a complete and correct escape function?
> > - how do I know that the code I wrote won't break with improvements to
> > likeExp() and likeIgnoreCaseExp()?
> >
> >
> > --
> > Øyvind Harboe
> > http://www.zylin.com
> >
>

Re: Is matchExp case insensitive or not?

[Mike Kienenberger <mk...@gmail.com>]

matchExp case sensitivity behavior is database dependent, as you've found.

I don't think there's any database-independent way to do
case-sensitivity-specific queries, which is why likeIgnoreCaseExp() is
used.

You're probably better off writing custom sql to handle this if you
can't be sure you've dealt with escaping.

For example, Oracle handles it by using "UPPER(parameter) like
UPPER(value)" (if I remember offhand).

Another possiblity is to use likeIgnoreCaseExp to pull in results, but
then go in and manually filter out anything that's not an exact match
in your code.   That's probably the safest bet and the most portable.
 Of course, you then have to deal with the possiblity that someone's
password is "%"

On 8/1/06, Øyvind Harboe <oy...@zylin.com> wrote:
> I need a matchExp which is case insensitve. When I run against MS
> Access, then matchExp is indeed case insensitive, whereas when I run
> against Derby, it is not.
>
> likeIgnoreCaseExp is *almost* what I'm looking for, but it allows wildcards.
>
> In my app, I do a matchExp(Users.PASSWORD_PROPERTY, password), where
> using likeIgnoreCaseExp would work much too well :-) Don't even ask
> why the passwords are stored in cleartext in the database.
>
> If I could find a way to safely escape the password, then I could use
> likeIgnoreCaseExp, i.e . "%" and other special chars for
> likeIgnoreCaseExp should be escaped so they are treated as literal
> chars.
>
> Problems with writing an escape fn from scratch:
>
> - how do I know that I've written a complete and correct escape function?
> - how do I know that the code I wrote won't break with improvements to
> likeExp() and likeIgnoreCaseExp()?
>
>
> --
> Øyvind Harboe
> http://www.zylin.com
>