Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

PostgreSQL Dollar-Quoted Strings Constants to Prevent SQL Injection

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;
like image 807
bendiy Avatar asked Nov 18 '11 21:11

bendiy


1 Answers

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 plpgsql code to pass values. Or at least quote_literal().

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

More about executing dynamic SQL with plpgsql:

  • PostgreSQL parameterized Order By / Limit in table function
  • Table name as a PostgreSQL function parameter

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

like image 198
Erwin Brandstetter Avatar answered Nov 15 '22 08:11

Erwin Brandstetter