如何处理存储过程中以逗号分隔的输入

Question

我需要编写一个过程，其中将有表名作为输入参数(一次应该只有一个表)，列名(应该有多个列名，逗号分隔)和列值(应该有多个列名，逗号分隔)。

我的尝试:

CREATE OR REPLACE PROCEDURE sp_test_insert(
p_table_name IN VARCHAR2,
p_column_name IN VARCHAR2,-- It can have multiple column names separated by comma 
p_column_value IN VARCHAR2-- It can have multiple column names separated by 
)
AS
lv_str VARCHAR2(4000);
BEGIN
lv_str := 'INSERT INTO '||p_table_name||'should have multiple column names' ||
'VALUES('||'should have multiple column names' ||')';
EXECUTE IMMEDIATE lv_str;
END;
Tool used: SQL Developer(18c)

我被困在如何处理多个列名和列值在过程体。如何定义数组并进行相应的操作?

Answer 1

不要。您正在设置一个容易受到SQL注入攻击的过程。

---

如果你真的想(请不要):

CREATE OR REPLACE PROCEDURE sp_test_insert(
p_table_name   IN VARCHAR2,
p_column_name  IN VARCHAR2,
p_column_value IN VARCHAR2 
)
AS
lv_str VARCHAR2(4000);
BEGIN
lv_str := 'INSERT INTO '||p_table_name||' (' || p_column_name || ') VALUES(' || p_column_value ||')';
EXECUTE IMMEDIATE lv_str;
END;
/

那么你可以这样做:

BEGIN
sp_test_insert(
'my_table',
'col1, col2, col3',
q'['a', DATE '2022-05-31', 42]'
);
END;
/

但是你也可以这样做:

BEGIN
sp_test_insert(
'my_table',
'col1, col2, col3',
q'['a', (SELECT DATE '1970-01-01' FROM secret_table WHERE username = 'Admin' AND password_hash = 'abcgefg1234'), 42]'
);
END;
/

不要让你的应用容易受到SQL注入攻击;尽量避免使用动态SQL !

---

如果你想让它更能抵抗SQL注入攻击，那么你可以使用DBMS_ASSERT包:

CREATE OR REPLACE PROCEDURE sp_test_insert(
p_table_name   IN VARCHAR2,
p_column_name  IN SYS.ODCIVARCHAR2LIST,
p_column_value IN SYS.ODCIVARCHAR2LIST 
)
AS
lv_str VARCHAR2(4000);
BEGIN
lv_str := 'INSERT INTO '
|| DBMS_ASSERT.SQL_OBJECT_NAME(
DBMS_ASSERT.ENQUOTE_NAME(p_table_name, FALSE)
)
||' ('
|| DBMS_ASSERT.ENQUOTE_NAME(p_column_name(1), FALSE);
FOR i IN 2 .. p_column_name.COUNT LOOP
lv_str := lv_str || ', '
|| DBMS_ASSERT.ENQUOTE_NAME(p_column_name(i), FALSE);
END LOOP;
lv_str := lv_str || ') VALUES('
|| DBMS_ASSERT.ENQUOTE_LITERAL(p_column_value(1));
FOR i IN 2 .. p_column_name.COUNT LOOP
lv_str := lv_str || ', '
|| DBMS_ASSERT.ENQUOTE_LITERAL(p_column_value(i));
END LOOP;
lv_str := lv_str || ')';
EXECUTE IMMEDIATE lv_str;
END;
/

:

BEGIN
sp_test_insert(
'MY_TABLE',
SYS.ODCIVARCHAR2LIST( 'COL1', 'COL2', 'COL3'),
SYS.ODCIVARCHAR2LIST( 'a', '31-MAY-2022', '42')
);
END;
/

但是，这些值现在都作为字符串传递到动态SQL语句中，这使得将数据传递到DATE,TIMESTAMP或INTERVAL(等)列变得更加困难，因为它依赖于隐式数据类型转换。

如果可能的话，你仍然应该避免动态SQL。

db<此处小提琴>