Win a copy of 97 Things Every Java Programmer Should Know this week in the Java in General forum!
  • 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
  • Paul Clapham
  • Jeanne Boyarsky
  • Junilu Lacar
  • Henry Wong
Sheriffs:
  • Ron McLeod
  • Devaka Cooray
  • Tim Cooke
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Frits Walraven
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Piet Souris
  • salvin francis
  • fred rosenberger

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: 21950
106
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: 25594
69
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: 4583
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: 25594
69
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.
 
Rancher
Posts: 1196
22
  • 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: 25594
69
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.
 
They weren't very bright, but they were very, very big. Ad contrast:
Devious Experiments for a Truly Passive Greenhouse!
https://www.kickstarter.com/projects/paulwheaton/greenhouse-1
    Bookmark Topic Watch Topic
  • New Topic