Faulty SQL query in JavaScript?

Question

Before I start, I'm aware of the risks I'm taking by connecting to a database via JavaScript. The thing with this project is that it's going to be for a slightly different purpose, so I'm fine with using JavaScript.

document.getElementsByClassName("option")[0].onclick = function() {
    event.preventDefault();
    var mysql = require('mysql');
    var con = mysql.createConnection({
        host: "localhost",
        user: "root",
        password: "",
        database: "dbname"
    });
    con.connect(function(err) {
        if (err) throw err;
        var email = document.getElementById("email").value;
        var password = document.getElementById("password").value;
        var sql = 'SELECT * FROM accounts WHERE email = ' + mysql.escape(email) + ' AND password = ' + mysql.escape(password);
        con.query(sql, function (err, result) {
            if (err) throw err;
            console.log(result);
        });
    });
}

So, I had technically done this before with PHP. It's just that I'm now doing it with JavaScript. Yet, something is clearly wrong. As you can see, I want to see the result in the console. Yet, I'm left with this:

I can't say I'm an experienced programmer - the truth is, this is just some kind of practice project, I'm a student. So any kind of help would be appreciated!

Why are you running the code in the `onclick` handler of an ` — Barmar, Jun 22 '20 at 21:21

score 1 · Accepted Answer · answered Jun 22 '20 at 21:13

1

You need to quote strings in SQL. You didn't put quotes around the email and password.

But it's better to use parameters rather than substituting variables into the SQL, even if you escape them.

    var sql = 'SELECT * FROM accounts WHERE email = ? AND password = ?';
    con.query(sql, [email, password], function (err, result) {
        if (err) throw err;
        console.log(result);
    });

answered Jun 22 '20 at 21:13

Barmar

741,623
53
500
612

Hello! I'm really thankful for the tip and for the quick answer. But I still must be doing something wrong. The result doesn't change. – Plamen Dobrev Jun 22 '20 at 21:18
If `result` is empty then there's no account that matches the given email and password. – Barmar Jun 22 '20 at 21:19
Just found out, the problem occurs because the password is encrypted. I used the `password_hash` function, is there a way this can be resolved? – Plamen Dobrev Jun 22 '20 at 21:21
See https://stackoverflow.com/questions/23015043/verify-password-hash-in-nodejs-which-was-generated-in-php – Barmar Jun 22 '20 at 21:23
1

Before you can use @Barmar's suggestion you're going to need to get the salt from the existing password. You should be able to use substring to get that from mysql, then use it to hash the password attempt before trying to validate the entire password. – Charlie Bamford Jun 22 '20 at 21:30
After you sent me this link, I did some research on **bcrypt** and managed to successfully do what I want! Once again, I'm really thankful for both of your's advice, answers and the time you spent on helping me! Have a nice day! – Plamen Dobrev Jun 22 '20 at 22:11
Please accept this answer if it helped you @PlamenDobrev . – FanoFN Jun 23 '20 at 04:22

Faulty SQL query in JavaScript?

1 Answers1