[LINK] Life isn't easy if your last name is 'Null' as it still breaks database entries the world over

[Rick Welykochy]

Antony Barry wrote:
> https://www.pcgamer.com/software/life-isnt-easy-if-your-last-name-is-null-as-it-still-breaks-database-entries-the-world-over/?lctg=1980929&utm_source=digitaltrends&utm_medium=email&utm_content=subscriber_id:1980929&utm_campaign=DTDaily20250225
<pedantic mode="on">

Seems strange since the string "NULL" and the token NULL sans quotes are treated completely differently in SQL.
Observe:

# sqlite3 abc.db

sqlite> create table 'null' (id, thing);
sqlite> insert into 'null' (id,thing) values (1,"NULL"), (2,'null'), (3,NULL);
sqlite> select * from 'null';
1|NULL
2|null
3|
sqlite> select count(*) from 'null' where thing like '%null%';
2
sqlite> select count(*) from 'null' where thing like '%Null%';
2
sqlite> select count(*) from 'null' where thing like null;
0
sqlite> select count(*) from 'null' where thing is null;
1


Lesson learned: "NULL" and 'null' are two matches when looking up the string "NULL"
whereas the token NULL does not match the strings.

The correct SQL to find someone with the last name of Null is

select * from table where lastname = 'Null';

whereas is unquoted, incorrect results are returned:

select * from table where lastname = Null;

Competent web developers know of SQL injection attacks. The last SQL statement
above is a simple example of such an animal and is to be avoided at the risk of
your entire database being compromised. And who would want that?


cheers
rickw

p.s. as shown above, SQL can even handle a table by the name of 'null' without getting confused.



-- 
----------------------------------------------------------------------------------
Welykochy, Rick                                                              MMXXV
----------------------------------------------------------------------------------

Nullius in verba - Take no one's word for it
     -- anon