問題描述
我最近在一個程序中發現了一個有趣的錯誤，該程序使用他們的私鑰為特定客戶選擇數據.考慮以下幾點:
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
這篇關于MySQL 松散比較，在具有整數值的 varchar 字段上的 WHERE 產生意外結果的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！