Sending an array of values to Oracle procedure to use in WHERE IN clause

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:
  1. I make my website vulnerable to SQL injections
  2. 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.

Answers:

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.

Method 1

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;

Method 2

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 PARAM_THAT_WILL_BE='2,3,4,5' and col1=3 we get:
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).

Method 3

For this scenario i used like this

CREATE PROCEDURE MY_TEST_PROC(CUR OUT SYS_REFCURSOR,A in VARCHAR2
)
AS
BEGIN
OPEN CUR FOR
SELECT *
FROM MY_TABLE
WHERE COL1 IN (SELECT REGEXP_SUBSTR(**A**,'[^,]+', 1, LEVEL)
FROM DUAL
CONNECT BY REGEXP_SUBSTR(**A**, '[^,]+', 1, LEVEL) IS NOT NULL)
END;

The A value should contain open and closed qutoes(‘).
EX: ‘512,456,4564’
if it one value ‘512’ like this


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x