1

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)?

Vamsi Emani
  • 10,072
  • 9
  • 44
  • 71
Michael
  • 2,827
  • 4
  • 30
  • 47

2 Answers2

4

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 :)

Jon Skeet
  • 1,421,763
  • 867
  • 9,128
  • 9,194
  • Currently, i may try to restrict the table name by a regex, although i believe that SQL injection still may try to bypass this restriction , by using the same permitted letters, i still want to demand some robustness in the table name and i might add an encrypted table name in the future, additionally i want to get the advantages of parsing once as you get in prepared statement, are there any other solutions beside restricting it? – Michael Jul 25 '12 at 07:41
  • 1
    @Michael: How would a SQL injection attack bypass that restriction? If you've *only* allowed alpha-numeric values - ideally with a length restriction as well - that cuts out SQL injection possibilities. I don't believe you *can* create a `PreparedStatement` which is parsed once when it comes to having a different table each time. Aside from anything else, part of the benefit of a prepared statement is a cached query plan, which won't make sense when you're changing tables. – Jon Skeet Jul 25 '12 at 07:45
  • fyi, mysql uses backticks (not `[...]`) to delimit "non-standard" schema element names – Bohemian Jul 25 '12 at 07:48
  • @Bohemian: Thanks, will edit that in. (And yes, backticks in SO markdown are a pain :) – Jon Skeet Jul 25 '12 at 07:49
  • 2
    If you want to be extra safe than you can prepare `SHOW TABLES WHERE tables = ?` and call it with supplied table name to check if it really exists. (The `WHERE` condition might need some correction because I don't have mysql under my fingers at the moment). – LisMorski Jul 25 '12 at 08:06
  • @Jon Skeet: You are right, it seems that if i don't allow spaces in the regex, i don't see a way to bypass the restriction, although i am not a specialist in this area. – Michael Jul 25 '12 at 08:45
  • @LisMorski: It is a great answer, please edit it as an answer, and i will except it, i may also suggest to combine this answer with a select statement in one prepared statement: pseudocode: preparedStatment(id show tables where tables=?, select ? from tableNameX where ? ) – Michael Jul 25 '12 at 09:02
  • @Michael I don't exactly understand your suggestion but made the answer from my comment and wrapped it in Java code. Thanks. – LisMorski Jul 25 '12 at 10:18
  • ie `\`table name\`` (I discovered how to put backticks-delimited - ie code - in a comment! you can escape them with a backslash, so this was coded as `\`\\`table name\\`\``) – Bohemian Jul 26 '12 at 00:10
1

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).

LisMorski
  • 399
  • 2
  • 7
  • 1
    Thanks i like this answer, I think the correct syntax is "SHOW TABLES LIKE = ? " , you may also use this syntax in MySQL: SELECT 1 FROM information_schema.tables WHERE table_schema = ? AND table_name = ? – Michael Jul 25 '12 at 11:35
  • 1
    @Michael yes, using `information_schema` is better because it's more portable -- should work on any ANSI SQL-compatible DBMS. But bear in mind that e.g. PostgreSQL implements schemas/schemata so you should add one more `AND` in `WHERE` clause to avoid matching a table in neighbouring schema. – LisMorski Jul 25 '12 at 12:22