sqlnode.jscleardb

Difference between where and like in CLEARDB


I'm trying a simple nodejs login system and want to use the following query:

"SELECT * FROM admin_cred WHERE username = '?' AND password = '?'", [username], [password]

But it simply doesn't return anything so I had to do it like this:

'SELECT * from admin_cred where username like "%'+username+'%" AND password like "%'+password+'%"'

This is the code segment:

const result = await database.query(
      "SELECT * FROM admin_cred WHERE username = '?' AND password = '?'", [username], [password]
   // 'SELECT * from admin_cred where username like "%'+username+'%" AND password like 
      "%'+password+'%"'
);

Can anyone point out why the first query is not working?

And the difference bertween the two statements?

N.B: This is the first time i'm using cleardb on heroku and a few things seems different from MySql. Everything else in the code works so I've narrowed the problem down

EDIT 1

I just noticed that the second query is running even though the password was wrong

UPDATE 1 Here is the node js code as requested:

class auth {
    constructor(app, database) {
        this.login(app, database);
    }
    //http://localhost:8000/api/auth/v1/login
    login(app, database) {
        app.post("/api/auth/v1/login", async (request, response) => {
            const username = request.body.username;
            const password = request.body.password;
            try {
            const result = await database.query(
                "SELECT * FROM admin_cred WHERE username = '?'", [username]
            );
            console.log(result);
            if(result.length > 0){
                if(password === result[0].password){
                    response.json({
                        loggedIn:"true",
                        data: username
                    })
                }else{
                    response.json({
                        loggedIn:"false",
                        data: "wrong username or pass"
                    })
                }
            }else{
                response.json({
                    loggedIn:"false",
                    data:"username doesnt exist"
                })
            }
        } catch (error) {
                console.log(error);
            }
        });
    }
}

And here is the post request from ReactJs:

const handleLogin = async (e) =>{
    e.preventDefault();
    const admin = {username, password};
    const response = await axios.post(
      "http://localhost:8000/api/auth/v1/login",
      admin
    );
    if(response.length > 0){
      console.log("response: " + response);
    }else{
      console.log("no response")
    }
  };

Solution

  • Use:

    const result = await database.query(
        'SELECT * FROM admin_cred WHERE username = "?" AND password = "?"', [username, password]
    );
    

    Tip: never use LIKE for authentication queries and try to encrypt passwords.