Can I parameterize the table name in a prepared statement?

Question

I've used the mysqli_stmt_bind_param function several times. However, if I separate variables that I'm trying to protect against SQL injection I run into errors.

Here's some code sample:

function insertRow( $db, $mysqli, $new_table, $Partner, $Merchant, $ips, $score, $category, $overall, $protocol )
{
    $statement = $mysqli->prepare("INSERT INTO " .$new_table . " VALUES (?,?,?,?,?,?,?);");
    mysqli_stmt_bind_param( $statment, 'sssisss', $Partner, $Merchant, $ips, $score, $category, $overall, $protocol );
    $statement->execute();
}

Is it possible to somehow replace the .$new_table. concatenation with another question mark statement, make another bind parameter statement, or add onto the existing one to protect against SQL injection?

Like this or some form of this:

function insertRow( $db, $mysqli, $new_table, $Partner, $Merchant, $ips, $score, $category, $overall, $protocol )
{    
    $statement = $mysqli->prepare("INSERT INTO (?) VALUES (?,?,?,?,?,?,?);");
    mysqli_stmt_bind_param( $statment, 'ssssisss', $new_table, $Partner, $Merchant, $ips, $score, $category, $overall, $protocol );
    $statement->execute();
}

No, a parameterised query doesn't just drop the parameter values in to the query string, it supplies the RDBMS with the parameterised query and the parameters separately. But such a query can't have a table name or field name as a parameter. The only way to do that is to dynamically code the table name into the query string, just as you have already done. If this string is potentially open to attack you should validate it first; such as against a white list list of allowable tables. — MatBailie, Jul 03 '12 at 14:23
The use of mysqli extension is safe, go ahead! But do not forget to sanitize and validate all your strings.. if table or field name or whatever standing against your data base! — devasia2112, Jul 03 '12 at 14:24
@user1475765 using escape functions on your table name does not protect you from anything, use a white-list. — jeroen, Jul 03 '12 at 15:49
Unless its necessary to use `mysqli` may I recommend PDO? It's flexible and makes it easy to switch to other database systems, and provides prepared statements (which if you **always** use, guarantees no SQL injection http://www.php.net/manual/en/pdo.prepare.php). Prepared statements are also more efficient when you're running similar queries and just need to swap out a variable). Short example: `$dbh = new PDO($dsn, $user, $password); $query = $dbh->prepare('SELECT username FROM users WHERE id=?'); $query->execute(array($id)); $result = $query->fetch(); //result is a row with one column (id)` — Raekye, Jul 03 '12 at 16:05
@Raeki PDO has the same problem with table names or column names as mysqli, so `no SQL injection` is never guaranteed. — jeroen, Jul 03 '12 at 16:10
I'm interested in the white list. Can someone explain a bit more how that works? @Raeki, I like your suggestion as well I was having trouble getting PDO to work on my server though for some reason — GK1667, Jul 03 '12 at 16:11
You also don't mention what sort of errors you're running into... — Cylindric, Jul 03 '12 at 16:24
You can simply create an ini file with an array with all your tables name, then you parse this file and compare if there are the name in your variable each time you need a statement .. White list are the best option in your case. Sanitize a string against regular expression (regex) is another option. Take a look -> http://br.php.net/manual/en/function.parse-ini-file.php — devasia2112, Jul 03 '12 at 16:26
But beware, INI files can be readable from internet, give the right permissions to it. — devasia2112, Jul 03 '12 at 16:28

Sam Graham · Accepted Answer · 2015-09-29T05:19:52.143

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.

Great advise, but in my experience this cannot apply to larger dynamic websites. If your site has multi-user setup, large and constantly updating inventory and etc this solution will be extremely hard to apply. — Igal Zeifman, Jul 04 '12 at 11:31
Size of website has very little to do with number of tables and knowing which ones end-users can direct queries at via parameters. Ultimately it's an odd website that lets end-users do this in the first place. — Sam Graham, Jul 05 '12 at 09:57

score 1 · Answer 2 · edited Jan 18 '22 at 14:09

1

The same rule applies when trying to create a "database".

You cannot use a prepared statement to bind a database.

I.e.:

CREATE DATABASE IF NOT EXISTS ?

will not work. Use a safelist instead.

edited Jan 18 '22 at 14:09

Dharman

30,962
25
85
135

answered Nov 08 '18 at 15:08

Funk Forty Niner

74,450
15
68
141

Can I parameterize the table name in a prepared statement?

2 Answers2

Linked

Related