Search code examples
mysqlsqlstringvarchar

MySQL loose comparison, WHERE on varchar field with integer value yields unexpected result

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

Which would return a row from the customers-table - despite it having a proper, string, key stored, such as in the first example.

The authenticationKey-field is of the type 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?

Solution

  • 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.

    Do

    select 0 = 'banana'

    to see this in action.

    Making your query compare to '0' instead of 0 would fix it.

    Example SQLFiddle