Niall Gallagher
asked on
ENCRYPTED FIELD NOT SHOWING UP IN SEARCH
I have a table with a few encrypted fields using a Encryption function. This has worked for years (since 2006) but today I did a search using the encrypt function
eg select * from table1 where encrypted_field = dbo.data_encrypt('11111111 11111111')
but it brings back no results but if I do
select dbo.data_encrypt('11111111 11111111')
which returns XYXRE!dr257itcsWEE
and then search
select * from table1 where encrypted_field = XYXRE!dr257itcsWEE
it brings back over 400 records.
We have recently changed from SQL2005 to SQL 2008. other than that I can't think of anything else. It also seems that the records are all from before we did the changeover
eg select * from table1 where encrypted_field = dbo.data_encrypt('11111111
but it brings back no results but if I do
select dbo.data_encrypt('11111111
which returns XYXRE!dr257itcsWEE
and then search
select * from table1 where encrypted_field = XYXRE!dr257itcsWEE
it brings back over 400 records.
We have recently changed from SQL2005 to SQL 2008. other than that I can't think of anything else. It also seems that the records are all from before we did the changeover
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
We found the solution after playing with the function.
Try using cast to enclosed the encrypt function to make sure that the data types match.