searching for encrypted fields in data columns

java-programmer, searching for encrypted fields in data columns I am new to database programming and was curious how others solve the
problem of storing encrypted in data in db table columns and then
subsequently searching for these records.
The particular problem that I am facing is in dealing with (privacy)
critical information like credit-card #s and SSNs or business critical
information like sales opportunity size or revenue in the database. The
requirement is that this data be stored encrypted (and not in the
clear). Just limiting access to tables with this data isn't sufficient.
Does any database provide native facilities to store specific columns as
encrypted data ? The other option I have is to use something like RC4 to
encrypt the data before storing them in the database.

However, the subsequent problem is how do I search/sort on these columns
? Its not a big deal if I have a few hundred records; I could
potentially retrieve all the records, decrypt the specific fields and
then do in process searches/sorts. But what happens when I have (say) a
million records - I really don't want to suck in all that data and work
on it but instead use the native db search/sort capabilities.

Any suggestions and past experiences would be greatly appreciated.

much thanks,
~s

java-programmer >> searching for encrypted fields in data columns Google in the Microsoft newsgroups and you'll find references to various
encryption products for SQLServer.

Why is encryption a requirement for your application? Encryption is for
authentication and for secure communication in an insecure environment.
Assuming the database server is located in a physically secure location and
assuming you can use an encrypted network protocol what extra security
benefit do you expect to gain from encryption in the database? Encrypted or
not the data will still (only) be secured by an access control mechanism of
some sort (a user name and password?). Why would access control be more
secure if the data is encrypted than if it isn't?

If you want to ensure privacy on an individual basis (each user has access
only to his/her own data) then encryption might make sense but in that case
you wouldn't expect to do global searches.

--
David Portas
SQL Server MVP

java-programmer >> searching for encrypted fields in data columns
<email***@***.com> wrote in message news:email***@***.com...

> Does any database provide native facilities to store specific columns as
> encrypted data ? The other option I have is to use something like RC4 to
> encrypt the data before storing them in the database.
>
> However, the subsequent problem is how do I search/sort on these columns
> ? Its not a big deal if I have a few hundred records; I could
> potentially retrieve all the records, decrypt the specific fields and
> then do in process searches/sorts. But what happens when I have (say) a
> million records - I really don't want to suck in all that data and work
> on it but instead use the native db search/sort capabilities.
>
>
PointBase is a Java database that supports data encryption in a way that is
transparent to the user. It supports several encryption algorithm
implementations you can choose from. You just use a database parameter to
set the algorithm and the key and data encryption/decryption is handled with
no futher attention from you. If you use the Server version, you can also
encrypt the communication between the client and the server.

java-programmer >> searching for encrypted fields in data columns On Mon, 01 Mar 2004 00:22:55 -0800, email***@***.com wrote:

>I am new to database programming and was curious how others solve the
>problem of storing encrypted in data in db table columns and then
>subsequently searching for these records.
>The particular problem that I am facing is in dealing with (privacy)
>critical information like credit-card #s and SSNs or business critical
>information like sales opportunity size or revenue in the database. The
>requirement is that this data be stored encrypted (and not in the
>clear). Just limiting access to tables with this data isn't sufficient.
>Does any database provide native facilities to store specific columns as
>encrypted data ? The other option I have is to use something like RC4 to
>encrypt the data before storing them in the database.
>
>However, the subsequent problem is how do I search/sort on these columns
> ? Its not a big deal if I have a few hundred records; I could
>potentially retrieve all the records, decrypt the specific fields and
>then do in process searches/sorts. But what happens when I have (say) a
>million records - I really don't want to suck in all that data and work
>on it but instead use the native db search/sort capabilities.
>
>Any suggestions and past experiences would be greatly appreciated.
>
>much thanks,
>~s

If you use PKI-style encryption, you can make the encryption key public, and
the decryption key private. This way, you can encrypt a card number, then do
a search for a match on that value. Since the card number will encrypt
identically each time, the search will match.

Of course, a fundamental problem with such a system is that, given the limited
number of valid card numbers relative to the number your database is likely to
contain, a constructing s brute force attack on such a system would not be
hard. This would be true of any system that allows searching for a card
number, no matter how it actually worked. This situation could be improved if
other key information was also encrypted so that even if a match were found,
the other necessary card owner information would remain unknown.

java-programmer >> searching for encrypted fields in data columns I am by no means as experienced as the rest in the DB area being a
programmer in mostly web apps but.... I needed to store usernames and
passwords in our db in some kind of secure mannner. I understand that if you
just encrypt the password a hacker can use brute force to easily (not to me
of course) find out passwords. The solution was to use a combination of the
username and password, encrypt this using a key compiled into our binary and
store this in the db. Do this whenever the user logs in and send the binary
data to the db for comparison with the encrypted password in the db. I guess
the thinking is that the hacker would have a harder time finding the
password if it is made of a combination of values before encryption. One
advantage is that no-one knows the key in our binary, only encrypted
passwords are passed on the web, and access to the db doesn't display the
password in a usable format. You could use this thinking to possibly create
an encryption from the username, password and credit card number? I don't
know hacker methods and don't know if this is the best way, or if you have
the ability to encrypt using the username, password, card... but just food
for thought.
<email***@***.com> wrote in message news:email***@***.com...
> I am new to database programming and was curious how others solve the
> problem of storing encrypted in data in db table columns and then
> subsequently searching for these records.
> The particular problem that I am facing is in dealing with (privacy)
> critical information like credit-card #s and SSNs or business critical
> information like sales opportunity size or revenue in the database. The
> requirement is that this data be stored encrypted (and not in the
> clear). Just limiting access to tables with this data isn't sufficient.
> Does any database provide native facilities to store specific columns as
> encrypted data ? The other option I have is to use something like RC4 to
> encrypt the data before storing them in the database.
>
> However, the subsequent problem is how do I search/sort on these columns
> ? Its not a big deal if I have a few hundred records; I could
> potentially retrieve all the records, decrypt the specific fields and
> then do in process searches/sorts. But what happens when I have (say) a
> million records - I really don't want to suck in all that data and work
> on it but instead use the native db search/sort capabilities.
>
> Any suggestions and past experiences would be greatly appreciated.
>
> much thanks,
> ~s
>

java-programmer >> searching for encrypted fields in data columns nbnet wrote:

> I am by no means as experienced as the rest in the DB area being a
> programmer in mostly web apps but.... I needed to store usernames and
> passwords in our db in some kind of secure mannner. I understand that if you
> just encrypt the password a hacker can use brute force to easily (not to me
> of course) find out passwords.

Use Oracle's built-in obfuscation toolkit and the only ones breaking
it by brute force will be NSA, CIA, and FBI or their foreign equivs.

You can look it up at http://tahiti.oracle.com

But if you think it is easy to break even simple encryptions try
your luck on this one:

x := 'p78o 8o 0 o42i4p';
SELECT TRANSLATE(x,'?????', '?????')
FROM dual;

and I'm even giving you the code required to do it. Just replace
each of the five question marks with the correct number of the
correct characters.

Answer is available at:
http://www.psoug.org/reference/translate_replace.html

As simplistic as this example is ... do you really think any of
your users could break it?
--
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
email***@***.com
(replace 'x' with a 'u' to reply)

java-programmer >> searching for encrypted fields in data columns > advantage is that no-one knows the key in our binary, only encrypted

Except someone who (legitimately or illegitimately) has the binary and can
therefore hack *any* users password... Or someone who hacks the users
machine... Or hostile code on the users machine... etc, etc.

Use a secure hash for this type of authentication security.

--
David Portas
SQL Server MVP

java-programmer >> searching for encrypted fields in data columns "David Portas" <email***@***.com> wrote in message news:<email***@***.com>...
> > advantage is that no-one knows the key in our binary, only encrypted
>
> Except someone who (legitimately or illegitimately) has the binary and can
> therefore hack *any* users password... Or someone who hacks the users
> machine... Or hostile code on the users machine... etc, etc.

The binary does not have to be on an accessible machine. It can reside
in the same database as the encrypted data.

With an Oracle, Informix or DB2 database, it's possible to embed Java
classes in the database because the servers include a Java VM.