SQL node.js Syntax error on what seems to be a valid query?

Question

I'm running an update on a table to set a position. I've extracted the query and manually run it on my database and works fine but when passed through connection.query() it seems to think there's a syntax error in my node.js console.

function sendShipPosition(position) {

    var input = '';

    if (position.moving === true) {
        var currentdate = new Date(); 
        var datetime = currentdate.getFullYear() + "-"  
                    + (currentdate.getMonth()+1)  + "-" 
                    + currentdate.getDate() + " "
                    + currentdate.getHours() + ":"  
                    + currentdate.getMinutes() + ":" 
                    + currentdate.getSeconds();
        var input = ', moving_datetime = ' + datetime;
    }

    connection.query('UPDATE ships SET x_axis = :x, y_axis = :y' + input + ' WHERE ship_id = :ship_id'), {
        x: parseInt(position.x),
        y: parseInt(position.y),
        ship_id: 1
    };
}

Here is the syntax error:

Error

Here's the input data value of 'position' variable:

{ x: '-605', y: '-257', moving: 0 }

I hope I'm not being too much of a dunce and sorry for the low quality question.

Thanks

You're using placeholders for X and Y, which is good. Why aren't you using a placeholder for the date value too? — Oz.
– Oz., Commented Apr 29, 2015 at 22:12
I felt it cleaner as I would have to put the value into the object as well to get the datetime in there. Plus I assume there's nothing wrong with inputs from using 'new Date();'? However now that I think about it, you make a good point, I'm being lazy and shouldn't go against best practices. I will update my code, thanks. — Jack
– Jack, Commented Apr 29, 2015 at 22:28

Mureinik · Accepted Answer · 2015-04-29 21:52:43Z

1

This function will generate SQL code which is missing quotes around the datetime variable, resulting in invalid SQL code.

function sendShipPosition(position) {

    var input = '';

    if (position.moving === true) {
        var currentdate = new Date(); 
        var datetime = currentdate.getFullYear() + "-"  
                    + (currentdate.getMonth()+1)  + "-" 
                    + currentdate.getDate() + " "
                    + currentdate.getHours() + ":"  
                    + currentdate.getMinutes() + ":" 
                    + currentdate.getSeconds();
        # Here!
        var input = ', moving_datetime = \'' + datetime + '\''
    }

    connection.query('UPDATE ships SET x_axis = :x, y_axis = :y' + input + ' WHERE ship_id = :ship_id'), {
        x: parseInt(position.x),
        y: parseInt(position.y),
        ship_id: 1
    };
}

answered Apr 29, 2015 at 21:52

Mureinik

316k54 gold badges399 silver badges405 bronze badges

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

1 Comment

Jack Over a year ago

I received an error again and then put quotes around all my values. Having quotes around your inputs is like SQL 101: back to basics! Doh! Thanks for the help :)

Collectives™ on Stack Overflow

SQL node.js Syntax error on what seems to be a valid query?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related