Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2003
    Posts
    148

    Unanswered: Question on SP's w.bind var's & SQL Injection

    Hey everyone...hope you're enjoyin your monday. I was just doing some readong over at askTom, specifically: http://asktom.oracle.com/pls/ask/f?p...23863706595353

    If that doesn't work, http://asktom.oracle.com and search for sql injection, you'll see a topic/question come up with the same name. In this thread he mentions you should use bind variables, not concat variables into your sql directly. My confusion stems from the definition of bind variables.

    He states this is dangerous (and I do see why...)
    Code:
    create or replace procedure p( p_where_clause in varchar2 )
    is
       l_var varchar2(250);
    begin
        
       execute immediate 'select emp_name from emp ' || p_where_clause 
          into l_var;
    and this is proper...
    Code:
    this:
    begin
    ...
       select emp_name into l_var 
         from emp where emp_no = var1; 
    
    ....
    However, in other parts of this thread, and others (search: bind variables, thread called the same) he references bind variables like this...
    Insert into myTable (col A) VALUES (:x);

    Which he states, is a bind variable. I have code that looks like this...
    Code:
     AS 
     PROCEDURE InsertQueue
     /*Procedure will insert a new queue into the DB*/
     (p_QueueName      IN  tbl_Queue.Q_Name%TYPE)
     IS
     p_nID        tbl_queue.Q_id%type;
       BEGIN
    
        Insert into Tbl_Queue (Q_Name, Active) VALUES (p_QueueName, 1);
     
     END;
    Will those be implicitly converted to bind variables? Should I be doing somethin further to protect my apps from SQL injection? Any thoughts or ops on this appreciated!

  2. #2
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    AFAIK, you don't have to fear SQL Injection here. As you said, you have some sort of "implicit binding" here thanks to PLSQL. The use of ":x" is only used in dynamic SQL, in PLSQL you use that with DBMS_SQL or EXECUTE IMMEDIATE when you can't do a static query like you did. In C or Java, you can only use bind variables with ":x" or "?" in queries, and then effectively bind them.

    HTH & Regards,

    RBARAER

Posting Permissions

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