Encrypting CLOBS With DBMS_CRYPTO Sucks!

Recently I had to project where I needed to encrypt the contents of clobs (character large object) inside an oracle table. I could not for the life of me figure out how to take a clob, encrypt it, and end up with clob with encrypted data in it. For some stupid reason, Oracle takes your clob, and gives you back a blob (binary large object). I looked all over the internet and I could not find a comprehensive solution to my problem, so I turned to Oracle support for help. Since there are probably others that will struggle with encrypting and decrypting clobs, the following is our solution.
------------------------------------
CREATE OR REPLACE
PROCEDURE ENCRYPT_CLOB
( intval IN tst_clob_tbl.id_col%type)
IS
-- This proc gets a clob out of a table, encrypts it, and then puts it back...
CLOB1 clob; -- select from table
CLOB2 clob; -- temporary clob
BLOB1 BLOB; -- temporary blob
l_key RAW(128); -- encryption key
Amount number := 4000;

Buffer_in RAW(32000);
Buffer_out varchar2(32000); -- allow for expansion
len number;
l number;
steps number;
left number;
chunksize number;
chunksize_hex varchar2(4);

offset number;

BEGIN

-- create temporary clobs and blobs
DBMS_LOB.CREATETEMPORARY(BLOB1, TRUE, DBMS_LOB.CALL);
DBMS_LOB.CREATETEMPORARY(CLOB2, TRUE, DBMS_LOB.CALL);
DBMS_LOB.OPEN(BLOB1, DBMS_LOB.LOB_READWRITE);
DBMS_LOB.OPEN(CLOB2, DBMS_LOB.LOB_READWRITE);

--create encryption key
l_key := utl_raw.cast_to_raw('abcdefgh');

-- get the clob out of tst_clob_tbl, use the id that was passed in
select clob_value into CLOB1
from tst_clob_tbl where id_col = intval;

--encrypt the clob
sys.dbms_crypto.encrypt(dst => BLOB1,
src=> CLOB1,
typ => dbms_crypto.des_cbc_pkcs5,
key => l_key);

-- encrypted data is now in BLOB1...but hey, I really wanted a clob

len := DBMS_LOB.GETLENGTH(BLOB1);
dbms_output.put_line('lenght encrypted blob : '||len);
steps := floor(len/amount);
left := mod(len,amount); -- this is the remainder chunk smaller than amount

-- loop though this bad boy and encode the chars back to 64 bit...ughh
for i in 1..steps loop
offset := 1+((i-1)*amount);
buffer_in := dbms_lob.substr(BLOB1,amount,offset);

buffer_out:=utl_raw.cast_to_varchar2(utl_encode.base64_encode(buffer_in));

chunksize := length(buffer_out);

chunksize_hex := lpad(trim(to_char(chunksize,'XXXX')),4,'0000');
dbms_output.put_line(chunksize||' = '||chunksize_hex);
dbms_lob.writeappend(lob_loc => CLOB2, amount=> 4, buffer => chunksize_hex);
dbms_lob.writeappend(lob_loc => CLOB2, amount=>chunksize, buffer => buffer_out);

end loop;
if left > 0 then
buffer_in := dbms_lob.substr(BLOB1,left,len-left+1); -- last piece

buffer_out:=utl_raw.cast_to_varchar2(utl_encode.base64_encode(buffer_in));

chunksize := length(buffer_out);

chunksize_hex := lpad(trim(to_char(chunksize,'XXXX')),4,'0000');
dbms_output.put_line(chunksize||' = '||chunksize_hex);

dbms_lob.writeappend(lob_loc => CLOB2, amount=> 4, buffer => chunksize_hex);
dbms_lob.writeappend(lob_loc => CLOB2, amount=>chunksize, buffer => buffer_out);

end if;
-- write terminator
buffer_out := 'FFFF';
dbms_lob.writeappend(lob_loc => CLOB2, amount => 4, buffer => buffer_out);

-- Ohh man what a bunch of work. I wish the crypto package would return clob...thanks oracle

-- store the encrypted clob back where it came from.
update tst_clob_tbl set clob_value=CLOB2 where id_col= intval;
commit;

-- clean up your mess
DBMS_LOB.FREETEMPORARY(CLOB2);
DBMS_LOB.FREETEMPORARY(BLOB1);
end;
/


CREATE OR REPLACE
PROCEDURE DECRYPT_CLOB
( intval IN tst_clob_tbl.id_col%type)
IS
-- This proc gets a clob out of a table, decrypts it, and then puts it back...
CLOB1 clob; -- select from table
CLOB2 clob; -- temporary clob
BLOB1 BLOB; -- tempory blob
l_key RAW(128); -- encryption key
v_amount PLS_INTEGER; -- amount for conversion
Buffer_out RAW(16000);
Buffer_in varchar2(32000); -- allow for expansion
len number;
l number;
steps number;
left number;

v_offset number;
chunksize number;
chunksize_hex varchar2(4);

v_done boolean := false;


BEGIN

-- create temporary clobs and blobs
DBMS_LOB.CREATETEMPORARY(BLOB1, TRUE, DBMS_LOB.CALL);
DBMS_LOB.CREATETEMPORARY(CLOB2, TRUE, DBMS_LOB.CALL);
DBMS_LOB.OPEN(BLOB1, DBMS_LOB.LOB_READWRITE);
DBMS_LOB.OPEN(CLOB2, DBMS_LOB.LOB_READWRITE);

--create encryption key
l_key := utl_raw.cast_to_raw('abcdefgh');

-- get the clob out of tst_clob_tb, use the id that was passed in
select clob_value into CLOB1
from tst_clob_tbl where id_col = intval;

-- encrypted data is now in CLOB1 but I cant pass clob in to decrypt so make it a blob
-- did I mention how much I hate dbms_crypto?

v_offset:=1;

-- loop through and decode the chars so they will fit in a blob.
while not v_done loop

chunksize_hex := dbms_lob.substr(lob_loc => CLOB1,amount => 4,offset => v_offset);
chunksize := to_number(chunksize_hex,'XXXX');

if chunksize_hex <> 'FFFF' then

v_offset:=v_offset+4;

buffer_in := dbms_lob.substr(lob_loc => CLOB1,amount => chunksize, offset => v_offset);
v_amount:=length(buffer_in);

buffer_out := utl_encode.base64_decode(utl_raw.cast_to_raw(buffer_in));

v_amount:=utl_raw.length(buffer_out);
begin

dbms_lob.writeappend(lob_loc => BLOB1,
amount => v_amount,
buffer => buffer_out);
exception
when others then
dbms_output.put_line('error in dbms_lob.writeappend');
dbms_output.put_line('v_amount : '||v_amount);
raise;
end;
v_offset := v_offset+chunksize;
else
v_done := true;
dbms_output.put_line('done, found terminator');
end if;
end loop;

len := DBMS_LOB.GETLENGTH(BLOB1);
dbms_output.put_line('lenght encrypted blob : '||len);

-- now that that is over...we can get back to the task at hand
-- decrypt the clob

sys.dbms_crypto.decrypt(DST => CLOB2,
SRC => BLOB1,
typ => dbms_crypto.des_cbc_pkcs5,
key => l_key);

-- Put it back where you got it.
update tst_clob_tbl set clob_value = CLOB2 where id_col=intval;
commit;
-- clean up your mess
DBMS_LOB.FREETEMPORARY(CLOB2);
DBMS_LOB.FREETEMPORARY(BLOB1);

end;
/
------------------------------------

Technorati Tags: Oracle
DBMS_CRYPTO
CLOB
BLOB

Lessons Not Learned

Apparently I haven’t learned my own lesson. Fortunately, I did not have as many emails to delete this time.




Technorati Tags: perl script
sendmail
inbox
email
error checking

Planned Parenthood Wireless

Now you can get out of your old wireless contract for free, get a free phone, and help murder helpless unborn babies all at the same time!

Planned Parenthood Wireless is a new choice for your cell phone service. By signing up for this service, you will help preserve reproductive rights, and ensure access to comprehensive family planning and medically accurate sex education for women and families around the world. You'll do something you do every day – talk on your cell phone – and you'll be helping Planned Parenthood as 10% of all monthly charges goes to Planned Parenthood Federation of America, at no extra cost to you.

Planned Parenthood Wireless is powered by Working Assets, the progressive phone company that delivers quality phone service using the all-digital nationwide Sprint® network.

<sarcasm>I know when I see a good deal, and this is it. Click here to sign up.</sarcasm> *


*My wife thinks my sarcasm is sometimes less than obvious, so I added these droll sarcasm tags...just in case.


Technorati Tags: Planned Parenthood
abortion
Sprint

An Interesting Agenda Item

I don’t get to make it to as many Tulsa City Council committee meetings as I’d like but this Tuesday I Think I’ll make the time. There is one item on the Urban & Economic Development committee agenda that I’m particularly interested in. (warning PDF)

Stephen Steven Roemerman - Appointment to the Sales Tax Overview Committee; term expires 12/31/2010; (replaces Ben Furlong). [UED 2/27/07] 07-162-1

The misspelling of my first name aside, I’m happy to announce that I’ve been asked to serve on the Sales Tax Overview Committee. It is an exciting opportunity, and if approved, I’ll look forward to serving Tulsa by keeping an eye on our sales tax dollars.


Technorati Tags: Tulsa
Tulsa City Council
STOC

Want to go grab a bagel?

For the past 2 days our company’s network has been hammered variants of the W32.IRCBot , and the W32.Spybot worms. It has so crippled our network that I cant get anything done. “The network’s down, want to go grab a bagel?”

Technorati Tags: worm
downtime
bagel

Discounting Evil

My Pastor has been preaching a series on the end times, and has frequently quoted Joel Rosenberg’s book Epicenter: Why the current rumblings in the Middle East will change your future. So I decided to pick up a copy, and I’m 4 chapters in. So far I’m completely fascinated by what Rosenberg has to say. Based on what I’ve already read, and the quotes from my pastor’s sermon, I already highly suggest this book.

I was particularly struck with what he had to say about the "experts" being blindsided by both Saddam’s invasion of Kuwait in 1990 and of the attacks on 9/11.

Rosenberg had written a fictional thriller about terrorists who used planes as weapons. In fact he was working on the second to last chapter on 9/11/2001! When asked how he could have anticipated that kind of attack when so many could not, he said it was not so much a failure of intelligence but imagination. “The nature of the attacks that would be carried out against the United States was beyond their ability to imagine. They had the dots, they simply could not connect them, at least not in time."

In 1990 he was struck by the failure of all the experts to see what would happen in Kuwait. All the evidence was there, most of it came directly from Saddam’s mouth. He said he was going to invade Kuwait, but no one believed he would actually do it.

Too many in Washington today have a modern, Western, secular mind-set that either discounts—or outright dismisses—the fact that evil is a real and active force in history. They insist on interpreting events only though the lenses of politics and economic. Yet to misunderstand the nature and threat of evil is to risk being blindsided by it, and that is precisely what happened on August 2, 1990, and September 11, 2001. Washington was blindsided by an evil it did not understand, just as it had been blindsided by Auschwitz, Dachau, and Perl Harbor, and much as I believe it will be blindsided by future events.

Isn’t interesting how quickly our culture wants to dismiss evil, how they want to deny its very existence? Right and Wrong, Good and Evil have been replaced with Politically Correct and Politically Incorrect. However, when we deny the existence of evil, as Rosenberg points out, there is a very real danger that we will be blindsided by it.


Technorati Tags: Joel Rosenberg
Epicenter

The Book Meme

I’m playing along with Bowden McElroy at Counseling Notes,

Hear are the Book meme rules:
1. Grab the book closest to you.
2. Open to page 123; go down to the fourth sentence.
3. Post the text of the following three sentences.
4. Name the author and book title.
5. Tag three people to do the same.

This is a small but vital component of the communication system. It keeps our main antenna aimed at Earth to with in a few thousands of a degree. This accuracy is required, since at our present distance of more than seven hundred million miles, earth is only a rather faint star, and our very narrow radio bean could easily miss it.

2001: A Space Odyssey by Arthur C. Clark

Prey by Michael Crichton was equidistant, and actually the first book I grabbed but the forth sentence had blasphemy in it so…you get 2001.


Technorati Tags:

Roasting Impressions

It has been a little over a month since I purchased a home coffee roaster, and I've made an interesting observation over the course of this last month.

My entire coffee scale has been thrown off. I put coffee in one of 4 categories, really good, good, passable, horrible. Since I've been roasting my own coffee every thing has been pushed down one notch(except what I thought was horrible...it still is). What was really good is now just good, what was good is now only passable, and what was passable joins the unfortunate crowd of horrible coffees. A few weeks ago I finally drank my first cup of coffee that I did not roast. I had had it before and I had previously thought it was passable, but after my first drink I wanted to spit it out. "Ughh, this is awful!"

I had been warned about this, during my research prior to entering the world of home roasting. "It will ruin coffee for you; you wont enjoy regular coffee anymore!" Now it is not as bad as all that, but I have been shocked at the difference I've noticed. But I don't think it is just me being a snob. I've shared this coffee with people at work and they all think it is really good. Even a colleague that didn't think he liked coffee thinks my home brew is really great.

Technorati Tags: coffee
roasting coffee
diy

Do Gene Patents Spur Innovation?

According to Michael Crichton, no.

Gene patents are now used to halt research, prevent medical testing and keep vital information from you and your doctor. Gene patents slow the pace of medical advance on deadly diseases. And they raise costs exorbitantly…the holder of the gene patent can charge whatever he wants, and does. Couldn’t somebody make a cheaper test? Sure, but the patent holder blocks any competitor’s test. He owns the gene…Ordinarily, we imagine patents promote innovation, but that’s because most patents are granted for human inventions. Genes aren’t human inventions, they are features of the natural world. As a result these patents can be used to block innovation, and hurt patient care.

Technorati Tags: Michael Crichton
gene patents

Let’s Clean Our Room

Getting the four-year-old and the two-year-old to clean their room has, in the past been quite the chore for the entire household. The hands-off-approach has not worked because nothing gets done. Micromanagement hasn’t worked because not only do I not like to be a micro manager, but the kids don’t really like to be bossed around. So we came up with a new strategy. The girls and I agreed on all the steps required to clean their room and I used those steps to make this check list.

When the girls believe they have completed a task, we inspect their progress and check the box if appropriate. When all of the tasks are complete, I vacuum and mark off the last box. It seems to work great. Everyone seems to like the new way of doing things. Before we even start, the girls know what needs to be accomplished and can take ownership for each task. They seem to think it is fun, I don’t have to nag or yell; but more importantly, the room gets cleaned.


Technorati Tags: parenting