2

What is the proper way to implement a wildcard search in PostgreSQL when using a parameter in a function that uses dynamic SQL?

As a starting point, here is an example from Erwin Brandstetter answering a different question on stackoverflow:

https://stackoverflow.com/a/12047277/538962

CREATE OR REPLACE FUNCTION report_get_countries_new (starts_with text
                                                   , ends_with   text = NULL)
RETURNS SETOF lookups.countries AS
$func$
DECLARE
   sql text := 'SELECT * FROM lookups.countries WHERE country_name >= $1';
BEGIN
   IF ends_with IS NOT NULL THEN
      sql := sql || ' AND country_name <= $2';
   END IF;

   RETURN QUERY EXECUTE sql
   USING starts_with, ends_with;
END
$func$ LANGUAGE plpgsql;

Let's suppose for country_name you wanted to do a leading and trailing wildcard search.

E.g., without using a parameter, AND country_name LIKE '%ic%'.

What is the best way to implement the wildcard search be in this scenario with respect to negating SQL injection risk?

  • I am using PostgreSQL version 9.5.1 – mg1075 Apr 9 '16 at 21:38
4

Let's suppose for country_name you wanted to do a leading and trailing wildcard search.

You don't need dynamic SQL for this. Just:

CREATE OR REPLACE FUNCTION report_get_countries_new (_pattern text)
  RETURNS SETOF lookups.countries AS
$func$
   SELECT *
   FROM   lookups.countries
   WHERE  country_name LIKE '%' || _pattern || '%'
$func$ LANGUAGE sql;

Call:

SELECT * FROM report_get_countries_new ('ic');  -- without wildcards!

This negates SQL injection risk completely, since that comes with dynamic SQL.

The caller can still include wildcards at will (unless you process the parameter to filter wildcards), but there is a hardcoded leading wildcard and also a trailing one (unless the parameter ends with \ removing the special meaning from the trailing %).

Even if you work with dynamic SQL and EXECUTE in PL/pgSQL, there is no risk for SQL injection as long as you pass values as values with the USING clause:

Related:

  • Thanks. For other reasons, I have to use dynamic sql. So if I understand you correctly, it would be ok to add wildcards to the parameter outside of the function and thereby call, SELECT * FROM report_get_countries_new ('%ic%'); – mg1075 Apr 11 '16 at 13:37
  • @mg1075: There is no risk for SQL injection as long as you don't convert user input to SQL code (or identifiers). Passing values as values with the USING clause is 100% safe. Concatenating user input into the query string needs more attention and should be avoided for values. – Erwin Brandstetter Apr 11 '16 at 13:50
  • Sorry, I might be getting tripped up by semantics. What if inside the function, prior to passing starts_with to the USING clause, I had a stand-alone clause saying starts_with = '%' || starts_with || '%'. This would be ok, since the value is passed as a value to the USING clause? – mg1075 Apr 11 '16 at 14:59
  • @mg1075: Concatenating strings is not crossing the value / code border and safe. Executing concatenated strings that include user input can be a problem - but not as long as you pass them as value. I added another link that should help to clarify. – Erwin Brandstetter Apr 17 '16 at 12:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.