[RESOLVED] Query to get data with AES_Decrypt

[Oombongo]

Hello,

I am trying to reterive data from a database. Here is my query:


SELECT snpk, snloy, snkvv, snpn, snpop, snpo, snos, AES_DECRYPT( snhk, 'encrypt' ) AS snhk, snen, snsn, AES_DECRYPT( SSN, 'encrypt' ) AS SSN FROM sibl_table
WHERE SSN = '$SSN'

Could someone please tell me what mistake I am making? I get 0 results although there's data in the table.

Thanks in advance

[NogDog]

I'm not sure what the sequence of events is, but if the WHERE clause is processed first to determine which rows to select, you may have to apply the decryption to SSN there, too, as in that case the decryption within the field list has not yet been done.

[Oombongo]

indeed, this is annoying. As long as I don't use WHERE SSN='$SSN', it works

I also tried WHERE SSN=$SSN (without quotes) and the result is the same :(

[NogDog]

What I was getting at is you may need to do...

. . . WHERE AES_DECRYPT(SSN, 'encrypt) = '$SSN'
...because the WHERE clause probably gets evaluated first to determine which rows to select, i.e. before the field list is selected.

[Oombongo]

Found the solution. Quite unusual I must say:


SELECT snpk,snloy,snkvv,snpn,snpop,snpo,snos,AES_DECRYPT(snhk,'encrypt') as snhk,snen,snsn,SSN,AES_DECRYPT(SSN,'encrypt') as SSNa FROM sibl_table WHERE SSN = AES_ENCRYPT('$SSN','encrypt')

[NogDog]

That's probably more efficient, as the variable only needs to be encrypted once. :)