title: PostgreSQL Dollar-Quoted Strings Constants to Prevent SQL Injection
body: <p>Can I safely prevent SQL Injection using PostgreSQL's Dollar-Quoted String Constants?</p>
  
  <p>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.  </p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>Stored Procedure:</p>
  
  <pre><code>function_name(tablename text, colnames text[], whereparam text)
  --Build dynamic query...
  </code></pre>
  
  <p>Function Call:</p>
  
  <pre><code>SELECT 
    function_name('tablename', ARRAY['col1', 'col2', 'col3'], 'AND replace(col1, ''$'', ''/$'') =  $$' || replace(alt_string_col, '$', '/$') || '$$ ')
  FROM alttable
  WHERE alt_id = 123;
  </code></pre>
  
  <p>Query Generated:</p>
  
  <pre><code>SELECT col1, col2, col3 FROM tablename WHERE 1=1 AND replace(col1, '$', '/$') =  $$un/safe'user /$/$ data;$$
  </code></pre>
  
  <p>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.</p>
  
  <p>Is this a safe way to escape strings in PostgreSQL stored procedure?</p>
  
  <p><strong>Edit1</strong></p>
  
  <p>Thanks to Erwin Brandstetter.  Using the <code>USING</code> clause for <code>EXECUTE</code> I was about to create a function that can be called like this:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>It gives some flexibility to the WHERE clauses that can be passed in.</p>
  
  <p>Inside the stored procedure I have something like this for the <code>EXECUTE</code> statement.</p>
  
  <pre><code>  FOR results IN EXECUTE(builtquery) USING 
      textParm1, 
      textParm2, 
      textParm3, 
      textParm4, 
      boolParm1, 
      boolParm2, 
      boolParm3, 
      boolParm4, 
      intParm1, 
      intParm2, 
      intParm3, 
      intParm4
    LOOP
      -- Do some stuff
    END LOOP;
  </code></pre>