Not able to retrieve record using eachRow method

Question

I am using below groovy code to select a row with a given username and password from mysql db.

For example,

I am searching for below user. Password is stored in db as a SHA1 hashed password.

    Name - test7
password - {SHA}01132E914B722741CC3823D0BEACB8364EEAC376

Below is the groovy code which is trying to retrieve the record and failing as I am not able to fetch the record.I suspect the sql query constructed is wrong. SecurityUtil class is just decrypting and returning a plaintext password back as a positional parameter to the query. We are using the sha1 method of mysql to generate the hash and prefix with string {SHA1} and then trying to retrieve the record.

sql.eachRow("SELECT id FROM users WHERE userName = ? AND password =CONCAT({SHA1},sha1(?))", [username, SecurityUtil.decrypt(password)]) {
authId = String.valueOf(it.id)
}

Answer 1

sql.eachRow("SELECT id FROM users WHERE userName = ? AND password =CONCAT('{SHA1}',sha1(?))", [username, SecurityUtil.decrypt(password)]) { authId = String.valueOf(it.id) }

You may need to put the {SHA} in quotes for use in the concat operator.

Answer 2

Welp if SQL doesn't want to play do it in Groovy. Using GStrings you get the same protection as prepared calls so no SQL Injection issues, and you can more easily do groovy operations:

sql.eachRow("SELECT id FROM users WHERE userName = ${userName} and password = ${'{SHA}' + SecurityUtil.decrypt(password)}") { 
    authId = String.valueOf( it.id )
}

The prepared statement syntax route:

sql.eachRow("SELECT id FROM users WHERE userName = ? and password = ?", [username, "{SHA}${SecurityUtil.descrypt(password)}"]) {
    authId = String.valueOf( it.id )
}