Postgresql – Wildcard search using parameters in function with dynamic SQL

dynamic-sqlplpgsqlpostgresqlsql-injection

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?

Best Answer

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:

Related Question