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-05 08:06:54Z

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 5, 2022 at 8:06

answered Nov 18, 2011 at 21:33

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

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

4 Comments

bendiy Over a year ago

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

Erwin Brandstetter Over a year ago

@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.

bendiy Over a year ago

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;

Erwin Brandstetter Over a year ago

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.

Collectives™ on Stack Overflow

PostgreSQL Dollar-Quoted Strings Constants to Prevent SQL Injection

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related