Difference between where and like in CLEARDB

Question

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")
    }
  };

Caio Santos · Accepted Answer · 2021-02-06T17:04:41.753

0

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.

edited Feb 06 '21 at 17:04

answered Feb 04 '21 at 18:12

Caio Santos

1,665
15
10

For the second sql you mentioned I get this error: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? AND password = ?' at line 1" I'm using ClearDb from heroku is it different than normal MySql? – Feb 04 '21 at 20:01
It should be the same as any other MySQL instance. So try using semicolon at the end and try using the quotes too. Make some combinations quotes + semicolon at the end, no quotes + semicolon at the end. It would be good if you post all your code, the lib you are using, etc. – Caio Santos Feb 05 '21 at 05:03
I fixed the answer. Did it run? – Caio Santos Feb 06 '21 at 17:05
"SELECT * FROM admin_cred WHERE username = '"+[username]+"'" this is what I'm currently using. I'm not sure whats the difference bertween all the different versions of statements I've came across – Feb 06 '21 at 19:49

Difference between where and like in CLEARDB

1 Answers1