2

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;
Prix
  • 19,417
  • 15
  • 73
  • 132
le_andrew
  • 131
  • 4
  • 1
    http://stackoverflow.com/a/15991422/285587 – Your Common Sense Mar 21 '14 at 21:59
  • 3
    Btw, adding columns on the fly is a sign of a bad design, not to say worse – Your Common Sense Mar 21 '14 at 22:00
  • I figured that. I just have no idea how else to structure the database. Not being a professional in any way, and just needing this to work for a specific purpose, not for public use, I figured my bad design wouldn't really cause any problems. I guess with this issue, and not wanting to go back to running queries using a standard connection, I can take this time to learn better db design though. Edit: I'll add that I'm not really adding columns on the fly. This is for a UI page where I can type in a name and click a button to add the column I want, instead of going into PHPMyAdmin to do it. – le_andrew Mar 21 '14 at 22:06
  • And thanks for that cite. I had read that it didn't work with table names, but didn't see that it also didn't work with field names. – le_andrew Mar 21 '14 at 22:07
  • @YourCommonSense could you expand on why altering a table by adding or deleting a column on the fly is a bad idea? I currently have a case where Im adding "products" which creates a new column in table. Nothing added to the table comes from user input. – FamousAv8er Dec 04 '19 at 16:21
  • @FamousAv8er honestly, I cannot even think of the case where one would need to add a new column to a products table. Mind getting into detail? – Your Common Sense Dec 04 '19 at 16:50
  • @YourCommonSense In this case it would be an administrative feature to either add or remove available products. The table being altered is one that associates customers to a product status (either enrolled or not enrolled). If there is a new product a new column has to be added to this table so that the customers status can be displayed. – FamousAv8er Dec 04 '19 at 16:54
  • @FamousAv8er I am not going into further discussion on the matter but such a structure is a fatal mistake. I would suggest you to ask a question regarding it here on stack overflow before it is too late. – Your Common Sense Dec 04 '19 at 18:30

1 Answers1

4

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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828