MySQL свободное сравнение, WHERE в поле varchar с целым значением дает неожиданный результат

Недавно я обнаружил интересную ошибку в программе, которая выбирает данные для конкретного клиента, используя свой закрытый ключ. Рассмотрим следующее:

SELECT `id` FROM (`customers`) WHERE `authenticationKey` = '#09209!ko2A-' LIMIT 1 

Ключ предоставляется во время запроса и надлежащим образом дезинфицируется перед отправкой запроса. Однако, если вы не предоставили ключ (который должен быть пойман раньше, проигнорируйте это), вы получите запрос, похожий на следующий:

 SELECT `id` FROM (`customers`) WHERE `authenticationKey` = 0 LIMIT 1 

Которая вернет строку из таблицы customers – несмотря на то, что она имеет правильный, строковый ключ, сохраненный, например, в первом примере.

Поле authenticationKey является типом VARCHAR(1024) .

Я предполагаю, что это имеет какое-то отношение к свободному сравнению. Что вызывает эту проблему, и как ее можно избежать?

MySQL будет пытаться и принуждать данные к сопоставимому типу. В этом случае он попытается преобразовать строки в числа. Любые строки, которые не могут иметь значение по умолчанию 0.

Делать

 select 0 = 'banana' 

чтобы увидеть это в действии.

Если бы ваш запрос был сравним с '0' а не с 0 он исправит его.

Пример SQLFiddle

MySQL неявно преобразует ведущие символы authenticationKey в int до тех пор, пока не найдет символ, который не является допустимым числом. Я предполагаю, что все строки, начинающиеся с нечислового символа, обрабатываются как 0.

Например, это дает «b», так как принудительное значение int равно 1:

 select (case when '1abc' = 0 then 'a' else 'b' end); 

Но это дает «a», поскольку ведущий символ не является допустимым числом, поэтому значение coerced int равно 0:

 select (case when '#1abc' = 0 then 'a' else 'b' end); 

Приложение следует избегать. Почему вы запрашиваете с 0 когда ключ не указан?