Bug #85418 DML with where clause without column name change all data
Submitted: 11 Mar 2017 3:45 Modified: 15 Mar 2017 5:24
Reporter: Shinya Sugiyama Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.7.x OS:Any
Assigned to: CPU Architecture:Any
Tags: DML

[11 Mar 2017 3:45] Shinya Sugiyama
Description:
If user forgot to put column name after where clause, all data will be changed.

How to repeat:
root@localhost [CONFIRM]> show create table T_DML_WITHOUT_COLUMN\G
*************************** 1. row ***************************
       Table: T_DML_WITHOUT_COLUMN
Create Table: CREATE TABLE `T_DML_WITHOUT_COLUMN` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `data` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

root@localhost [CONFIRM]> 

root@localhost [CONFIRM]> select * from T_DML_WITHOUT_COLUMN;
+----+--------+
| id | data   |
+----+--------+
|  1 | blue   |
|  2 | green  |
|  3 | red    |
|  4 | yellow |
+----+--------+
4 rows in set (0.00 sec)

root@localhost [CONFIRM]> select * from T_DML_WITHOUT_COLUMN where 12345;
+----+--------+
| id | data   |
+----+--------+
|  1 | blue   |
|  2 | green  |
|  3 | red    |
|  4 | yellow |
+----+--------+
4 rows in set (0.00 sec)

root@localhost [CONFIRM]> update T_DML_WITHOUT_COLUMN set data = 'confirmation' where 1;
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4  Changed: 4  Warnings: 0

root@localhost [CONFIRM]> select * from T_DML_WITHOUT_COLUMN;
+----+--------------+
| id | data         |
+----+--------------+
|  1 | confirmation |
|  2 | confirmation |
|  3 | confirmation |
|  4 | confirmation |
+----+--------------+
4 rows in set (0.00 sec)

root@localhost [CONFIRM]> 

Suggested fix:
This might be user mistake but this should be become error.
In case if user forgot to put column name, it should became error since it is not right sql.
[13 Mar 2017 10:54] MySQL Verification Team
Thank you for the report and feedback.
[13 Mar 2017 12:35] Shinya Sugiyama
Hi Umesh,

Thank you for your confirmation.

Additional Note: 
If user put 0, it doesn't update any data.

root@localhost [CONFIRM]> update T_DML_WITHOUT_COLUMN set data = 'confirmation 2nd' where 1;
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

root@localhost [CONFIRM]> select * from T_DML_WITHOUT_COLUMN;
+----+------------------+
| id | data             |
+----+------------------+
|  1 | confirmation 2nd |
|  2 | confirmation 2nd |
|  3 | confirmation 2nd |
|  4 | confirmation 2nd |
+----+------------------+
4 rows in set (0.01 sec)

root@localhost [CONFIRM]> update T_DML_WITHOUT_COLUMN set data = 'confirmation 2nd' where 0;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

root@localhost [CONFIRM]> select * from T_DML_WITHOUT_COLUMN;
+----+------------------+
| id | data             |
+----+------------------+
|  1 | confirmation 2nd |
|  2 | confirmation 2nd |
|  3 | confirmation 2nd |
|  4 | confirmation 2nd |
+----+------------------+
4 rows in set (0.00 sec)

root@localhost [CONFIRM]>
[15 Mar 2017 5:24] Shinya Sugiyama
■ Original Data
mysql> select * from T_DML_WITHOUT_COLUMN;
+----+--------+
| id | data   |
+----+--------+
|  1 | blue   |
|  2 | green  |
|  3 | red    |
|  4 | yellow |
+----+--------+
4 rows in set (0.00 sec)

■ select without values after row. (id = numeric row)

mysql> select * from T_DML_WITHOUT_COLUMN where id;
+----+--------+
| id | data   |
+----+--------+
|  1 | blue   |
|  2 | green  |
|  3 | red    |
|  4 | yellow |
+----+--------+
4 rows in set (0.00 sec)

■ select without values after row. (id = string row)

mysql> select * from T_DML_WITHOUT_COLUMN where data;
Empty set, 4 warnings (0.00 sec)

Warning (Code 1292): Truncated incorrect INTEGER value: 'blue'
Warning (Code 1292): Truncated incorrect INTEGER value: 'green'
Warning (Code 1292): Truncated incorrect INTEGER value: 'red'
Warning (Code 1292): Truncated incorrect INTEGER value: 'yellow'
mysql>

■ select without column name after where with '0' numeric value.

mysql> select * from T_DML_WITHOUT_COLUMN where 0;
Empty set (0.00 sec)

■ select without column name after where with non '0' numeric value.

mysql> select * from T_DML_WITHOUT_COLUMN where 1;
+----+--------+
| id | data   |
+----+--------+
|  1 | blue   |
|  2 | green  |
|  3 | red    |
|  4 | yellow |
+----+--------+
4 rows in set (0.00 sec)

■ select without column name after where with non '0' numeric value.
mysql> select * from T_DML_WITHOUT_COLUMN where 1234;
+----+--------+
| id | data   |
+----+--------+
|  1 | blue   |
|  2 | green  |
|  3 | red    |
|  4 | yellow |
+----+--------+
4 rows in set (0.00 sec)

It seems just checking True or false. (0 or 1)

■ In case forgot value after colum that is numeric row.
mysql> update T_DML_WITHOUT_COLUMN set data = 'forgot' where id;
Query OK, 4 rows affected (0.02 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> select * from T_DML_WITHOUT_COLUMN where 1;
+----+--------+
| id | data   |
+----+--------+
|  1 | forgot |
|  2 | forgot |
|  3 | forgot |
|  4 | forgot |
+----+--------+
4 rows in set (0.00 sec)

mysql> 

■ In case forgot value after column that is string row.
mysql> update T_DML_WITHOUT_COLUMN set data = 'forgot2' where data;
ERROR 1292 (22007): Truncated incorrect INTEGER value: 'forgot'
mysql> 

This is operation related problem; however, output should be error.

Best Regards
Shinya