Best practice database transactions and storing files onto filesystem in PHP

Question

What would be the best practice regarding integrity if a user uploads user data together with a file where the user data is stored in a database and the file is stored onto the filesystem?

At the moment I would do something like the following code snippet using PHP and PDO (code is not tested, but I hope you will got my point). I don't like the save image part in the User::insert method. Is there a good way around this?

<?php
User::insert($name, $image, $ext);

Class User{
    static public function insert($name, $image, $ext){
        $conn = DB_config::get();

        $conn->beginTransaction();

        $sth = $conn->prepare("
                                INSERT INTO users (name)
                                values(:name)
                                ;");

        $sth->execute(array(
                                ":name"     =>  $name
                                ));

        if ($conn->lastInsertId() > -1 && Image::saveImage($image, IMAGE_PATH . $conn->lastInsertId(). $ext))
            $conn->commit();
        else
            $conn->rollback();

        return $conn->lastInsertId();
    }
}

Class Image{
    static public function saveimage($image, $filename){
        $ext = self::getExtensionFromFilename($filename);

        switch($ext){
            case "jpg":
            case "jpeg":
                return imagejpeg(imagecreatefromstring($image), $filename);
        }

        return false;
    }
?>

Answer 1

Try this.

• Save the image to the disk in a work area. It's best to save it to the work area that's on the same volume as the eventual destination. It's also best to put it in a separate directory.

• Start the transaction with the database.

• Insert your user.

• Rename the image file after the User ID.

• Commit the transaction.

What this does is it performs the riskiest operation first, the saving of the image. All sorts of things can happen here -- system can fail, disk can fill up, connection can close. This is (likely) the most time consuming of your operations, so it's definitely the riskiest.

Once this is done, you start the transaction and insert the user.

If the system fails at this time, your insert will be rolled back, and the image will be in the temporary directory. But for your real system, effectively "nothing has happened". The temporary directory can be cleaned using an automated feature (i.e. clean on restart, clean everything that's over X hours/days old, etc.). Files should have a very short time span in this directory.

Next, rename the the image to its final place. File renames are atomic. They work or they do not.

If the system after this, the user row will be rolled back, but the file will be in its final destination. However, if after restart someone tries to add a new user that happens to have the same user id as the one that failed, their uploaded image will simply overwrite the existing one -- no harm, no foul. If the user id can not be re-used, you will have an orphaned image. But this can reasonably be cleaned up once a week or once a month through an automated routine.

Finally commit the transaction.

At this point everything is in the right place.

Answer 2

This seems like a perfect time to use try/catch block to control flow execution. It also appears that you are missing a big part of the puzzle, which is to save the image path created during the image save to the user, within the user table.

Following code is untested, but should put you on the right track:

Class User{

    static public function insert($name, $image, $ext)
    {
        $conn = DB_config::get();

        // This will force any PDO errors to throw an exception, so our following t/c block will work as expected
        // Note: This should be done in the DB_config::get method so all api calls to get will benefit from this attribute
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        try {

            $conn->beginTransaction();

            $sth = $conn->prepare("
                INSERT INTO users (name)
                values(:name);"
            );

            $sth->execute(array(":name" => $name));

            $imagePath = Image::saveImage($image, IMAGE_PATH . $conn->lastInsertId(). $ext));

            // Image path is an key component of saving a user, so if not saved lets throw an exception so we don't commit the transaction
            if (false === $imagePath) {
                throw new Exception(sprintf('Invalid $imagePath: %s', $imagePath));
            }

            $sth = $conn->prepare("UPDATE users SET image_path = :imagePath WHERE id = :userId LIMIT 1");

            $sth->bindValue(':imagePath', $imagePath, PDO::PARAM_STR);
            $sth->bindValue(':userId', $conn->lastInsertId(), PDO::PARAM_INT);

            $sth->execute();

            // If we made this far and no exception has been thrown, we can commit our transaction
            $conn->commit();

            return $conn->lastInsertId();

        } catch (Exception $e) {

            error_log(sprintf('Error saving user: %s', $e->getMessage()));

            $conn->rollback();
        }

        return 0;
    }
}