Sorry if my question is confusing.
Why does when I query
SELECT email FROM users WHERE email = 'some-email@email.com' AND password = 'pass134';
I’m using a Node.js server with SQL, and when I use:
app.get('/login', function(req, res) {
var params = '?' + req.url.split('?').pop(); // "?email=test@test.net&pass=13456"
const pass = (new URLSearchParams(params).get('pass')); // "13456"
connection.query(`
SELECT email FROM users WHERE email = '${email}' AND password '${pass}';
`, function(err, result) {
if (err) throw err;
if (typeof result[0].email != "undefined") {
// User logged in successfully
} else {
// The email/password is incorrect
}
});
});
I try going to https://www.example.com/login?email=test@test.com&pass=Pass13456, and it logs in.
But when the password is lowercase or uppercase, it still logs in.
I think this is the WHERE
query not being specific enough.
How can I make SQL select value with WHERE as a specific value?
(e.g., WHERE str = 'only equal to this string, not lowercase or uppercase';
)
>Solution :
So where clauses are case insensitive in mysql. To fix it, you can return the password from the query, hold that in node as a variable and then compare it (one solution)
Or you could use a binary comparison. I still need to test this but i believe you just add the “BINARY” keyword to your query. So like
SELECT email FROM users WHERE email = ‘some-email@email.com’ AND BINARY password = ‘pass134’;
Im writing this on my phone so i dont know if this is the best and i still need to test the binary solution