Hi All
In the following statement, I compare the table’s id field with an integer.
$criteria->compare(‘id’, 123, false);
But in the resulting sql statement, the id field is compared to a string.
id=‘123’
This could impact MySql in several ways:
1. Casting
MySql now has to cast either of the values before comparing them.
I get different opinions on the web about whether MySql:
option-a: casts the string expression value to an integer, or;
option-b: casts all values of the table’s id field to strings.
A comment in this post suggests that the answer is option-b. link
Further comments suggest that option-b constitutes a performance impact AND renders all indexes on the id field useless.
2. User input
If for some reason the user entered 123a instead of 123 - and you did not validate the input to be an integer - then MySql will still find the record with id = 123, which might be a bit worrying in most cases to say the least.
How do we force an integer in the query expression if an integer was used in the compare statement?