Hi
I have a variable $1 which hold comma separated email addresses like [email protected],[email protected] . I wish to pass this variable in a where clause like
where myColumn in ($1)
But obviously this won't work, I tried APEX_UTIL.STRING_TO_TABLE and DBMS_UTILITY.COMMA_TO_TABLE but in vain.
Any help appreciated.
As Pavanred alluded to, the easiest way -- though not necessarily the best -- is to interpolate the values yourself. You don't say what your calling language is, but something like:
sql = "SELECT something FROM whatever WHERE myColumn in (" + $1 + ")"
However, this means it's very important that you have pre-checked all the values in $1 to make sure that they are either numbers, or properly escaped strings, or whatever else it is that you need to pass but cannot be raw values supplied by a user, to avoid a SQL injection.
The other option is to make it a two-step process. First, insert the values from $1 into a temporary table, then select those values as a subquery:
WHERE myColumn in (SELECT temp_value FROM temp_table)
A simple workaround, which might be good enough in your case (but is not performant), is to use LIKE instead:
select * from something where $1 like '%,'||mycolumn||',%';
$1 is your comma separated list, as a string, with a comma prepended and appended, e.g.
,[email protected],[email protected],[email protected],
It's not pretty, but you can achieve a 100% one-step SQL injection-proof solution like this. You plug in the "criteria" CTE to your query, which parses the comma-sperated list into a table. Then you can use it in an in query like "where column in (select item from criteria)":
with criteria as
(
select
substr(criteria_list, item_start, item_length) as item
from
(
select
criteria_list,
comma_pos + 1 as item_start,
nvl(next_comma_pos - 1, length(criteria_list)) - comma_pos as item_length
from
(
select
criteria_list,
comma_pos,
lead(comma_pos) over(order by comma_pos) as next_comma_pos
from
(
select
$1 as criteria_list,
instr($1, ',', 1, level) as comma_pos
from
dual
connect by
instr($1, ',', 1, level) > 0 and instr($1, ',', 1, level) <= instr($1, ',', -1)
union all
select
$1,
0
from
dual
)
)
)
)
select * from some_table where column in (select item from criteria)
