How to process comma separated input in stored procedure-CodePudding

I need to write one procedure in which there will be table name as an input parameter (Should have only one table at a time) and column names (should have multiple column names comma separated) and column values(should have multiple column names comma separated).

My Attempt:

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)

I am stuck on how to handle multiple column names and column values inside the procedure body. How will I define an array and proceed accordingly?

CodePudding user response：

Don't. You are setting yourself up to have a procedure that is vulnerable to SQL injection attacks.

If you really want to (please don't):

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;
/

Then you can do:

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

But you can also do:

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;
/

Don't make your application vulnerable to SQL injection attacks; avoid dynamic SQL if you can help it!

If you want to make it more resistant to SQL injection attacks then you can use the DBMS_ASSERT package:

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;
/

Then:

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

However, the values are now all passed into the dynamic SQL statement as strings which makes it more difficult to pass data to DATE, TIMESTAMP or INTERVAL (etc.) columns as it relies on implicit data-type conversions.

You should still avoid dynamic SQL if possible.

db<>fiddle here