MySQL's INSERT IGNORE and NOT NULL columns

Last week I was working on an application that has an idempotent API, meaning the same request can come in multiple times without generating errors or side effects: the request can be safely replayed, as it won’t affect the state of the server.

Since I was using MySQL as a storage engine behind this API, INSERT IGNORE was my first thought.

What a tragic mistake.

Hidden problems with IGNORE

Let’s create a dummy table with a couple fields:

1
2
3
4
5
6
7
CREATE TABLE `item` (
  `id` INT(11) COLLATE utf8_unicode_ci AUTO_INCREMENT NOT NULL,
  `title` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `reference` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY (`reference`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Let’s say we want to insert in this table a list of items we have to sell — each item will have a unique human-readable title and a reference in our system.

Let’s add some records into our system:

1
2
3
4
5
mysql> INSERT INTO item (title, reference) VALUES("iPad - the best tablet in the world", "IPAD-64GB");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO item (title, reference) VALUES("iPad - the best tablet in the world", "IPAD-64GB");
ERROR 1062 (23000): Duplicate entry 'IPAD-64GB' for key 'reference'

as expected, the second insert fails it an item with the same reference already exists in the database.

Now, suppose that we’d like to allow for our API to allow sending the same request twice wthout throwing an error — we can use an INSERT IGNORE:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
mysql> SELECT * FROM item;
+----+-------------------------------------+-----------+
| id | title                               | reference |
+----+-------------------------------------+-----------+
|  1 | iPad - the best tablet in the world | IPAD-64GB |
+----+-------------------------------------+-----------+
1 row in set (0.00 sec)

mysql> INSERT IGNORE INTO item (title, reference) VALUES("iPad - the best tablet in the world", "IPAD-64GB");
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> SHOW WARNINGS;
+---------+------+-------------------------------------------------+
| Level   | Code | Message                                         |
+---------+------+-------------------------------------------------+
| Warning | 1062 | Duplicate entry 'IPAD-64GB' for key 'reference' |
+---------+------+-------------------------------------------------+
1 row in set (0.00 sec)

mysql>

Perfect, we tried to re-insert a record with an existing reference and the query went through, without throwing errors or adding / updating records in the DB.

Now, this is exactly what INSERT IGNORE is supposed to do: trigger an insert and, if it causes an error, don’t make a fuss out of it.

But…Surprise, surprise! Let’s try with a slightly different query:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
mysql> SELECT * FROM item;
+----+-------------------------------------+-----------+
| id | title                               | reference |
+----+-------------------------------------+-----------+
|  1 | iPad - the best tablet in the world | IPAD-64GB |
+----+-------------------------------------+-----------+
1 row in set (0.00 sec)

mysql> INSERT IGNORE INTO item (reference) VALUES("SOMETHING");
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> SHOW WARNINGS;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1364 | Field 'title' doesn't have a default value |
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM item;
+----+-------------------------------------+-----------+
| id | title                               | reference |
+----+-------------------------------------+-----------+
|  1 | iPad - the best tablet in the world | IPAD-64GB |
|  6 |                                     | SOMETHING |
+----+-------------------------------------+-----------+
2 rows in set (0.00 sec)

mysql>

What the heck is happening here? Rather than silencing an error (no value provided for the non-nullable column title) the INSERT IGNORE simply decides that it’s better to let the INSERT go through with all the missing non-nullable values set to an empty string — which is something I wasn’t expecting at all.

ON DUPLICATE KEY

I simply decided to convert my INSERT IGNORE to ON DUPLICATE KEY in order to avoid sloppy clients sending data without some required fields and then finding empty strings all over the database.

The switch was very simple:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> SELECT * FROM item;
Empty set (0.00 sec)

mysql> INSERT INTO item (title, reference) VALUES("iPad - the best tablet in the world", "IPAD-64GB") ON DUPLICATE KEY UPDATE id  = id;
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO item (title, reference) VALUES("iPad - the best tablet in the world", "IPAD-64GB") ON DUPLICATE KEY UPDATE id  = id;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM item;
+----+-------------------------------------+-----------+
| id | title                               | reference |
+----+-------------------------------------+-----------+
|  7 | iPad - the best tablet in the world | IPAD-64GB |
+----+-------------------------------------+-----------+
1 row in set (0.00 sec)

mysql>

…and that’s about it: I must say I was very surprised at this behavior but I guess it makes sense since INSERT IGNORE ignores all errors, not just duplicate key ones.

To be honest, it looks like I’d better stop using INSERT IGNORE sooner rather than later :)

Adios!


Hi there! I recently wrote an ebook on web application security, currently sold on leanpub, the Amazon Kindle store and gumroad.

It contains 150+ pages of content dedicated to securing web applications and improving your security awareness when building web apps, with chapters ranging from explaining how to secure HTTP cookies with the right flags to understanding why it is important to consider joining a bug bounty program.

Feel free to skim through some of the free chapters published on this blog and, if the content seems interesting enough to you, grab a copy on leanpub, the Amazon Kindle store, gumroad or simply checkout right down below!

Buy the Web Application Security ebook for $6.99

In the mood for some more reading?

...or check the archives.