Отправка массива значений в процедуру Oracle для использования в предложении WHERE IN

У меня есть хранимая процедура в Oracle, как показано ниже:

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;

На стороне приложения ASP.NET у меня есть элемент выбора с несколькими параметрами. Я хочу использовать эти элементы списка в своем предложении WHERE. Я знаю, что у меня может быть входной параметр VARCHAR2 в моей хранимой процедуре, сделать строку, разделенную запятыми, из элементов списка, отправить ее в процедуру. Есть две проблемы с этим путем:

  1. Я делаю свой сайт уязвимым для SQL-инъекций
  2. В моей хранимой процедуре я должен использовать шаблон EXECUTE ('SELECT ...'), которого я хотел бы избежать.

Как я могу отправить эти элементы списка в хранимую процедуру и использовать их внутри предложения WHERE IN? Я использую ODP.NET и слышал о UDT, но не знаю, как его использовать.


csv c# asp.net oracle stringtokenizer
person Mikayil Abdullayev    schedule 27.11.2012    source источник

Ответы (3)


arrow_upward
2
arrow_downward

Вы можете добавить этот входной параметр, разделенный запятыми, как varchar() и использовать следующий оператор where:

where (','||PARAM_THAT_WILL_BE||',' like '%,'||COL1||',%')

например, если PARAM_THAT_WILL_BE='2,3,4,5' и col1=3 мы получаем:

where (',2,3,4,5,' like '%,3,%')

и TRUE, если значение COL1 есть в этом списке. Здесь вы не используете динамический запрос, чтобы избежать проблем 1) и 2).

person valex    schedule 27.11.2012
comment
Действительно разумный подход, хотя и может стоить производительности. Например, если в col1 есть индекс, он может не использоваться. - person A.B.Cade; 28.11.2012
comment
Это не умный подход. Динамический SQL всегда должен быть последним средством. Кроме того, он накладывает ограничения на формат (без пробелов, без табуляции) того, что в основном является свободным текстовым полем. А еще есть производительность, как говорит AB. - person APC; 23.07.2013
comment
Действительно грамотный подход. Работал на меня. Спасибо - person Mahtab; 27.01.2015

arrow_upward
5
arrow_downward

Одним из способов может быть использование VARRAY для параметра PARAM_THAT_WILL_BE _USED_INSIDE_WHERE_IN и использование его, как описано здесь
Я не уверен , однако, как вызвать его из С#.

Другой способ - использовать varchar2 с csv, как вы указали в своем вопросе, но без динамического sql, например:

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;
person A.B.Cade    schedule 27.11.2012

arrow_upward
1
arrow_downward

Для этого сценария я использовал вот так

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;

Значение A должно содержать открытые и закрытые qutoes ('). ПРИМЕР: «512 456 4564», если это одно значение «512», как это

person Rajesh D    schedule 30.12.2015