NodeJS mysql ignores SAFE MODE and overwrites all table rows when receiving bad SQL that makes it ignore the WHERE part

[baditaflorin]

When I try and run this command

"UPDATE table_name SET street = '5600, Bl `A' #201' WHERE ID = 3;"

using MysqlWorkbench or other clients, I will get Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

When I run it using mysql in nodeJS it will run and replace everything.

OkPacket {
  fieldCount: 0,
  affectedRows: 12822760,
  insertId: 0,
  serverStatus: 2082,
  warningCount: 65535,
  message: '=Rows matched: 12822760  Changed: 12822760  Warnings: 12822760',
  protocol41: true,
  changedRows: 12822760
}

Create Table and insert SQL data

CREATE TABLE table_name (
    id int,
     address varchar(255)
);

INSERT INTO table_name (id, address) VALUES (1, 'FirstStreet');
INSERT INTO table_name (id, address) VALUES (2, 'SecondStreet');
INSERT INTO table_name (id, address) VALUES (3, 'ThirdStreet');
INSERT INTO table_name (id, address) VALUES (4, 'LastStreet');

run_mysql_statement.js

let mysql = require('mysql');
let $badly_formatted_sql = "UPDATE table_name SET street = '5600, Bl `A' #201' WHERE ID = 3;"

let myCon = mysql.createConnection({
   host: 'localhost',
   port: '3306',
   database: 'database',
   user: 'user',
   password: 'pass'
});
    myCon.query($badly_formatted_sql, function(err, sets, fields){
     if(err) console.log(err);
     console.log(sets);
    });
});
myCon.end();

[dougwilson]

Very strange; this module sends the SQL to the server for processing, so the server should be doing the same validation just like any other client.

Can you try and see if another similar client has this same issue, for example PHP, Java, Python, or any other programming language interface?

[mbaumgartl]

@baditaflorin you either have to start the MySQL server using the --save-updates option or set sql_safe_updates variable after connection is established:

https://dev.mysql.com/doc/refman/8.0/en/mysql-tips.html#safe-updates

[dougwilson]

Closing due to no response from OP. Unable to reproduce the issue. Further information or a pull request with a fix is always welcome.