問(wèn)題描述
我最近在一個(gè)程序中發(fā)現(xiàn)了一個(gè)有趣的錯(cuò)誤，該程序使用他們的私鑰為特定客戶選擇數(shù)據(jù).考慮以下幾點(diǎn):
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
密鑰在請(qǐng)求時(shí)提供，并在查詢之前進(jìn)行了適當(dāng)?shù)那謇?但是，如果沒(méi)有提供密鑰(應(yīng)該在之前被捕獲；忽略它)，將產(chǎn)生類似于以下內(nèi)容的查詢:
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
它會(huì)從 customers-table 返回一行 - 盡管它存儲(chǔ)了一個(gè)正確的字符串鍵，例如在第一個(gè)示例中.
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).
我的猜測(cè)是這與松散比較有關(guān).導(dǎo)致此問(wèn)題的原因是什么，如何正確避免?
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 會(huì)嘗試將數(shù)據(jù)強(qiáng)制轉(zhuǎn)換為可比較的類型.在這種情況下，它會(huì)嘗試將字符串轉(zhuǎn)換為數(shù)字.任何無(wú)法理解的字符串默認(rèn)為 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'
看到這一點(diǎn).
將您的查詢與 '0' 而不是 0 進(jìn)行比較可以解決問(wèn)題.
Making your query compare to '0' instead of 0 would fix it.
示例 SQLFiddle
這篇關(guān)于MySQL 松散比較，在具有整數(shù)值的 varchar 字段上的 WHERE 產(chǎn)生意外結(jié)果的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！