• 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 Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Dynamic Query

 
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
 
Sheriff
Posts: 22787
131
Eclipse IDE Spring Chrome Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 28263
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Like Rob said, use a PreparedStatement. Start with this:

 
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have used NamedParameterJdbcTemplate and resolved the problem. https://www.javatpoint.com/spring-NamedParameterJdbcTemplate-example
 
Paul Clapham
Marshal
Posts: 28263
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
A solution which Spring already made for you! That's clearly the way to go.
 
Bartender
Posts: 1205
22
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 28263
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic