PostgreSQL Dollar-Quoted Strings Constants to Prevent SQL Injection

Question

Can I safely prevent SQL Injection using PostgreSQL's Dollar-Quoted String Constants?

I know the best was to handle dynamic queries is to have them generated in a application layer with a parametrized query, that's not what this question is about. All of the business logic is in stored procedures.

I have a stored procedure that takes parameters and generates a query, runs it, formats the results and returns it as a chunk of text. This function is passed a table name, column names and WHERE parameters. The WHERE parameters passed to the function are from user entered data in the database. I would like to make sure that the stings are sanitized so the query that is built is safe.

Using PostgreSQLs Dollar-Quoted Strings Constants, I should be able to safely sanitize all string input other than ' $$ '. However, if I do a string replace on "$" to escape it, I should be able to do a string comparison that is safe.

Stored Procedure:

function_name(tablename text, colnames text[], whereparam text)
--Build dynamic query...

Function Call:

SELECT 
  function_name('tablename', ARRAY['col1', 'col2', 'col3'], 'AND replace(col1, ''$'', ''/$'') =  $$' || replace(alt_string_col, '$', '/$') || '$$ ')
FROM alttable
WHERE alt_id = 123;

Query Generated:

SELECT col1, col2, col3 FROM tablename WHERE 1=1 AND replace(col1, '$', '/$') =  $$un/safe'user /$/$ data;$$

Since I'm escaping the col1 field before I compare it to escaped user data, even if the user enters, "un/safe'user $$ data;" in the field, alt_string_col, the double dollar sign does not break the query and the comparison passes.

Is this a safe way to escape strings in PostgreSQL stored procedure?

Edit1

Thanks to Erwin Brandstetter. Using the USING clause for EXECUTE I was about to create a function that can be called like this:

SELECT function_name(
        'tablename',
        ARRAY['col1', 'col2', 'col3'], 
        ARRAY[' AND col1 =  $1 ', ' OR col2 = $5 '],
        quote_literal(alt_string_col)::text, --Text 1-4
        NULL::text,
        NULL::text,
        NULL::text,
        alt_active_col::boolean, --Bool 1-4
        NULL::boolean,
        NULL::boolean,
        NULL::boolean,
        NULL::integer, --Int 1-4
        NULL::integer,
        NULL::integer,
        NULL::integer
        )
FROM alttable 
WHERE alt_id = 123;

It gives some flexibility to the WHERE clauses that can be passed in.

Inside the stored procedure I have something like this for the EXECUTE statement.

  FOR results IN EXECUTE(builtquery) USING 
    textParm1, 
    textParm2, 
    textParm3, 
    textParm4, 
    boolParm1, 
    boolParm2, 
    boolParm3, 
    boolParm4, 
    intParm1, 
    intParm2, 
    intParm3, 
    intParm4
  LOOP
    -- Do some stuff
  END LOOP;

Erwin Brandstetter · Accepted Answer · 2022-11-05T08:06:54.483

5

Use quote_ident() to safeguard against SQL injection while concatenating identifiers. Or format() in Postgres 9.1 or later.

Use the USING clause for EXECUTE in PL/pgSQL code to pass values. Or at least quote_literal().

To make sure a table name exists (and is quoted and schema-qualified automatically where necessary when concatenated), use the special data type regclass.

More about executing dynamic SQL with PL/pgSQL:

Since PostgreSQL 9.0 you can also use anonymous code blocks with the DO statement to execute dynamic SQL.

edited Nov 05 '22 at 08:06

answered Nov 18 '11 at 21:33

Erwin Brandstetter

605,456
145
1,078
1,228

I tried the `USING` clause for `EXECUTE`, but you have to know how many WHERE clauses your are passing it. It works for 1 in my example, but what if you have 3 for one call and 6 for another. I'm actually using an ARRAY[] to pass these in so I can loop over each parameter and add it it the WHERE. I'll look into quote_indent() some more. Thanks – bendiy Nov 18 '11 at 21:49
1

@bendiy: it's `quote_ident()` for identifiers (table names, column names, etc.) and `quote_literal()` for values. There is also some leeway, how many WHERE clauses you can attach with parameters from `USING`: add the *all* parameters you might possible use in the clause. It's no problem if not all of them end up being used. And you know the maximum number of parameters you have to care for, because you know the outer function's parameters. – Erwin Brandstetter Nov 18 '11 at 22:04
quote_literal() seems to be what I need and leads to a much more elegant: `SELECT function_name('tablename', ARRAY['col1', 'col2', 'col3'], 'AND col1 = ' || quote_literal(alt_string_col)) FROM alttable WHERE alt_id = 123;` – bendiy Nov 18 '11 at 22:04
`USING` has additional performance benefits, because the values don't have to be cast to text representation, quoted, and cast back. This can be especially helpful with floating point numbers where you can preserve precision without processing that way. – Erwin Brandstetter Nov 18 '11 at 22:08

PostgreSQL Dollar-Quoted Strings Constants to Prevent SQL Injection

1 Answers1