UPDATE with variables in PHP

Question

I need to update a 1 record but only touch one of its columns, the user gives the name of the column through an input in a form.

In my code I am receiving the name of the column in the variable $ii through a $_GET, I need help with the sitanxis

$query_nuevanota = mysql_query("UPDATE nota SET ".$ii." = $nom_aux WHERE id=$id");

or

$query_nuevanota = mysql_query("UPDATE nota SET $ii = $nom_aux WHERE id=$id");

If you allow users to specify the column to be updated, you **must** use a whitelist to validate the user input. For your current code to work the value would need to be quoted. Your code is vulnerable to [SQL Injection](https://www.php.net/manual/en/security.database.sql-injection.php). You should be using [parameterized prepared statements](https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php), which are provided by [MySQLi](https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [PDO](https://www.php.net/manual/en/pdo.prepared-statements.php). — user1191247, Mar 11 '23 at 17:58
Both will work (it's just a string, so see https://www.php.net/manual/en/language.types.string.php) but neither is a good idea: use parameters for data, as nnichols says, and only allow the user to choose from a preset list of columns, see https://stackoverflow.com/q/182287/157957 — IMSoP, Mar 11 '23 at 18:01
**Warning:** `mysql_*` extension is deprecated as of PHP 5.5.0 (2013), and has been removed as of PHP 7.0.0 (2015). Instead, either the [mysqli](https://www.php.net/manual/en/book.mysqli.php) or [PDO_MySQL](https://www.php.net/manual/en/book.pdo.php) extension should be used. See also the [MySQL API Overview](https://www.php.net/manual/en/mysqlinfo.api.choosing.php) for further help while choosing a MySQL API. — Dharman, Mar 11 '23 at 22:06

user1191247 · Answer 1 · 2023-03-11T18:54:11.217

This is a crude example but should give you some idea of what you need to do:

// whitelist of column names which can be accepted via user input
$column_whitelist = ['col1', 'col2', 'col3'];

if (!in_array($_GET['col_name'], $column_whitelist)) {
    // do your error handling
} else {
    $ii = $_GET['col_name'];

    // prepare your SQL query    
    $stmt = $pdo->prepare("UPDATE nota SET `$ii` = ? WHERE id = ?");

    // pass in the variables to be bound to the placeholders in
    // the query and execute it
    $stmt->execute([$nom_aux, $id]);
}

or you could provide the user with a select element to choose the column they are searching:

<select name="col_name">
    <option value="1">col 1</option>
    <option value="2">col 2</option>
    <option value="3">col 3</option>
</select>

and then in your PHP do something like:

$column_whitelist = [
    1 => 'my_table_col_1',
    2 => 'my_table_col_2',
    3 => 'my_table_col_3'
];

if (isset($column_whitelist[(int)$_GET['col_name']])) {
    $ii = $column_whitelist[(int)$_GET['col_name']];

    // prepare your SQL query    
    $stmt = $pdo->prepare("UPDATE nota SET `$ii` = ? WHERE id = ?");

    // and so on
}

UPDATE with variables in PHP

1 Answers1