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.

Answer 1

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