PostgreSQL: preventing sql injection on multiinsertion

Question

I'm looking for the fastest way to parse, validate and insert data in table(Postgresql 9.3).

The data is an json-array which contains 1..N items.

[{"name":"a","value":"1"},{"name":"b","value":"2"}]

The table looks like:

CREATE TABLE logs
(
  id serial NOT NULL,
  name text ,
  value text,
  CONSTRAINT "log_Pkey" PRIMARY KEY (id)
);

For that i have stored procedure:

CREATE OR REPLACE FUNCTION insert_logs(v json)
  RETURNS  integer AS
$BODY$
DECLARE
    sql text;
    i json;
    logs_part_id int;
BEGIN
    SELECT INTO logs_part_id id from another_table_with_that_id where some_condition.

    sql = '';
     FOR i IN SELECT * FROM json_array_elements(v)
      LOOP
      sql = sql||'insert into logs_'||logs_part_id ||'
        (name, value)
         values( ' ||quote_literal(i->>'name')||' , ' ||quote_literal(i->>'value')||' );';

      END LOOP;
    raise notice '%',sql;

    EXECUTE sql;
    return 1;

END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

(function returns integer as a response status)

Function call:

select * from insert_logs('[{"name":"a","value":"1"},{"name":"b","value":"2"}]'::json);

Actually the "insert.." statement is quite bigger - 15 columns to insert and aparently some of them should be checked in order to prevent sql injection.

Question: Is there any way to rewrite this stored procedure in order to improve performance? Should I use prepared statements?

EDIT.

The reason i build sql string because the table name is unknown because of the tables partitioning. The table name format is: logs_id where id - int which is obtained just before insert.

score 3 · Answer 1 · answered Sep 22 '14 at 08:31

If you need to speed up your query, json_populate_recordset() does exactly what you need:

insert into logs
select * from json_populate_recordset(null::logs, '[...]')

As, for SQL-injection: you should always use prepared statements, or at least execute your sql with parameters sent separately (f.ex. with PQexecParams() if you use libpq directly).

Craig Ringer · Accepted Answer · 2014-09-22T12:25:13.087

2

Why are you building an SQL multi-statement string then EXECUTEing it at all?

Just:

  insert into logs (name, value)
  values( i->>name , i->>value );

There's no need for explicit quoting because i->>name is a text value that's inserted as a bound parameter into the insert by PL/PgSQL. It's never parsed as SQL.

If you must build the statement dynamically (e.g. varying table name, per comment) use EXECUTE ... USING with format:

  EXECUTE format('insert into %I (name, value) values( $1, $2 );', 'logs_'||log_partition_id) 
     USING i->>name , i->>value;

in your case

edited Sep 22 '14 at 12:25

answered Sep 22 '14 at 08:36

Craig Ringer

307,061
76
688
778

Unfortunatelly I need to build multistatement string because table name is not fixed. It depends on some parameter. I edited my 1-st post – xardas Sep 22 '14 at 12:20
@xardas Good detail to include first time around, especially since your function didn't actually show that at all. See edit. – Craig Ringer Sep 22 '14 at 12:25

PostgreSQL: preventing sql injection on multiinsertion

2 Answers2