• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 473
  • Last Modified:

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('1111111111111111')
but it brings back no results but if I do
select dbo.data_encrypt('1111111111111111')
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
0
Niall292
Asked:
Niall292
  • 2
1 Solution
 
arnoldCommented:
What is the data type for the column?
Try using cast to enclosed the encrypt function to make sure that the data types match.
0
 
Niall292Author Commented:
We finally found the problem for some reason when we moved to SQL 2008 the encrytion was putting a line feed after the encryption, I can't explain why and don't know if this is a bug or not but that was my issue and maybe it might give somebody some help if they have the same problem.
0
 
Niall292Author Commented:
We found the solution after playing with the function.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now