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 four DELETEs and want only one 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 statement into PhpMyAdmin it is always successful.

If I write it in a single query it succeeds, 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');
                });
            });
        });
    });

That is because you can only have 1 query per request.

majidarif
– majidarif

2014-04-24 11:25:52 +00:00
Commented Apr 24, 2014 at 11:25 — majidarif
– majidarif, Commented Apr 24, 2014 at 11:25

majidarif · Accepted Answer · 2018-06-01 16:04:13Z

237

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 1, 2018 at 16:04

answered Apr 24, 2014 at 11:28

majidarif

20.3k18 gold badges97 silver badges137 bronze badges

Sign up to request clarification or add additional context in comments.

14 Comments

randomness Over a year ago

Will this work with place holder queries like connection.query('SELECT * FROM books` WHERE author = ?', ['David'], function (error, results, fields) { });`

majidarif Over a year ago

@randomness yes, that syntax is correct. Refer to github.com/felixge/node-mysql#performing-queries

randomness Over a year ago

@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?.

majidarif Over a year ago

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 Over a year ago

@RossHarding you can ask it as a separate question. but yes, you can do that.

|

Rahith R R · Accepted Answer · 2022-01-14 09:56:17Z

9

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

answered Jan 14, 2022 at 9:56

Rahith R R

1774 silver badges16 bronze badges

Comments

Graphics Factory · Accepted Answer · 2023-07-28 02:49:48Z

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

Rasikh · Accepted Answer · 2021-08-16 23:07:22Z

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

}); });

Collectives™ on Stack Overflow

node-mysql multiple statements in one query

4 Answers 4

Multiple statement queries

Example

14 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Multiple statement queries

Example

14 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related