CDbCriteria Compare Integer/String issue

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?