Why is SQL "WHERE" return value from table lowercase?

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"
  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

Leave a Reply