Dynamické SQL

EXECUTE IMMEDIATE

V SQL procedurach lze bezproblémově vkládat do kódu většinu SQL příkazů, které můžeme parametrizovat proměnnými. Parametr nelze použít ve smyslu názvu sloupce nebo proměnné. Také nelze vložit např. SQL příkaz CREATE FUNCTION. Proto existuje tzv. dynamické SQL - příkazy EXECUTE IMMEDIATE a EXECUTE. Příkaz EXECUTE IMMEDIATE provede libovolný SQL příkaz předaný v řetězci. Výsledkem mohou být naplněné určené proměnné, pokud použijete klauzuli INTO.

CREATE OR REPLACE FUNCTION fx() 
RETURNS int AS 
$$
  BEGIN 
    DECLARE a,b int; 
    EXECUTE IMMEDIATE 'SELECT 10,20' INTO a,b; 
    PRINT a,b; 
    RETURN a+b; 
  END; 
$$ LANGUAGE plpgpsm;

Syntaxe

EXECUTE IMMEDIATE vyraz [INTO (seznam promennych| promenna typu RECCORD nebo ROW)]

PREPARE a EXECUTE

EXECUTE IMMEDIATE se nehodí pro opakované provádění SQL příkazu. Pokaždé se vytváří nový prováděcí plán, což v některých případech může mít znatelně zpomalovat. Potom se vyplatí uvažovat o předzpracovaných SQL příkazech. Předzpracovaný SQL příkaz získáme příkazem PREPARE a necháme jej provést příkazem EXECUTE. Můžeme použít klauzuli INTO pro uložení výsledku dotazu do proměnných. V případě, že jsme použili parametrizovaný SQL příkaz (obsahuje symboly ? na pozicích parametrů) musíme předpřipravený SQL příkaz spouštět s parametry předávanými prostřednictvím klauzule USING. Použitím parametrů eliminujeme riziko SQL injection.

CREATE OR REPLACE FUNCTION fxx(a int)
RETURNS float AS 
$$
  BEGIN
    DECLARE f float;
    PREPARE prep(float) from 'select $1';
    EXECUTE prep INTO f USING a; 
    RETURN f ;
  END;
$$ LANGUAGE plpgpsm;
CREATE FUNCTION

Syntaxe

PREPARE nazev [ '(' seznam typu parametru ')' ] FROM vyraz 

EXECUTE nazev [INTO (seznam promennych | promenna typu RECORD nebo ROW)]
              [USING seznam promennych]

Dynamické kurzory

Příkazy EXECUTE a EXECUTE IMMEDIATE neumožňují zpracovat více než jeden řádek. Proto byl příkaz OPEN rozšířen a umožňuje jako zdroj použít i předzpracovaný příkaz. Pokud předzpracovaný příkaz vyžaduje parametry, pak příkaz OPEN musí obsahovat klauzuli USING. Dynamické kurzory nelze použít jako posuvné kurzory.

CREATE OR REPLACE FUNCTION fx(a int)                                                                                    
RETURNS int AS                                                                                                          
$$                                                                                                                      
#option dump                                                                                                            
  BEGIN                                                                                                                 
    DECLARE x, y integer;                                                                                               
    DECLARE SQLSTATE char(5);                                                                                           
                                                                                                                        
    DECLARE cx CURSOR FOR prepstmt;                                                                                     
                                                                                                                        
    PREPARE prepstmt(int) FROM 'SELECT a*100, b*100 FROM Foo WHERE Foo.a = $1';                                         
                                                                                                                        
    OPEN cx USING a;                                                                                                    
                                                                                                                        
    FETCH cx INTO x, y;                                                                                                 
    WHILE SQLSTATE = '00000' DO                                                                                         
      PRINT x, y;                                                                                                       
      FETCH cx INTO x, y;                                                                                               
    END WHILE;                                                                                                          
                                                                                                                        
    CLOSE cx;                                                                                                           
    RETURN a;                                                                                                           
  END;                                                                                                                  
$$ LANGUAGE plpgpsm;        

SQL injection

SQL injection je jedním ze způsobů napadnutí SQL serveru. Útočník se pokusí pomocí parametrů modifikovat SQL příkaz tak, aby se dostal k obsahu libovolné tabulky v databázi. Za normálních okolností jsou SQL procedury odolné vůči tomuto typu útoku díky internímu používání předzpracovaných SQL příkazů (prepared statements). Dynamické SQL ale představuje pootevřená zadní vrátka, kudy se útočník může do systému dostat. Dynamickému SQL se v řadě případů nevyhneme, nicméně je nutné pečlivě kontrolovat obsah proměnných (nebo používat klauzuli USING).

  --nebezpecne, promenna _tab a _var predstavuji bezpecnostni riziko
  EXECUTE IMMEDIATE 'SELECT * FROM '|| _tab || ' WHERE a = ''' || _var ||''' INTO _a, _b;

  -- predstavuje bezpecnostni riziko, staci vsak jen test, zda _tab neobsahuje specialni znaky
  -- rizikem je pouze promenna _tab
  PREPARE psel(varchar) 'SELECT * FROM ' || _tab || 'WHERE a = ?';
  EXECUTE psel INTO _a, _b  USING _var;