CODEWITHSUNDEEP
phpmysqlsqlpdo
le_andrew
le_andrew

Reputation: 131

Prepared statement not working with ALTER table queries

I am writing a simple function to add a column to a table using PHP with PDO to prepare the query.

The connection ($dbh) works, other queries that do not involve parameters work. However, the important query that adds the column does not.

When I check the database, there is a new column with the column named ? (i.e. just a question mark) and all the attributes I specified.

I checked to make sure the $column variable is coming through correctly and it is, additionally, the execute statement returns false, so allegedly the statement fails, but somehow a column is still created.

The error info is not very helpful (to me at least): 

Array ( [0] => 00000 ).

I scoured the code for simple typos, but can't spot anything. Any ideas?

$qry='ALTER TABLE `completed` ADD `:column` TINYINT(1) NOT NULL DEFAULT 0';
$stmt = $GLOBALS['dbh']->prepare($qry);
$stmt->bindParam(":column",$column,PDO::PARAM_STR);
$stmt->execute();
$arr = $stmt->errorInfo();
print_r($arr);
$stmt===TRUE ? $return=1 : $return=0;

Upvotes: 2

Views: 2454

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562498

http://dev.mysql.com/doc/refman/5.6/en/prepare.html says:

Parameter markers can be used only where data values should appear, not for SQL keywords, identifiers, and so forth.

By identifiers they mean database names, table names, column names, index names, partition names, etc.

By data values, they mean a numeric literal, quoted string literal, or quoted date literal.

To add a new column, you need to include the name of that column in the SQL string before you prepare the query. This means it's up to you to ensure that there are no funny characters in the column name that could create an SQL injection vulnerability.

Upvotes: 5

Related Questions