Search code examples
mysqlnode.jsknex.jsobjection.js

How to apply a function on a value of a where clause using Knex?

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?

Solution

  • 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.