>>>>> "Richard" == Richard Antecki
> <rantecki@stripped> writes:
Richard> I have a problem matching strings using the BINARY keyword.
Richard> I have fields in a table 'auth' defined as
Richard> username CHAR(25) BINARY
Richard> passwd CHAR(25) BINARY
Richard> So obviously all string comparisons are case sensitive. However, I
Richard> noticed that trailing spaces in a string cause problems:
Richard> select 'user'='user '; will return 1
Richard> Leading spaces do not cause the same problem:
Richard> select 'user'=' user'; will return 0
Richard> If, for example, a valid username is 'user', then
Richard> select passwd from auth where username='user'
Richard> will work. However, if some idiot decides to insert trailing spaces after
Richard> his username, the following will also match to 'user':
Richard> select passwd from auth where username='user '
Richard> This doesn't bother me by itself, but this behaviour doesn't seem to be
Richard> consistant across INSERT, SELECT and UPDATE.
Richard> Assuming table 'blah' also contains a username field of type char(25)
Richard> BINARY, If I run:
Richard> insert into blah values ('user ', ... )
Richard> then
Richard> select ... from blah where username='user '
Richard> this does not return a match!
Richard> This is causing me problems, as then it will try to INSERT a new record
Richard> instead of UPDATE and I get a DUPLICATE KEY error!
Richard> This will work if I use strcmp, but it will be much slower. Does anyone
Richard> know why it does this?
This problem is described in the MYSQL manual.
The problem is that all CHAR columns automaticly remove all end space
characters.
The simple fix (if you are using BINARY columns) is to use
select ... from blah where username=trim('user ').
Regards,
Monty
