Javascript possibly disrupting MySQL query [closed]

1

Recently I started working with SQL databases and I have had problems storing date type columns in my tables. Basically what happens when I try to store a variable in the format yyyy-mm-dd , for example 2016-12-23 , something happens and what goes into the table is 0000-00-00 . I tried to change the type of the variable from date to text and the stored value was 1981 , result of subtraction 2016 - 12 - 23 = 1981 . Has anyone ever had this problem or know how to solve it? I'm writing my project on node using the NPM MySQL module.

var temp = "1994-03-09";
newStaticQuery = {
    sql: 'INSERT INTO SaudeParamEstaticos (idPaciente, data, steps) VALUES (${id}, ${temp}, ${activity[property][0].value})',
    timeout: 10000
}
connection.query(newStaticQuery, function(err, rows, fields) {
    console.log(err);
    console.log(rows);
});

Here is a simpler example of my problem, following the code below, the date is entered correctly in the table, however when trying to put it in a temporary variable as in the case above the problem happens.

newStaticQuery = {
    sql: 'INSERT INTO SaudeParamEstaticos (idPaciente, data, steps) VALUES (${id}, '1994-03-09', ${activity[property][0].value})',
    timeout: 10000
}
connection.query(newStaticQuery, function(err, rows, fields) {
    console.log(err);
    console.log(rows);
});

Would it be some javascript syntax question?

    
asked by anonymous 23.12.2016 / 20:44

1 answer

3

It would be the case to append the quotation marks in the query template too:

sql: 'INSERT INTO ... VALUES (VALUES (${id}, '${temp}', ${activity[property][0].value})'
                                             ^-aspas-^

As it is only a substitution of values, if you pass without the quotes, something like this is generated ...

VALUES (1, 1994-03-09, 489)

... instead of generating like this:

VALUES (1, '1994-03-09', 489)

And really, without the quotes, MySQL will deal numerically with the information, calculating the value, as you had already realized with the tests.

    
24.12.2016 / 16:34