経緯

業務中、SELECT文で意図しないレコードがSELECTされた。

12.2 式評価での型変換

今更ではありますが私としてはMySQLでも暗黙的な型変換あったんだ～ってなりました。
型に沿った形で検索しないと暗黙的な型変換により意図しないレコードが検索される恐れがあるため、
プログラム側で適切なバリデーションを行い、MySQLへ引き渡して検索を行う必要がありますね。
…っていうのはわかってるんだけどもともとの作りでバリデーション厳密にしていないところとかってあるじゃないですか。
なのでそういうときにどういうレコードが検出される可能性があるのか考慮まとめておきました。

検証準備

検証用のテーブルを記載します。

定義

mysql> show create table users;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                          |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users | CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

レコード

mysql> SELECT * FROM users;
+----+--------+
| id | name   |
+----+--------+
|  0 | ゼロ   |
|  1 | 太郎   |
|  2 | 次郎   |
|  3 | 三郎   |
+----+--------+
4 rows in set (0.00 sec)

※AUTO_INCREMENTなので通常id=1からですが検証のためid=0も挿入しております。

検証

WHERE id = 2

mysql> SELECT * FROM users WHERE id = 2;
+----+--------+
| id | name   |
+----+--------+
|  2 | 次郎   |
+----+--------+
1 row in set (0.00 sec)

特に問題なし。

WHERE id = '2'

mysql> SELECT * FROM users WHERE id = '2';
+----+--------+
| id | name   |
+----+--------+
|  2 | 次郎   |
+----+--------+
1 row in set (0.00 sec)

特に問題なし。

WHERE id = a

mysql> SELECT * FROM users WHERE id = a;
ERROR 1054 (42S22): Unknown column 'a' in 'where clause'

mysql> SHOW WARNINGS;
+-------+------+--------------------------------------+
| Level | Code | Message                              |
+-------+------+--------------------------------------+
| Error | 1054 | Unknown column 'a' in 'where clause' |
+-------+------+--------------------------------------+
1 row in set (0.00 sec)

エラーになってくれるので特に問題なし。

WHERE id = 'a'

mysql> SELECT * FROM users WHERE id = 'a';
+----+--------+
| id | name   |
+----+--------+
|  0 | ゼロ   |
+----+--------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+---------------------------------------+
| Level   | Code | Message                               |
+---------+------+---------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'a' |
+---------+------+---------------------------------------+
1 row in set (0.00 sec)

ゼロさんが引っかかってしまいます。
誤ったDOUBLE値って扱いで切り捨てられ、0として検索したようですね。

WHERE id = a2

mysql> SELECT * FROM users WHERE id = a2;
ERROR 1054 (42S22): Unknown column 'a2' in 'where clause'

mysql> SHOW WARNINGS;
+-------+------+---------------------------------------+
| Level | Code | Message                               |
+-------+------+---------------------------------------+
| Error | 1054 | Unknown column 'a2' in 'where clause' |
+-------+------+---------------------------------------+
1 row in set (0.00 sec)

エラーになってくれるので特に問題なし。

WHERE id = 'a2'

mysql> SELECT * FROM users WHERE id = 'a2';
+----+--------+
| id | name   |
+----+--------+
|  0 | ゼロ   |
+----+--------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'a2' |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

ゼロさんが引っかかってしまいます。
誤ったDOUBLE値って扱いで切り捨てられ、0として検索したようですね。

WHERE id = 2a

mysql> SELECT * FROM users WHERE id = 2a;
ERROR 1054 (42S22): Unknown column '2a' in 'where clause'

mysql> SHOW WARNINGS;
+-------+------+---------------------------------------+
| Level | Code | Message                               |
+-------+------+---------------------------------------+
| Error | 1054 | Unknown column '2a' in 'where clause' |
+-------+------+---------------------------------------+
1 row in set (0.00 sec)

エラーになってくれるので特に問題なし。

WHERE id = '2a'

mysql> SELECT * FROM users WHERE id = '2a';
+----+--------+
| id | name   |
+----+--------+
|  2 | 次郎   |
+----+--------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '2a' |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

次郎さんが引っかかってしまいます。
誤ったDOUBLE値って扱いで切り捨てられ、2として検索したようですね。

WHERE id = 2.1

mysql> SELECT * FROM users WHERE id = 2.1;
Empty set (0.00 sec)

特に問題なし。

WHERE id = '2.1'

mysql> SELECT * FROM users WHERE id = '2.1';
Empty set (0.00 sec)

特に問題なし。

WHERE id = '2a1'

mysql> SELECT * FROM users WHERE id = '2a1';
+----+--------+
| id | name   |
+----+--------+
|  2 | 次郎   |
+----+--------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '2a1' |
+---------+------+-----------------------------------------+
1 row in set (0.00 sec)

次郎さんが引っかかってしまいます。
誤ったDOUBLE値って扱いで切り捨てられ、2として検索したようですね。