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

Question

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);

ADyson · Accepted Answer · 2022-09-22 17:10:29Z

5

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.

edited Sep 22, 2022 at 17:10

answered Sep 22, 2022 at 17:04

ADyson

62.9k16 gold badges86 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user19103974 Over a year ago

This is great advice, thank you! This is my first foray into working with MySQL outside of a CMS, so I'm definitely a bit nervous about security so I appreciate that tip as well.

Rakesh Mehta Over a year ago

@user19103974 Please accept the answer if it solves your problem.

user19103974 Over a year ago

@RakeshMehta plan on it, just hasn't been enough time yet

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related