• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Jeanne Boyarsky
  • Devaka Cooray
  • Paul Clapham
Sheriffs:
  • Tim Cooke
  • Knute Snortum
  • Bear Bibeault
Saloon Keepers:
  • Ron McLeod
  • Tim Moores
  • Stephan van Hulst
  • Piet Souris
  • Ganesh Patekar
Bartenders:
  • Frits Walraven
  • Carey Brown
  • Tim Holloway

Dynamic Query

 
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

Table name will be dynamic and need to return the list of objects dynamically. The parameters also dynamic however 4 parameters will be same for any table.

Method name accepts the table name and 4 parameters required to query any table

   List<?> conversations = jdbcTemplate.queryForList(
            "select * from "+ tableName + " where id=? and userName=? and
    password=? and tenantId=?" , paramsObjectArray);

tableName is a string which comes dynamically
paramsObjectArray is a Object[] which comes dynamically

Currently the query throws invalid column type.

Thanks.
 
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Are you directly querying your database, or are you using JPA API between your database and your application ?



 
Leinad Nongag
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
 
Sheriff
Posts: 21759
102
Eclipse IDE Spring VI Editor Chrome Java Ubuntu Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Leinad, your code is very vulnerable for SQL injection. If you're going to use JDBC directly, you should at least use a PreparedStatement.
 
Kathir jeyap
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The above code is very vulnerable.

I'm looking to execute the method queryForList with the parameters. Please help me with a generic solution.
 
Marshal
Posts: 24594
55
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Like Rob said, use a PreparedStatement. Start with this:

 
Rancher
Posts: 4117
47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Kathir jeyap wrote:
Currently the query throws invalid column type.

Thanks.



Can you show us the column types for the table throwing the error, and the datatypes being supplied in the Object[]?

At least one of them has a mismatch.
 
Leinad Nongag
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Rob Spoor wrote:Leinad, your code is very vulnerable for SQL injection. If you're going to use JDBC directly, you should at least use a PreparedStatement.



I know, it's only examples I took from tutorials.
 
Kathir jeyap
Ranch Hand
Posts: 57
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have used NamedParameterJdbcTemplate and resolved the problem. https://www.javatpoint.com/spring-NamedParameterJdbcTemplate-example
 
Paul Clapham
Marshal
Posts: 24594
55
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
A solution which Spring already made for you! That's clearly the way to go.
 
Ranch Hand
Posts: 1168
11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Paul Clapham wrote:Like Rob said, use a PreparedStatement. Start with this:



Let's just hope dbName doesn't have a value like... "Students; DROP TABLE Students; --"

https://xkcd.com/327/
 
Paul Clapham
Marshal
Posts: 24594
55
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Ryan McGuire wrote:

Paul Clapham wrote:Like Rob said, use a PreparedStatement. Start with this:



Let's just hope dbName doesn't have a value like... "Students; DROP TABLE Students; --"

https://xkcd.com/327/



Yes, you're right, my answer is still subject to SQL injection attacks. That could probably be fixed by validating the dbName variable but fortunately Kathir has found a better answer.
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!