Node.js mysql query syntax for retrieving

Question

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.

Amadan · Accepted Answer · 2015-07-29 06:49:26Z

2

*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.

answered Jul 29, 2015 at 6:49

Amadan

199k23 gold badges252 silver badges321 bronze badges

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

5 Comments

Siddharth Over a year ago

how can i convert this date to unix_timestamp(date_format())

Amadan Over a year ago

Which date, specifically?

Siddharth Over a year ago

i am sending date as a string var dateStart = "2015-12-10" . after validating using moment.js . the problem is that i need date for yesterday, from yesterrday till last week , and from yestedat till last month and so for year. i have to contruct date . for that i have to get current date and manipulate for these requirements.

Amadan Over a year ago

You can use Date.parse('2015-12-10') / 1000 if file_ts is a UNIX timestamp (in seconds).

Amadan Over a year ago

Even better, if you are using Moment, moment("2015-12-10").add(-1, "week").unix() will give you a UNIX timestamp (in seconds) of time one week before that; if file_ts is an integer column, you can just use that directly.

Collectives™ on Stack Overflow

Node.js mysql query syntax for retrieving

1 Answer 1

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Related