Wsrtjtyk

Since encrypting certain information in my database I am having a great deal of difficulty using an existing search function. Its function is to search a table of students by either first, last or both names.

The old working code is as follows:

SELECT *

FROM students

WHERE CONCAT(TRIM(firstname), ' ', TRIM(lastname)) LIKE '%$result%'

AND currentyear = $currentyear

ORDER BY students.lastname

The new non working code following encryption:

SELECT *, 

       AES_DECRYPT(firstname,UNHEX(SHA2('',512))) AS stfirst,

       AES_DECRYPT(lastname,UNHEX(SHA2('',512))) AS stlast

FROM students

WHERE CONCAT(TRIM(stfirst), ' ', TRIM(stlast)) LIKE '%$result%'

AND currentyear = $currentyear

ORDER BY stlast

Am I missing something really stupid here? Thank you.

you can't use select alias name in where condition .. you should repeat the code

and if you want compare encrypted values you should also encypt the matching value

and last try using concat('%', $result,'%') instead of '%$result%'

SELECT *

  , AES_DECRYPT(firstname,UNHEX(SHA2('',512))) AS stfirst

  , AES_DECRYPT(lastname,UNHEX(SHA2('',512))) AS stlast

FROM students

WHERE CONCAT(TRIM(AES_DECRYPT(firstname,UNHEX(SHA2('',512)))), ' ', 

              TRIM(AES_DECRYPT(lastname,UNHEX(SHA2('',512))))) 

              LIKE TRIM(AES_DECRYPT( concat('%', $result,'%'),

                          UNHEX(SHA2('',512)))))

AND currentyear = $currentyear

ORDER BY stlast

Presumably, you are using MySQL. If so, MySQL extends SQL so you can use a HAVING clause with column aliases, So, you can write the query as:

SELECT s.*, 

       AES_DECRYPT(firstname,UNHEX(SHA2('', 512))) AS stfirst,

       AES_DECRYPT(lastname,UNHEX(SHA2('', 512))) AS stlast

FROM students s

WHERE currentyear = $currentyear

HAVING CONCAT(TRIM(stfirst), ' ', TRIM(stlast)) LIKE '%$result%' 

ORDER BY stlast;