I'm getting parse errors when I try to use node-mysql to invoke a query on a MYSQL database. I'm pretty sure that the query works. It runs without doubt via phpmyadmin.

Message.save = function(message, callback){
    db.query("INSERT INTO e_message (chatid, message, userid) VALUES(" + message.chatid + ", '" + message.message +"', " + message.userid + "); SELECT * FROM e_message WHERE chatid = " + message.chatid + " ORDER BY timestamp DESC LIMIT 0, 1;", 
    function(err, rows, fields){
        console.log(err);
        callback(err, new Message(rows[0]));
    });
}

I'm getting the follwing 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 'SELECT * FROM e_message WHERE chatid = 1 ORDER BY timestamp DESC LIMIT 0, 1' at line 1]
  code: 'ER_PARSE_ERROR',
  errno: 1064,
  sqlState: '42000',
  index: 0 }

The query looks like this via console.log():

INSERT INTO e_message (chatid, message, userid) VALUES(1, 'test123', 1);
SELECT * FROM e_message WHERE chatid = 1 ORDER BY timestamp DESC LIMIT 0, 1;

I don't know whats wrong with this...

EDIT: If I split it into two queries, I get the result I wanted:

Message.save = function(message, callback){
    db.query("INSERT INTO e_message (chatid, message, userid) VALUES(" + message.chatid + ", '" + message.message +"', " + message.userid + ");", function(err, rows, fields){
        db.query("SELECT * FROM e_message WHERE userid = " + message.userid + " AND chatid = " + message.chatid + " ORDER BY timestamp DESC LIMIT 0, 1;", function(err, rows, filds){
            callback(err, new Message(rows[0]));
        });
    });
}

Thank you!

I'm getting parse errors when I try to use node-mysql to invoke a query on a MYSQL database. I'm pretty sure that the query works. It runs without doubt via phpmyadmin.

Message.save = function(message, callback){
    db.query("INSERT INTO e_message (chatid, message, userid) VALUES(" + message.chatid + ", '" + message.message +"', " + message.userid + "); SELECT * FROM e_message WHERE chatid = " + message.chatid + " ORDER BY timestamp DESC LIMIT 0, 1;", 
    function(err, rows, fields){
        console.log(err);
        callback(err, new Message(rows[0]));
    });
}

I'm getting the follwing 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 'SELECT * FROM e_message WHERE chatid = 1 ORDER BY timestamp DESC LIMIT 0, 1' at line 1]
  code: 'ER_PARSE_ERROR',
  errno: 1064,
  sqlState: '42000',
  index: 0 }

The query looks like this via console.log():

INSERT INTO e_message (chatid, message, userid) VALUES(1, 'test123', 1);
SELECT * FROM e_message WHERE chatid = 1 ORDER BY timestamp DESC LIMIT 0, 1;

I don't know whats wrong with this...

EDIT: If I split it into two queries, I get the result I wanted:

Message.save = function(message, callback){
    db.query("INSERT INTO e_message (chatid, message, userid) VALUES(" + message.chatid + ", '" + message.message +"', " + message.userid + ");", function(err, rows, fields){
        db.query("SELECT * FROM e_message WHERE userid = " + message.userid + " AND chatid = " + message.chatid + " ORDER BY timestamp DESC LIMIT 0, 1;", function(err, rows, filds){
            callback(err, new Message(rows[0]));
        });
    });
}

Thank you!

• javascript
• mysql
• node.js
• node-mysql

• Could it be having issues with doing 2 sql statements in one query? Try breaking them into two queries first, see if that helps. – Chris Commented Dec 29, 2013 at 16:04
• Split your query into 2 queries. There are 2 separate queries. – BlitZ Commented Dec 29, 2013 at 16:04
• Also, try using the array parameter with the query method, it's so much cleaner, and it does string escaping for you. – Chris Commented Dec 29, 2013 at 16:05
• If I split it to two queries and two users save a message at the same time, the result could be incorrect. (Because node is async...) EDIT: OK i could filter against userID to resolve this. – SFL Commented Dec 29, 2013 at 16:06

1 Answer 1

node-mysql won't by default allow you to issue multiple SQL statements in a single query.

To allow that, you will need to set the multipleStatements connection option when creating the connection.

Note that allowing this may/will put you at risk of SQL injection, particularly if building the statements as strings. For example, if your message.userid was set to the string 1);drop database production;SELECT (, you'd be in trouble.

In this case what you really want may be to do the insert and a second SELECT LAST_INSERT_ID() to get the id the latest record was inserted with. It will return the latest inserted auto increment key for the session, that is, it will not be affected by other inserts by other connections/sessions.