Add/remove MySQL column to existing table via PHP / PDO with user defined name and datatype

I want users of my PHP web app to be able to insert columns into some tables without requiring somebody with SQL knowledge. What is the best way to do this, obviously SQL injection and id-10-t errors are a concern.

Can I just use

sql = 'ALTER TABLE table_name ADD ? ?'

and then pass in say 'new_col' and 'varchar(100)' as strings? Or is this outside the abilities of PDO?

Solution

You cannot bind the column name or properties in an ALTER TABLE statement with PDO parameters, unfortunately. Instead, as protection from SQL injection, I would recommend validating the column name against a regular expression which permits only letters, numbers, and underscores, and be sure to backquote the column name in the statement.

if (!preg_match('/^[a-z0-9_]+$/i', $column_name)) {
  // Fail!!! submitted column name doesn't match regex
}

^ = beginning of the string
[a-z0-9_]+ = one or more letters, numbers, underscore
$ = end of the string
/i = case-insensitive

Next, to verify the column's data type, you should only allow a specific set of options, and validate them with an array. This is called a whitelist.

// Allowed types:
$acceptable_types = array('VARCHAR(200)', 'VARCHAR(10)', 'INT', 'FLOAT', 'etc...');
if (!in_array($column_data_type, $acceptable_types)) {
  // Fail!!! Invalid type submitted
}

The above options would best be served via something like a <select> list. You could also specify additional options like NULL / NOT NULL as checkbox inputs to construct the ALTER TABLE statement.

// Only after everything is successfully validated:
$sql = "ALTER TABLE `tablename` ADD `$valid_column_name` $valid_column_datatype $NULL_option";