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?