MySQL 松散比较，在具有整数值的 varchar 字段上的 WHERE 产生意外结果
我最近在一个程序中发现了一个有趣的错误，该程序使用他们的私钥为特定客户选择数据.考虑以下几点:
I recently discovered an interesting bug in a program, which selects data for a specific customer using their private key. Consider the following:
SELECT `id` FROM (`customers`) WHERE `authenticationKey` = '#09209!ko2A-' LIMIT 1
密钥在请求时提供，并在查询之前进行了适当的清理.但是，如果没有提供密钥(应该在之前被捕获；忽略它)，将产生类似于以下内容的查询:
The key is provided at request-time, and properly sanitized before put to query. However, failing to providing a key (which should be caught before; ignore that), would yield a query similar to the following:
SELECT `id` FROM (`customers`) WHERE `authenticationKey` = 0 LIMIT 1
它会从 customers-table 返回一行 - 尽管它存储了一个正确的字符串键，例如在第一个示例中.
Which would return a row from the customers-table - despite it having a proper, string, key stored, such as in the first example.
authenticationKey 字段的类型为 VARCHAR(1024).
我的猜测是这与松散比较有关.导致此问题的原因是什么，如何正确避免?
My guess is that this has something to do with loose comparasion. What is causing this problem, and how can it properly be avoided?
推荐答案
MySQL 会尝试将数据强制转换为可比较的类型.在这种情况下，它会尝试将字符串转换为数字.任何无法理解的字符串默认为 0.
MySQL will try and coerce data to a comparable type. I this case it will try and convert strings to numbers. Any strings that it can't make sense of default to 0.
做
select 0 = 'banana'
看到这一点.
将您的查询与 '0' 而不是 0 进行比较可以解决问题.
Making your query compare to '0' instead of 0 would fix it.
示例 SQLFiddle
相关文章