4 Replies Latest reply: May 2, 2012 2:47 AM by 737189 RSS

    APEX_PLSQL_JOB.SUBMIT_PROCESS (exec immediate) and avoiding sql injection

    737189
      Hi!

      I have apex job (APEX_PLSQL_JOB call) which is in WEB page defined as:
      l_sql := 'BEGIN some_proc('
                            || l_actions_diary_id
                            ||','
                            ||:P305_ACTIVE_PERIOD
                            ||','''
                            ||:APP_USER
                            ||''','
                            ||:APP_ID
                            ||','
                            ||:G_XE_APP_ID
                            ||');  END;'
               ;
      l_retval := APEX_PLSQL_JOB.SUBMIT_PROCESS (p_sql   => l_sql);
      This is security problem (SQL injection point) which coul be resolved as:
      l_sql := 'BEGIN some_proc('
                            || l_actions_diary_id
                            ||',:P305_ACTIVE_PERIOD, :APP_USER, :APP_ID, :G_XE_APP_ID);END ;'
               ;
      but then no variables (:P305_ACTIVE_PERIOD, :APP_USER, :APP_ID, :G_XE_APP_ID) will be parsed with their real values!?

      What is the best security approach for such a cases?

      Rg,
      Damir