2

I'm trying to learn how to use MySQL with Java and as the title says, I'm having problems with prepared statements.

I have a MySQL table named temp which contains the values (output directly from MySQL console):

mysql> select * from temp;
+------+-----------------------------------------------+
| id   | value                                         |
+------+-----------------------------------------------+
|    1 | this is a first item                          |
|    2 | this is the second item                       |
|    3 | This is the third item and slightly redundant |
+------+-----------------------------------------------+
3 rows in set (0.00 sec)

In Java I'm accessing the DB like this:

stmt = conn.prepareStatment("select * from ?");
stmt.setString(1,"temp");
ResultSet rs = stmt.executeQuery(); //This method call throws the exception

stmt.toString reveals: select * from 'temp' and the exception message is: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''temp'' at line 1

When I type select * from 'temp' (The output of stmt.toString()) directly into the MySQL console I get the exact same message.

As you may have imagined, I'm planning on applying this concept to a JSP webpage, where the table name will be a HTTP GET parameter. So my question is: How do I bind the table name to the prepared statement and if it's not possible (which is the vibe I'm getting from similar questions for PHP), how would I sanitize input for the table name?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Alex
  • 1,388
  • 1
  • 10
  • 19
  • Yeah, I saw that but the answers weren't very confident or helpful for that matter. I'm hoping for a better answer or some solution to the problem, which doesn't sacrifice security, for the sake of everyone who runs into this problem in the future. – Alex Jan 19 '14 at 06:27
  • You shouldn't need to purify a table name (unlike form data), so I don't see how it's a problem. – blackcompe Jan 19 '14 at 06:37
  • I'm trying to make a flashcard program with multiple subjects of cards, so I want each subject to be a different table (different subjects may need to store different information). – Alex Jan 19 '14 at 07:10
  • That still doesn't necessitate the use of a prepared statement. You just need to see it done. – blackcompe Jan 19 '14 at 07:44

3 Answers3

1

You can't do that. You need to construct the sql with string concatenation. PreparedStatement is for the column values not for table name.

jagmohan
  • 2,052
  • 2
  • 26
  • 41
1

You can not use stmt.setString() or setInt() function for table name, it is valid only for column values.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
java seeker
  • 1,246
  • 10
  • 13
0

This kind of requirement points to flaws in your database design. If your "subject" tables contain different information, how can you use the same query to retrieve that information?

You are selecting all columns from your table and making your application sort out what it does and doesn't want. This creates risks and vulnerabilities in your application, redundant data transport on your communications channels, and difficulties for database optimisation when your application becomes popular. I suspect your application code is a tangle of if's and cases to sort out the columns coming back. If you rationalise that code so each table format is known and handled by a specific class, your code will be much simpler and your table name doesn't have to be a parameter any more.

Substituting a table name also creates insurmountable security problems due to creating an SQL injection vulnerability. What if somebody gives you (fish INNER JOIN mysql.user ON TRUE) for the table name. That, in conjunction with your all-column select will provide them with your MySQL user table! Do you want that?? Escaping the table value doesn't help you at all. In fact it only gives the attacker a wider range of characters they can inject into your SQL statement.

What you need to do is rationalise and normalise your database design into a table that has a "subject" column that you can select on using a prepared statement parameter.

Terrible Tadpole
  • 607
  • 6
  • 20