0

I have a query in format

namedJdbcTemplate.queryForObject("""
select * from table
where column = 'somethins :param'
""", new MapSqlParameterSource('param', someValue), someReturnType);

How can i make it inject the param value into the string?

This is a simplified case. Actual usecase uses a function on DB side to construct a complex query, all im giving it is a where clause and params for the where clause:

namedJdbcTemplate.queryForObject("""
    select myfunc($$%s$$)
    """.formatted(getFilterClauseFromDB()), params, someReturnType);

When the formatting does its work it should look something like this

 namedJdbcTemplate.queryForObject("""
        select myfunc($$AND val1 = :val1 and val2 = :val2 $$)
        """, params, someReturnType);

where params is a Map<string, object> of parameters parsed by a generic parameter parsing system to put them into correct format. But the namedJDBCTemplate does not map parameters to :val1 and :val2 for some reason. Function intenrally runs a postgres crosstab function. Even if i do away with my function the issue remains. I need to inject parameters into a string that is inside SQL since crosstab accepts queries in the format of string.

Somehow i need to get the params placed into the filterclause that is a string that is built on the runtime. Is there a way i can do it? Currently it throws error The column index is out of range: 1, number of columns: 0

Marko Taht
  • 1,448
  • 1
  • 20
  • 40
  • Now if there would be a aptly named [`NamedParameterJdbcTemplate`](https://docs.spring.io/spring-framework/docs/current/javadoc-api/org/springframework/jdbc/core/namedparam/NamedParameterJdbcTemplate.html) that would just do that. – M. Deinum Jul 12 '23 at 08:53
  • It wonnt inject if it is a pat of the string. Like `val = :val` will work but `val = ':val'` gives me error. As in the string i need to inject into is also a string. – Marko Taht Jul 12 '23 at 08:57
  • If hte parameter is properlly marked as string you don't need the quotes, the JDBC driver will act accordingly. I suspect you are trying to outsmart the JDBC driver – M. Deinum Jul 12 '23 at 09:00
  • Like i said. This is simplified example. In real usecase i have something like `'AND val1 = :val1 and val2 = :val2'` that gets passed to myfunc as a string parameter. – Marko Taht Jul 12 '23 at 09:02
  • Then replace it yourself. Instead of adding variables add the actual value. Yes that is prone to SQL injection. But either do that or don't use Strings like this. – M. Deinum Jul 12 '23 at 09:13
  • cant allow sql injection danger. How would you handle a situation where the expected result is a product of crosstab with dynamic amount of columns where each column is user defined on runtime and you need to use this result of crosstab as a filter for another query? – Marko Taht Jul 12 '23 at 09:22
  • I would generate a normal query and not invoke a function. Just run the query. – M. Deinum Jul 12 '23 at 09:24
  • But what if all the data you need exists in DB in multiple tables and colelctind the data for crosstab is non trivial? – Marko Taht Jul 12 '23 at 09:29
  • That query is still somewhere, regardless of anything the query needs to be written. If you do that in a function/procedure or Java with JDBC it is still the same query. – M. Deinum Jul 12 '23 at 09:40
  • There is tho 1 thing. I might be able to buuild it on Java side, but main issue is. Crosstab takes queries as strings so the issue remains. And i need crosstab since i need a pivot table. – Marko Taht Jul 12 '23 at 09:41
  • What is crosstab what you are referencing? – M. Deinum Jul 12 '23 at 09:44
  • https://www.sqlshack.com/how-to-use-the-crosstab-function-in-postgresql/ a function in postgresql to create pivot tables. – Marko Taht Jul 12 '23 at 09:45
  • Now that is some crucial piece of information you want to include in your question. – M. Deinum Jul 12 '23 at 09:49
  • According to [this](https://stackoverflow.com/a/53006570/2696260) you can obtain the SQL String from a PostgresSQL statement. So you can make it a 2 part process. First prepare the query you want to pass to the `crosstab` function, get it as a `String` then prepare the second query. – M. Deinum Jul 12 '23 at 09:55

0 Answers0