• 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
  • Bear Bibeault
  • Jeanne Boyarsky
  • Tim Cooke
Sheriffs:
  • Knute Snortum
  • Junilu Lacar
  • Devaka Cooray
Saloon Keepers:
  • Ganesh Patekar
  • Tim Moores
  • Carey Brown
  • Stephan van Hulst
  • salvin francis
Bartenders:
  • Ron McLeod
  • Frits Walraven
  • Pete Letkeman

Dynamic Query  RSS feed

 
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: 21504
96
Chrome Eclipse IDE Java Spring Ubuntu VI Editor 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.
 
Sheriff
Posts: 23714
50
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:

 
Master Rancher
Posts: 3678
40
  • 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
Sheriff
Posts: 23714
50
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: 1159
9
  • 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
Sheriff
Posts: 23714
50
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.
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!