24. Juli 2008

Einfacher BLOB Viewer: Mit Oracle11g und DBMS_EPG

English title:

Es ist ja mit nahezu allen Werkzeugen recht einfach, sich Tabelleninhalte anzusehen; selbst SQL*Plus reicht zum Ansehen von Tabellen völlig aus. Komplizierter wird es, wenn man eine BLOB-Spalte vor sich hat: Zwar kann SQL*Plus ab Oracle11g BLOB-Spalten anzeigen, man sieht jedoch nur die Bytes des Blobs im Hexcode. Möchte man nun wissen, was da eigentlich drin ist, muss man schon programmieren.
Viewing table data is even with SQL*Plus a very easy task. But if you want to view data in BLOB columns things get difficult. Although SQL*Plus in Oracle11g can display BLOB columns this is not helpful in all cases - it just shows the hex bytes. Seeing the hex bytes does not give a clue about the document contents - except one knows the binary formats very well. To see the document (Word, PDF, Image) programming efforts are required.
Wenn es um Bilder geht, kann der SQL Developer recht gut weiterhelfen, hat man es aber auch mit PDF oder anderen Dateien zu tun, geht auch das nicht mehr.
If it's just about images SQL Developer might be of good help - but when it comes to office or PDF documents we are stuck with the hex bytes again.
Da ich schon etwas häufiger vor dieser Situation stand, habe ich mir einen einfachen "BLOB Viewer" geschrieben. Als Betrachtungswerkzeug nutze ich den Webbrowser - der kann ja mit fast allem umgehen. Um mit dem Webbrowser auf die Datenbank zugreifen zu können, nutze ich das seit Oracle11g verfügbare Embedded PL/SQL Gateway; man kann das Tool auch in älteren Datenbankversionen benutzen, braucht dann jedoch einen Apache Webserver mit dem mod_plsql zum Zugriff auf die Datenbank durch das Web (für APEX-Nutzer kein Problem). Das Embedded PL/SQL Gateway nutzt den HTTP-Protokollserver der XML DB (den gibt es ja schon seit Oracle9i), um PL/SQL-Prozeduren per URL verfügbar zu machen. Die Prozedur selbst ist mit den "guten alten" HTP und OWA_UTIL-Paketen programmiert.
Since I had the situation frequently I wrote a "simple BLOB viewer" for a web browser. To access the database with the web browser I use the new Embedded PL/SQL Gateway introduced with Oracle11g; the viewer could also run in older database versions - but an additional Apache webserver with the mod_plsql is then required (no problem for APEX users). The "Embedded PL/SQL Gateway" uses XML DB's HTTP protocol server (which was introduced some time ago: Oracle9i). The procedure itself is implemented using the "good ol'" HTP, HTF and OWA_UTIL packages.
Hier ist also der Code (Das Skript selbst immer als SYS laufen lassen): Zunächst wird das "Embedded PL/SQL Gateway" mit dem Paket DBMS_EPG eingerichtet. Das Skript prompted nach dem Datenbankuser, für den der DAD eingerichtet werden soll. Wenn man SYS nimmt, kann man mit dem Viewer BLOBs der ganzen Datenbank ansehen.
So here's the code (please run the script as SYS): First it configures the DAD for the Embedded PL/SQL Gateway using the DBMS_EPG package. It prompts for the database schema to use - if you want to have a single URL for viewing the BLOBs of all database schemas use SYS here.
Dann kommt die eigentliche Prozedur: Ziel ist, dass man zumindest den Namen der Tabelle oder View und den Wert des Primärschlüssels angeben muss. Den Rest (PK-Spalte, BLOB-Spalte) versucht die Prozedur selbst herauszufinden. Man kann sie natürlich dennoch übergeben (für den Fall, dass mehrere BLOB-Spalten vorhanden oder kein Primary Key definiert ist). Wenn eine Tabelle einen zusammengesetzten Primärschlüssel hat, werden die Spalten einfach konkatentiert. Die Prozedur wird im Schema des zu Beginn des Skripts angegebenen Users erzeugt.
Then the actual procedure follows: The procedure's goal is to work with as least parameters as possible and to find out the missing items byself. So if a primary key is defined and the procedure is contained in the DAD's schema you just have to provide the table or view's name and the primary key value. Of course you can also explicitly provide the BLOB column, the mimetype column and the primary key column(s). If a table's primary key consists of more than one column the procedure concatenates them. The procedure is created in the DAD users' schema.
set verify off

accept schemaname default 'PARTNER' prompt '>> DAD einrichten für Datenbankschema: [PARTNER] '

begin
  dbms_epg.drop_dad(
     dad_name => 'SHOW_LOB_DAD'
  );
end;
/
sho err

begin
  dbms_epg.create_dad(
    dad_name => 'SHOW_LOB_DAD',
    path     => '/lobviewer/*'
  );
  DBMS_EPG.SET_DAD_ATTRIBUTE( 
    dad_name => 'SHOW_LOB_DAD', 
    attr_name => 'database-username', 
    attr_value => upper('&schemaname.')
  );
  DBMS_EPG.AUTHORIZE_DAD(
    dad_name => 'SHOW_LOB_DAD',
    user => upper('&schemaname.')
  );
end;
/
sho err

commit
/

create or replace procedure &schemaname..show(
  p_table_view   in varchar2,
  p_id_col       in varchar2 default null,
  p_blob_col     in varchar2 default null,
  p_mimetype_col in varchar2 default null,
  p_owner        in varchar2 default SYS_CONTEXT('userenv', 'current_schema'),
  p_id           in varchar2 default '1'
) is
  v_sql       varchar2(32767);
  v_mimetype  varchar2(200)    := null;
  v_blob      blob;
  v_lob_col   varchar2(200)    := null;
  v_id_col    varchar2(200)    := null;
  v_data_type varchar2(200)    := null;
begin
  if p_blob_col is null then
   begin
    select 
      case
        when data_type = 'ORDIMAGE' then '"SYS_ALIAS$"."'||column_name ||'"."SOURCE"."LOCALDATA"' 
        else '"SYS_ALIAS$"."'||column_name ||'"'
      end into v_lob_col 
    from all_tab_columns 
    where table_name = p_table_view and owner = p_owner and data_type in ('BLOB', 'ORDIMAGE')
    and rownum <= 1;
   exception 
    when NO_DATA_FOUND then
     raise_application_error(-20000, 'table "'||p_table_view||'" has no BLOB column');
   end;
  else
   begin
    select data_type into v_data_type from all_tab_columns
    where table_name = p_table_view and owner = p_owner
    and column_name = p_blob_col;
    if v_data_type = 'BLOB' then 
     v_lob_col := '"SYS_ALIAS$".' || dbms_assert.enquote_name(p_blob_col);
    elsif v_data_type = 'ORDIMAGE' then 
     v_lob_col := '"SYS_ALIAS$".'||dbms_assert.enquote_name(p_blob_col)||'."SOURCE"."LOCALDATA"'; 
    else 
     raise_application_error (-20000, 'Supplied column "'||p_blob_col||'" is not a BLOB or ORDIMAGE column.');
    end if;
   exception
    when NO_DATA_FOUND then 
     raise_application_error (-20000, 'Supplied BLOB column "'||p_blob_col||'" does not exist.');
   end;
  end if;  
  if p_id_col is null then 
    v_id_col := '';
    for pkcols in (
      select ucc.column_name 
      from all_constraints uc, all_cons_columns ucc
      where uc.table_name = ucc.table_name
      and uc.constraint_name = ucc.constraint_name
      and uc.constraint_type='P'
      and uc.table_name = p_table_view
      and ucc.owner = p_owner
      order by ucc.position
    ) loop
      v_id_col := v_id_col || '"SYS_ALIAS$"."'||pkcols.column_name ||'"||';
    end loop;
    v_id_col := substr(v_id_col, 1, length(v_id_col) - 2);
    if v_id_col is null then 
      raise_application_error(-20000, 'PK column could not be determined');
    end if;
  else 
    v_id_col := '"SYS_ALIAS$".'||dbms_assert.enquote_name(p_id_col);
  end if;


  if p_mimetype_col is null then
    v_sql := 'select '||v_lob_col|| ' ' ||
             'from '||dbms_assert.schema_name(p_owner)||'.'||dbms_assert.enquote_name(p_table_view)||' SYS_ALIAS$ '||
             'where '||v_id_col||' = :a';
    execute immediate v_sql
    into v_blob
    using p_id;
  else 
    v_sql := 'select '||v_lob_col||', '||dbms_assert.enquote_name(p_mimetype_col)||' '||
             'from '||dbms_assert.schema_name(p_owner)||'.'||dbms_assert.enquote_name(p_table_view)||' SYS_ALIAS$ '||
             'where '||v_id_col||' = :a';
    execute immediate v_sql
    into v_blob, v_mimetype
    using p_id;
  end if;

  owa_util.mime_header(v_mimetype, false);
  htp.p('Content-Length: '||dbms_lob.getlength(v_blob));
  htp.p('Content-Disposition: inline');
  owa_util.http_header_close;
  wpg_docload.download_file(v_blob);
exception when others then
  owa_util.mime_header('text/plain', true);
  htp.p(dbms_utility.format_error_stack);
  htp.p(dbms_utility.format_call_stack);
  htp.p;
  htp.p(v_sql);
  htp.p;
  htp.p('V_LOB_COL: '||v_lob_col);
end;
/
sho err
Ein (möglicher) Aufruf im Browser könnte dann so aussehen (es wird nur die Tabelle und der Wert des Primärschlüssels übergeben). Den Rest findet die Prozedur selbst raus:
One possible procedure call looks like this (only the table name and the primary key value are provided). The procedure determines the other values.
http://localhost:8080/lobviewer/show?P_TABLE_VIEW=DOKUMENT_TAB&P_ID=3
Eine andere Variante übergibt alle Parameter explizit:
Another call: This example passes all values explicitly.
http://localhost:8080/lobviewer/show?
  P_TABLE_VIEW=DOKUMENT_TAB&
  P_OWNER=PARTNER&
  P_ID_COL=ID&
  P_MIMETYPE_COL=MIMETYPE&
  P_BLOB_COL=DOKUMENT&
  P_ID=2

Kommentare:

Patrick Wolf hat gesagt…

Hallo Carsten,

interessante Funktion, koennte manchmal wirklich hilfreich sein!

Aber vielleicht solltest Du nochmal auf den Security Aspekt hinweisen, weil ja doch jede Tabelle mit einem BLOB ausgelesen werden kann und wenn man sich ein wenig mit spielt kann man ueberhaupt jede Tabelle auslesen, auch jene wo kein BLOB drinnen ist. Du solltest die Parameter p_id_col und p_mimetype_col mit DBMS_Assert absichern um SQL Injections zu vermeiden.

Schoene Gruesse aus Wien
Patrick

Carsten Czarski hat gesagt…

Hi Patrick,

grundsätzlich hast Du natürlich völlig recht - sobald man dynamisches SQL programmiert, sollte wirklich jeder Tabellen- oder Spaltenname durch DBMS_ASSERT laufen. Ich hab's mir einfach aus Faulheit - und weil ich die Prozedur eher als Hilfsmittel für Entwickler angedacht hatte gespart ... Mal gucken - vielleicht stell ich die abgesicherte Version nochmal ein ...

Viele Grüße

-Carsten

Carsten Czarski hat gesagt…

... so ... ist nun geschehen. Dort, wo ein vom außen übergebener Parameter ins SQL eingebaut wird, wird er nun vorher mit DBMS_ASSERT auf SQL Injection-Attacken geprüft.

Viel Spaß damit!

-Carsten

Beliebte Postings