On Sat, Jul 31, 2010 at 01:12:36PM -0400, Nikolaus Rath scratched on the wall:
>
> When tracing down bugs in an application that uses SQLite, I have
> repeatedly faced the problem that I wanted to send (or get) a copy of
> the sqlite database without disclosing potentially sensitive contents.
>
> I think it would be fantastic if SQLite had a build-in crypt() function,
> so that I could simply copy the database and then do
>
> UPDATE my_secret_contacts SET name=crypt(name, 'bz'), phone=crypt(phone,
> 'za');
>
> i.e., scramble all the information but keep the structure intact.
I would forget the hash all together. Just assign random data.
If you know the columns do NOT have duplicate values, or you don't
care about matching rows, this is trivial:
UPDATE my_secret_contacts SET name = hex( randomblob( 16 ) ),
phone = hex( randomblob( 16 ) );
If the columns DO have duplicate values and you want to assign the
same random hex to each instance, you need to be a bit more tricky.
You'll need to take this three step approach for each column you want
to obscure:
-- create map table of name to random data
CREATE TEMP TABLE name_rand AS
SELECT name AS name, hex( randomblob( 16 ) ) AS rand
FROM my_secret_contacts GROUP BY name;
-- overwrite each name with assigned random data
UPDATE my_secret_contacts SET name = ( SELECT rand FROM name_rand
WHERE name_rand.name = my_secret_contacts.name );
-- get rid of map data (?)
DROP TABLE name_rand;
If you're sending the database to someone else, you could even keep
the mapping table around. That way, if a specific record is found to
be causing issues, you can map it back to the original record.
Be sure you VACUUM the database after making the changes.
> Therefore I was wondering what the opinions are about including a
> crypt() function in SQLite itself. Is that a sensible idea or nonsense?
Not worth the trouble. I assume you're talking about the Unix style
crypt(). As you know, this is a one-way hash, so it cannot be used
for encrypting and recovering values, only for some forms of hash
authentication. The classic one is also extremely weak, by today's
standards, although the newer DES based one is better.
It just doesn't seem that useful for most database work. If you're
doing authentication, you shouldn't be doing that in the database.
Other edge cases, like yours, seem better serviced in other ways.
If this was to go into the core, you would want cross-platform,
cross-OS compatibility, which means putting the actual hash function
itself into SQLite, and not depending on a system library call.
So you'd be shipping crypto code. Because the SQLite project is located
in the United States, that's going to get very messy, from a legal
standpoint, especially with SQLite's not-a-license distribution.
Finally, if you just want to expose the the system crypt() function
to the SQL environment, that's a 15 minute project. Maybe an hour if
you've never written a custom SQL function before. Package it all up
into an extension, and you can drop it into any environment, no
patches required.
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Intelligence is like underwear: it is important that you have it,
but showing it to the wrong people has the tendency to make them
feel uncomfortable." -- Angela Johnson
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
