Forum Stats

  • 3,854,903 Users
  • 2,264,431 Discussions
  • 7,905,825 Comments

Discussions

PL/SQL 101 : Substitution vs. Bind Variables

2

Comments

  • [Deleted User]
    [Deleted User] Posts: 0 Silver Trophy

    Brilliant as usual Blu. As always I'm learning from your articles: I didn't know that the PL engine was clever enough to implicitly create bind variables (so I always wrote them myself to make sure). Nice to know! Makes code quite a bit more readable imho.

    BluShadow
  • BluShadow
    BluShadow Member, Moderator Posts: 42,349 Red Diamond

    Great article.

    As I read thru, I was reminded of how "CURSOR_SHARING" influences parsing/bind variables and how "CURSOR_SHARING=FORCE" can be a dangerous thing, and may be used as a quick (dirty) fix, when in actual fact the query/application itself needs fixing, not permanently setting a db parameter for what should be only temporary work around at best. Might be worth mentioning the pitfalls/traps of setting this parameter to FORCE.

    Cheers,

    Gaz.

    So very true Gaz, however as it's a 101 article just giving the basics for beginners, best not confuse people with things they don't understand.  The problem with telling people about what they shouldn't do, is that they then go and try to do it and use it without fully understanding the implications.

    Don't think of a banana.  Oops you just did. 

  • 3531062
    3531062 Member Posts: 1

    Greetings,

    I have a simple question,

    ----code below----

    DECLARE

    V_ONE int;

    BEGIN

    V_ONE:=1;

    IF V_ONE=1 THEN

       &ONE;

    ELSE

       &TWO;

    END IF;

    END;

    ---

    The upper code, should prompt only once for &one right?

    But seems its not the situation, after i give value to &one and press enter another prompt &two also gets called.

    What is the logic for using substitution vairables in if...else conditions?

    Sanjiv Ranjit

  • BluShadow
    BluShadow Member, Moderator Posts: 42,349 Red Diamond

    Greetings,

    I have a simple question,

    ----code below----

    DECLARE

    V_ONE int;

    BEGIN

    V_ONE:=1;

    IF V_ONE=1 THEN

       &ONE;

    ELSE

       &TWO;

    END IF;

    END;

    ---

    The upper code, should prompt only once for &one right?

    But seems its not the situation, after i give value to &one and press enter another prompt &two also gets called.

    What is the logic for using substitution vairables in if...else conditions?

    Sanjiv Ranjit

    If you read the above article you'll come to understand why.

    In short, you are using "substitution variables" in your code.  At the point you submit this code, the code hasn't even reached the database, and is NOT being executed as code.  Before the code gets submitted to be executed by the database, the interface (e.g. SQL*Plus) wants to substitute the substitution variables you've specified for actual values/text, so it will prompt you for ALL of those variables, before the code is then submitted to be executed.  Only once they've all be substituted will the code execute, and the IF statement determine that it needs to execute whatever code is substituted in place of &ONE;

    L. FernigriniRyan O. Lee
  • Vikronium
    Vikronium Member Posts: 25 Bronze Badge
  • Excellent Post.

    Substitution and input bind variables are both methods for replacing a variable in a query or procedural code with a corresponding value from your program. Some databases call bind variables "parameters".

    Thanks For Sharing..!

  • User_OLK44
    User_OLK44 Member Posts: 8 Red Ribbon

    Another example of using bind variable is this:


    create or replace function myfn(p_deptno in number) return varchar2 is

     v_dname varchar2(20);

    begin

     select dname

     into  v_dname

     from  dept

     where deptno = p_deptno;

     return v_dname;

    end;

    /


    Function created.


    Now, this isn't immediately obvious, but what we have here is the ability of the PL language to seamlessly integrate SQL within it (giving us PL/SQL). It looks as though we just have an SQL statement in our code, but in reality, the PL engine parses the query and supplies the query to the SQL engine with a bind variable placeholder for where the PL variable (parameter p_deptno in this case) is within it. So the SQL engine will get a query like...


    select dname

    from  dept

    where deptno = :1


    #################################

    If above is the case, then why developers needs to explicitly use bind variables in code to prevent SQL injection.

  • BluShadow
    BluShadow Member, Moderator Posts: 42,349 Red Diamond

    If above is the case, then why developers needs to explicitly use bind variables in code to prevent SQL injection.


    If you understand how SQL injection works, then when someone just concatenated data together to create a dynamic SQL statement, it's possible for someone to turn that one statement in to multiple statements or include additional columns etc. That's a bit like you could do with the substitution variables.

    With Bind variables, the database will quite literally take whatever value is supplied and treat it as an atomic value within the statement. Someone trying to provide a value (especially for strings) that contained other columns or statement terminators with further statements, would actually just be providing a value for the comparison which wouldn't match.

    So, in a simplistic sense, if you had:

    sql := 'select name from employee where employee_id = '||emp_id;
    execute immediate sql;
    

    and emp_id was a string, then someone could supply a value like '123; delete from password;'

    resulting in a dynamic statement to be executed:

    select name from employee where employee_id = 123; delete from password;
    

    which could have devastating effects.

    Using bind variables:

    select name into v_name from employee where employee_id = emp_id;
    

    someone supplying that same value would just get no data back, because oracle would consider the query to be:

    select name from employee where employee_id = :1
    

    internally, and the value of :1 would be the supplied string, with no employee_id matching '123; delete from password'

  • User_OLK44
    User_OLK44 Member Posts: 8 Red Ribbon

    Thats a great explaination.

    Few more followup questions.

    1. So SQL injection impacts only concatenated strings ? and NOT the sqls in which only some data values are passed from variables( like in stored procedures) used maybe in select part or where condition ( Not concatenated  || )
    2. Can we say every PLSQL variable is a bind variable and we do NOT need to write it explicity using colon(:) syntax if we are not using concatenated strings in PLSQL procedure code ?