CODEWITHSUNDEEP
javamysqlprepared-statementdaotablename
Michael
Michael

Reputation: 2867

Can I pass table name as argument to a java prepared statement?

I am writing a DAO layer IN Java for my Tomcat server application,

I wish to use Prepared Statement wrapping my queries (1. parsing queries once, 2. defend against SQL injections), My db design contains a MyISAM table per data source system. And most of the queries through DBO are selects using different table names as arguments.

Some of this tables may be created on the fly.

I already went though many posts that explain that i may not use table name as an argument for Prepared statement.

I have found solutions that suggest to use some type of function (e.g. mysql_real_escape_string) that may process this argument and append the result as a string to the query,

Is there any built in Jave library function that may do it in the best optimized way, or may be you may suggest to do something else in the DAO layer (i do not prefer to add any routines to the DB it self)?

Upvotes: 1

Views: 1778

Answers (2)

LisMorski
LisMorski

Reputation: 399

If you want to be extra safe than you can prepare a query and call it with supplied table name to check if it really exists:

PreparedStatement ps = conn.prepareStatement("SHOW TABLES WHERE tables = ?");
ps.setString(1, nameToCheck);
if(!ps.executeQuery().next())
  throw new RuntimeException("Illegal table name: " + nameToCheck);

(The WHERE condition might need some correction because I don't have mysql under my fingers at the moment).

Upvotes: 1

Jon Skeet
Jon Skeet

Reputation: 1500785

Are you able to apply restrictions to the table names? That may well be easier than quoting. For example, if you could say that all table names had to match a regex of [0-9A-Za-z_]+ then I don't think you'd need any quoting. If you need spaces, you could probably get away with always using `table name` - but again, without worrying about "full" quoting.

Restricting what's available is often a lot simpler than handling all the possibilities :)

Upvotes: 4

Related Questions