Oracle Database Express Edition (XE)

Nice job.

Excellent article BluShadow.  I use substitution variables in combination with my Unix shell scripting and dynamically creating SQL scripts (not to be confused with dynamic sql) but using a substitution variable for different schema names.

Nice blu..:)

Nice article.

Just one suggestion. I'd change comments BADBINDING and GOODBINDING to WITHOUTBINDING and WITHBINDING. BADBINDING could be a bit misleading since there is no binding in that SQL at all. Same way GOODBINDING is misleading too. Binding isn't good or bad - we either use binding or not.

SY.

Simple, sound and well written document.. waiting for the next one from you..;)

Nice article.

Just one suggestion. I'd change comments BADBINDING and GOODBINDING to WITHOUTBINDING and WITHBINDING. BADBINDING could be a bit misleading since there is no binding in that SQL at all. Same way GOODBINDING is misleading too. Binding isn't good or bad - we either use binding or not.

SY.

Solomon Yakobson wrote:

Nice article.

Just one suggestion. I'd change comments BADBINDING and GOODBINDING to WITHOUTBINDING and WITHBINDING. BADBINDING could be a bit misleading since there is no binding in that SQL at all. Same way GOODBINDING is misleading too. Binding isn't good or bad - we either use binding or not.

SY.

That's a fair point Solomon, so I've now updated those comments.

Good Article @"BluShadow"

very good article.

I'd add a note on "set define off" and "set define on" and how that impacts the substitutions

very good article.

I'd add a note on "set define off" and "set define on" and how that impacts the substitutions

Certainly something I may consider, however those commands are specific to the SQL*Plus tool, so aren't necessary relevant if someone is using another tool such as Toad, PL/SQL Developer or SQL Developer etc. as they have their own ways of turning substitution variable detection on/off (in some cases it depends on whether the script is run directly in the tool or "run as script" as well)

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.

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.

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. 

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

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;

Excellent

Nice.

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..!

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.

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'

Thats a great explaination.
Few more followup questions.
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 || )
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 ?

Typically, yes.
Yes, PL/SQL variables used in SQL statements are automatically bound in to the query behind the scenes. The ":" binding is only needed where the query is dyanamically generated, so is therefore a string because you're indicating it as a placeholder to be bound in later.

Kindly help me with a way to use bind variable in utl_file.fopen, i have a vulnerability in my stored procedure code because of below statement.
infile_handle := UTL_FILE.FOPEN(i_directory, i_inFileName, 'R');
---------------------------------------------------------------------
I tried to change it like below but it doesn't work --
vsql := 'UTL_FILE.FOPEN(:1, :2, ''R'')';
EXECUTE IMMEDIATE vsql INTO infile_handle USING i_directory, i_inFileName;

Please do not double post questions. (https://community.oracle.com/tech/developers/discussion/4477277/how-to-use-bind-variable-in-utl-file-fopen)
Your dynamic call is a POL/SQL call - not SQL. It needs to be an anonymous PL/SQL block. It is not.

Considering the brilliance of this article, you humility is all the more impressive.