I have a stored procedure in Oracle as shown below:
CREATE PROCEDURE MY_TEST_PROC( CUR OUT SYS_REFCURSOR, PARAM_THAT_WILL_BE _USED_INSIDE_WHERE_IN ) AS BEGIN OPEN CUR FOR SELECT * FROM MY_TABLE WHERE COL1 IN (here I want to put values received from C#) END;
On the ASP.NET application side I have a select element with several options. I want to use these list items in my WHERE clause. I know that I can have a VARCHAR2 input parameter in my stored proc, make a comma separated string from the list items, send it to the procedure. There are two concerns with going this way:
- I make my website vulnerable to SQL injections
- In my stored proc I have to use EXECUTE (‘SELECT …’) pattern which I would like to avoid.
How can I send these list items to the stored procedure and use them inside the WHERE IN clause? I’m using ODP.NET and have heard of UDT but don’t know how to use it.
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
One way could be to use a
VARRAY for the
PARAM_THAT_WILL_BE _USED_INSIDE_WHERE_IN parameter and use it as described here
I’m not sure, though, how to call it from c#.
Another way is to use varchar2 with a csv as you stated in your question but without dynamic sql, like this:
CREATE PROCEDURE MY_TEST_PROC( CUR OUT SYS_REFCURSOR, PARAM_THAT_WILL_BE varchar2) AS BEGIN OPEN CUR FOR SELECT * FROM MY_TABLE WHERE COL1 IN ( select regexp_substr(PARAM_THAT_WILL_BE, '[^,]+',1,level) p from dual t connect by level <= regexp_count(PARAM_THAT_WILL_BE, ',') + 1 ) END;
You can add this comma separated input parameter as a varchar() and use following where statement:
where (','||PARAM_THAT_WILL_BE||',' like '%,'||COL1||',%')
for example if
where (',2,3,4,5,' like '%,3,%')
and it’s TRUE if COL1 value is in this list.
Here you don’t use a dynamic query so you avoid concerns 1) and 2).
For this scenario i used like this
CREATE PROCEDURE MY_TEST_PROC(CUR OUT SYS_REFCURSOR,A in VARCHAR2
OPEN CUR FOR
WHERE COL1 IN (SELECT REGEXP_SUBSTR(**A**,'[^,]+', 1, LEVEL)
CONNECT BY REGEXP_SUBSTR(**A**, '[^,]+', 1, LEVEL) IS NOT NULL)
The A value should contain open and closed qutoes(‘).
if it one value ‘512’ like this