CODEWITHSUNDEEP
mysqlpostgresqlgroovy
N P
N P

Reputation: 2619

Quotes around dynamic expression Groovy SQL

I am doing a query to my database using Groovy, the query is working perfectly and bringing back the correct data however I get this error in my terminal.

In Groovy SQL please do not use quotes around dynamic expressions (which start with $) as this means we cannot use a JDBC PreparedStatement and so is a security hole. Groovy has worked around your mistake but the security hole is still there.

Here is my query

sql.firstRow("""select elem
                        from site_content,
                        lateral jsonb_array_elements(content->'playersContainer'->'series') elem
                        where elem @> '{"id": "${id}"}'
                     """)

If I change it to just $id or 

sql.firstRow("""select elem
                        from site_content,
                        lateral jsonb_array_elements(content->'playersContainer'->'series') elem
                        where elem @> '{"id": ?}'
                     """, id)

I get the following error

org.postgresql.util.PSQLException: The column index is out of range: 1, number of columns: 0.

Upvotes: 2

Views: 2896

Answers (1)

injecteer
injecteer

Reputation: 20717

Positional or named parameters are handled by groovy sql properly and should be used instead of "'$id'".

As @Opal mentioned and as described here, you should be passing your params either as a list or map:

sql.execute "select * from tbl where a=? and b=?", [ 'aa', 'bb' ]
sql.execute "select * from tbl where a=:first and b=:last", first: 'aa', last: 'bb'

Upvotes: 5

Related Questions