Mysql return result even id is wrong-CodePudding

I am facing an issue when adding a random string after the id value still query return result.

Ideally, it should return an empty result.

mysql> select * from pricelists where id = '1abcd';
 ---- --------- -------- -------------- -------------- --------------------- ----- ------------------- -------- ------------- -------------------- ---------------- ------------ --------------------- --------------------- --------------- 
| id | name    | markup | routing_type | quality_base | initially_increment | inc | shadow_billing_id | status | reseller_id | pricelist_id_admin | routing_prefix | call_count | creation_date       | last_modified_date  | decimal_value |
 ---- --------- -------- -------------- -------------- --------------------- ----- ------------------- -------- ------------- -------------------- ---------------- ------------ --------------------- --------------------- --------------- 
|  1 | default | 0      |            0 |            1 |                   0 |  60 |                 0 |      0 |           0 |                  0 |                |          0 | 2016-07-25 00:00:00 | 2022-07-06 10:36:31 |             4 |
 ---- --------- -------- -------------- -------------- --------------------- ----- ------------------- -------- ------------- -------------------- ---------------- ------------ --------------------- --------------------- --------------- 
1 row in set, 1 warning (0.00 sec)

CodePudding user response：

when adding a random string after the id value still query return result. Ideally, it should return an empty result.

This means that id column is numeric one, and the comparing have numeric context. The string literal is converted to the numeric value implicitly, and id value is compared with numeric 1 value.

You must set string context for the compare. For example, you may use explicit CAST (where CAST(id AS CHAR) = '1abcd') or any implicit convertion (for example, where CONCAT(id, '') = '1abcd').

CodePudding user response：

Its actually no question, but the reason is that this id column is an int column and the value in your condition is automatically parsed to int. So its "1" in the end.