Para melhor visualização, recomendo resolução de no mínimo 1024 x 768 e navegador Mozilla Firefox


quinta-feira, 2 de julho de 2015

Gerando hash MD5 do conteúdo de colunas do tipo LOB no Oracle com a package DBMS_CRYPTO

Por Eduardo Legatti

Olá,

Nos artigos de Novembro/2007 e Dezembro/2009 e eu demonstrei de forma prática como gerar arquivos externos ao banco de dados através das packages UTL_FILE e DBMS_LOB, a utilização do objeto DIRECTORY, bem como a apresentação de uma função para geração de hash MD5 através da package DBMS_OBFUSCATION_TOOLKIT para geração de senhas e pequenos textos. Neste artigo, irei abordar novamente a utilização dos objetos DIRECTORY e da package DBMS_LOB quanto a geração de hash MD5. A diferença é que agora irei demonstrar como gerar um hash MD5 para dados do tipo LOB (BLOB, CLOB) através da package DBMS_CRYPTO disponível à partir do Oracle 10g.

O objetivo do artigo será demonstrar uma função de banco de dados que chamarei de md5_blob que receberá como parâmetro a coluna de uma tabela do tipo de dado BLOB. Em alguns casos ela poderá ser muito útil, como foi pra mim, quando precisei verificar em uma tabela a quantidade de documentos binários duplicados (*.doc, *.xls, *.pdf, *.jpg, etc.) que estavam armazenados em uma coluna do tipo BLOB.

Segue abaixo os comandos executados para criar um ambiente de forma a simular uma tabela contendo documentos binários. No caso em questão, irei armazenar um arquivo chamado imagem.jpg.
 
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 2 10:04:59 2015

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Conectado a:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create or replace directory arquivo_dir as '/tmp';

Diretório criado.

-- Criando a tabela de teste
SQL> create table tab_imagem (
  2    id number primary key,
  3    nome varchar2(20),
  4    imagem blob
  5  );

Tabela criada.

SQL> grant write,read on directory arquivo_dir to scott;

Concessão bem-sucedida.

SQL> grant execute on dbms_crypto to scott;

Concessão bem-sucedida.

-- Listando o arquivo que será carregado para a tabela tab_imagem
[oracle@linux tmp]$ ls -l
total 4
-rwxr--r-- 1 oracle oinstall 820 Jul 2 10:09 imagem.jpg

$ sqlplus scott/tiger

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 2 10:14:32 2015

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Conectado a:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

-- Criando a stored procedure carrega_imagem
SQL> create or replace procedure carrega_imagem (p_nome_arquivo in tab_imagem.nome%type) as
  2   v_bfile bfile;
  3   v_blob blob;
  4  begin
  5    insert into tab_imagem (id,nome,imagem)
  6    values (1,p_nome_arquivo,empty_blob())
  7    return imagem into v_blob;
  8    v_bfile := bfilename('ARQUIVO_DIR',p_nome_arquivo);
  9    dbms_lob.fileopen(v_bfile, dbms_lob.file_readonly);
 10    dbms_lob.loadfromfile(v_blob,v_bfile,dbms_lob.getlength(v_bfile));
 11    dbms_lob.fileclose(v_bfile);
 12    commit;
 13  end;
 14  /

Procedimento criado.

-- Carregando o arquivo para a tabela tab_imagem
SQL> execute carrega_imagem('imagem.jpg');

Procedimento PL/SQL concluído com sucesso.
  
Pronto. O arquivo imagem.jpg foi carregado para a tabela conforme demonstrado abaixo.
 
SQL> select * from tab_imagem;

        ID NOME             IMAGEM
---------- ---------------- ------------------------------------------------------------
         1 imagem.jpg       47494638396109000600F70000000000B1B1B1AFAFAFA3A3A39999997777
                            777171716B6B6B5555554B4B4B3F3F3F3D3D3DEEEEEE3B3B3B3333332727
                            27CCCCCC191919171717111111C0C0C00D0D0D090909070707AAAAAAA4A4
                            A49696968A8A8A8888887070706868686666666060605E5E5E5C5C5C5858
                            58FFFFFF4848484444444040403838383232322E2E2EDDDDDD2828282626
                            262424242222221E1E1E181818141414121212101010BBBBBB080808B3B3
                            B30000000000000000000000000000000000000000000000000000000000
                            262424242222221E1E1E181818141414121212101010BBBBBB080808B3B3
                            000000000000000000000000000000000000000000000000000000000000
                            262424242222221E1E1E181818141414121212101010BBBBBB080808B3B3
                            777171716B6B6B5555554B4B4B3F3F3F3D3D3DEEEEEE3B3B3B3333332727
                            262424242222221E1E1E181818141414121212101010BBBBBB080808B3B3
                            000000000000000000000000000000000000000000000000000000000000
                            A49696968A8A8A8888887070706868686666666060605E5E5E5C5C5C5858
                            262424242222221E1E1E181818141414121212101010BBBBBB080808B3B3
                            777171716B6B6B5555554B4B4B3F3F3F3D3D3DEEEEEE3B3B3B3333332727
                            A49696968A8A8A8888887070706868686666666060605E5E5E5C5C5C5858
                            000000000000000000000000000000000000000000000000000000000000
                            262424242222221E1E1E181818141414121212101010BBBBBB080808B3B3
                            000000000000000000000000000000000000000000000000000000000000
                            000000000000000000000000000000000000000000000000000000000000
                            A49696968A8A8A8888887070706868686666666060605E5E5E5C5C5C5858
                            777171716B6B6B5555554B4B4B3F3F3F3D3D3DEEEEEE3B3B3B3333332727
                            000000000000000000000000000000000000000000000000000000000000
                            000000000000000000000000000000000000000000000000000000000000
                            000000000000000000000000000000000000000000000000000000000000
                            002C000000000900060000080E0049081C48B0A0C1830813260C08003B00

Segue abaixo a função de banco de dados que será utilizada para gerar o hash MD5 da coluna do tipo BLOB.
 
SQL> create or replace function md5_blob (p_blob in blob)
  2  return varchar2
  3  is
  4  begin
  5     return dbms_crypto.hash (src   => p_blob,
  6                              typ   => dbms_crypto.hash_md5);
  7  end;
  8  /

Função criada.
  
Após a criação da função, poderemos ver abaixo através da instrução SELECT que o hash MD5 do conteúdo da coluna IMAGEM da tabela TAB_IMAGEM foi gerado com sucesso.

SQL> select id,nome,md5_blob(imagem) from tab_imagem;

        ID NOME            MD5_BLOB(IMAGEM)
---------- --------------- --------------------------------
         1 imagem.jpg      4B812BDC6240D0770A12DA89E4630BF2

1 linha selecionada.

Apenas para comprovar que o hash MD5 que foi gerado é válido, utilizarei o comando md5sum no Linux para verificar que o valor do hash é o mesmo que foi gerado pelo Oracle.
 
$ md5sum imagem.jpg
4b812bdc6240d0770a12da89e4630bf2  imagem.jpg

Em relação à colunas do tipo CLOB, a função é praticamente idêntica, o que muda um  pouco é a utilização da procedure utl_raw.cast_to_raw como demonstrado abaixo.

SQL> create or replace function md5_clob (p_clob in clob)
  2  return varchar2
  3  is
  4  begin
  5     return dbms_crypto.hash (src   => utl_raw.cast_to_raw (p_clob),
  6                              typ   => dbms_crypto.hash_md5);
  7  end;
  8  /

Função criada.
  

Google+

Nenhum comentário:

Postagens populares