Js mysql query not using boolean value given with GET request-CodePudding

I'm trying to search the MySQL database for active or inactive users that have the value isActive, the idea is that a GET request is sent with either isActive=true or isActive=false and all users that are active or inactive will be sent in the response. The problem is that the isActive value is not being used, I can only get it to work by hardcoding it into the SQL query.

When I send a request with the firstName value defined it does work, so I think it might have to do with the isActive being a boolean and not converting correctly but I have not been able to fix that.

Also, I'm using MySQL2 for the database.

Any help would be greatly appreciated.

    getUsers: (req, res, next) => {
        const queryParams = req.query
        logger.debug(queryParams)

        let { firstName, isActive } = req.query
        
        let queryString = 'SELECT * FROM `user`'
        if (firstName || isActive) {
            queryString  = ' WHERE '
            if (firstName) {
                queryString  = '`firstName` LIKE ?'
                firstName = '%'   firstName   '%'
            }
            if (firstName && isActive) queryString  = ' AND '
            if (isActive) {
                queryString  = '`isActive` = ?'
            }
        }
        queryString  = ';'
        logger.debug(`queryString = ${queryString}`)

        dbconnection.getConnection(function (err, connection) {
            if (err) next(err) // not connected!

            // Use the connection
            connection.query(queryString, [firstName, isActive], function (error, results) {
                    // When done with the connection, release it.
                    connection.release()

                    // Handle error after the release.
                    if (error) next(error)

                    // Don't use the connection here, it has been returned to the pool.
                    logger.debug('#results = ', results.length)
                    res.status(200).json({
                        status: 200,
                        results: results,
                    })
                }
            )
        })
    },

Here are the logs of when I call the GET method:

2022-05-20T14:16:12Z [DEBUG] user.controller.js:66 : { isActive: 'false' }
2022-05-20T14:16:12Z [DEBUG] user.controller.js:83 : queryString = SELECT * FROM `user` WHERE `isActive` = ?;
2022-05-20T14:16:13Z [DEBUG] dbconnection.js:23 : Connected to database '2186751'
2022-05-20T14:16:13Z [DEBUG] dbconnection.js:27 : Connection 46 acquired
2022-05-20T14:16:13Z [DEBUG] dbconnection.js:31 : Connection 46 released
2022-05-20T14:16:13Z [DEBUG] user.controller.js:97 : #results =  0

What the firstName and isActive are defined as in the MySQL database:

  `firstName` varchar(255) NOT NULL,
  `isActive` tinyint NOT NULL DEFAULT '1',

CodePudding user response：

You need to convert from true/false to 1/0 for MySQL to understand the boolean values.

Also, since you're building the query string dynamically, you also have to build the parameters array dynamically, so that the parameters match the placeholders.

getUsers: (req, res, next) => {
  const queryParams = req.query
  logger.debug(queryParams)

  let {
    firstName,
    isActive
  } = req.query

  let queryString = 'SELECT * FROM `user`'
  let params = [];
  if (firstName || isActive) {
    queryString  = ' WHERE '
    if (firstName) {
      queryString  = '`firstName` LIKE ?'
      firstName = '%'   firstName   '%'
      params.push(firstName);
    }
    if (firstName && isActive) queryString  = ' AND '
    if (isActive) {
      queryString  = '`isActive` = ?'
      params.push(isActive == 'true' ? 1 : 0);
    }
  }
  queryString  = ';'
  logger.debug(`queryString = ${queryString}`);
  logger.debug(`params = ${JSON.stringify(params)}`)

  dbconnection.getConnection(function(err, connection) {
    if (err) next(err) // not connected!

    // Use the connection
    connection.query(queryString, params, function(error, results) {
      // When done with the connection, release it.
      connection.release()

      // Handle error after the release.
      if (error) next(error)

      // Don't use the connection here, it has been returned to the pool.
      logger.debug('#results = ', results.length)
      res.status(200).json({
        status: 200,
        results: results,
      })
    })
  })
},