Null variables in functions handled incorrectly in WHERE clause (?)
am 13.09.2003 04:30:33 von jds------=_NextPart_000_0022_01C3797D.7A8930B0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
This behavior does not seem correct and I found no references to it in =
help or in the bug database...
When I use an undefined variable as an argument to a function (e.g. =
PASSWORD() or UPPER()) in a WHERE clause, rows are returned when it =
appears they should not be. If NULL is passed directly, no rows are =
returned as expected. When a null variable is used, however, rows are =
returned erroneously:
CREATE TABLE test.temp_table (
name VARCHAR(50) NOT NULL PRIMARY KEY,
pw VARCHAR(16) NOT NULL);
INSERT INTO test.temp_table (name, pw)
VALUES ('tom', PASSWORD('my_pw'));
SET @pass=3D'my_pw';
SET @wrong=3D'incorrect';
# returns 1 row (OK)
SELECT name FROM test.temp_table=20
WHERE name=3D'tom' AND pw=3DPASSWORD(@pass);
# returns 0 rows (OK)
SELECT name FROM test.temp_table=20
WHERE name=3D'tom' AND pw=3DPASSWORD(@wrong);
# returns 1 row (WRONG)
SELECT name FROM test.temp_table=20
WHERE name=3D'tom' AND pw=3DPASSWORD(@undefined);
# returns 1 row (WRONG)
SELECT name FROM test.temp_table=20
WHERE pw=3DPASSWORD(@undefined) AND name=3D'tom';
# returns 1 row (WRONG)
SELECT name FROM test.temp_table=20
WHERE pw=3DPASSWORD(@undefined);
# returns 1 row (WRONG)
SELECT name FROM test.temp_table=20
WHERE name=3D'tom' AND pw=3DUPPER(@undefined);
# returns 0 rows (OK)
SELECT name FROM test.temp_table=20
WHERE name=3D'tom' AND pw=3DPASSWORD(Null);
Am I overlooking something, or is this a bug? The uninitialized =
variable should evaluate to NULL, the function should then evaluate to =
NULL, and the WHERE clause should match no records (the same as if NULL =
is used directly). This could easily happen if a variable is accidently =
uninitialized before calling a script that uses it. In this case, a =
user could be validated when clearly none should be.
This happens in version 4.0.13 under both Win XP (mysqld-nt) and Win =
98SE (mysqld), as well as in 4.0.11-gamma under Linux (i586).
Thanks.
Jerry
------=_NextPart_000_0022_01C3797D.7A8930B0--