Results 1 to 5 of 5
  1. #1
    Join Date
    May 2014
    Posts
    2

    Unanswered: Problem with dblink variable in plsql

    Hi,

    I've tried and failed to compile. Would you please helping with code below to see
    why dblink variable not passing? Error message is:
    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    14/37 PLS-00103: Encountered the symbol "PUBLIC" when expecting one of
    the following:

    * & = - + ; < / > at in is mod remainder not rem return
    returning <an exponent (**)> <> or != or ~= >= <= <> and or
    like like2 like4 likec between into using || multiset bulk
    --------------------------------------------------------------
    PROCEDURE load_cur_dba_role_privs_dat(a1 varchar2) IS

    begin

    execute immediate 'truncate table cur_dba_role_privs_dat' ;


    EXECUTE IMMEDIATE
    'insert into cur_dba_role_privs_dat
    SELECT
    DECODE (sa.grantee#, 1, 'PUBLIC', u1.name),
    u2.name,
    DECODE (MIN (option$), 1, 'YES', 'NO'),
    DECODE (MIN (u1.defrole),
    0, 'NO',
    1, 'YES',
    2, DECODE (MIN (ud.role#), NULL, 'NO', 'YES'),
    3, DECODE (MIN (ud.role#), NULL, 'YES', 'NO'),
    "NO"),
    ( select NAME FROM V$DATABASE@'|| a1 ||' )
    FROM sys.sysauth$@'|| a1 ||' sa,
    sys.user$@'|| a1 ||' u1,
    sys.user$@'|| a1 ||' u2,
    sys.defrole$@'|| a1 ||' ud
    WHERE sa.grantee# = ud.user#(+)
    AND sa.privilege# = ud.role#(+)
    AND u1.user# = sa.grantee#
    AND u2.user# = sa.privilege# ';

    commit;

    end;


    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You're enabling SQL Injection, so slightly different rules apply. In order to allow users to take control of your database more easily, you need to double the apostrophes within the dynamic code.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    May 2014
    Posts
    2
    Hi,

    Thanks for your help. But when I double quote like you suggested then I got run time error -
    ------------------------------------------------------
    execute immediate
    'insert into cur_dba_role_privs_dat
    SELECT
    DECODE (sa.grantee#, 1, "PUBLIC", u1.name),
    u2.name,
    DECODE (MIN (option$), 1, "YES", "NO"),
    DECODE (MIN (u1.defrole),
    0, "NO",
    1, "YES",
    2, DECODE (MIN (ud.role#), NULL, "NO", "YES"),
    3, DECODE (MIN (ud.role#), NULL, "YES", "NO"),
    "NO"),
    ( select NAME FROM V$DATABASE@'||a1||' )
    FROM sys.sysauth$@'|| a1 ||' sa,
    sys.user$@'|| a1 ||' u1,
    sys.user$@'|| a1 ||' u2,
    sys.defrole$@'|| a1 ||' ud
    WHERE sa.grantee# = ud.user#(+)
    AND sa.privilege# = ud.role#(+)
    AND u1.user# = sa.grantee#
    AND u2.user# = sa.privilege# ';


    ------------------------------------------------------------------
    BEGIN CUR_DATA_LOAD_PKG2.LOAD_CUR_DBA_ROLE_PRIVS_DAT('co mpfdr'); END;
    Error at line 7
    ORA-00904: "NO": invalid identifier
    ORA-06512: at "NM_CENTRAL.CUR_DATA_LOAD_PKG2", line 11
    ORA-06512: at line 1
    -------------------------------------------------------------------

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I didn't explain well... If you want to make it easy for users to take over your server you need to use two apostrophes instead of a double quote character.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Ask Anantha: Q-quote operator introduced in Oracle 10g

    The standard advice when (ab)using EXECUTE IMMEDIATE is to compose the SQL statement in a single VARCHAR2 variable
    Then print the variable before passing it to EXECUTE IMMEDIATE.
    COPY the statement & PASTE into sqlplus to validate its correctness.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •