compare columns matching decrypted values in different tables with different encryption types

example:
table 1 = person
column = person_id (encrypted with rijndael)
column = name

table 2 = address
column = address_person_id (encrypted with hash)
column = address_line_1

I want to search for a name in table 1 in the results page would show the persons name and address from table 2. The encrypted database values would be different, but the decrypted values would be the same. How can I do this?

you shouldn't be encrypting the ID column


I can understand encrypting the name or password, but there is nothing to be gained in encrusting the ID column.

the ID column should be a numeric value, and let the database auto increment in the person table to force a unique value.

the ID column is used to look up the users information, the information can be encrypted, but the id column itself should not.

The goal in encrypting the id column was to make it more difficult to associate a persons name to their contact information. Hopefully making id theft less likely in case the database information were accessed by an unathorized individual. I thought encrypting the id columns would lead to faster load times because there would be fewer columns with less data being encrypted and decrypted. Is this not possible, or just not a good idea?

another person knowing the ID of the record in the database is not what you should be worried about, the only way this does anybody any good is if they have access to the database directly.

and in that case, you're best hope is to have the sensitive data in each of the columns encrypted.

It will not speed up the database either, if anything it will slow it down since the values will be in text. text values take up more space in the database than numbers do, thereby slowing down any indexing.

this is an ineffectual strategy for protecting data in the database.

Ok, thanks Jason. It made sense to me logically, but I didn't have a full understanding of how the process would work. Thanks for your advice, and good luck working out all the bugs in the latest updates!

you're welcome.

If I do like you suggested and encrypt the data columns rather than the id columns, will I be able to edit my search forms to search those columns? For example if the LastName column were encrypted with RIJNDAEL is it possible to create a search form that will search the last name column? If so, how much is that going to slow things down (I'm assuming it's going to have to decrypt every row in that column)?

With RIJNDAEL encryption, this is problematic because like you say you have decrypt in the database to be able to search, you may want to see this thread at stack overflow.
http://dba.stackexchange.com/questions/23908/how-to-search-a-mysql-database-with-encrypted-fields

to be honest, i think it is a little over board to encrypt columns like first or last name.

I can see encrypting the street address, that is a lot more personal than the first and last name.

when it comes to encryption, it shouldn't be applied as a blanket for the entire disabuse, but used sparingly on data that is truly private, and you need to weigh tradeoffs like the ability to search,