Any SQL variable inside dynamic SQL

Hi all,

I couldn't find a solution for this in the forum, GitHub issues, or StackOverflow.

The query in the problem needs something like this,

select $m${{filter}}$m$;

No matter which type of variable filter is, Text, Number, Date, or Field filter, it always throws the below exception,

The column index is out of range: 1, number of columns: 0..

I need this because I'm using dynamically built(through case stmts in SQL) SQL queries that provide the results I need. The field filter must be substituted inside a string(concatenated to a SQL query inside single quotes).

Please provide a solution(or a workaround :pray:) for this!

Thanks & Regards
z3r0c0o1

Hi @z3r0c0o1
What is $m$ ? Can you link to some documentation?
When you use Text (or Date) variable, then Metabase will insert the filter content as string (single-quote).
The variables are meant to be in the WHERE-clause. It is possible to use some of the variables elsewhere, but that is not officially supported and would depend on your database type etc.

Sounds like you are looking for something like this:
https://github.com/metabase/metabase/issues/4639 - upvote by clicking :+1: on the first post

Hi @flamber,

Thanks for the response. I'm using PostgreSQL database, $m$ is just a way of dollar quoting. I understand that the filters are supposed to be in the WHERE-clause. My WHERE clause is inside a string(because the entire SQL query is generated dynamically and executed).

I use a function like the below to run my dynamically generated SQL query,

CREATE OR REPLACE FUNCTION public.exec(
text)
RETURNS SETOF RECORD
LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
    RETURN QUERY EXECUTE $1 ;
END
$BODY$;

SELECT * from exec('SELECT ... WHERE {{filter}}');

I use this to provide dynamic GROUPing with dynamic filters.

@z3r0c0o1 But Metabase sends parameterized queries to Postgres, so select {{textfilter}} becomes something like select $1 with $1 being defined in the parameter of the database call to prevent SQL injections.
You are essentially seeing this issue:
https://github.com/metabase/metabase/issues/16949 - upvote by clicking :+1: on the first post

@flamber I understand.

But the parameters are replaced in the query by the database adapter before sending to the database for execution(in Metabase backend), init? So, it should work by that logic, right?

In the quoted GitHub issue, the last comment shows a similar example, that is what I'm trying to achieve. And it works for the commenter( sanchitahuja ).

@z3r0c0o1
Metabase does not handle the parameter, the driver does. And almost every driver handles it as parameters, since it would otherwise allow SQL injections.

If you have found a workaround, then use that.