Not difficult at all. Here's a walkthrough:


I have a table that looks like this:
SQL> show table tbl_person;
INT_PERSON INTEGER Not Null
STR_NAME VARCHAR(100) Nullable
DTM_DOB DATE Nullable
STR_CCARD VARCHAR(2000) Nullable
CONSTRAINT INTEG_2:
Primary key (INT_PERSON)
SQL>

You'll notice that str_ccard is a big varchar, and NOT a char(16) - just
enough for the ccard number

I'll do the encryption in PHP, as it free, and everyone should be using
it...;-)


----------- Begin listing of 'secure_test.php' -----------------
#!/usr/local/bin/php -e
<?php

// Two very handy functions - put them in your include file someday!
function db_encrypt($string,$key) {
srand((double) microtime() * 1000000); //for sake of MCRYPT_RAND
$key = md5($key); //to improve variance
/* Open module, and create IV */
$td = mcrypt_module_open('des', '','cfb', '');
$key = substr($key, 0, mcrypt_enc_get_key_size($td));
$iv_size = mcrypt_enc_get_iv_size($td);
$iv = mcrypt_create_iv($iv_size, MCRYPT_RAND);
/* Initialize encryption handle */
if (mcrypt_generic_init($td, $key, $iv) != -1) {

/* Encrypt data */
$c_t = mcrypt_generic($td, $string);
mcrypt_generic_deinit($td);
mcrypt_module_close($td);
$c_t = $iv.$c_t;
return $c_t;
} //end if
}

function db_decrypt($string,$key) {
$key = md5($key); //to improve variance
/* Open module, and create IV */
$td = mcrypt_module_open('des', '','cfb', '');
$key = substr($key, 0, mcrypt_enc_get_key_size($td));
$iv_size = mcrypt_enc_get_iv_size($td);
// Obtain the iv from the beginning of the string
$iv = substr($string,0,$iv_size);
$string = substr($string,$iv_size);
/* Initialize encryption handle */
if (mcrypt_generic_init($td, $key, $iv) == -1) {
// Non-encrypted data - just return it untouched
return $string;
} else {
/* Decrypt data */
$c_t = mdecrypt_generic($td, $string);
mcrypt_generic_deinit($td);
mcrypt_module_close($td);
return $c_t;
} //end if
}

// Set up your secret key, and the text you wish to encrypt
$key = "4 v4ry 53cur4 k3y";
$cleartext = "1234 5678 9012 3456";

// Connect to the database
if(!$conn =
ibase_connect("localhost:/u1/db/temp.fdb","sysdba","masterkey")){
echo "Could not connect. Aborting\n";
} else {

// Prepare the SQL string
$sql = "INSERT INTO tbl_person (int_person, str_name, str_ccard) VALUES
(gen_id(gen_tbl_person,1), 'Test Person', '".
str_replace("'","''",db_encrypt($cleartext,$key))."')";

// Echo out the insert string
echo "SQL for insert: ".$sql."\n";

// Run the query, inserting the record
$rec = ibase_query($sql);

// Query the table, and get the info back out
$sql = "SELECT int_person, str_name, str_ccard FROM tbl_person";
$rec = ibase_query($sql);

// Rattle thourhg the records, decrypting as we go.
while($obj = ibase_fetch_object($rec)){
echo $obj->INT_PERSON.", ".$obj->STR_NAME.", ".$obj->STR_CCARD."\n";

echo "Decrypted: ".db_decrypt($obj->STR_CCARD,$key)."\n";

} // End of while loop
} // End of successful connect



----------- 'End of listing of secure_test.php' ------------

It's output looks like:

dungeon:~/test> ./secure_demo.php
SQL for insert: INSERT INTO tbl_person (int_person, str_name, str_ccard)
VALUES (gen_id(gen_tbl_person,1), 'Test Person', '93+nN,<ui?@uO)?"
T')
13, Test Person, 93+nN,<ui?@uO)?"
T
Decrypted: 1234 5678 9012 3456



dungeon:~/test>

> -----Original Message-----
> From: firebird-support@yahoogroups.com
> [mailto:firebird-support@yahoogroups.com]On Behalf Of Mitchell Peek
> Sent: Wednesday, 4 January 2006 9:10 AM
> To: firebird-support@yahoogroups.com
> Subject: [firebird-support] data encryption
>
>
> I proposed this question as a reply to an old thread,
> throught it might
> have been lost due to the age of the thread, so decided to
> re-intorduce
> it in its own. Sorry for the redundancy...
>
> someone suggested:
>
> >You may need to store things like credit card numbers or passwords in
> >the database. DBAs have full access to the database, and may need it,
> >but you don't want them to know this information. If you need to get
> >the information back out, then use encryption (blowfish etc)
> and store
> >the encrypted data only.
> >
>
>
> Any suggestions on the best means of implementing encryption
> (any, not necessarily blowfish) of this sort? UDF? trigger?
> etc? How does one go about implementing such a thing and
> making this workable?
>
> Thanks,
> Mitchell Peek
>
>
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://firebird.sourceforge.net and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>
>
>
>
>
> __________ NOD32 1.1350 (20060103) Information __________
>
> This message was checked by NOD32 antivirus system.
> http://www.eset.com
>
>