0

I need to use crosstab in my query. But the queries within crosstab also require filtering. Entire crosstab is dynamically constructed so I require someway to dynamically insert filters into the SQL inside crosstab.

Java Spring ignores all the named parameters inside string quotes. After a long search, I found the only way to achieve what I need is to use prepared statements to construct the inner SQL and then constructing the crosstab from them. Problem is the preparedStatemnts drain the connection pool and after 3-4 queries connection cannot be gotten anymore.

I also added custom implementation of namedJdbcTemplate to get access to the internal statement creator and connection.

    try(
    PreparedStatement statement1 = jdbcTemplate.getPreparedStatement("select * from table where 1=1 %s").formatted(filterString), Paramap);
    PreparedStatement statement2 = jdbcTemplate.getPreparedStatement("select distinct name from table where 1=1 %s".formatted(filterString), ParamMap))
{
   return """select * from crosstab($$%s$$, $$%s$$) as (%s)""".formatted(statement1.toString(), statement2.toString(), columndefinition);
}

After running few times I get "HikariPool-1 - Connection is not available, request times out after 30000ms.". It seems like the statements don't release their connections or something... I'm not sure.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Marko Taht
  • 1,448
  • 1
  • 20
  • 40
  • It isn't Spring that is ignoring the parameters that is aligned with JDBC, as content in quotes is handled as is, as String/varchar. You have a custom template, apparently that is leaking connections. I suspect you are returning a prepared statement and doing something like `datasource.getConnection().prepareStatement` this leaks a connection. You need to close the connection and statement after executing. – M. Deinum Jul 18 '23 at 05:58
  • 1
    What makes you think that those prepared statements are leaking connections? Hikari should give you a log message telling you where the leaked connection was made, see https://stackoverflow.com/questions/54883940/apparent-connection-leak-detected-with-hikari-cp – tgdavies Jul 18 '23 at 06:00
  • I tried that. both close and cancel. Latst attempot is try-with-resource. Nothing seems to fix it. – Marko Taht Jul 18 '23 at 06:01
  • @M.Deinum you wereright. Had to manually find and close the connection. Closing statmenet itseld was not enough. – Marko Taht Jul 18 '23 at 06:17
  • Ideally when using an transaction this is the same connection and you should have use `DataSourceUtils.obtainConnection` which will re-use a single connection within a transaction. Which would then be automatically closed. – M. Deinum Jul 18 '23 at 06:19
  • This is just a special case. Most of the time we use namedJdbcTemplate.query and such that manages it for us. But because of extremly dynamic situation i had to create this monstrosity, – Marko Taht Jul 18 '23 at 06:22
  • You are leaking `PreparedStatements`. You need to close them. – user207421 Jul 18 '23 at 07:08
  • You can still use this special one but you need to take care of which connection you are obtaining and closing. If you are unlucky this code will use 3 connections which will lead to starvation quite quickly. Hence the proper way of obtaining the (possible) thread bound managed connection. – M. Deinum Jul 18 '23 at 07:31
  • @user207421 The OP is using a try-with-resources, so they are not leaking them. – Mark Rotteveel Jul 18 '23 at 07:31
  • OP, you're trick to generate the crosstab only works by accident (and I guess you're using MySQL or MariaDB), because there is no guarantee whatsoever that `toString()` on a prepared statement generates a properly escaped statement with the parameter values interpolated, and in the worst case, it might look like it does that, but then not properly escape the values, making you vulnerable to SQL injection. In most JDBC drivers, parameter values are never interpolated back in the query string: a statement is prepared on the server, and on execute the parameter values are sent separately. – Mark Rotteveel Jul 18 '23 at 07:35
  • @MarkRotteveel im using postgresql. If this is not a good solution what would you reccomend? I cant use normal jdbcTemplate because i cant inject parameters into the queries inside there. I cant use CTE-s because crosstab does not support it. And i cant predefine it because of the higly dynamic nature of the query. – Marko Taht Jul 18 '23 at 08:44
  • As far as I'm aware, PostgreSQL just renders something which looks close to a valid SQL statement, but I'm not sure it will correctly escape values in its `toString()`, making you vulnerable to SQL injection. Actual execution doesn't use what you see in its `toString()`. – Mark Rotteveel Jul 18 '23 at 08:55
  • I have no experience with crosstab, so I can't offer you an alternative, other than suggesting you manually generate the entire statement, and use `Statement.enquoteLiteral` to properly quote literals. – Mark Rotteveel Jul 18 '23 at 08:56
  • crosstab is nothing more than a pivot table. It takes a deep table and converts it to a wide table bades on category_sql and column definitions. Problem is that it takes in Queries in the form of strings, whitch breaks anykind of normal way of doing it. – Marko Taht Jul 18 '23 at 08:57
  • @MarkRotteveel with some small testing... on first sight it seems that PG implementation of PreparedStatement.toString() is sqlInjection safe. I tried sending it values like d or 1=1 and when i looked at the query what had happened was 'd or 1=1'. But i wouldnt call this conclusive testing tho... – Marko Taht Jul 18 '23 at 12:13

0 Answers0