MySQL syntax issue when updating numeric column with dynamic field name

Other Questions MySQL
1

I’m building a web application that tracks territory statistics for players in a game-like system. I need to update numeric values in my database but I’m running into problems with my SQL query.

My database table called users has these columns:

  • forest_total
  • town_total
  • cave_total
  • sky_total

I want users to pick a territory type from a dropdown, then my PHP code should update the right column. Here’s what I tried:

// Build column name from form input
$territory = $_POST['territory_type'] . '_total';

if (!isset($_POST['subtract'])) 
{
    $operation = 'INCREASED';
    try 
    {
        $statement = 'UPDATE users SET 
            `:territory` = `:territory` + :amount WHERE user_id = :user_id';
        $cmd = $database->prepare($statement);
        $cmd->bindValue(':territory', $territory);
        $cmd->bindValue(':amount', $_POST['amount']);
        $cmd->bindValue(':user_id', $_POST['user_id']);
        $cmd->execute();
    }
    catch (PDOException $ex)
    {
        $message = 'Database update failed: ' . $ex->getMessage();
        include './error_page.php';
        exit();
    }
}

But I get this error message:

Database update failed: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘‘town_total’’ in ‘field list’

When I remove the backticks around the placeholder I get a different syntax error. How can I make this work with dynamic column names?

2

When using PDO, keep in mind that you cannot bind column names as placeholders. The issue arises because PDO interprets :territory as a string literal instead of a dynamic column name. I faced a similar issue while implementing dynamic updates in my application. To resolve this, construct the SQL query by directly inserting the validated column name. Make sure to implement a validation mechanism to ensure that the provided column name is safe and exists in your database schema. This approach will prevent SQL injection vulnerabilities and resolve the update errors you’re encountering.