PHP - using a variable as part of a column name in an INSERT INTO MySQL statement-CodePudding

I have a database set up and there are 2 different columns and I want to insert values into one of those two columns dynamically based on an ID that is passed in from $_GET. I have the bindParam variable part working, but I'm not sure how to use a variable in the INSERT INTO portion of the statement.

One column is called product1_vote and the other is product2_vote. I am getting the 1 or 2 from $_GET and I want to pass that into the prepare call to determine which column to update.

$productID = $_GET['id'];

$stmt = $pdo->prepare('INSERT INTO products (id, title, product1_vote) 
    VALUES(:id, :title, :product1_vote);
$id = $pdo->lastInsertId();
$title = 'Test';
$date = date('m/d/Y h:i:s', time());
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
$stmt->bindParam(':title', $title, PDO::PARAM_STR);
$stmt->bindParam(':product1_vote', $date, PDO::PARAM_STR);

How would I go about changing the INSERT INTO part to work dynamically instead of the current hardcoded product1_vote.

Something like this to give you an idea of what I'm after:

$stmt = $pdo->prepare('INSERT INTO products (id, title, product.$productID._vote) 
    VALUES(:id, :title, :product.$productID._vote);
$id = $pdo->lastInsertId();
$title = 'Test';
$date = date('m/d/Y h:i:s', time());
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
$stmt->bindParam(':title', $title, PDO::PARAM_STR);
$stmt->bindParam(':product.$productID._vote', $date, PDO::PARAM_STR);

CodePudding user response：

You can't parameterise a column name, but also, to guard against SQL injection you don't want to allow direct user input into the query without validation.

A common solution to this is to make a "whitelist" of allowed values and ensure that the user-provided value matches one of them before including it in the query.

For example:

$productID = $_GET['id'];
$voteIDs = ["1", "2"];
if (!in_array($productID, $voteIDs)) {
  echo "invalid input value";
  die();
};

$stmt = $pdo->prepare('INSERT INTO products (id, title, product'.$productID.'_vote) 
    VALUES(:id, :title, :product1_vote);

P.S. It's possible this has arisen because your database could be better normalised. If you have multiple votes per product, consider storing them in a separate "productVotes" table with a foreign key back to the products table. Then you wouldn't need to vary the column names in your query.