Using Prepared Statements to Set Table Name

How to use a tablename variable for a java prepared statement insert

You can't. You need to contruct the sql with string concatenation/placeholder with String.format. prepared statement is for the column values not for table name.

Using Prepared Statements to set Table Name

A table name can't be used as a parameter. It must be hard coded. So you can do something like:

private String query1 = "SELECT plantID, edrman, plant, vaxnode FROM [" + reportDate + "?]";

How to pass table name to a Prepared Statement in a SELECT COUNT query?

You can only bind values in a PreparedStatement, not syntactic elements or object names (in this case, the table name). You'll have to resort to string manipulation:

final String query = String.format("SELECT COUNT(*) FROM %s", tablename);
final PreparedStatement preparedStatement = connection.prepareStatement(query);
final ResultSet resultSet = preparedStatement.executeQuery();

Note that there are no placeholders in this query, so it's questionable whether there's really any advantage in using a PreparedStatement as opposed to a plain old Statement.

prepared statement - using parameter to specify table name

It is not possible. The prepare statement is persistent execution plan - and execution plan contains pined source of data - so tables, column names cannot be mutable there.

When you change table, columns, then you change the semantic of query - you will got different execution plan and then this behave is not possible in prepared statements. The main use case of prepared statements is reusing of execution plans - plan once, execute more. But there are some principal limits - only some parameters can be changed.

Can I parameterize the table name in a prepared statement?

Short answer to your question is "no".

In the strictest sense, at the database level, prepared statements only allow parameters to be bound for "values" bits of the SQL statement.

One way of thinking of this is "things that can be substituted at runtime execution of the statement without altering its meaning". The table name(s) is not one of those runtime values, as it determines the validity of the SQL statement itself (ie, what column names are valid) and changing it at execution time would potentially alter whether the SQL statement was valid.

At a slightly higher level, even in database interfaces that emulate prepared statement parameter substitution rather than actually send prepared statements to the database, such as PDO, which could conceivably allow you to use a placeholder anywhere (since the placeholder gets replaced before being sent to the database in those systems), the value of the table placeholder would be a string, and enclosed as such within the SQL sent to the database, so SELECT * FROM ? with mytable as the param would actually end up sending SELECT * FROM 'mytable' to the database, which is invalid SQL.

Your best bet is just to continue with

SELECT * FROM {$mytable}

but you absolutely should have a white-list of tables that you check against first if that $mytable is coming from user input.

Safe way to use table name as parameter in JDBC query

I would try to solve the design problem, so you don't have to set the table name dynamically. If this is not possible, I would go for a design where you manage a list of available tables and users pick one from there, BY ID, so you can retrieve the real table name from the chosen id and replace the table name placeholder with it, avoiding any chance of sql injection in the table name replacement.

Dynamic table names and prepared statements using MySQLi

Yes, it is possible but you don't have to create a table to hold the table names and maintain it manually - of course mysql already has such a table. So it could be just

$sql = "SELECT 1 FROM information_schema.TABLES 
        WHERE TABLE_NAME = ? AND TABLE_SCHEMA IN (SELECT DATABASE())";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param("s", $table);
$stmt->execute();
return (bool)$stmt->get_result()->fetch_row();

However, the usage should be a bit different. It makes no sense to run a query like SELECT * FROM WHERE with just empty table name. You should only verify the function's result and throw an error in case it is empty.

Why prepared statements don't allow fields and table names as parameters?

There are basically two reasons that I am aware of:

1. Although details vary per database system, conceptually, when a statement is prepared, it is compiled and checked for correctness (do all tables and columns exist). The server generates a plan which describes which tables to access, which fields to retrieve, indexes to use, etc.

   This means that at prepare time the database system must know which tables and fields it needs to access, therefore parameterization of tables and fields is not possible. And even if it where technically possible, it would be inefficient, because statement compilation would need to be deferred until execution, essentially throwing away one of the primary reasons for using prepared statements: reusable query plans for better performance.

   And consider this: if table names and field names are allowed to parameterized, why not function names, query fragments, etc?

2. Not allowing parameterization of objects prevents ambiguity. For example in a query with a where column1 = ?, if you set the parameter to peter, would that be a column name or a string value? That is hard to decide and preventing that ambiguity would make the API harder to use, while the use case of even allowing such parameterization is almost non-existent (and in my experience, the need for such parameterization almost always stem from bad database design).

Allowing parameterization of objects is almost equivalent to just dynamically generating the query and executing it (see also point 1), so why not just forego the additional complexity and disallow parameterization of objects instead.

---

Related Topics