CODEWITHSUNDEEP
javasql
Benoit Goderre
Benoit Goderre

Reputation: 537

PreparedStatement in Java with variables for database and column names

I am writing some code in SQL using the PreparedStatement to help with SQL injection.

Example: 

stm = c.prepareStatement("UPDATE "
              + listResults.get(i).get(TemplateFile.TYPEOFPLAY).toLowerCase()
              + " SET score=? WHERE player_name_fkey=? AND school_name_fkey=? AND tournament_number=?;");

stm.setInt(1, Utilities.readNumber(listResults.get(i).get(TemplateFile.TOURNAMENT + j - 1)));
stm.setString(2, listResults.get(i).get(TemplateFile.NAME));
stm.setString(3, listResults.get(i).get(TemplateFile.SCHOOL_NAME));
stm.setInt(4, j);

My question is the following: is there a way to get the PreparedStatement object to allow parameters (the ? sign) to be used for database names and columns. For example:

stm = c.prepareStatement("UPDATE ? SET score=? WHERE player_name_fkey=? AND school_name_fkey=? AND tournament_number=?;");

stm.setString(1, listResults.get(i).get(TemplateFile.TYPEOFPLAY).toLowerCase());
stm.setInt(2, Utilities.readNumber(listResults.get(i).get(TemplateFile.TOURNAMENT + j - 1)));
stm.setString(3, listResults.get(i).get(TemplateFile.NAME));
stm.setString(4, listResults.get(i).get(TemplateFile.SCHOOL_NAME));
stm.setInt(5, j);

It would look so much better and be easier to read as well.

Thanks in advance for any help!

Upvotes: 0

Views: 268

Answers (3)

usha
usha

Reputation: 29349

you cannot do that. You can only bind column values on PreparedStatement. If you are not getting the table name from end user you can the following

String query = "UPDATE <tablename> SET score=? WHERE player_name_fkey=? AND school_name_fkey=? AND tournament_number=?";
query.replace("<tablename>", listResults.get(i).get(TemplateFile.TYPEOFPLAY).toLowerCase())
c.prepareStatement(query);

If you are getting the table name from enduser, then have a whitelist of table names and check your input with the whitelist

Upvotes: 1

Kayaman
Kayaman

Reputation: 73558

No, PreparedStatement parametrizes only the column values, nothing else.

Setting the table name dynamically might be indicative of a design issue, since normally you would hardcode it just like the column names, joins etc.

Upvotes: 1

Brian
Brian

Reputation: 6450

This kind of coding is a bit dated now, albeit it "works". Have you considered using an ORM - Object/Relational Mapper - like iBatis or Hibernate? Appreciate it's a more "big commitment" change than you're perhaps looking to take on, but to use one of their own examples, with a bit of XML to contain the SQL, you get to look after fairly plain-text SQL:

<select id="getProduct" parameterClass="java.lang.Long" resultClass="com.example.Product">
select PROD_ID as id,
           PROD_DESC as description
      from PRODUCT
     where PROD_ID = #value#
</select>

And invoke code to specify that "#value#" token:

Product resultProduct = (Product) sqlMapClient.queryForObject("getProduct", 123);

As I check links, the Java route here is maintained - actively - at:

http://blog.mybatis.org/

but I'm leaving the original Wiki example as I think it's a clearer illustration.

FWIW I've been a big user of this, and found it excellent to work with. The ability to properly maintain clear, formatted, plain-text SQL is tremendous.

Taken from: https://en.wikipedia.org/wiki/IBATIS#Usage

Upvotes: 1

Related Questions