UPDATE a single specific MySQL Cell wont work

Question

im fairly new to PHP/MySQL but i found no answers in the net for my problem:

I've got a form with 4 textfields

    <form method="post" action="updateuserdatatest.php">
<input type="text" value="Hans" name="username">
<input type="text" value="1234" name="password"> 
<input type="text" value="desired cell" name="desiredcell">
<input type="text"  value="desired value" name="desiredvalue">
<input type="submit">
</form>

I want to update the named "desired cell" with the "desired value". So i have to type in the username, his password, a column name (i.e. "streetname","postcode" or "city") and after that the stringvalue which shall be submitted to the database.

I use this code:

    $pdo = new PDO(everything is OK here! - Checked this out many times);
    $sql = 'UPDATE user SET :desiredcell = :desiredvalue WHERE username = :username AND password = :password';

    $stmt = $pdo->prepare($sql);

    $stmt->bindValue(":desiredcell", $_POST['desiredcell'], PDO::PARAM_STR);
    $stmt->bindValue(":desiredvalue", $_POST['desiredvalue'], PDO::PARAM_STR);
    $stmt->bindValue(":username", $_POST['username'], PDO::PARAM_STR);
    $stmt->bindValue(":password", $_POST['password'], PDO::PARAM_STR);
    $stmt->execute();

if ($stmt->errno) { echo "FAILURE!!! " . $stmt->error;
}
else echo "Updated {$stmt->affected_rows} rows";

$response = array();
$response['success'] = true;

echo json_encode($response);

?>

This does not work!

But when i change the sql query to a specific columnname like 'UPDATE user SET streetname = :desiredvalue WHERE username = :username AND password = :password';

then it works! why? i want to type in the cellname which has to be updated manually!

i tried this with mysqli queries before like UPDATE user SET ?=? WHERE password=? AND username=? same problem there

What am i doing wrong? Thanks in advance.

EDIT: seems that i cant post images in a comment, so i make another answer: this is my dummy table,

when i try to insert the column variable like your example nothing happens inside the table, but i still get the success response.

    $column = in_array($_POST['desiredcell'], ['streetname', 'postcode','state']) ? $_POST['desiredcell'] : 'streetname';          
$sql = 'UPDATE user SET $column = :desiredvalue WHERE username = :username AND password = :password';

$stmt = $pdo->prepare($sql);   

$stmt->bindValue(":desiredvalue", $_POST['desiredvalue'], PDO::PARAM_STR);
$stmt->bindValue(":username", $_POST['username'], PDO::PARAM_STR);
$stmt->bindValue(":password", $_POST['password'], PDO::PARAM_STR);
$stmt->execute();

any tips? =/

EDIT: Found the solution:

$sql = 'UPDATE user SET '.$column.' = :desiredvalue WHERE username = :username AND password = :password';

thank you guys.

With [PDO](http://php.net/manual/en/book.pdo.php) you cannot replace table and column names. Please see this question: https://stackoverflow.com/questions/182287/can-php-pdo-statements-accept-the-table-or-column-name-as-parameter — tread, Sep 08 '17 at 08:11
Possible duplicate of [Can PHP PDO Statements accept the table or column name as parameter?](https://stackoverflow.com/questions/182287/can-php-pdo-statements-accept-the-table-or-column-name-as-parameter) — Qirel, Sep 08 '17 at 08:15
Neither PDO nor MySQLi supports bounded table or column-names. You can use variables directly in the query **IF, and only if**, you whitelist them first (which basically means you check what the input was - and use your own, serverside defined variable which has the proper column/table). — Qirel, Sep 08 '17 at 08:15

score 1 · Accepted Answer · answered Sep 08 '17 at 08:21

As it mentioned it the comments, you can't bind column names. You should use a variable and make sure the input is a real column name (for security reasons)

Example:

<?php
$column = in_array($_POST['desiredcell'], ["column1", "column2"]) ? $_POST['desiredcell'] : "column1";
$sql = "UPDATE user SET $column = :desiredvalue WHERE username = :username AND password = :password'";

Also bear in mind that storing a plain text passwords in a database is a real bad idea. Check password_hash function.

And the plain passwords are just for testing purpose! – Eirik Fesker Sep 08 '17 at 09:34 — Eirik Fesker, Sep 08 '17 at 09:34

UPDATE a single specific MySQL Cell wont work

1 Answers1