Anorm & MySQL SHA1 password encrytion is not same with Mysql Workbench command

Question

In my Play Framework Scala project I am using MySQL as database. In my database I have a column with the name PASSWORD. I will store the users passwords in this column when they create a new user in my project. I use SHA1 encryption for password field while inserting a user vales. If I run the command directly in my database from MySQL Workbench, it will store the different encrypted string in my PASSWORD column.

Command run in MySql Workbench

INSERT into user('PASSWORD') VALUES(SHA1('hello'));

this SQL Query insert aaf4c61ddcc5e8a2dabede0f3b482cd9aea9434d this string in PASSWORD field

INSERT command from Project

def insert(password: String) = {
    DB.withConnection { implicit connection =>
      SQL(
        """
          insert into user(PASSWORD) 
          values (
            SHA1('{password}')
          )
        """
      ).on(       
        'password -> password       
      ).executeUpdate()
    }
  }

This insert operation stores the PASSWORD value as f0e2d8610edefa0c02b673dcac7964b02ce3e890

What issue I am facing

Update my PASSWORD from project store value as aaf4c61ddcc5e8a2dabede0f3b482cd9aea9434d for password hello

SELECT USERNAME from USER WHERE PASSWORD = SHA1('hello') 

This query returns null in my project because it is considering SHA1('hello') as f0e2d8610edefa0c02b673dcac7964b02ce3e890

Answer 1

f0e2d8610edefa0c02b673dcac7964b02ce3e890 is the SHA-1 hash of {password}.

You need to remove the quotes (') around {password} to make it work:

def insert(password: String) = {
    DB.withConnection { implicit connection =>
      SQL(
        """
          insert into user(PASSWORD) 
          values (
            SHA1({password})
          )
        """
      ).on(       
        'password -> password       
      ).executeUpdate()
    }
  }

Otherwise the framework interprets {password} as a String and not as a variable and therefore calculates the hash of {passowrd} instead of hello.