in user_load
line 60:
$query[]= "LOWER($key) = LOWER('%s')";
should be:
$query[]= "$key = LOWER('%s')";

This is because the LOWER() function (in mysql 4.0) does not work on column names.
e.g.
select * from users;
returns a bunch of rows, whereas
select * from LOWER(USERS);
returns
--------------
select * from LOWER(USERS)
--------------

ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'LOWER(USERS)' at line 1

Susie

Comments

killes@www.drop.org’s picture

killes@www.drop.org commented

this works for me on mysql 4.1

select * from users where LOWER(NAME) = LOWER('admin');

Steven’s picture

Steven commented
Status: Needs review » Closed (won't fix)

Er, I'm pretty sure the query is right. Your 'example' tries to apply LOWER() on a table. The goal in our user.module query is not to lowercase the column name, but the value that is inside the column.

susie’s picture

susie commented

sorry, bad example.
I added in a column called 'personid' into user, so if I desc users, I get:

+-----------+------------------+------+-----+---------+-------+
| Field     | Type             | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| uid       | int(10) unsigned |      | PRI | 0       |       |
| name      | varchar(60)      |      | UNI |         |       |
| pass      | varchar(32)      |      |     |         |       |
| mail      | varchar(64)      | YES  |     |         |       |
| mode      | tinyint(1)       |      |     | 0       |       |
| sort      | tinyint(1)       | YES  |     | 0       |       |
| threshold | tinyint(1)       | YES  |     | 0       |       |
| theme     | varchar(255)     |      |     |         |       |
| signature | varchar(255)     |      |     |         |       |
| created   | int(11)          |      |     | 0       |       |
| access    | int(11)          |      | MUL | 0       |       |
| login     | int(11)          |      |     | 0       |       |
| status    | tinyint(4)       |      |     | 0       |       |
| timezone  | varchar(8)       | YES  |     | NULL    |       |
| language  | varchar(12)      |      |     |         |       |
| picture   | varchar(255)     |      |     |         |       |
| init      | varchar(64)      | YES  |     |         |       |
| data      | longtext         | YES  |     | NULL    |       |
| personid  | int(10)          | YES  | MUL | NULL    |       |
+-----------+------------------+------+-----+---------+-------+

if I type:
select * from users where PERSONID = LOWER('<my staff number>'); or
select * from users where personid = LOWER('<my staff number>');
I get results.

if I type:
select * from users where LOWER(PERSONID) = LOWER('<my staff number>'); or
select * from users where LOWER(personid) = LOWER('<my staff number>');
I get zero results.

Susie

susie’s picture

susie commented
Status: Closed (won't fix) » Active

see last post (sorry forgot to change the status last time)

killes@www.drop.org’s picture

killes@www.drop.org commented
Status: Active » Closed (won't fix)

the problem is due to your modifications to Drupal, setting back to won't fix.

The proper way to do what you want it to add an extra table.

susie’s picture

susie commented
Status: Closed (won't fix) » Active

Hi again,
sorry if I'm starting to annoy you by now, but its not a problem with my changing of 'user', its a problem with the code and mysql. Mysql is case insensitive in general. Most of the time LOWER is not needed.
e.g.

select "ABC" = "abc";
+---------------+
| "ABC" = "abc" |
+---------------+
|             1 |
+---------------+

so, when you are testing on things like select * from users where lower(name) = lower('<name>');, it will give back positive results.

The problem is when you are trying to compare numbers. LOWER changes the comparison of an int into strings, so if you are trying to compare two ints, one with preceding zeros and one without, it will break. e.g.

select 1 = lower('01');
+-----------------+
| 1 = lower('01') |
+-----------------+
|               1 |
+-----------------+
select lower(1) = lower('01');
+------------------------+
| lower(1) = lower('01') |
+------------------------+
|                      0 |
+------------------------+

or is it your intention to not have 1 == 01?

Susie

jvandyk’s picture

jvandyk commented
Status: Active » Closed (won't fix)

Drupal's user table does not contain columns with leading zeros.