msql table name with $ sign

Question

I have tables in Mysql 5 db with names prefixed with a dollar sign '$' ie tablename $MYTABLE

I am using Spring 3.0 JdbcTemplate to do my select query but couldn't get it to work.

ie

    String tablename = "$AAPL";

    private static final String STOCK_SELECT = 
    "select symbol, open, high, low, close, vol, ev from ?";

    jdbcTemplate.query(STOCK_SELECT, 
                       new Object[] { tablename },
                       new RowMapper() { .... } );

This will always throws InvalidSqlException, presumably because of the $ sign. If I just do a normal query with no param, ie.

    private static final String STOCK_SELECT = 
    "select symbol, open, high, low, close, vol, ev from $AAPL";

Then everything works.

How can I escape the $ sign using jdbcTemplate?

-- Edit, what I ended up doing --

Instead of passing the table name "$AAPL" to jdbcTemplate, I just create the SQL string manually, i.e.

 jdbcTemplate.query( getStockSelect("$AAPL", .., .. ));

Answer 1

SQL supports delimited identifiers so you can use punctuation, white space, special symbols, international characters, or SQL keywords as table names or column names.

See my past answer to Do different databases use different name quote?

In MySQL, use back-quotes:

private static final String STOCK_SELECT = 
"select symbol, open, high, low, close, vol, ev from `$AAPL`";

Or set SQL_MODE to ANSI mode and use double-quotes:

private static final String STOCK_SELECT = 
"select symbol, open, high, low, close, vol, ev from \"$AAPL\"";

You can't use a ? placeholder for a table name in SQL. That's just not supported by the language. You can use a parameter only where you could normally use a literal value, like an integer or a single-quoted string.

Answer 2

Database information like table and column names/identifers are not meant to be parameterized. MySQL uses the backtick (`) for table and column names/identifiers. Your parameterized query is probably going in as:

 select symbol, open, high, low, close, vol, ev from "$AAPL"

I'm not aware of any standard API for parameterizing/escaping identifiers like that. I'd recommend, if you can, just to have it in there statically. Since they're the same columns partitioning the table might also be an option. Finally, if you still need it to be dynamic, you'll have to escape the table name yourself, I'd recommend only pulling it in from a whitelist.

You may want to take a look at the mysql documentation on how to escape identifiers.