3

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.

APC
  • 144,005
  • 19
  • 170
  • 281
Mikayil Abdullayev
  • 12,117
  • 26
  • 122
  • 206

3 Answers3

6

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;
Community
  • 1
  • 1
A.B.Cade
  • 16,735
  • 1
  • 37
  • 53
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).

valex
  • 23,966
  • 7
  • 43
  • 60
  • A smart approach indeed, though may cost in performance. For example if there is an index on col1, it might not be used – A.B.Cade Nov 28 '12 at 14:43
  • 2
    This is not a smart approach. Dynamic SQL should always be a last resort. Also it places constraints on the format (no spaces, no tabs) of what is basically a free text field. And then there's the performance, as AB says. – APC Jul 23 '13 at 06:53
  • Really smart approach . Worked for me. Thanks – Mahtab Jan 27 '15 at 12:29
1

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

Rajesh D
  • 167
  • 1
  • 13