1

Basically, I would like to know how I can produce this query with Knex :

SELECT * FROM usr_info WHERE login = ? AND password = PASSWORD(?)

For example :

knex('usr_info')
  .where({
    'login': login,
    'password': password   // <-- specify a function to wrap the value into
  })
;

Looking through the docs, I see no example allowing this. Is this possible?

Yanick Rochon
  • 51,409
  • 25
  • 133
  • 214

1 Answers1

3

You probably need to use whereRaw()

knex('usr_info')
  .whereRaw('login = ? AND password = PASSWORD(?)', [login, password]);

By the way, you should not use MySQL's PASSWORD() function for your own app authentication. This function was removed in MySQL 8.0.11. In MySQL 5.7, it's deprecated, and there's a note:

https://dev.mysql.com/doc/refman/5.7/en/encryption-functions.html#function_password

PASSWORD() is used by the authentication system in MySQL Server; you should not use it in your own applications. For that purpose, consider a more secure function such as SHA2() instead.

Besides, it's not a good idea to pass the plaintext password to your query, because it may be logged in plaintext in the query log.

Instead, I recommend you search only for login in your query, then the result will include the hashed password stored in your database. You can hash the password in your app code and compare it to the hash returned by your query.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Yes, I am aware of the security issues with the implementation. Unfortunately, this is from a system over a decade old, and everything is tied to this. Eventually the auth module will be re-written, but this is what It is right now. – Yanick Rochon Oct 28 '19 at 19:12
  • Sigh. I understand, but I see this as an even worse problem that the app is a decade old, and still no one has summoned the will to correct grievous security flaws. But that's a management problem, not a technology problem. – Bill Karwin Oct 29 '19 at 14:49
  • FWIW the problem is being corrected, and it's all a matter of time by now. The whole point of this is so I can create a "temporary" auth module using the current user and data. Everything is outdated :) – Yanick Rochon Oct 29 '19 at 20:28