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

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.204 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.