MySQL madness and Rails (opens in new tab)

(phenoelit.org)

13 pointsnperson13y ago5 comments

5 comments

At the end of the post, the author says: "this issue wont be fixed for now." I asked on Twitter for more details, and this was his reply:

> response was roughly like "We might change this in Rails 4" and "$affected_lib should fix it on its side"[1]

[1] https://twitter.com/joernchen/status/298836052410519552

qqshfox13y ago

> For comparisons of a string column with a number, MySQL cannot use an index on the column to look up the value quickly. If str_col is an indexed string column, the index cannot be used when performing the lookup in the following statement:

> SELECT * FROM tbl_name WHERE str_col=1;

add index please

Gigablah13y ago

Am I missing something?

SELECT * FROM user WHERE "string" = 0;

will indeed match all users, but

SELECT * FROM user WHERE token = 0;

will not. (Presuming the token is stored in a table column)

joernchen13y ago

Rails will do the following:

User.find_by_reset_password_token(0)

  User Load (0.8ms)  SELECT `users`.* FROM `users` WHERE `users`.`reset_password_token` = 0 LIMIT 1

 => #<User id: 1,[...]

hope that illustrates it

Gigablah13y ago

You're right, I was testing with tokens that started with numbers, so 0 didn't match anything.

  SELECT * FROM users where reset_token = 0;

will match the token "abc";

  SELECT * FROM users where reset_token = 1;

will match the token "1abc";

  SELECT * FROM users where reset_token = 123;

will match the token "123aasfas".

j / k navigate · click thread line to collapse

5 comments

telemachos13y ago

At the end of the post, the author says: "this issue wont be fixed for now." I asked on Twitter for more details, and this was his reply:

> response was roughly like "We might change this in Rails 4" and "$affected_lib should fix it on its side"[1]

[1] https://twitter.com/joernchen/status/298836052410519552

qqshfox13y ago

> SELECT * FROM tbl_name WHERE str_col=1;

add index please

Gigablah13y ago

Am I missing something?

SELECT * FROM user WHERE "string" = 0;

will indeed match all users, but

SELECT * FROM user WHERE token = 0;

will not. (Presuming the token is stored in a table column)

joernchen13y ago

Rails will do the following:

User.find_by_reset_password_token(0)

  User Load (0.8ms)  SELECT `users`.* FROM `users` WHERE `users`.`reset_password_token` = 0 LIMIT 1

 => #<User id: 1,[...]

hope that illustrates it

Gigablah13y ago

You're right, I was testing with tokens that started with numbers, so 0 didn't match anything.

  SELECT * FROM users where reset_token = 0;

will match the token "abc";

  SELECT * FROM users where reset_token = 1;

will match the token "1abc";

  SELECT * FROM users where reset_token = 123;

will match the token "123aasfas".

j / k navigate · click thread line to collapse