CODEWITHSUNDEEP
javascriptmysqlnode.jsexpress
Siddharth
Siddharth

Reputation: 545

Node.js mysql query syntax for retrieving

I am using nodejs with MySQL nom to retrieve date. currently select * from table works fine but if I want to query db and retrieve date between 2 values it's not working.

The node code is

app.get('/api',function(req,res)
{


  var startDate = req.param('startDate');
  var endDate = req.param('endDate');
  var sqlQuery =null;
  var message ={};
  message.status=false;

  log("Params Found Changing SQL Queries For Start And End Date");
    sqlQuery ="SELECT * from sentiment_data where *file_ts* >= "+startDate+" and *file_ts* <= "+endDate+" order by file_ts";
    log(sqlQuery);
    if(user.isDB)
    {  
      connection.query(sqlQuery, function(err, rows, fields)
      {
        if (!err)
        {
          message = rows;
          res.send(message);
        }
      });
    }
    else
    {
      log("DB Error");
    }

});

The SQL statement I am executing when building it with start time and end time is 

SELECT * from sentiment_data 
 where *file_ts* >= "+startDate+" 
  and *file_ts* <= "+endDate+" 
 order by file_ts

I am building this query and its not working.

Upvotes: 1

Views: 850

Answers (1)

Amadan
Amadan

Reputation: 198324

*file_ts* is not valid SQL; nor is it possible to just plonk an unquoted date into a query. Use parameter binding; it will also protect you from Bobby Tables.

var sqlQuery = "SELECT * FROM sentiment_data WHERE file_ts >= ? AND file_ts <= ? ORDER BY file_ts";
// ...
connection.query(sqlQuery, [startDate, endDate], function(err, results) {
  // ...
});

And depending on what is in startDate and endDate, you might need to use Date.parse to make them understandable.

Upvotes: 2

Related Questions