node-mysql multiple statements in one query

I'm using nodejs 10.26 + express 3.5 + node-mysql 2.1.1 + MySQL-Server Version: 5.6.16.

I got 4 DELETE's and want only 1 Database Request, so i connected the DELETE commands with a ";"... but it fails always.

var sql_string = "DELETE FROM user_tables WHERE name = 'Testbase';";
sql_string += "DELETE FROM user_tables_structure WHERE parent_table_name = 'Testbase';";
sql_string += "DELETE FROM user_tables_rules WHERE parent_table_name = 'Testbase';";
sql_string += "DELETE FROM user_tables_columns WHERE parent_table_name = 'Testbase';";

connection.query(sql_string, function(err, rows, fields) {
   if (err) throw err;
   res.send('true');
});

It throws this error:

Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELETE FROM user_tables_structure WHERE parent_table_name = 'Testbase';DELETE FR' at line 1

But if i paste this SQL in PhpMyAdmin it is always successful...

If i write it in single query's its succeed, too.

		connection.query("DELETE FROM user_tables WHERE name = 'Testbase'", function(err, rows, fields) {
        if (err) throw err;
    
        connection.query("DELETE FROM user_tables_structure WHERE parent_table_name = 'Testbase'", function(err, rows, fields) {
            if (err) throw err;
        
            
            connection.query("DELETE FROM user_tables_rules WHERE parent_table_name = 'Testbase'", function(err, rows, fields) {
                if (err) throw err;

                connection.query("DELETE FROM user_tables_columns WHERE parent_table_name = 'Testbase'", function(err, rows, fields) {
                    if (err) throw err;

                    res.send('true');
                });
            });
        });
    });

Thanks for help!

I guess you are using node-mysql. (but should also work for node-mysql2)

The docs says:

> Support for multiple statements is disabled for security reasons (it > allows for SQL injection attacks if values are not properly escaped).

Multiple statement queries

To use this feature you have to enable it for your connection:

var connection = mysql.createConnection({multipleStatements: true});

Once enabled, you can execute queries with multiple statements by separating each statement with a semi-colon ;. Result will be an array for each statement.

Example

connection.query('SELECT ?; SELECT ?', [1, 2], function(err, results) {
  if (err) throw err;

  // `results` is an array with one element for every statement in the query:
  console.log(results[0]); // [{1: 1}]
  console.log(results[1]); // [{2: 2}]
});

So if you have enabled the multipleStatements, your first code should work.

Using "multiplestatements: true" like shown below worked for me

var connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '',
    database: '',
    multipleStatements: true
});
connection.connect();
 
var sql = "CREATE TABLE test(id INT DEFAULT 1, name VARCHAR(50));ALTER TABLE test ADD age VARCHAR(10);";
 
connection.query(sql, function(error, results, fields) {
    if (error) {
        throw error;
    }
});

To Fetch Data from DB(SQL), the following function would work accurately

router.get('/', function messageFunction(req, res){ //res.send('Hi Dear Rasikh, Welcome to Test Page.') //=> One Way dbConn.query('SELECT COUNT(name) as counted, name, last_name, phone, email from students', function (err, rows, fields) { // another Way if (err) throw err

  dbConn.query('SELECT name, author from books',
  function (err, rowsBook, fields) { // another Way
      if (err) throw err
    // console.log('The counted is: ', rows[0].counted);    //=> Display in console
    // res.send('Hi Dear Rasikh, Welcome to Test Page.'+ rows[0].counted)  //=> Display in blank page
    
    res.render('main/index',{data:rows, myData:rowsBook});
  })

}); });