1

I have the following query and result:

mysql> SELECT item_id FROM phppos_items WHERE item_id = '5CG4500RRL';
+---------+
| item_id |
+---------+
|       5 |
+---------+

item_id is an int(11) primary key

How do I prevent this from matching? It looks like it is somehow becoming 5 when matching.

I still want to run this code so I don't have to change a lot of logic so I would prefer to keep it in mysql to do a strict comparison if possible.

Chris Muench
  • 17,444
  • 70
  • 209
  • 362
  • 2
    `WHERE CAST(item_id AS CHAR) = '5CG4500RRL'` will work but will not use indexes. Any particular reason you need to compare different types in this particular way? – Joachim Isaksson Jul 08 '20 at 19:20
  • It is user-entered data and sometimes they enter odd data. I could filter it out before the sql query but I want to change as little as possible – Chris Muench Jul 08 '20 at 19:33
  • If it's "user entered" data then you should force matching using strings/VARCHAR, as @JoachimIsaksson says. – The Impaler Jul 08 '20 at 20:04
  • I would recommend against string matching for this. It will make all legitimate queries much slower, and all broken queries... return nothing. The best solution would be to check for data integrity before putting the data in -- ideally with a prepared statement, because it sounds like you're open to SQL injection right now. Failing that, you could do something like `item_id = '$STR' AND CAST(CAST( 'STR' AS INTEGER) AS CHAR) = '$STR'`. The second clause should be executed just once by the SQL engine, and if the string doesn't survive the conversion to integer and back, it will return none. – zebediah49 Jul 08 '20 at 20:26

1 Answers1

0

I can be done by several methods. For example:

SELECT item_id 
FROM phppos_items 
WHERE '5CG4500RRL' REGEXP '^[0-9]+$' AND item_id = '5CG4500RRL';

Here we check is input value digits only and it equal to item_id. Here you can find more options to check input value.

Slava Rozhnev
  • 9,510
  • 6
  • 23
  • 39