0

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';)

Parking Master
  • 551
  • 1
  • 4
  • 20
  • 1
    MySQL is case-insensitive by default. – Barmar Nov 05 '21 at 23:04
  • 3
    You shouldn't be storing plaintext passwords in the DB in the first place. – Barmar Nov 05 '21 at 23:05
  • https://stackoverflow.com/questions/5629111/how-can-i-make-sql-case-sensitive-string-comparison-on-mysql – PM 77-1 Nov 05 '21 at 23:06
  • @PM77-1 Thanks so much. Just what I was looking for! – Parking Master Nov 05 '21 at 23:10
  • I hope you never take this code into production as there are several severe flaws in your code. As Barmar mentioned, [you should never ever store passwords in plain text](https://cheatsheetseries.owasp.org/cheatsheets/Password_Storage_Cheat_Sheet.html) in your database. They should always be hashed and preferably salted. Your query is also open to [SQL Injection attacks](https://en.wikipedia.org/wiki/SQL_injection) (use prepared statements!) and passwords should never be passed via the URL, because those are logged on the server and in the browser history and where not. – Ivar Nov 08 '21 at 09:53
  • 1
    @Ivar that's just an example of my code. Obviously it's a security issue, I work in cybersecurity. – Parking Master Nov 08 '21 at 14:05

1 Answers1

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 = '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

bartius
  • 202
  • 2
  • 7