Pfthb

I have a table in sqlserver, my table has a field as string, for example FirstName (it's just example!). This field "EncryptByPassPhrase" before insert on database.

Before encode: Ali
After encode: 0x010000001D905174BB7947AE1C600A4AB564A123310F92C21C9A4221

Now i would search on this field for example i would get all fields that contains charcters 'Al'.

I can decode this field and search on such as:

select * from EncodeTest e
where dbo.Decoder(e.FirstName) like '%Al%'

But this way very slow on many numbers of record.
Please help me for best solution.

Thanks.

What I have tried:

declare @EncodedName varbinary(max)
select @EncodedName =EncryptByPassPhrase('key', cast( @Name as varbinary(max)) )


declare @DecodedName nvarchar(max)
select @DecodedName=convert(varchar(100),DecryptByPassPhrase('key',cast( @EncodedName as varbinary(max)) ))

select * from EncodeTest e
where dbo.Decoder(e.FirstName) like '%Al%'

Encryption (security in general) is a compromise with convenience. If you want to be secure, you must sacrifice the easy of access. If you want to be easy accessible and fast, then you must sacrifice security. Your job as developer is to find the right balance between these. In your case, obviously the server need to decrypt all of your data to be able to search for the text you want. If you need to search in encrypted data, then your design is broken. Either find another way to protect your data, or give up searching it efficiently.

I would say, why are you encrypting names? The database already has security layers to protect your data (Transparent Data Encryption, database users rights, etc.) which will protect the data from unauthorized access. You should encrypt only really sensitive information, such credit card numbers, social security numbers and its equivalents, etc. Abusing with encryption by using it for every single field (or relatively harmless fields like names) will make your application quite inconvenient and usually doesn't worth the efforts.