Using Postgres JSON function in Hibernate with injection

Question

I am using Hibernate + SpringBoot and have a @Query() with nativeQuery=true where I am attempting to use a Postgres jsonb function to find a row where a JSON list has an entry with a specific field value, where banana_id is a parameter:

WHERE jsonb_path_exists(CAST(foo.things as jsonb), '$[*] ? (@.id == :banana_id)')

but no matter what I try, the query fails. According to this post Using Postgres JSONB query with Spring Data and bind parameter fails with InvalidDataAccessApiUsageException it's impossible to inject into a json path query, so that's why :banana_id doesn't work, but I can't figure out how to reframe this without that.

evilfred · Accepted Answer · 2022-12-13 22:56:44Z

0

Ended up injecting the whole query as a string and casting it to jsonpath, with this:

jsonb_path_exists(CAST(foo.things as jsonb), CAST(:query_str as jsonpath))

answered Dec 13, 2022 at 22:56

evilfred

2,4264 gold badges32 silver badges47 bronze badges

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

Collectives™ on Stack Overflow

Using Postgres JSON function in Hibernate with injection

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related