How to prevent SQL injection when doing batch insert in PostgreSQL?

Question

I have up to 100 items I would like to insert in one batch operation. I am doing it like this:

INSERT INTO MyTable (f1, f2, ..., fk) VALUES
  (v11, v12, ..., v1k),
  (v21, v22, ..., v2k),
  ...
  (vn1, vn2, ..., vnk)

All is fine, but I am building this string by concatenating the values as is, which means my code is vulnerable to SQL injection.

How can I continue using the bulk insert syntax on one hand, yet be protected from the SQL injection?

EDIT 1

I would like to provide a bit more context. The actual SQL that I am going to use (writing the code at this very moment) has the following form:

WITH new_parent AS (
  INSERT into parent (g1, g2, ..., gm) VALUES (v1, v2, ..., vm) RETURNING id
) INSERT INTO MyTable (parent_id, f1, f2, ..., fk) VALUES
  (new_parent.id, v11, v12, ..., v1k),
  (new_parent.id, v21, v22, ..., v2k),
  ...
  (new_parent.id, vn1, vn2, ..., vnk)

What's wrong with passing values through escaping function? Without it even COPY command will be vulnerable to \. sequence. — Tomasz Myrta
– Tomasz Myrta, Commented Oct 20, 2013 at 11:06
Actually, it seems that PostgreSQL can handle up to 2^15 - 1 parameters in a PREPARE statement, so seems like I can just replace the set of values {vij} with the respective named parameters of the form $1, $2, ... Just found out. — mark
– mark, Commented Oct 20, 2013 at 11:08

Pavel Stehule · Accepted Answer · 2013-10-20 13:22:07Z

1

Use a COPY statement - if it is possible. It is little bit faster, require significantly less memory and it is SQL injection bulletproof - because data use a different channel.

answered Oct 20, 2013 at 13:22

Pavel Stehule

46.7k6 gold badges103 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

mark Over a year ago

I have added EDIT 1. Is the COPY statement still relevant?

Pavel Stehule Over a year ago

You need to use CTE? Why you don't use temp table with COPY?

mark Over a year ago

Can you give me an example of how my query would look like if using COPY?

Pavel Stehule Over a year ago

@mark with COPY, you need two steps - first, you create temp table, then you fill table with COPY statement. Using a COPY statement depends on your environment.

mark Over a year ago

But how do I populate the temp table? Am I not back to the same problem of inserting records into a table?

|

vag fot · Accepted Answer · 2021-01-28 11:56:28Z

You can build a concatenated string of parameters instead of the actual values and then pass the values with a single array including all of the values in the right order. For example:

    CREATE TYPE paramValues AS {
    "ID" integer,
    "V1" text
    .
    .
    .
    }
    
    CREATE OR REPLACE FUNCTION bulk(data paramValues[])
      RETURNS integer AS
    $BODY$
    DECLARE 
    
    BEGIN
        WITH new_parent AS (
          INSERT into parent (g1, g2, ..., gm) VALUES (v1, v2, ..., vm) RETURNING id
        ) INSERT INTO MyTable (parent_id, f1, f2, ..., fk) VALUES
          ($1, $2, $3, ..., $k),
          ($k+1, $k+2, $k+3, ..., $k+k),
          ...
          ($m, $m+1, $m+2, ..., $m+k);
        return 1;
    
    END$BODY$

    LANGUAGE 'plpgsql' VOLATILE;

Collectives™ on Stack Overflow

How to prevent SQL injection when doing batch insert in PostgreSQL?

2 Answers 2

10 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related