Home » SQL & PL/SQL » SQL & PL/SQL » Decrypt PDF file
Decrypt PDF file [message #332582] Wed, 09 July 2008 01:17 Go to next message
pmapc
Messages: 46
Registered: July 2008
Member
hi,

I was wondering is there any way to decrypt pdf files which has been already encrypted by another application and know are save in our oracle DB as BLOB fields?

I have spent some time with Decrypt procedure with no success because of getting "ORA-01405: fetched column value is NULL" error message :

PROCEDURE Decrypt (dst IN OUT NOCOPY BLOB,
src IN BLOB,
typ IN PLS_INTEGER,
key IN RAW,
iv IN RAW DEFAULT NULL);

I realy don't know is this error occure becuase type of my file which is PDF or is it because other resoans?

Thanks in advance,
Re: Decrypt PDF file [message #332585 is a reply to message #332582] Wed, 09 July 2008 01:36 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
Please see the posting guidelines and format your post first
Re: Decrypt PDF file [message #332587 is a reply to message #332582] Wed, 09 July 2008 01:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As this is YOUR procedure and YOUR code and YOUR call and YOU don't to share any of these, YOU are On Your Own (YOYO).

Regards
Michel
Re: Decrypt PDF file [message #332595 is a reply to message #332585] Wed, 09 July 2008 02:07 Go to previous messageGo to next message
pmapc
Messages: 46
Registered: July 2008
Member
At first I read the encrypted pdf file from server and then put it to BLOB_COLUMN table and use this process to decrypt it:
DECLARE
  enCrypBlob  BLOB;
   DecrypBlob  BLOB;
   DecrypRaw   RAW(1000);
   deCrawlen   BINARY_INTEGER;
   leng        INTEGER;
   l_Key       RAW(128);
BEGIN
  l_Key := utl_Raw.Cast_To_Raw('XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
  
  SELECT Blob_Column
  INTO   enCrypBlob
  FROM   Blob_Content
  WHERE  Id = 22;
  
  dbms_Crypto.Decrypt(DecrypBlob,enCrypBlob,dbms_Crypto.aes_cbc_pkcs5,
                      l_Key,HexToraw('00000000000000000000000000000000'));
END;

version: 10.2.0.1.0

Thanks,

[Mod-Edit: Frank added code-tags to improve readability]

[Updated on: Wed, 09 July 2008 03:42] by Moderator

Report message to a moderator

Re: Decrypt PDF file [message #332603 is a reply to message #332595] Wed, 09 July 2008 02:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As it has been requested, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: Decrypt PDF file [message #332618 is a reply to message #332595] Wed, 09 July 2008 03:43 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
As you can see, I edited your post. I surrounded your code with [code] and [/code] tags. It conserves indentation and uses a non-proportional font, which makes it easier to read.
Please do this yourself from now on.
Re: Decrypt PDF file [message #332634 is a reply to message #332595] Wed, 09 July 2008 04:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you don't post what you REALLY did with the REAL code, we can't help.

Regards
Michel
Re: Decrypt PDF file [message #332650 is a reply to message #332634] Wed, 09 July 2008 05:03 Go to previous messageGo to next message
pmapc
Messages: 46
Registered: July 2008
Member
Thank for all replies and corrections. I don't know which part of my question is unclear.I ask it in other words, maybe this time someone give me some advices or clues:

How can I decode a Blob file in oracle and apex environment?
I have some PDF files which has been encrypted using AES-256 in an .NET application. These are the input of my new application in oracle and my application need to decrypt these files before show them to users.

I am new in oracle.I am seeking for a procedure or package or anything else that help to DECRYPT a FILE.

Any help would be highly appreciated.
Re: Decrypt PDF file [message #332653 is a reply to message #332650] Wed, 09 July 2008 05:13 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Since we have no way of knowing how the .net application ENcrypted them, there is no way of telling how they should be DEcrypted.

Talk to the person who wrote the .net application, that's the only one who might be able to help you.

Re: Decrypt PDF file [message #332654 is a reply to message #332650] Wed, 09 July 2008 05:14 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Have a look at this
Re: Decrypt PDF file [message #332672 is a reply to message #332653] Wed, 09 July 2008 06:08 Go to previous messageGo to next message
pmapc
Messages: 46
Registered: July 2008
Member
Dear ThomasG:
there is no need to know the way of their encrption because AES- 254 a STANDARD algorithm for encryption which should be the same in all platforms. we just need to know the method of encryption that is AES256.

Dear tahpush:
I have used this procedure as I told in my first post in this thread.But it seems we can not use this procedure for FILE decryption. I just could use it for decption a string which has been encrpted by AES256.

Re: Decrypt PDF file [message #332677 is a reply to message #332672] Wed, 09 July 2008 06:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
there is no need to know the way of their encrption because AES- 254 a STANDARD algorithm for encryption which should be the same in all platforms.

Algorithm is the same but there are many parameters that modifies the result, otherwise it would not be encryption.
If you read DBMS_CRYPTO documentation you'll see how parameters modify the result for the same passphrase.

Regards
Michel
Re: Decrypt PDF file [message #332690 is a reply to message #332677] Wed, 09 July 2008 06:41 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Can you show us what line you are getting you are getting the error at?
An ORA-1405 is usually returned by an OCI package when it fetches a null value from the database and doesn't handle it properly.
Re: Decrypt PDF file [message #332692 is a reply to message #332677] Wed, 09 July 2008 06:42 Go to previous messageGo to next message
pmapc
Messages: 46
Registered: July 2008
Member
declare
srcblob blob;
encrypblob blob;

begin

select BLOB_COLUMN into srcblob from BLOB_CONTENT where id = 50;
DBMS_CRYPTO.Encrypt(encrypblob,
            srcblob,
            DBMS_CRYPTO.AES_CBC_PKCS5,
            hextoraw 
('000102030405060708090A0B0C0D0E0F101112131415161718191A1B1C1D1E1F'),
            hextoraw('00000000000000000000000000000000'));
insert into blob_content (id, blob_column) values(500,encrypblob);

end;


I know the parameter are different and have difference effects, as test case I even can not encrypt my blob content using this procedure[look at the sample code above]. this is my error report:

Error report:
ORA-01405: fetched column value is NULL
ORA-06512: at "SYS.DBMS_CRYPTO_FFI", line 26
ORA-06512: at "SYS.DBMS_CRYPTO", line 20
ORA-06512: at line 8
01405. 00000 -  "fetched column value is NULL"
*Cause:    
*Action:


I am sure that the column is not null, because I can see its data.
Re: Decrypt PDF file [message #332703 is a reply to message #332692] Wed, 09 July 2008 07:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Are you sure srcblob contains something?

Regards
Michel
Re: Decrypt PDF file [message #332710 is a reply to message #332692] Wed, 09 July 2008 07:27 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The first problem with this is that the destination BLOB is not initialised.
There are other problems after that, but they're mostly to do with the structure of this example.
Here's some working code:
drop table blob_content;

create table blob_content (id number, blob_column blob);

insert into blob_content values (50,rawtohex('alksjalksjlakjslakjs'));

declare
srcblob blob;
encrypblob blob;

begin

dbms_lob.createtemporary(encrypblob,true);

select BLOB_COLUMN into srcblob from BLOB_CONTENT where id = 50;

if srcblob is null then
  raise no_Data_found;
end if;

DBMS_CRYPTO.Encrypt(encrypblob,
                    srcblob,
                    DBMS_CRYPTO.ENCRYPT_AES256 + DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5,
                    hextoraw('000102030405060708090A0B0C0D0E0F101112131415161718191A1B1C1D1E1F')
);

insert into blob_content (id, blob_column) values(500,encrypblob);

end;
Re: Decrypt PDF file [message #332933 is a reply to message #332710] Thu, 10 July 2008 02:45 Go to previous messageGo to next message
pmapc
Messages: 46
Registered: July 2008
Member
JRowbottom, thank you for reply. you are right.I fixed my problem in this step but I encountered with another one.
I a procedure to download my files form a blob column in DB
download_file(v_mime IN VARCHAR2,v_file_name IN VARCHAR2,p_file in number)
or
download_file('application/pdf','test.pdf',3);

I also have 2 records in my BLOB_CONTENT table, one of them is content of pdf file without any decryption or encryption and other one is the content of a same encrypted/decrypted pdf file using this piece of code(I worked on the same PDF file in each step):
insert into blob_content values (3,rawtohex('alksjalksjlakjslakjs'));

declare
srcblob blob;
encrypblob blob;
decrypblob blob;

begin

dbms_lob.createtemporary(encrypblob,true);
select BLOB_COLUMN into srcblob from BLOB_CONTENT where id = 1;

DBMS_CRYPTO.Encrypt(encrypblob,
                    srcblob,
                    DBMS_CRYPTO.ENCRYPT_AES256 + DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5,
                    hextoraw('000102030405060708090A0B0C0D0E0F101112131415161718191A1B1C1D1E1F')
);

decrypblob:=encrypblob;
DBMS_CRYPTO.Decrypt(decrypblob,
                    encrypblob,
                    DBMS_CRYPTO.ENCRYPT_AES256 + DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5 ,
                    hextoraw('000102030405060708090A0B0C0D0E0F101112131415161718191A1B1C1D1E1F')
                    );

UPDATE blob_content set blob_column=decrypblob where id=3;
end;

by using download_file I can download my first pdf file (which one with no encryption or decryption) without any problem, but for the second one that has been encrypted and then decrypted by the above piece of code I get this error message in apex:
ORA-01403: no data found 

I check the content of these BLOB cloumn ib DB, they were identical with same size and content.
Does anyone have any idea?

[Updated on: Thu, 10 July 2008 02:46]

Report message to a moderator

Re: Decrypt PDF file [message #332941 is a reply to message #332933] Thu, 10 July 2008 03:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
insert into blob_content values (3,rawtohex('alksjalksjlakjslakjs'));

Quote:
download_file('application/pdf','test.pdf',3);

Quote:
select BLOB_COLUMN into srcblob from BLOB_CONTENT where id = 1;

Regards
Michel
Re: Decrypt PDF file [message #332942 is a reply to message #332933] Thu, 10 July 2008 03:06 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It doesn't error when I run it from TOAD.

Might the problem be related to you updating ID=3, but selecting ID=1 - is it possible you've got another piece of SQL somewhere that is trying to fetch a record with Id=3 and getting the NDF?

Drat - beaten to it.

[Updated on: Thu, 10 July 2008 03:10]

Report message to a moderator

Re: Decrypt PDF file [message #332954 is a reply to message #332942] Thu, 10 July 2008 03:23 Go to previous messageGo to next message
pmapc
Messages: 46
Registered: July 2008
Member
I don't think so,

ID=1 is the content of pdf file without encrption or decription

ID=3 is the content of pdf file after encryption and decryption

I got ID=1 then encrypted/decrypted it and put it in ID=3!
Re: Decrypt PDF file [message #332965 is a reply to message #332954] Thu, 10 July 2008 03:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But is there something in id=1?
Nothing in your post prove it.

Regards
Michel
Re: Decrypt PDF file [message #332967 is a reply to message #332965] Thu, 10 July 2008 03:51 Go to previous messageGo to next message
pmapc
Messages: 46
Registered: July 2008
Member
YES,I have done it in another step ... I am sure it is not empty ... Sad

I also can get the file using this command:
download_file('application/pdf','test.pdf',1);


both of them have content.

[Updated on: Thu, 10 July 2008 03:52]

Report message to a moderator

Re: Decrypt PDF file [message #333077 is a reply to message #332967] Thu, 10 July 2008 08:01 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I've run your code, and it's not the code that you've posted that's producing the error.
Re: Decrypt PDF file [message #333492 is a reply to message #333077] Fri, 11 July 2008 22:41 Go to previous message
pmapc
Messages: 46
Registered: July 2008
Member
I don't know why but this line of code in download_file procedure was the reason of error:

owa_util.mime_header( nvl(v_mime,'application/octet'),FALSE );


Thank you for replies, they helped me to find the way. Smile
Previous Topic: Alter Table for insert the Column
Next Topic: how to get the sample table?
Goto Forum:
  


Current Time: Tue Dec 06 16:32:11 CST 2016

Total time taken to generate the page: 0.09230 seconds