This content has been marked as final. Show 4 replies
Damir Vadas wrote:Check the variables are what you expect using <tt>dbms_assert</tt>/regular expressions/an <tt>is_number</tt> function.
What is the best security approach for such a cases?
Alternatively, set and run up the job using <tt>dbms_scheduler</tt> instead of <tt>apex_plsql_job</tt>. The scheduler enables you to create a named program with defined formal arguments, and set the actual arguments when you come to run it using <tt>set_job_argument_value/set_job_anydata_value</tt>.
I do not know for Oracle scheduler, but I need APEX_PLSQL_JOB.SUBMIT_PROCESS because this proc is generating PDF through BI Publisher interface, so Apex vars are needed to be set. I thought it was the only way to perform that through APEX_PLSQL_JOB call?
Another problem starts when variable is VARCHAR2 type. What to do then (when is_number is not available).
I mean dbms_assert cannot cover that? So, could you make small example?
The easiest approach , and rather safe, is to use DBMS_ASSERT as already stated.
Here is how your modified code will look
The actual values of the Items just thus get substituted.
l_sql := 'BEGIN some_proc(' || l_actions_diary_id || ' DBMS_ASSERT.ENQUOTE_LITERAL('||:P305_ACTIVE_PERIOD)||'),'|| ' DBMS_ASSERT.ENQUOTE_LITERAL('|| :APP_USER||'),'|| ' DBMS_ASSERT.ENQUOTE_LITERAL('|| :APP_ID||'),'|| ' DBMS_ASSERT.ENQUOTE_LITERAL('|| :G_XE_APP_ID||') );END ;' ;
With this approach you do not have to worry about is_numeric if your proc/function has the parameters defined as numbers Oracle will perform the implicit conversions.