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 <option>? It probably should be run when the user clicks a button. — Barmar
– Barmar, Commented Jun 22, 2020 at 21:21

Barmar · Accepted Answer · 2020-06-22 21:13:58Z

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, 2020 at 21:13

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Plamen Dobrev Over a year ago

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.

Barmar Over a year ago

If result is empty then there's no account that matches the given email and password.

Plamen Dobrev Over a year ago

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?

Barmar Over a year ago

See stackoverflow.com/questions/23015043/…

Charlie Bamford Over a year ago

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.

|

Collectives™ on Stack Overflow

Faulty SQL query in JavaScript?

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related