Search code examples
postgresqlsql-likefreeradius

How do I match variables from FreeRADIUS in PostgreSQL with the LIKE operator?

In a PostgreSQL query, executed by FreeRADIUS, I want to do something similar to (the table names and values are just examples):

SELECT name
FROM users
WHERE city LIKE '%blahblah%';

but there is a catch: the blahblah value is contained in a FreeRADIUS variable, represented with '%{variable-name}'. It expands to 'blahblah'.

Now my question is: How do I match the %{variable-name} variable to the value stored in the table using the LIKE operator?

I tried using

SELECT name
FROM users
WHERE city LIKE '%%{variable-name}%';

but it doesn't expand correctly like that and is obviously incorrect.

The final query I want to achieve is

...
WHERE city LIKE '%blahblah%';

so it matches the longer string containing 'blahblah' stored in the table, but I want the variable to expand dynamically into the correct query. Is there a way to do it?

Thanks!

Solution

  • Wild guess:

    Assuming that FreeRADIUS is doing dumb substitution across the entire SQL string, with no attempt to parse literals, etc, before sending the SQL to PostgreSQL then you could use:

    SELECT name
FROM users
WHERE city LIKE '%'||'%{variable-name}'||'%';

    Edit: To avoid the warnings caused by FreeRADIUS not parsing cleverly enough, hide the %s as hex chars:

    WHERE city LIKE E'\x25%{variable-name}\x25';

    Note the leading E for the string marking it as a string subject to escape processing.