6

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 currently using PostgreSQL 9.5.1.

0

1 Answer 1

9

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
  LANGUAGE sql AS
$func$
   SELECT *
   FROM   lookups.countries
   WHERE  country_name LIKE '%' || _pattern || '%'
$func$;

Call:

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

This negates SQL-injection risk completely, which 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:

4
  • 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
    Commented Apr 11, 2016 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. Commented Apr 11, 2016 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
    Commented Apr 11, 2016 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. Commented Apr 17, 2016 at 12:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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