Data rows returning empty array-CodePudding

I am running into a trouble where when I submit the login button, the data rows section returned is empty:

[nodemon] restarting due to changes... [nodemon] starting node server.js Example app listening on port 8080 Connected to database

{ username: 'torontofam', password: 'password' }
    result{
    command: 'SELECT',
      rowCount: 0,
      oid: null,
      rows: [],
      fields: [
        Field {
          name: 'id',
          tableID: 19247,
          columnID: 1,
          dataTypeID: 23,
          dataTypeSize: 4,
          dataTypeModifier: -1,
          format: 'text'
        },
        Field {
          name: 'username',
          tableID: 19247,
          columnID: 2,
          dataTypeID: 1043,
          dataTypeSize: -1,
          dataTypeModifier: 259,
          format: 'text'
        },
        Field {
          name: 'password',
          tableID: 19247,
          columnID: 3,
          dataTypeID: 1043,
          dataTypeSize: -1,
          dataTypeModifier: 259,
          format: 'text'
        },
    }

Here's what psql is returning when I run the query:

    midterm=# SELECT * FROM users WHERE username = 'torontofam' AND password = '$2a$10$FB/BOAVhpuLvpOREQVmvmezD4ED/.JBIDRh70tGevYzYzQgFId2u.';;
 id |  username  |                           password                           |  latitude  |  longitude  
---- ------------ -------------------------------------------------------------- ------------ -------------
  1 | torontofam | $2a$10$FB/BOAVhpuLvpOREQVmvmezD4ED/.JBIDRh70tGevYzYzQgFId2u. | 43.6523736 | -79.3857858
(1 row)

Here's the actual query requested from DB:

router.post("/login", (req, res) => {
const { username, password } = req.body;
console.log(req.body);
const queryString = `SELECT * FROM users WHERE username = $1 AND password = $2;`;
db.query(queryString, [username, password])
  .then((data) => {
    console.log(data);
    const user = data.rows[0];

    if (!user) {
      return res
        .status(400)
        .send({ message: "Username not found in database" });
    }

    const validPassword = bcrypt.compareSync(password, user.password);

    if (!validPassword) {
      return res
        .status(400)
        .send({ message: "Password does not match username" });
    }
    req.session.user_id = user.id;
    res.redirect("/");
  })
  .catch((err) => {
    res.status(500).json({ error: err.message });
  });

});

I can't seem to figure out why the login request is returning : POST /api/users/login 400 26.831 ms - 44 {"message":"Username not found in database"}

CodePudding user response：

Is this MySQL? Try using question marks(?) at both places instead of $1 and $2.

CodePudding user response：

After debugging I figured out why the data.rows was returning an empty array.

The issue lies here:

const queryString = `SELECT * FROM users WHERE username = $1 AND password = $2;`;
db.query(queryString, [username, password])

I shouldn't have to select the password as well since the username alone is enough to retrieve the row.

I changed the code to the following:

const queryString = `SELECT * FROM users WHERE username = $1;`;
db.query(queryString, [username])

It's now returning:

{ username: 'torontofam', password: 'password' }
{
  id: 1,
  username: 'torontofam',
  password: '$2a$10$FB/BOAVhpuLvpOREQVmvmezD4ED/.JBIDRh70tGevYzYzQgFId2u.',
  latitude: 43.6523736,
  longitude: -79.3857858
}