Translated from 日本語 • View original The Contact Form 7 questionnaire form plugin only sends emails, so Contact Form DB is a plug-in that stores data in the database.
By adding both plugins to WordPress, you can easily store the contents of the input form in the DB.
However, the table created by this Contact Form DB was a crook.
When you view the Contact Form DB admin from your WordPress admin, you can see a list of data entered from the input form, which looks as if posts from a single form are stored as a single record. However, in the actual table, each input of the input form is registered as one record.
If there are three input items in the form, three records will be registered in one post, and in each record, only the contents of each input item will be recorded in the key with the posting time, input form name, and input item name.
By the way, the table structure is as follows.
mysql> DESC wp_cf7dbplugin_submits;
+-------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| submit_time | decimal(16,4) | NO | MUL | NULL | |
| form_name | varchar(127) | YES | MUL | NULL | |
| field_name | varchar(127) | YES | MUL | NULL | |
| field_value | longtext | YES | | NULL | |
| field_order | int(11) | YES | | NULL | |
| file | longblob | YES | | NULL | |
+-------------+---------------+------+-----+---------+-------+
6 rows in set (0.03 sec)
The index list is as follows.
mysql> SHOW INDEX FROM wp_cf7dbplugin_submits;
+------------------------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| wp_cf7dbplugin_submits | 1 | submit_time_idx | 1 | submit_time | A | 2 | NULL | NULL | | BTREE | | |
| wp_cf7dbplugin_submits | 1 | form_name_idx | 1 | form_name | A | 1 | NULL | NULL | YES | BTREE | | |
| wp_cf7dbplugin_submits | 1 | field_name_idx | 1 | field_name | A | 26 | NULL | NULL | YES | BTREE | | |
+------------------------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
By the way, when I inserted a record with the same time but different post content, I was able to register it, and when I browsed the data from the management screen in that state, two posts with the same time were displayed as a jumbled one record. (Well, of course)
The same time is the same in milliseconds, so there may be no problem if you don't care so much about websites, but you should be especially concerned about websites that handle personal information.
So, as a countermeasure, change the table structure.
First, delete the index.
ALTER TABLE wp_cf7dbplugin_submits DROP INDEX submit_time_idx;
ALTER TABLE wp_cf7dbplugin_submits DROP INDEX form_name_idx;
ALTER TABLE wp_cf7dbplugin_submits DROP INDEX field_name_idx;
Next, set it back with the compound key.
alter table wp_cf7dbplugin_submits add constraint primary key(submit_time, form_name, field_name);
The result is like this.
mysql> SHOW INDEX FROM wp_cf7dbplugin_submits;
+------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| wp_cf7dbplugin_submits | 0 | PRIMARY | 1 | submit_time | A | 173 | NULL | NULL | | BTREE | | |
| wp_cf7dbplugin_submits | 0 | PRIMARY | 2 | form_name | A | 173 | NULL | NULL | | BTREE | | |
| wp_cf7dbplugin_submits | 0 | PRIMARY | 3 | field_name | A | 1038 | NULL | NULL | | BTREE | | |
+------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
By the way, it seems that some versions of MySQL do not allow you to set composite keys, but you can do with modern ones.
Now, even if there is a post at exactly the same time, one of them will not be registered, so the integrity of the data can be maintained.