How to input dynamic table to function in PostgreSQL using mybatis?

Question

I implemented a Postgres function that takes the table name, column name, and column id as parameters.

It is dynamic because the result varies according to the table name.

This works very well when tested in sql.

postgresql function

create or replace function test(layer_name anyelement, field_name text, object_id text)
   returns setof anyelement
   language plpgsql
as $function$
   begin 
       return query execute format('
           select 
            *
           from
            %s
           where
            %s = cast($1 as int4)'
           , pg_typeof(layer_name), field_name)
           using object_id;
   end;
$function$
;

call

select * from test(null::table_name,'field_name','2');

But I call the function using mybatis, and it fails without receiving results.

<select id="test" parameterType="map" resultType="map">
    select * from test(null::#{layer_name}, #{field_name}, #{object_id})
</select>

or

<select id="test" parameterType="map" resultType="map">
    select * from test(cast(null as #{layer_name}), #{field_name}, #{object_id})
</select>

What am I missing and what do I need to try to make it work?

Error Code 1

### The error occurred while setting parameters
### SQL: select * from test(null::?, ?, ?)
### Cause: org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"

Error Code 2

### The error occurred while setting parameters
### SQL: select * from test(cast(null as ?), ?, ?)
### Cause: org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"

score 1 · Accepted Answer · answered Sep 02 '20 at 14:28

1

You need to use text substitution ${} instead of placeholder #{} for a table name.
Please see this FAQ entry for the details.

<select id="test" resultType="map">
  select * from test(null::${layer_name}, #{field_name}, #{object_id})
</select>

Here is an executable demo.

answered Sep 02 '20 at 14:28

ave

3,244
2
14
20

1

Consider that using `${layer_name}` may make your code vulnerable to SQL Injection. Make sure you fully control the value of the property `layer_name` at all times. – The Impaler Sep 02 '20 at 21:45
thank you. Is there a way to make it work while preventing sql injection? – Hans Sep 03 '20 at 05:57
1

@Hans You should be able to validate the `layer_name` value before executing the statement e.g. `tableNames.contains(layerName)`. In case the target tables are dynamically created, you may have to get the list of tables first. See this [answer](https://stackoverflow.com/a/14730638/1261766). – ave Sep 03 '20 at 08:25

How to input dynamic table to function in PostgreSQL using mybatis?

1 Answers1