Does ALTER TABLE work with PDO?

I’m not getting past the prepare() method
Here’s my code:

try {
$sql = ’
ALTER TABLE org_layer2
ADD :column_nam
VARCHAR(30)
';
$stmt3 = $db3->prepare($sql);
} catch (PDOException $e) {
echo $e->getMessage() ;
}
`

What error message did you get?

My guess is, though I don’t know for certain (never tried it), is you can’t bind column names, only values.

coming to same conclusion.

So, PDO is a nogo for alter tables?

Not PDO, but prepared statements such as that.

AFAIK, placeholders in queries work only for field values not field names or other “identifiers”.

The only way I have been able to have script add identifiers is to construct the query as a string and then eval it. Personally, I would advise that eval be used only as a last resort after every other possible way had been tried. i.e. my use of eval was for a me-only, localhost-only script where I wasn’t concerned about security risk and willing to accept problems that might result because of an error. The use of eval is not something that should be used casually especially on a live site.

how do you eval a string in pdo to avoid the identifier limitation?

Where is the table name coming from?
Is it user input or taken from some pre-defined set of strings?

it’s a col name.

It’s coming from a post array

Yes, column not table.

So it looks like user input, you will have to be very careful including it in a query.

From what type of input?
If it’s a text type input, that will be hardest. Something with pre-defined values will be safer.
In sanitising it you will want to be as strict as possilbe. Could it be reduced to only alpha-numeric characters with limited length?

1 Like

That’s my thinking too.

Switched to mysqli and checkboxes.

Problem solved within the time I allowed myself.

Many thanks to SamA74 and Mittineague

1 Like

PDO should still work, just not the prepared statement.
With the checkboxes, I just to confirm, you are validating the values given against pre-set values. Values in a form can easily be tampered with.
Safer to use an abstract values which can be used as keys in an array of valid values.

$columns = array('a' => 'this', 'b' => 'that', 'c' => 'other'); // List of valid expected values

if(!array_key_exists($_POST['column'], $columns) {
   // That's not a valid value !!
}
else{ $columnName = $columns[$_POST['column']];} // Coming from your code, not user input

In the form the input values will be a, b or c.

1 Like

@nichemtktg,
What are you doing that you are modifying the DB Schema with PHP? Unless this is some sort of phpmyadmin application you really shouldn’t be doing this in Php.

Yes, prepared statements only allows placeholders for values. No column or table names etc. But it easy enough to protect. Just check against a known set of names.

$validSchemaNames = ['col1','col2'];
$colName = $_POST['colName'];
if (!in_array($colName,$validSchemaNames)) {
    echo "Dies Scum!";
    die();
}
1 Like

Always used PDO with prepared()

What’s the advantage of PDO without prepared and mysqli?

EDIT:
Googled it no need to respond to this post unless yoiu want to

how do you get variables into pdo without binding?

By string concatenation. If the variable contains an identifier such as a table name then validate the variable against valid table names (see @ahundiak’s answer) and use it.

If the variable contains a value then you can use the old-style quote:

$pdo->exec("UPDATE mytable SET v=v+1 WHERE name=" . $pdo->quote($var));

Personally, I think concats end up making things harder to read. There are several other approaches to building strings. And I can’t think of any reason to use PDO::quote for values. Always use prepared statements for consistency, safethy and readability.

$colName = 'whatever'; // Already validated
$sql = "UPDATE mytable SET v=v+1 WHERE {$colName} = ?";
echo $sql . "\n";
// UPDATE mytable SET v=v+1 WHERE whatever = ?;

And while overkill for this example, the heredoc syntax works well for more complex strings.

$colName = 'whatever';
$sql = <<< EOT
  UPDATE mytable
  SET v = v + 1
  WHERE {$colName} = ?;
EOT;
echo $sql . "\n";

sprintf also works well in some cases.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.