绑定变量oracle注入,PL/SQLChallenge每日一题：2015820绑定变量以及SQL注入

最先答对且答案未经编辑的puber将获得纪念章一枚(答案不可编辑但可发新贴补充或纠正)&＃xff0c;其他会员如果提供有价值的分析、讨论也可获得纪念章一枚。

以往旧题索引&＃xff1a;

http://www.itpub.net/forum.php?m ... eid&typeid&＃61;1808

原始出处&＃xff1a;

http://www.plsqlchallenge.com/

作者&＃xff1a;Steven Feuerstein

运行环境&＃xff1a;SQLPLUS, SERVEROUTPUT已打开

注&＃xff1a;本题给出答案时候要求给予简要说明才能得到奖品

我执行了下列语句:

CREATE TABLE plch_employees

(

employee_id     INTEGER,

department_id   INTEGER,

last_name       VARCHAR2 (100)

)

/

BEGIN

INSERT INTO plch_employees

VALUES (100, 100, &＃39;Squirrel&＃39;);

INSERT INTO plch_employees

VALUES (200, 100, &＃39;Fox&＃39;);

INSERT INTO plch_employees

VALUES (300, 100, &＃39;Chipmunk&＃39;);

COMMIT;

END;

/

CREATE OR REPLACE PACKAGE plch_pkg

AUTHID DEFINER

IS

FUNCTION last_names_for (where_in   IN VARCHAR2,

type_in    IN VARCHAR2)

RETURN SYS_REFCURSOR;

PROCEDURE show_count (cur_in IN SYS_REFCURSOR);

END plch_pkg;

/

CREATE OR REPLACE PACKAGE BODY plch_pkg

IS

FUNCTION last_names_for (where_in   IN VARCHAR2,

type_in    IN VARCHAR2)

RETURN SYS_REFCURSOR

IS

c_query    VARCHAR2 (32767)

:&＃61;    &＃39;select last_name from plch_employees WHERE &＃39;

|| CASE type_in

WHEN &＃39;WHOLE&＃39; THEN where_in

ELSE &＃39;department_id &＃61; :where_in&＃39;

END;

l_cursor   SYS_REFCURSOR;

BEGIN

DBMS_OUTPUT.put_line (c_query);

CASE type_in

WHEN &＃39;WHOLE&＃39;

THEN

OPEN l_cursor FOR c_query;

ELSE

OPEN l_cursor FOR c_query USING where_in;

END CASE;

RETURN l_cursor;

END last_names_for;

PROCEDURE show_count (cur_in IN SYS_REFCURSOR)

IS

l_count   PLS_INTEGER :&＃61; 0;

l_name    plch_employees.last_name%TYPE;

BEGIN

LOOP

FETCH cur_in INTO l_name;

EXIT WHEN cur_in%NOTFOUND;

l_count :&＃61; l_count &＃43; 1;

END LOOP;

DBMS_OUTPUT.put_line (&＃39;Count &＃61; &＃39; || l_count);

END show_count;

END plch_pkg;

/

哪些选项执行之后屏幕上会显示 "Count &＃61; 3" ?

(A)

DECLARE

l_rows   SYS_REFCURSOR;

BEGIN

DBMS_OUTPUT.put_line (&＃39;Show Me!&＃39;);

l_rows :&＃61;

plch_pkg.last_names_for (&＃39;department_id &＃61; 100&＃39;, &＃39;WHOLE&＃39;);

plch_pkg.show_count (l_rows);

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack);

END;

/

(B)

DECLARE

l_rows   SYS_REFCURSOR;

BEGIN

DBMS_OUTPUT.put_line (&＃39;Show Me!&＃39;);

l_rows :&＃61;

plch_pkg.last_names_for (&＃39;department_id &＃61; 100&＃39;, &＃39;PART&＃39;);

plch_pkg.show_count (l_rows);

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack);

END;

/

(C)

DECLARE

l_rows   SYS_REFCURSOR;

BEGIN

DBMS_OUTPUT.put_line (&＃39;Show Me!&＃39;);

l_rows :&＃61;

plch_pkg.last_names_for (

&＃39;department_id &＃61; 100 UNION

select &＃39;&＃39;USER: &＃39;&＃39; || username from all_users&＃39;,

&＃39;WHOLE&＃39;);

plch_pkg.show_count (l_rows);

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack);

END;

/

(D)

DECLARE

l_rows   SYS_REFCURSOR;

BEGIN

DBMS_OUTPUT.put_line (&＃39;Show Me!&＃39;);

l_rows :&＃61; plch_pkg.last_names_for (&＃39;100&＃39;, &＃39;PART&＃39;);

plch_pkg.show_count (l_rows);

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack);

END;

/