node-mysql multiple statements in one query

Question

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!

That is because you can only have 1 query per request. – majidarif Apr 24 '14 at 11:25 — majidarif, Apr 24 '14 at 11:25

majidarif · Accepted Answer · 2018-06-01T16:04:13.283

230

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.

edited Jun 01 '18 at 16:04

answered Apr 24 '14 at 11:28

majidarif

18,694
16
88
133

Will this work with place holder queries like `connection.query('SELECT * FROM `books` WHERE `author` = ?', ['David'], function (error, results, fields) { });` – randomness Jul 03 '15 at 03:26
@randomness yes, that syntax is correct. Refer to https://github.com/felixge/node-mysql#performing-queries – majidarif Jul 03 '15 at 03:48
@majidarif , I think I should make myself more clear. Is it possible to have place holder styled multi statement query using the node-mysql library?. – randomness Jul 07 '15 at 05:01
@randomness. Sorry I'm confused by your question. I think it is the same answer. Example `connection.query('SELECT ?, SELECT ?', [1, 2], function (error, results, fields) { });` ? – majidarif Jul 07 '15 at 14:18
Thank You! I will try that out – randomness Jul 08 '15 at 06:30
@randomness will appreciate an upvote too :). My mistake on the example, use this `SELECT ?; SELECT ?` not `SELECT ?, SELECT ?`. – majidarif Jul 08 '15 at 08:01
@majidarif using this method, are the queries executed in parallel? I cannot seem to find that in the node-mysql documentation. Thanks. – user2278120 Jan 06 '17 at 18:58
@user2278120 not entirely sure but I think you should check the mysql documentation. I think node-mysql sends the whole query and not split the multi query and send 1 by 1. So, I think, it runs synchronously. – majidarif Jan 06 '17 at 19:03
2

If you want it to run parallel, you should consider using Promise.all() and use single queries for each statement instead of multiple in one statement. – majidarif Jan 06 '17 at 19:04
@majidarif This got me curious, would you mind answering the new question I made? http://stackoverflow.com/questions/41513128/node-mysql-execute-multiple-queries-the-fastest-possible – user2278120 Jan 06 '17 at 19:48
1

wow, thanks for this. i can't believe the error is so misleading – Jona May 16 '19 at 06:02
Is there a way to check the result of each query before executing the next one? I only want to run later queries if the first ones succeed – rharding Feb 13 '20 at 17:39
1

@RossHarding you can ask it as a separate question. but yes, you can do that. – majidarif Feb 14 '20 at 10:48
Thanks @majidarif, posted here https://stackoverflow.com/questions/60230245/node-mysql-multiple-statements-in-one-query-execute-conditionally-on-response-f – rharding Feb 14 '20 at 16:34
Thank you so much!! I was lost without this tip. – Tyguy7 Mar 11 '20 at 18:18
And if you have say just one row in your result from the second SELECT query, you access the 'name' column entry for this via: **result[1][0]['name']**. When comparing a MySQL Boolean entry (implemented as TINYINT, i.e. 0 or 1, in MySQL) you must use a value of 0 or 1, **not** *false* or *true*. – Trunk Mar 25 '22 at 22:18

score 8 · Answer 2 · answered Jan 14 '22 at 09:56

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;
    }
});

score 0 · Answer 3 · answered Aug 16 '21 at 23:07

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});
  })

}); });

score 0 · Answer 4 · answered Jul 28 '23 at 02:49

This worked for me in Next.js...

export default async function handler(req, res) {
  try {
    // Build your multiple MySQL queries here
    const querySql = "DELETE FROM favorites WHERE user = ? AND listingID= ?";
    const querySql2 = "UPDATE properties SET saves = ? WHERE listingid = ?";

    // Pass any params here
    const valuesParams = [user, listingID];
    const valuesParams2 = [upDateSaves, listingID];

    //Execute your multiple MySQL queries here
    const data = await query({query: querySql, values: valuesParams });
    const data2 = await query({query: querySql2, values: valuesParams2 });

    //Combine the results
    const combinedResults = [data, data2];

    res.status(200).json({ 
    text: combinedResults,
   });

  } catch (error) {
    res.status(500).json({ error: 'Error fetching data' });
  }
}

node-mysql multiple statements in one query

4 Answers4

Multiple statement queries

Example

Linked

Related