29. September 2008

String Tokenizer für PL/SQL

English title: String tokenizer for PL/SQL

Letzte Woche musste ich (wie so oft) einen String anhand eines Trennzeichens (Delimiter) in einzelne Teile zu zerlegen. In Java ist das unproblematisch: Da gibt es die Klasse java.util.StringTokenizer, mit der man das ganz einfach machen kann. So etwas schwebte mir auch für PL/SQL vor - und deshalb poste ich heute etwas generischen PL/SQL-Code als Äquivalent zur besagten Java Klasse: Den PL/SQL String-Tokenizer.
Last week I had to tokenize a string ... Not for the first time, of course, but ow I decided to create a generic solution for this. In Java it is very simple: There is the java.util.StringTokenizer class which gives a very easy interface to tokenize a string and to access to tokens. I'd like such an API also for PL/SQL and therefore I created some PL/SQL code which should behave similar to the java class: The PL/SQL string tokenizer.
Nun findet man im Internet zahlreiche Codeschnipsel zu diesem Thema: Ich bin jedoch bewusst den Weg mit dem Objekktypen gegangen - dadurch wird es auch möglich, in einer Datenbanksession mehrere Tokenizer-Objekte parallel zu verwenden - wenn man das in einem Package haben möchte, muss man die Verwaltung der einzelnen "Instanzen" komplett selbst programmieren - das war mir zu umständlich. Die Java-Klasse java.util.StringTokenizer war Vorbild für den PL/SQL Objekttypen.
Although there are more PL/SQL string tokenizer code snippets than sand at the beach I've written another implementation: as a database object type. The reason for this is that with this approach you can have multiple instances of your tokenizer in your session at the same time. Using simple PL/SQL packages would require me to code the instance handling by myself - I considered this as too much effort. The java StringTokenizer was the model for the API of the PL/SQL object type.
Das folgende Skript erzeugt zwei Objekttypen: Der Typ STRING_TOKENIZER zerlegt VARCHAR2-Datentypen - und da ich mit dem Objekttypen arbeite, gilt die SQL-Grenze von 4.000 Bytes. Für größere Objekte wird der Typ CLOB_TOKENIZER angelegt - darin habe ich die Funktionen des DBMS_LOB Package verwendet; dieser Typ kann also beliebig große Zeichenketten (CLOB) zerlegen. CLOB_TOKENIZER ist wegen der DBMS_LOB-API langsamer als STRING_TOKENIZER, man sollte für kurze Strings (bis 4.000 bytes) also stets den STRING_TOKENIZER nutzen.
The following script generated two object types: STRING_TOKENIZER is for dealing with VARCHAR2 contents - and since this is an object type, the SQL limit of 4.000 bytes applies. For larger Strings there is the CLOB_TOKENIZER type. This one uses the DBMS_LOB API for parsing the string and therefore can tokenize any string regardless of its length. Therefore the CLOB_TOKENIZER is slower than STRING_TOKENIZER so for short strings you should always use STRING_TOKENIZER.
drop type clob_tokenizer
/

drop type string_tokenizer
/

drop type token_list
/

drop type token_t
/

create type token_t as object(
  token_text varchar2(4000),
  start_pos  number,
  length     number
)
/

create type token_list as table of token_t
/

create type clob_tokenizer as object(
  value_string       clob,
  delimiter          varchar2(10),
  parser_current_pos number,
  last_token         varchar2(4000),
  constructor function clob_tokenizer (p_string in clob, p_delim in varchar2) return self as result,
  member function has_more_tokens return number,
  member function next_token(self in out nocopy clob_tokenizer) return varchar2,
  static function all_tokens (p_string in clob, p_delim in varchar2) return token_list pipelined parallel_enable,
  static function all_tokens_cur (p_cursor in sys_refcursor, p_delim in varchar2) return token_list pipelined parallel_enable (partition p_cursor by any)
);
/
sho err

create or replace type body clob_tokenizer is
  constructor function clob_tokenizer (p_string in clob, p_delim in varchar2) return self as result as
  begin
    self.value_string := p_string;
    self.delimiter := p_delim;
    self.parser_current_pos := 1;
    self.last_token := null;
    return ;
  end;
    
  member function has_more_tokens return number as
  begin
    if self.parser_current_pos <= dbms_lob.getlength(value_string) then 
      return 1;
    else 
      return 0;
    end if;
  end;

  member function next_token(self in out nocopy clob_tokenizer) return varchar2 is
    l_next_delim_pos   number;
    l_token            varchar2(4000);
  begin
    if self.has_more_tokens() = 1 then 
      l_next_delim_pos := dbms_lob.instr(self.value_string, self.delimiter, self.parser_current_pos);
      if l_next_delim_pos = 0 then
        l_token := dbms_lob.substr(
          lob_loc => self.value_string, 
          amount  => (dbms_lob.getlength(self.value_string) - self.parser_current_pos) + 1,  
          offset  => self.parser_current_pos
        );
        parser_current_pos := dbms_lob.getlength(self.value_string) + 1; 
      else 
        l_token := dbms_lob.substr(
          lob_loc => self.value_string, 
          amount  => l_next_delim_pos  - self.parser_current_pos, 
          offset  => self.parser_current_pos
        );
        parser_current_pos := l_next_delim_pos + length(self.delimiter);
      end if;
    else 
      l_token := null;
    end if;
    self.last_token := l_token;
    return l_token;
  end;

  static function all_tokens (p_string in clob, p_delim in varchar2) return token_list pipelined parallel_enable is
    l_st clob_tokenizer := clob_tokenizer(p_string, p_delim);
    l_startpos number;
    l_token    varchar2(4000);
  begin
    while l_st.has_more_tokens = 1 loop
      l_startpos := l_st.parser_current_pos;
      l_token := l_st.next_token();
      pipe row (token_t(l_token, l_startpos, nvl(length(l_token),0)));
    end loop;
    return;
  end;

  static function all_tokens_cur (p_cursor in sys_refcursor, p_delim in varchar2) return token_list pipelined parallel_enable (partition p_cursor by any) is
    l_st       clob_tokenizer;
    l_string   clob;
    l_startpos number;
    l_token    varchar2(4000);
  begin
    loop
      fetch p_cursor into l_string;  
      exit when p_cursor%notfound;
     
      l_st := clob_tokenizer(l_string, p_delim);
      while l_st.has_more_tokens = 1 loop
        l_startpos := l_st.parser_current_pos;
        l_token := l_st.next_token();
        pipe row (token_t(l_token, l_startpos, nvl(length(l_token),0)));
      end loop;
    end loop;
    return;
  end;

end;
/
sho err

create type string_tokenizer as object(
  value_string       varchar2(4000),
  delimiter          varchar2(10),
  parser_current_pos number,
  last_token         varchar2(4000),
  constructor function string_tokenizer (p_string in varchar2, p_delim in varchar2) return self as result,
  member function has_more_tokens(self in out nocopy string_tokenizer) return number,
  member function next_token(self in out nocopy string_tokenizer) return varchar2,
  static function all_tokens (p_string in varchar2, p_delim in varchar2) return token_list pipelined parallel_enable,
  static function all_tokens_cur (p_cursor in sys_refcursor, p_delim in varchar2) return token_list pipelined parallel_enable (partition p_cursor by any)
);
/
sho err

create or replace type body string_tokenizer is
  constructor function string_tokenizer (p_string in varchar2, p_delim in varchar2) return self as result as
  begin
    self.value_string := p_string;
    self.delimiter := p_delim;
    self.parser_current_pos := 1;
    self.last_token := null;
    return ;
  end;
    
  member function has_more_tokens(self in out nocopy string_tokenizer) return number as
  begin
    if self.parser_current_pos <= length(value_string) then 
      return 1;
    else 
      return 0;
    end if;
  end;

  member function next_token(self in out nocopy string_tokenizer) return varchar2 as
    l_next_delim_pos   number;
    l_next_enclose_pos number;
    l_token            varchar2(4000);
  begin
    if self.has_more_tokens() = 1 then 
      l_next_delim_pos := instr(self.value_string, self.delimiter, self.parser_current_pos);
      if l_next_delim_pos = 0 then
        l_token := substr(value_string, self.parser_current_pos);
        parser_current_pos := length(self.value_string) + 1; 
      else 
        l_token := substr(self.value_string, self.parser_current_pos, l_next_delim_pos  - self.parser_current_pos);
        parser_current_pos := l_next_delim_pos + length(self.delimiter);
      end if;
    else 
      l_token := null;
    end if;
    self.last_token := l_token;
    return l_token;
  end;

  static function all_tokens (p_string in varchar2, p_delim in varchar2) return token_list pipelined parallel_enable is
    l_st string_tokenizer := string_tokenizer(p_string, p_delim);
    l_startpos number;
    l_token    varchar2(4000);
  begin
    while l_st.has_more_tokens = 1 loop
      l_startpos := l_st.parser_current_pos;
      l_token := l_st.next_token();
      pipe row (token_t(l_token, l_startpos, nvl(length(l_token),0)));
    end loop;
    return;
  end;

  static function all_tokens_cur (p_cursor in sys_refcursor, p_delim in varchar2) return token_list pipelined parallel_enable (partition p_cursor by any) is
    l_st       string_tokenizer;
    l_string   varchar2(4000);
    l_startpos number;
    l_token    varchar2(4000);
  begin
    loop
      fetch p_cursor into l_string;  
      exit when p_cursor%notfound;
     
      l_st := string_tokenizer(l_string, p_delim);
      while l_st.has_more_tokens = 1 loop
        l_startpos := l_st.parser_current_pos;
        l_token := l_st.next_token();
        pipe row (token_t(l_token, l_startpos, nvl(length(l_token),0)));
      end loop;
    end loop;
    return;
  end;
end;
/
sho err
Die Typen können prozedural ...
You can use the types in a procedural manner ...
create or replace procedure st_tester (p_string in varchar2, p_delim in varchar2) is
  v_st string_tokenizer := string_tokenizer(p_string, p_delim);
  v_cnt pls_integer := 0;
begin
  while v_st.has_more_tokens() = 1 loop
    dbms_output.put_line(v_cnt||': '||v_st.next_token());
    v_cnt := v_cnt + 1;
  end loop;
end;
/

exec st_tester('##a#b#c#d#e#f##','#');

0: ?
1: ?
2: a
3: b
4: c
5: d
6: e
7: f
8: ?
... oder in einer SQL-Abfrage genutzt werden.
... as well as in a SQL query.
select rownum, token_text, start_pos, length from table(string_tokenizer.all_tokens('##a#b#c#d#e#f##', '#') );

    ROWNUM TOKEN_TEXT                      START_POS     LENGTH
---------- ------------------------------ ---------- ----------
         1 ?                                       1          0
         2 a                                       2          1
         3 b                                       4          1
         4 c                                       6          1
         5 d                                       8          1
         6 e                                      10          1
         7 f                                      12          1
         8 ?                                      14          0
Zurück zum Ursprungsproblem: Die Tabelle mit der kommaseparierten Spalte (TABELLE.MERKMALE (VARCHAR2(400)) konnte ich wie folgt "normalisieren".
Back to the initial problem. Using those types I could "normalize" the table with the column containing comma-separated content (TABELLE.MERKMALE VARCHAR2(400)) using a simple SQL statement.
create table m1 as 
select t.id, m.token_text from tabelle t, table(string_tokenizer.all_tokens(t.merkmale, ',')) m
/

Tabelle wurde erstellt.

Abgelaufen: 00:00:05.14


select count(*) from m1;

  COUNT(*)
----------
    204006
Von der Zeit her gar nicht sooo schlecht ... für einen Laptop.
For a laptop computer the consumed time seems not too bad ...

22. September 2008

Schutz vor SQL Injection with DBMS_ASSERT

English title: SQL Injection protection with DBMS_ASSERT

Vor ein paar Wochen hat der Patrick Wolf mich auf den fehlenden SQL Injection-Schutz in einem Blog-Posting hingewiesen. Nun, SQL Injection wird ja ziemlich stark diskutiert ... aber der von der Datenbank angebotene Schutzmechanismus DBMS_ASSERT ist kaum bekannt - also habe ich mich entschlossen, dem ein (oder vielleicht auch mehrere) Postings zu widmen.
Some weeks ago Patrick Wolf recognized my missing SQL injection protection in a previous blog posting. Now ... the topic "as-is" is discussed more and more ... but the protection package which the database offers (DBMS_ASSERT) is hardly known by anyone ... So I dediced to write (at least) one posting about it.
SQL Injection ist ja bekanntlich (wie der Name schon sagt) das "Injizieren" von SQL-Code in die Datenbank durch eine entsprechende Schwachstelle in der Applikation. Solche Angriffe finden über das ganz normale Frontend der Applikation statt - nur dass der Angreifer dort Werte eingibt, an die ein "normaler" Nutzer nicht im Traum denkt. SQL Injection-Lücken entstehen dann, wenn Entwickler die Benutzereingaben ohne weitere Prüfung (per Stringverkettung) in SQL-Kommandos einfügen. So ist der folgende Java bzw. JDBC-Code denn auch ein typisches Beispiel für eine SQL Injection-Lücke.
As most know SQL injection is (as the name already tells) the "injection" of SQL commands into the database using the vulnerability in the application. SQL injection is done via the application's front end every other user uses. But an attacker enters values which most normal users don't even think about. SQL injection vulnerabilities arise when the application developer concatenates user-supplied parameters without checking into SQL queries and statements. So the following Java (JDBC) code is a typical example for a SQL injection vulnerability.
 :
 int iEmpno = request.getParameter("EMPNO");
 :
 PreparedStatement pstmt = con.prepareStatement("select sal from emp where empno = " + iEmpno);
 ResultSet rs = pstmt.executeQuery();
 while (rs.next()) {
   // Resultset hier auslesen
 }
 rs.close();
 pstmt.close();
 :
Wenn ein Angreifer nun als HTTP-Parameter EMPNO anstelle von bspw. 7839 ein 7839 or 1=1 übergibt, werden alle Datensätze selektiert. Neben dem einfachen or 1=1 könnte auch ein UNION ALL (...) mit einer Dictionary Tabelle eingebaut werden - so bekäme der Angreifer eine Übersicht über die vorhandenen PL/SQL-Prozeduren und Funtionen - alle SQL-Funktionen, für die das Datenbankschema Privilegien hat, lassen sich dann ebenfalls bequem durch die SQL-Abfrage ausführen.
Generall kann man sagen, dass in den meisten Fällen, in denen eine Sicherheitslücke in der Datenbank ausgenutzt wird, der Angriff zunächst über eine SQL Injection-Lücke erfolgt. SQL Injection ist quasi die "Eintrittskarte in die Datenbank". Je nachdem, wer dann da eingetreten ist und welche Absichten er hat, wird mehr oder weniger Schaden angerichtet.
Now let's assume that the end user provides not the expected value (e.g. 7839) but 7839 or 1=1 as the HTTP parameter EMPNO. Now the whole table gets selected. But SQL injection allows not only to bypass a query filter. Providing 7839 union all (...) allows to select other tables (including dictionary tables) and (of course) every accessible SQL function can now be executed.
Generally most attacks to Oracle databases firstly use a SQL injection vulnerability to get into the database. SQL injection is (so-to-say) the attackers' entry ticket to the database. Depending on who had entered it and what his or her objectives are, more or less damage is produced.
Zum Glück ist es recht einfach, SQL Injection-Lücken zu schließen. Aber der größte Feind des Entwicklers ist, dass der Schutz vor SQL Injection konsequent und umfassend sein muss - man muss eben immer dran denken. Die Lücke in obigem Beispiel ließe sich am einfachsten durch die Verwendung von Bindevariablen schließen. Die Verwendung von Bindevariablen bringt nicht nur bessere Performance, sondern i.d.R. auch Schutz vor SQL Injection - einfach weil das SQL-Kommando dann nicht mehr dynamisch ist.
In PL/SQL sind die Dinge einfach ... denn wenn man folgende Prozedur hat ...
Fortunately it's quite easy to prevent SQL injection attacks - but the fact that SQL injection protection must be comprehensive over each and every dynamic SQL statement is the developer's greatest enemy. You just have always to think about it. The vulnerability in the above java code is easy to handle: Just use bind variables. Using bind variables not only gives you better performance - it also prevents SQL injection since the SQL query is no longer a "dynamic one".
In PL/SQL things are easy ... if you have the following procedure ...
create or replace procedure doit(p_empno in number)
is
  v_sal EMP.SAL%TYPE;
begin
  select sal into v_sal from emp where empno = p_empno;
end;
... dann macht die Datenbank daraus ohnehin eine Bindevariable - tatsächlich wird als SQL dann ausgeführt:
... the database uses bind variables anyway - in fact it executed the following SQL query:
select sal into :1 from emp where empno = :2
Also ist SQL Injection kein Thema für PL/SQL-Programmierer? Mitnichten - auch in PL/SQL werden häufig dynamische SQL-Statements zusammengesetzt und gerade dann kann können SQL Injection-Lücken auch im PL/SQL-Code sein. Sobald Ihr mit dem Paket DBMS_SQL oder den Sprachkonstrukten EXECUTE IMMEDIATE bzw. OPEN ... FOR arbeitet, ist SQL Injection ein Thema.
So SQL Injection is a no-brainer for PL/SQL developers? No - PL/SQL allows also to construct dynamic SQL statements - and in this cases SQL injection vulnerabilities are possible. If you're using the DBMS_SQL package or the EXECUTE IMMEDIATE or OPEN ... FOR commands you have to think about SQL injection.
create or replace procedure doit(
  p_table    in varchar2, 
  p_valuecol in varchar2,
  p_wherecol in varchar2,
  p_param    in varchar2
)
is
  v_value varchar2(32767);
  v_sql   varchar2(32767);
begin
  v_sql := 'select '||p_valuecol||' from '||p_table||' where '||p_wherecol||' = '''||p_param||'''';
  execute immediate v_sql into v_value;
end;
Dieses Beispiel ist nun für SQL Injection-Angriffe anfällig; denn als Parameter können beliebige Texte übergeben werden - es können wieder alle erreichbaren SQL- und PL/SQL-Funktionen aufgerufen und alle erreichbaren Tabellen und Views selektiert werden.
This example is a PL/SQL which could be used for SQL injection attacks, since you can provide any arbitrary string for the procedure parameters. Since the parameters are concatenated into the SQL command without any checking an attacker could again: select any accessible table and execute any accessible function.
Doch nun zur Lösung. Mittlerweile gibt es das PL/SQL-Paket DBMS_ASSERT, mit dem der PL/SQL-Programmierer seine zusammengesetzten SQL-Anweisungen prüfen kann. Die wichtigsten Funktionen sind ENQUOTE_NAME und ENQUOTE_LITERAL. Beide Funktionen arbeiten so, dass der übergebene Wert in Quotes eingeschlossen und zurückgegeben wird, wenn alles in Ordnung ist und dass ein Fehler ausgelöst wird, wenn ein SQL Injection-Angriff erkannt wird.
Now we come to the solution: The package DBMS_ASSERT can be used to check the user-supplied parameters before concatenating them into the SQL statement. The most important functions are ENQUOTE_NAME and ENQUOTE_LITERAL. Both functions enquote the supplied parameter if the parameter is OK and raise an exception if there's a SQL injection attack.
SQL> select dbms_assert.enquote_literal('KING') from dual;

DBMS_ASSERT.ENQUOTE_LITERAL('KING')
--------------------------------------------------------------------------------
'KING'

SQL> select dbms_assert.enquote_name('EMP') from dual;

DBMS_ASSERT.ENQUOTE_NAME('EMP')
--------------------------------------------------------------------------------
"EMP"
So wie der Wert zurückkommt kann er nun ins SQL eingebaut werden. Im folgenden nun ein paar Beispiele, wie DBMS_ASSERT reagiert, wenn SQL Injection-Angriffe versucht werden
.
As this value returns it may be built into the SQL statement. The following are examples for DBMS_ASSERT behaviour when SQL injection is detected.
  • Der "klassische" Angriff: Übergabe von KING' or 1=1 -- (Quote schließen, eigene Bedingung anhängen und Rest auskommentieren)
    SQL> select dbms_assert.enquote_literal('KING''or 1=1') from dual;
    select dbms_assert.enquote_literal('KING''or 1=1') from dual
           *
    FEHLER in Zeile 1:
    ORA-06502: PL/SQL: numerischer oder Wertefehler
    ORA-06512: in "SYS.DBMS_ASSERT", Zeile 310
    ORA-06512: in "SYS.DBMS_ASSERT", Zeile 369
    
    ... Wenn DBMS_ASSERT also einen Fehler auslöst, besteht SQL Injection-Gefahr.

  • Die Übergabe von O'Neill muss jedoch gehen (damit wir mit den Quotes nicht durcheinanderkommen, verwenden wir die ab 10gR2 verfügbare neue Quote-Syntax).
    SQL> select dbms_assert.enquote_literal(q'#O''Neill#') from dual;
    
    DBMS_ASSERT.ENQUOTE_LITERAL(Q'#O''NEILL#')
    --------------------------------------------------------------------------------
    'O''Neill'
    
  • Objektnamen müssen mit ENQUOTE_NAME behandelt werden. Grund sind die unterschiedlichen "Quote"-Zeichen - Objektnamen müssen mit doppelten Anführungszeichen versehen werden.
    SQL> select dbms_assert.enquote_name('EMP" union all (select 1 from dual)--') from dual;
    select dbms_assert.enquote_name('EMP" union all (select 1 from dual)--') from dual
           *
    FEHLER in Zeile 1:
    ORA-06502: PL/SQL: numerischer oder Wertefehler
    ORA-06512: in "SYS.DBMS_ASSERT", Zeile 310
    ORA-06512: in "SYS.DBMS_ASSERT", Zeile 354
    ORA-06512: in Zeile 1
    
  • Ein Versuch mit ENQUOTE_LITERAL erkennt die SQL Injection nicht. Man sieht, wie wichtig es ist, die richtige Funktion im Paket DBMS_ASSERT zu benutzen.
    SQL> select dbms_assert.enquote_literal('EMP" union all (select 1 from dual)--') from dual;
    
    DBMS_ASSERT.ENQUOTE_LITERAL('EMP"UNIONALL(SELECT1FROMDUAL)--')
    --------------------------------------------------------------------------------
    'EMP" union all (select 1 from dual)--'
    
    
  • The "classic" attack: Passing KING' or 1=1 -- (close the quote, add own condition and comment the rest out)
    SQL> select dbms_assert.enquote_literal('KING''or 1=1') from dual;
    select dbms_assert.enquote_literal('KING''or 1=1') from dual
           *
    FEHLER in Zeile 1:
    ORA-06502: PL/SQL: numeric or value error
    ORA-06512: in "SYS.DBMS_ASSERT", Zeile 310
    ORA-06512: in "SYS.DBMS_ASSERT", Zeile 369
    
    ... so we see: if DBMS_ASSERT raises an exception, SQL injection was detected.
  • Let's check another literal: O'Neill has an apostroph - does this also lead to a SQL injection alert? For better readability we use the new quote syntax intruduced in 10gR2.
    SQL> select dbms_assert.enquote_literal(q'#O''Neill#') from dual;
    
    DBMS_ASSERT.ENQUOTE_LITERAL(Q'#O''NEILL#')
    --------------------------------------------------------------------------------
    'O''Neill'
    
  • Object (table, view, package and other) names have to be treated with ENQUOTE_NAME. Reason is the different quote character (object names are quoted with the double quote)
    SQL> select dbms_assert.enquote_name('EMP" union all (select 1 from dual)--') from dual;
    select dbms_assert.enquote_name('EMP" union all (select 1 from dual)--') from dual
           *
    ERROR in line 1:
    ORA-06502: PL/SQL: numeric or value error
    ORA-06512: in "SYS.DBMS_ASSERT", Zeile 310
    ORA-06512: in "SYS.DBMS_ASSERT", Zeile 354
    ORA-06512: in line 1
    
  • An attempt using ENQUOTE_LITERAL does not detect the SQL Injection attack - it's obvious that using the correct function within DBMS_ASSERT is essential.
    SQL> select dbms_assert.enquote_literal('EMP" union all (select 1 from dual)--') from dual;
    
    DBMS_ASSERT.ENQUOTE_LITERAL('EMP"UNIONALL(SELECT1FROMDUAL)--')
    --------------------------------------------------------------------------------
    'EMP" union all (select 1 from dual)--'
    
Extrem wichtig ist also die richtige Verwendung von ENQUOTE_NAME bzw. ENQUOTE_LITERAL. Wenn man versucht, SQL Injection-Angriffe auf Objektnamen mit ENQUOTE_LITERAL zu erkennen, geht das genauso schief wie der Versuch auf Literalen mit ENQUOTE_NAME. Verwendet immer die richtige DBMS_ASSERT-Funktion, je nachdem, ob Ihr ein Literal oder einen Datenbank-Objektnamen in euer SQL einbaut. Obige PL/SQL-Prozedur würde dann so aussehen:
It is absolutely essential to use ENQUOTE_NAME or ENQUOTE_LITERAL in the correct context. Attempts to detect SQL injection attacks on object names with ENQUOTE_LITERAL fails as well as with ENQUOTE_NAME on literals. Using the correct function in the correct context makes cure that SQL injection attacks will be detected. Applied to the above PL/SQL procedure the code looks as follows:
create or replace procedure doit(
  p_table    in varchar2, 
  p_valuecol in varchar2,
  p_wherecol in varchar2,
  p_param    in varchar2
)
is
  v_value varchar2(32767);
  v_sql   varchar2(32767);
begin
  v_sql := 'select '||dbms_assert.enquote_name(p_valuecol)||' '||
           'from '||dbms_assert.enquote_name(p_table)||' '||
           'where '||dbms_assert.enquote_name(p_wherecol)||' = '||dbms_assert.enquote_literal(p_param);
  execute immediate v_sql into v_value;
end;
/
Wenn man es richtig einsetzt, kann man seine PL/SQL-Prozeduren mit DBMS_ASSERT also recht gut gegen SQL Injection-Angriffe absichern. Ich würde sogar so weit gehen: Die Verwendung von DBMS_ASSERT ist bei dynamischem SQL inzwischen zur absoluten Pflicht geworden (ich muss mich bei meinen künftigen Postings nur selbst dran halten).
Übrigens: DBMS_ASSERT ist zwar erst ab Version 11 dokumentiert, wurde jedoch auf 9.2.0.8 und 10.2.0.3 portiert; ist also in allen aktuellen Datenbankversionen vorhanden.
DBMS_ASSERT is a nice approch to fix SQL injection vulnerabilities in PL/SQL code. The important thing is to use it correctly (say: the correct function). I'd so far to say that the usage of DBMS_ASSERT is a must when using dynamic SQL statements in PL/SQL code. The only thing I have to do in the future is to keep on this.

17. September 2008

Den Speicherverbrauch einer Datenbank-Session ermitteln - nur mit SQL

English title: Determine the memory consumption of a database session

Gerade wenn man viel PL/SQL-Logik programmiert, muss man hin und wieder feststellen, wieviel Hauptspeicher eine Datenbanksession verbraucht. Und man hat nicht immer die Zeit, die grafische Oberfläche zu starten ... und manchmal (Entwicklermaschine) hat man auch gar nicht mehr die Ressourcen dafür frei ...
When you're coding lots of PL/SQL code you might need to determine the sessions's memory consumption sometimes. And sometimes you don't have the time to start a graphical user interface just for that - and there are situations where you just don't have the resources (on your development machine) to start Enterprise Manager oder SQL Developer or any other tool.
Heute habe ich (zum x-ten Mal) wieder ein SQL-Skript dafür geschrieben - und damit ich es das nächste Mal nicht schon wieder zusammensuchen muss, poste ich es nun hier. Und vielleicht ist es ja für den einen oder andeeren ebenfalls hilfreich.
Today I've compiled (for the x-th time) a SQL script displaying the memory statistics for a particular database session. And today I'm posting it here - just that I have it ready the next time I need it. And perhaps it's also useful for you ...
col program format a30
col machine format a30
col name format a30

set verify off
set define '&'

set lines 120

accept UNAME default 'SYS' prompt '>> Username [SYS] : '

select si.sid, si.program, si.machine, sa.name, sum(ss.value) value
from v$sesstat ss, v$statname sa, v$session si
where ss.sid = si.sid and (sa.name like '%pga%' or sa.name like '%uga%')
and si.username = '&UNAME.' and sa.statistic# = ss.statistic#
group by si.sid, si.program, si.machine, sa.name
order by 1,2,3
/
Mit der LIKE Abfrage kann man die Statistiken eingrenzen, die man sehen möchte - das Beispiel selektiert den PGA und UGA Verbrauch. Mit einem select distinct name from v$statname kann man sich ansehen, welche Statistiken es gibt. Das Skript zeigt einen "Snapshot" der aktuellen Situation. Die Ausgabe sieht dann so aus:
You can select the statistic you want to have displayed by changing the LIKE expression in the query. A select distinct name from v$statname shows you the available statistics. Running the script gives you a "snapshot" of the current situation. The output looks like this:
SQL> @mem
>> Username [SYS] : PARTNER

       SID PROGRAM                        MACHINE                        NAME                               VALUE
---------- ------------------------------ ------------------------------ ------------------------------ ---------
       137 sqlplus.exe                    DE-ORACLE\cczarski-de          session uga memory max           3369532
       137 sqlplus.exe                    DE-ORACLE\cczarski-de          session uga memory               1480156
       137 sqlplus.exe                    DE-ORACLE\cczarski-de          session pga memory max           8500756
       137 sqlplus.exe                    DE-ORACLE\cczarski-de          session pga memory               8500756
         : :                              :                              :                                      :

12. September 2008

PL/SQL Paket "LOB_WRITER_PLSQL": Patch 0.5.2 verfügbar

English title: PL/SQL package LOB_WRITER_PLSQL: Patch version 0.5.2 available

Gestern habe ich nach einem Hinweis durch einen Kollegen einen Fehler im Package LOB_WRITER_PLSQL meines "PL/SQL-Betriebssystem-Paketes" (OS_COMMAND) behoben. Der Fehler tritt nur auf Windows-Plattformen auf und äußert sich dadurch, dass Binärdateien (Prozedur WRITE_BLOB) nicht korrekt geschrieben werden. Ursache ist das verwendete Paket UTL_FILE; die Prozedur FOPEN benötigt auf Windows den OPEN_MODE 'wb' (anstelle von 'w') um korrekt arbeiten zu können. Auf Unix/Linux-Plattformen macht das keinen Unterschied. Das ist in der jüngsten Version 0.5.2 behoben. Der "Patch" betrifft nur das Paket LOB_WRITER_PLSQL.
Yesterday a collegue found a bug in my PL/SQL packages for operating system interaction "OS_COMMAND". The error is in the LOB_WRITER_PLSQL package and occurs only on Windows platforms: Binary files are not written correctly into the file system - the resulting files are corrupt. The reason is the procedure FOPEN in the UTL_FILE package - if you want to write binary content on windows platforms make sure that you provide the OPEN_MODE 'wb' and not just 'w'. On Unix/Linux platforms this does not make a difference. The newest version 0.5.2 corrects this. Only the package LOB_WRITER_PLSQL is affected by the "patch".
Wer nicht neu herunterladen möchte, kann auch direkt selbst korrgieren ...
If you don't want to download again you can also correct yourself ...
procedure write_blob(
  p_directory varchar2,
  p_filename  varchar2,
  p_data      blob
) is
:
  v_file := utl_file.fopen(
    location => p_directory,
    filename => p_filename,
    open_mode => 'wb',
    max_linesize => 32000
  );
  :
Alle anderen finden die korrigierte Version 0.5.2 wie immer auf Sourceforge.
You find the corrected version 0.5.2 as usual on Sourceforge.

7. September 2008

Ein einfacher Ansatz, Dateien eines Ordners komplett in Tabellen zu laden

English title: A simple approach to load an entire directory into a table

Letzte Woche bekam ich die Frage, wie man denn am einfachsten ein ganzes Verzeichnis voller Dateien (Bilder, PDF-Dokumente oder andere) in die Datenbank laden kann. Der SQL*Loader setzt ja voraus, dass alle Inhalte in einer oder zumindest in bestimmten, bekannten Dateien enthalten sind. Im vorliegenden Fall ist die Anforderung ja eher, dass man ein Verzeichnis kennt und einfach den ganzen Inhalt in eine Tabelle mit einer BLOB-Spalte laden möchte.
Last week I was asked for an easy approach to load an entore directory containing files (PDF documents, images, etc) into a database table. SQL*Loader is not appropriate for this since all the content must be in one or multiple known files. But this case is another - the directory is known, but not the files in it.
Meist fängt man dann an, umfangreiche Java oder C-Programme zu schreiben - das muss aber nicht unbedingt sein. Hilfreich kann die PL/SQL-Unterstützung für Betriebssystem-Kommandos (OS_COMMAND) sein, welche ich vor einiger Zeit veröffentlicht habe und welche hier zum Download bereitsteht.
Most people start coding at this point: some C/C++, Java or PL/SQL logic which imports the files into the database. But this is not necessary: The PL/SQL package for operating system interaction (OS_COMMAND) which I published some time ago (and which is downloadable here) might be helpful here.
Nach Installation steht der Objekttyp FILE_TYPE und das Paket FILE_PKG zur Verfügung. Zunächst brauchen wir das Listing eines Verzeichnisses: Nichts leichter als das!
After installation we have an object type FILE_TYPE and the PL/SQL package FILE_PKG. First we need the directory listing: Nothing is easier!
SQL> select e.file_name, e.is_dir, e.file_size 
  2  from table(file_pkg.get_file_list(file_pkg.get_file('/path/to/my/dir'))) e

FILE_NAME                      I  FILE_SIZE
------------------------------ - ----------
another_directory              Y          0
DSC_5112_m.jpg                 N    5384837
DSC_5128_m.jpg                 N    7005470
DSC_5130_m.jpg                 N    6212896
DSC_5131_m.jpg                 N    7203724
DSC_5134_m.jpg                 N    7295177
DSC_5135_m.jpg                 N    7057265
DSC_5136_m.jpg                 N    7194343
:                              :          :
Mit der Methode GET_CONTENT_AS_BLOB können die Dateien ausgelesen werden; geht es also darum, eine Tabelle effizient zu füllen, könnte das so aussehen:
The method GET_CONTENT_AS_BLOB retrieves the file contents as a BLOB. To import the files into a table we can use it as follows:
drop table bilder_tab
/

create table bilder_tab (
  filename varchar2(200),
  content  blob
) nologging
/

/*
 * All at once 
 */

declare
  v_dirname varchar2(200) := '/path/to/my/dir';
begin
  insert /*+ APPEND*/ into bilder_tab (
    select e.file_name, e.get_content_as_blob() 
    from table(file_pkg.get_file_list(file_pkg.get_file(v_dirname))) e
    where e.is_dir = 'N'
  );
end;
/
sho err

commit
/
Ein anderer Ansatz (mit Commit-Intervall) könnte so aussehen:
Another approach (which COMMITs every n files) might look as follows:
set serveroutput on

declare
  v_dirname varchar2(200) := '/path/to/my/dir';
  v_counter pls_integer := 0;
  v_commit  pls_integer := 5;
begin
  for i in (
    select e.file_name, e.get_content_as_blob() as content
    from table(file_pkg.get_file_list(file_pkg.get_file(v_dirname))) e
  ) loop
    v_counter := v_counter + 1;
    insert into bilder_tab values (i.file_name, i.content);
    if mod(v_counter, v_commit) = 0 then
      commit;
      dbms_output.put_line(v_counter || 'file loaded: COMMIT!');
    end if;
  end loop;
end;
/
sho err

commit
/

3. September 2008

Deutschsprachige "DBA Community"-Seite: Schaut mal rein

English title: German "DBA Community" page - have a look!

Heute möchte ich mal ein wenig Werbung für die neue deutschsprachige "DBA Community"-Seite meiner Kollegen Ulrike Schwinn, Heinz-Wilhelm Fabry, Sebastian Solbach und Ralf Durben machen:
If you're able to read german, the new "DBA Community" page of my collegues Ulrike Schwinn, Heinz-Wilhelm Fabry, Sebastian Solbach and Ralf Durben might be interesting for you:
Wie bei der APEX Community findet Ihr dort Tipps, Tricks und HowTos - zum Thema Datenbankadministration:
  • DBMS_REDEFINITION
  • Datenbank-Recovery
  • Datenverschlüsselung
  • Und mehr ...
Schaut einfach mal rein - wer möchte, kann sich für den Newsletter, der alle 14 Tage erscheint, registrieren.
Similar to the (also german) APEX Community page you'll find tips, tricks and how to documents - focusing on database administration topics:
  • DBMS_REDEFINITION
  • Database recovery
  • Data encryption
  • and more ...
Just have a look - you can also register for the newsletter which releases every two weeks ... if you like.

Beliebte Postings