PHP PDO dynamic update query to MYSQL

Question

I have a form with an image upload and text inputs. it keeps replacing the profile_picture field with NULL. Therefore, I'm trying to create a dynamic update query, where if one value is empty it's excluded from the query altogether.

Any help is appreciated.

IMAGE UPLOAD:

if (!empty($_FILES['profile_picture']) && $_FILES['profile_picture']['error'] == UPLOAD_ERR_OK) {

    // Rename the uploaded file
    $uploadName = $_FILES['profile_picture']['name'];
    $tmp_name = $_FILES['profile_picture']['tmp_file'];
    $ext = strtolower(substr($uploadName, strripos($uploadName, '.')+1));
    $filename = round(microtime(true)).mt_rand().'.'.$ext;

if (move_uploaded_file($_FILES['profile_picture']['tmp_name'],'../profile_picutres/'. $filename)) {

}

}

UPDATE QUERY:

$stmt = $dbh->prepare("UPDATE 001_user_table_as SET profile_picture=:profile_picture, first_name=:first_name, last_name=:last_name, phone_number=:phone_number, nationality=:nationality, years_experience=:years_experience, data=:data WHERE id=:id");

 $stmt->bindParam(':profile_picture', $filename);
 $stmt->bindParam(':first_name', $first_name);
 $stmt->bindParam(':last_name', $last_name);
 $stmt->bindParam(':phone_number', $phone_number);
 $stmt->bindParam(':nationality', $nationality);
 $stmt->bindParam(':years_experience', $years_experience);
 $stmt->bindParam(':data', $cv_data);
 $stmt->bindParam(':id', $user_id);

if($stmt->execute()){
$response["message"] = 'success';   
}else{
$response["message"] = 'error'; 
$errors++;
}

for reference: A canonical example I wrote: PDO examples. How to create a WHERE clause for PDO dynamically — Your Common Sense
– Your Common Sense, Commented Nov 27, 2017 at 9:06
As of your (overly complicated) code, you have at least a double colon, may be other issues. Why don't you echo the resulting query out and just see the SQL? — Your Common Sense
– Your Common Sense, Commented Nov 27, 2017 at 9:11
wait. it's UPDATE. you are trying to use a helper code for SELECT to create an UPDATE query. — Your Common Sense
– Your Common Sense, Commented Nov 27, 2017 at 9:13

Your Common Sense · Accepted Answer · 2024-06-05 07:26:39Z

Below is the solution that creates correct SQL statement without the risk of SQL injection.

Note that this code will update only values that present in the $_POST array and not empty. If you want to allow empty values, remove the && strlen($_POST[$key] === 0) part.

// List of allowed column names. OBLIGATORY to prevent errors and injections
$allowed = ["profile_picture","first_name","last_name", "phone_number", "nationality", "years_experience", "data" ];

// initialize array with values for execute:
$params = [];

// initialize a string with `fieldname` = :placeholder pairs
$setStr = "";

// loop over source data array
foreach ($allowed as $key)
{
    if (isset($_POST[$key]) && strlen($_POST[$key] === 0))
    {
        $setStr .= "`$key` = :$key ,";
        $params[$key] = $_POST[$key];
    }
}
$setStr = rtrim($setStr, ",");

// add value for id to array with parameters
$params['id'] = $_SESSION['user_id'];

$dbh->prepare("UPDATE 001_user_table_as SET $setStr WHERE id = :id")->execute($params);

Amin NAIRI · Accepted Answer · 2022-05-23 09:56:34Z

Rather than relying on a global variable, you could use a function to generate the SQL string that depends only on a table name, allowed columns and columns provided. This allows you to react to any source of request (forms, raw body, ...).

<?php

function getPreparedUpdateSql(string $table, array $allowedColumns, array $columns): string
{
    $set = [];

    foreach ($columns as $column) {
        if (!in_array($column, $allowedColumns)) {
            continue;
        }

        $set[] = "$column = :$column";
    }

    $set = implode(", ", $set);

    return "UPDATE $table SET $set WHERE id = :id";
}

And here is an example usage of that function anywhere you need it.

<?php

$connection = new PDO("mysql:dbname=dbname;host=127.0.0.1", "user", "pass");

$jsonRequestBody = json_decode(file_get_contents("php://input"), true);
// ["firstname" => "firstname", "lastname" => "lastname"]

$entityId = 1;

$table = "users";

$allowedColumns = ["firstname", "lastname", "email", "role"];

$columns = array_keys($jsonRequestBody);
// ["firstname", "lastname"]

$sql = getPreparedUpdateSql($table, $allowedColumns, $columns);
// UPDATE users SET firstname = :firstname, lastname = :lastname WHERE id = :id

$query = $connection->prepare($sql);

$query->execute([...$jsonRequestBody, "id" => $entityId]);

If you want to use it on traditional forms, you can simply change the columns variable to this.

<?php

$columns = array_keys($_POST);

Do not forget to check for thrown exceptions!

Collectives™ on Stack Overflow

PHP PDO dynamic update query to MYSQL

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related