Invalid condition in a MySQL UPDATE's WHERE clause - updated EVERYTHING - instead of NOTHING

user_12229800 0
Viewed 133

What I meant to write:

UPDATE items_ordered_list ords
    SET ords.last_ordered='2019-10-16 10:49:23'
    WHERE ords.cust_id='11656165';

What I actually wrote:

UPDATE items_ordered_list ords
    SET ords.last_ordered='2019-10-16 10:49:23'
    WHERE ords.cust_id-'11656165';

Instead of giving me some sort of error that the WHERE clause wasn't a logical expression - it just happily went along updating everything. I have a backup - and a way to recover the previous value - but - the "-" is adjacent to the "=" on the keyboard - took me a while to see this.

I tried it with a SELECT / LIMIT statement - and sure enough - it brought back as many records as I set in the LIMIT...

Is this a flaw...or "by design"? Is there some way that "ords.cust_id-'11656165'" evaluates to "TRUE"???

Please let me know - usually, for big / dangerous updates, I'll do a SELECT or EXPLAIN first - just to be sure that the SCOPE returned is reasonable - this little bit of code, I just change the cust_id - but - I accidentally deleted the "=" - and when I re-typed it - I (obviously) made a typo...any help / advice will be appreciated!!!

Thanks!

mariadb where-clause mysql
1 Answers

Assuming that all your cust_id customer ID values be either less or greater than the integer 11656165, then by casting rules the following expression would be a positive/negative number, which also evaluates to true, e.g.

ords.cust_id - '11656165'

For example:

SELECT 11650000 - '11656165'; -- returns -6165

and:

SELECT
    IF(11650000 - '11656165', 'TRUE', 'FALSE') -- returns TRUE

The lesson here is to check your code before running it. In this case, a typo still resulted in a valid update query, which may have damaged your data.

user_18632290
Related