Wildcard search using parameters in function with dynamic SQL

Question

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.

Erwin Brandstetter · Answer 1 · 2021-10-29T00:59:45.590

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:

answered Apr 11 '16 at 01:31

Erwin Brandstetter

175,982
27
439
600

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

Wildcard search using parameters in function with dynamic SQL

1 Answers1