Dealing with quotes added by PDO::prepare()

问题:

According to the PHP Documentation PDO::prepare() adds quotes to all your parameters so that you don't have to worry about doing it:

"The parameters to prepared statements don't need to be quoted; the driver automatically handles this. If an application exclusively uses prepared statements, the developer can be sure that no SQL injection will occur (however, if other portions of the query are being built up with unescaped input, SQL injection is still possible)."

The problem with this for me is the way I am building my queries and my database structure. Usually the FROM part of an SQL Statement wouldn't need to be parametrized because the Table probably would be defined by direct user input. However with my code that is the case in some places and thus I feel more comfortable with the parametrized version.

SELECT * FROM ? WHERE ?=?

as opposed to SELECT * FROM tablename WHERE ?=?

So my question is this, is it possible to prevent my PDO Object from adding the quotes around the FROM parameter so that I don't get SQL errors thrown in my face? Or do I have to do this in a different manner.

回答1:

The placeholders in prepared statements are for values only. The only way to insert dynamic table names is to do it yourself

"SELECT FROM `".$table."` WHERE `".$column."` = ?"

回答2:

@KingCrunch is mostly correct in his answer. You should really escape the string on your own. Something like this should protect against most injections:

//make sure $table and $column only contain alphanumeric chars  
$table = preg_replace("/[^A-Za-z0-9]/", '', $table);
$column = preg_replace("/[^A-Za-z0-9]/", '', $column); 

$query = "SELECT FROM `{$table}` WHERE `{$column}` = ?"