PL/SQL
Beispiele setzen folgende Tabellenstruktur voraus:
CREATE TABLE users (
username VARCHAR2(8) UNIQUE,
accessed_at DATE,
superuser NUMBER(1,0)
);
INSERT INTO users VALUES ('janihur', sysdate, 0);
INSERT INTO users VALUES ('petdance', sysdate - 12, 1);
INSERT INTO users VALUES ('albundy', sysdate - 3, 0);
INSERT INTO users VALUES ('donduck', sysdate - 18, 0);
Wenn möglich, immer statisches SQL bevorzugen
Statisches SQL kann keine SQL-Einschleusung verursachen.
CREATE OR REPLACE FUNCTION user_access (
p_uname IN VARCHAR2
) RETURN date AS
v_accessed_at date;
BEGIN
SELECT accessed_at INTO v_accessed_at FROM users WHERE username = p_uname;
RETURN v_accessed_at;
END;
/
SELECT user_access('janihur')
AS "JANIHUR LAST SEEN" FROM DUAL;
JANIHUR LAST SEEN
-------------------
2011-08-03 17:11:24
SELECT user_access('whocares'' or superuser = 1 or username = ''whocares')
AS "SUPERUSER LAST SEEN" FROM DUAL;
SUPERUSER LAST SEEN
-------------------
Wenn dynamisches SQL benötigt wird, soll wenn möglich Stringverknüpfung vermieden werden
Durch Stringverknüpfung kann es zu SQL-Einschleusung kommen:
CREATE OR REPLACE FUNCTION user_access (
p_uname IN VARCHAR2
) RETURN date AS
v_accessed_at date;
v_query constant varchar2(32767) :=
'SELECT accessed_at FROM users WHERE username = ''' || p_uname || '''';
BEGIN
EXECUTE IMMEDIATE v_query INTO v_accessed_at;
RETURN v_accessed_at;
END;
/
SELECT user_access('janihur')
AS "JANIHUR LAST SEEN" FROM DUAL;
JANIHUR LAST SEEN
-------------------
2011-08-03 17:11:24
SELECT user_access('whocares'' or superuser = 1 or username = ''whocares')
AS "SUPERUSER LAST SEEN" FROM DUAL;
SUPERUSER LAST SEEN
-------------------
2011-07-22 17:11:24
Man benutzt stattdessen Bindevariablen:
CREATE OR REPLACE FUNCTION user_access (
p_uname IN VARCHAR2
) RETURN date AS
v_accessed_at date;
v_query constant varchar2(32767) :=
'SELECT accessed_at FROM users WHERE username = :a';
BEGIN
EXECUTE IMMEDIATE v_query INTO v_accessed_at USING p_uname;
RETURN v_accessed_at;
END;
/
SELECT user_access('janihur')
AS "JANIHUR LAST SEEN" FROM DUAL;
JANIHUR LAST SEEN
-------------------
2011-08-03 17:11:24
SELECT user_access('whocares'' or superuser = 1 or username = ''whocares')
AS "SUPERUSER LAST SEEN" FROM DUAL;
SUPERUSER LAST SEEN
-------------------
Einschleusung durch implizite Datentypumwandlung
Auch die NLS-Sitzungsparameter (NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT, NLS_TIMESTAMP_TZ_FORMAT, NLS_NUMERIC_CHARACTER) können benutzt werden, um SQL-Abfragen zu verändern oder in sie einzuschleusen.
Im nächsten Beispiel findet Datentypumwandlung statt, wenn p_since implizit in einen String für die Verknüpfung umgewandelt wird. Achtung: der Wert von NLS_DATE_FORMAT beeinflusst den Abfragestring in der Funktion users_since()!
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE OR REPLACE TYPE userlist_t AS TABLE OF VARCHAR2(8);
/
CREATE OR REPLACE FUNCTION users_since(
p_since IN DATE
) RETURN userlist_t PIPELINED AS
v_users userlist_t;
v_query constant varchar2(32767) :=
'SELECT username FROM users WHERE superuser = 0 and accessed_at > ''' || p_since || ''' order by accessed_at desc';
BEGIN
DBMS_OUTPUT.PUT_LINE('v_query = ' || v_query);
EXECUTE IMMEDIATE v_query BULK COLLECT INTO v_users;
FOR i IN v_users.FIRST .. v_users.LAST LOOP
PIPE ROW(v_users(i));
END LOOP;
RETURN;
END;
/
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD"SUPRISE!"';
SELECT COLUMN_VALUE AS "REGULARS" FROM TABLE(users_since(sysdate - 30));
v_query = SELECT username FROM users WHERE superuser = 0 and accessed_at >
'2011-07-04SUPRISE!' order by accessed_at desc
REGULARS
--------
janihur
albundy
donduck
ALTER SESSION SET NLS_DATE_FORMAT = '"'' or superuser = 1 or username = ''whocares"';
SELECT COLUMN_VALUE AS "SUPERUSER IS" FROM TABLE(users_since(sysdate - 30));
v_query = SELECT username FROM users WHERE superuser = 0 and accessed_at > ''
or superuser = 1 or username = 'whocares' order by accessed_at desc
SUPERUSE
--------
petdance
Abhilfe schafft, den Formatwandler explizit anzugeben: to_char(p_since, 'YYYY-MM-DD').
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE OR REPLACE TYPE userlist_t AS TABLE OF VARCHAR2(8);
/
CREATE OR REPLACE FUNCTION users_since(
p_since IN DATE
) RETURN userlist_t PIPELINED AS
v_users userlist_t;
v_query constant varchar2(32767) :=
'SELECT username FROM users WHERE superuser = 0 and accessed_at > ''' || p_since || ''' order by accessed_at desc';
BEGIN
DBMS_OUTPUT.PUT_LINE('v_query = ' || v_query);
EXECUTE IMMEDIATE v_query BULK COLLECT INTO v_users;
FOR i IN v_users.FIRST .. v_users.LAST LOOP
PIPE ROW(v_users(i));
END LOOP;
RETURN;
END;
/
Jetzt wird der Wert des NLS-Parameters NLS_DATE_FORMAT während der Abfrage nicht mehr beachtet.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD"SUPRISE!"';
SELECT COLUMN_VALUE AS "REGULARS" FROM TABLE(users_since(sysdate - 30));
v_query = SELECT username FROM users WHERE superuser = 0 and accessed_at >
'2011-07-04' order by accessed_at desc
REGULARS
--------
janihur
albundy
donduck
