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"

ave · Accepted Answer · 2020-09-02 14:28:08Z

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 2, 2020 at 14:28

ave

3,7142 gold badges16 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

The Impaler Over a year ago

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.

Hans Over a year ago

thank you. Is there a way to make it work while preventing sql injection?

ave Over a year ago

@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.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related