Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Tomislav.

Asked: November 28, 2019 - 8:04 pm UTC

Last updated: November 29, 2019 - 1:31 am UTC

Version: 19c

Viewed 1000+ times

You Asked

Hi T.O.M.'s
I was reading APEX developer guide and ran into page about bind variables:
https://docs.oracle.com/en/database/oracle/application-express/19.2/htmdb/managing-session-state-values.html#GUID-A052DC04-0D04-4DEA-BDEF-4DCBFF489E07

and there is example of using bind variable:
SELECT * FROM employees WHERE last_name like '%' || :SEARCH_STRING || '%' 


Wouldn't such example make good SQL injection attack candidate?

Regards,
Tomislav

and Connor said...

No this is still OK.

Even though it *looks* like a concatenation, because we will parse the bind first, we'll be OK.

For example, in SQL Plus

--
-- using a bind
--
SQL> variable x varchar2(300);
SQL> exec :x := '''x'' union all select username from dba_users'

PL/SQL procedure successfully completed.

SQL> select * from dual where dummy like '%'||:x||'%';

no rows selected

--
-- compare that to using a substitution
--
SQL> define y = "'x' union all select username from dba_users"
SQL> select * from dual where dummy like '%'||&&y;
old   1: select * from dual where dummy like '%'||&&y
new   1: select * from dual where dummy like '%'||'x' union all select username from dba_users

DUMMY
-----------------------------------------------------------------------------------------------
SYS
SYSTEM
XS$NULL
LBACSYS
OUTLN
DBSNMP
...



But its pleasing to see it raising alarm bells with you. That is probably the most important - to always be on the lookout for risks

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

APEX

Keep your APEX skills fresh by attending their regular Office Hours sessions.