Javascript variables not working with MYSQL

Question

I am trying to pull data from a table in MYSQL and save it to another table. Here is my code: The two tables have the exact same columns. NOTE: The row does get saved but every value is apparently zero. Whenever I save this, MYSQL makes a new row with an index but all values are zero.

client.query('SELECT * FROM archive ORDER BY stamp LIMIT 1', function(err, result){
        var nor = result[0].Northatt;
        var eas = result[0].Eastatt;
        var sou = result[0].Southatt;
        var wes = result[0]. Westatt;
        var time = result[0].stamp;

        client.query("INSERT INTO quartly (Northatt, Eastatt, Southatt, Westatt, stamp) VALUES ('+nor+','+eas+','+sou+','+wes+','+time+')", function()err{
});
});

All of the variables are holding their respective 'int'...pretty sure I'm using the right syntax to save variables too.

Any help would be appreciated!

Which MySQL library are you using? You seem to be completely ignoring proper SQL escaping concerns. — tadman, Jul 29 '13 at 17:33
I take it the +variable+ denotes a marker that the client parses and inserts the js value set above back into the SQL statement. It must also handle escaping, if necessary on strings? If these are all ints, it seems you don't need to quote them (unless client lib needs that too). — williambq, Jul 29 '13 at 17:39
The library you're using should support parameterized queries so you avoid nasty [SQL injection bugs](http://bobby-tables.com/). It also has the side-effect of making your queries easier to read and debug. — tadman, Jul 29 '13 at 17:43
@pj409: read this [section](https://github.com/felixge/node-mysql#escaping-query-values) — go-oleg, Jul 29 '13 at 17:56

score 0 · Answer 1 · answered Jul 29 '13 at 17:30

0

you have missed double quotes like that

    VALUES ('" +nor+"','"+eas+"','"+sou+"','"+wes+"','"+time+"')

answered Jul 29 '13 at 17:30

echo_Me

37,078
5
58
78

Yes, but no. You don't create queries using string concatenation. – tadman Jul 29 '13 at 17:33
Is there a better way to do it? Also, this does not seem to be working properly for the 'stamp' column, which is a 'timestamp' type. – pj409 Jul 29 '13 at 17:39

score 0 · Answer 2 · edited May 23 '17 at 10:31

Escape parameters client side:

client.query('SELECT * FROM archive ORDER BY stamp LIMIT 1', function(err, result){
        var nor = result[0].Northatt;
        var eas = result[0].Eastatt;
        var sou = result[0].Southatt;
        var wes = result[0].Westatt;
        var time = result[0].stamp;

        client.query("INSERT INTO quartly (Northatt, Eastatt, Southatt, Westatt, stamp) VALUES (?,?,?,?,?)", [nor, eas, sou, wes, time], function()err{
        });
});

Or use prepared statements with node-mysql2 (that way parameters are sent independently and not as part of sql query string)

client.query('SELECT * FROM archive ORDER BY stamp LIMIT 1', function(err, result){
        var nor = result[0].Northatt;
        var eas = result[0].Eastatt;
        var sou = result[0].Southatt;
        var wes = result[0].Westatt;
        var time = result[0].stamp;

        client.execute("INSERT INTO quartly (Northatt, Eastatt, Southatt, Westatt, stamp) VALUES (?,?,?,?,?)", [nor, eas, sou, wes, time], function()err{
        });
});

Also you can do this in one query - see 'SQL Insert into … values ( SELECT … FROM … )' SO question

Javascript variables not working with MYSQL

2 Answers2