CODEWITHSUNDEEP
postgresqldynamicinsertuser-inputplpgsql
codebot
codebot

Reputation: 697

How to syntax dynamic insert in plpgsql

I was wondering if there is a way to syntax an INSERT using EXECUTE in plpgsql.

Suppose I want to have a dynamic insert with mutliple IFs. I try to create something like the following 

CREATE  FUNCTION __a_inj(creator text) RETURNS integer   
  AS $query$
  DECLARE ii ALIAS FOR $1;
BEGIN
    EXECUTE'
    INSERT INTO deleteme(name) VALUES($1) RETURNING id'
    USING creator;
    return ii;

END;
$query$
LANGUAGE plpgsql;

I call it with select __a_inj('drop table deleteme;--'); and get

ERROR: invalid input syntax for integer: "drop table deleteme;--" CONTEXT: PL/pgSQL function __a_inj(text) while casting return value to function's return type SQL state: 22P02

If I replace the INSERT line with INSERT INTO deleteme(name) VALUES($1) RETURNING id into ii' I get

ERROR: syntax error at or near "into" LINE 2: ... INSERT INTO deleteme(name) VALUES($1) RETURNING id into ii

If the function is 

CREATE  FUNCTION __a_inj(creator text) RETURNS integer   
  AS $query$
  DECLARE ii ALIAS FOR $1;
BEGIN
    RETURN EXECUTE'
    INSERT INTO deleteme(name) VALUES($1) RETURNING id into ii'
    USING creator;
    --return ii;

END;
$query$
LANGUAGE plpgsql;

I get

ERROR: syntax error at or near "USING" LINE 8: USING creator;

How can I have a dynamic INSERT with EXECUTE

OR

I guess there is no need to syntax one. Just use ifs to create the INTO and values part and then use a simple INSERT that is also strong against SQL injection attacks ?

Upvotes: 1

Views: 490

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246268

Both statements are dynamic SQL (EXECUTE), but the second one has a syntax error (there is no INTO clause in SQL). You get the error in the first statement because the string you insert cannot be converted to an integer.

You are safe from SQL injection if you use the USING clause, because that uses a statement with parameters. SQL injection can only happen if you concatenate a user input string with an SQL statement.

Upvotes: 1

Related Questions