Why is SQL "WHERE" return value from table lowercase? [duplicate]

Question

Sorry if my question is confusing.

Why does when I query

SELECT email FROM users WHERE email = '[email protected]' 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 protected]&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/[email protected]&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';)

You shouldn't be storing plaintext passwords in the DB in the first place. — Barmar
– Barmar, Commented Nov 5, 2021 at 23:05
@Ivar that's just an example of my code. Obviously it's a security issue, I work in cybersecurity. — Parking Master
– Parking Master, Commented Nov 8, 2021 at 14:05

bartius · Accepted Answer · 2021-11-05 23:13:24Z

1

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 = '[email protected]' 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

answered Nov 5, 2021 at 23:13

bartius

2022 silver badges7 bronze badges

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

2 Comments

Parking Master Over a year ago

Thanks! I never knew SQL was case-insensitive.

Joe Over a year ago

SQL is generally case sensitive - this is an oddity of MySQL

Collectives™ on Stack Overflow

Why is SQL "WHERE" return value from table lowercase? [duplicate]

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related