Image file name deleted unintentionally from MySQL after PHP CRUD blog update

Question

I can't seem to redefine this variable $featured_image = ""; If I update a blog post with a new image, old image or leave it blank, I end up losing the original image name in MySQL, which in turn removes the image from the blog post. The actual file is still in the right folder, the filename is just missing from MySQL. Part of the problem is that I cannot define the existing file name thats in the database. Thanks!

<?php
// Post variables
$post_id = 0;
$isEditingPost = false;
$published = 0;
$title = "";
$post_slug = "";
$body = "";
$featured_image = ""; // <-- there's the culprit
$post_topic = "";

// lots of other functions omitted for clarity
function editPost($role_id)
{
    global $conn, $title, $post_slug, $body, $published, $isEditingPost, $post_id;
    $sql = "SELECT * FROM posts WHERE id=$role_id LIMIT 1";
    $result = mysqli_query($conn, $sql);
    $post = mysqli_fetch_assoc($result);
    // set form values on the form to be updated
    $title = $post['title'];
    $body = $post['body'];
    $published = $post['published'];
}

function updatePost($request_values)
{
    global $conn, $errors, $post_id, $title, $featured_image, $topic_id, $body, $published;

    $title = esc($request_values['title']);
    $body = esc($request_values['body']);
    $post_id = esc($request_values['post_id']);
    if (isset($request_values['topic_id']))
    {
        $topic_id = esc($request_values['topic_id']);
    }
    // create slug: if title is "The Storm Is Over", return "the-storm-is-over" as slug
    $post_slug = makeSlug($title);

    if (empty($title))
    {
        array_push($errors, "Post title is required");
    }
    if (empty($body))
    {
        array_push($errors, "Post body is required");
    }
    // if new featured image has been provided
    if (isset($_POST['featured_image']))
    {
        // Get image name
        $featured_image = $_FILES['featured_image']['name'];

        // somewhere around here I need an else statement to not overwrite the existing
        //file name stored in mysql. The global variable is defined as "" and that is
        //overriding the existing file. Also, if i try to upload a new file or the
        //original, it won't take either.


        // image file directory
        $target = "../static/images/" . basename($featured_image);
        if (!move_uploaded_file($_FILES['featured_image']['tmp_name'], $target))
        {
            array_push($errors, "Failed to upload image. Please check file settings for your server");
        }
    }

    // register topic if there are no errors in the form
    if (count($errors) == 0)
    {
        $query = "UPDATE posts SET title='$title', slug='$post_slug', views=0, image='$featured_image', body='$body', published=$published, updated_at=now() WHERE id=$post_id";
        // attach topic to post on post_topic table
        if (mysqli_query($conn, $query))
        { // if post created successfully
            if (isset($topic_id))
            {
                $inserted_post_id = mysqli_insert_id($conn);
                // create relationship between post and topic
                $sql = "INSERT INTO post_topic (post_id, topic_id) VALUES($inserted_post_id, $topic_id)";
                mysqli_query($conn, $sql);
                $_SESSION['message'] = "Post created successfully";
                header('location: posts.php');
                exit(0);
            }
        }
        $_SESSION['message'] = "Post updated successfully";
        header('location: posts.php');
        exit(0);
    }
}

score 0 · Answer 1 · answered Apr 08 '20 at 04:27

There are several issues with your code:

You are using the mysqli library, which leaves you vulnerable to SQL injection attacks. To help prevent this, you should use prepared statements and parameterized queries.
On the one hand, you're looking for $_POST['featured_image']:
```
if (isset($_POST['featured_image']))
```

And then a few lines later you're looking in $FILES['featured_image']:

    // Get image name
    $featured_image = $_FILES['featured_image']['name'];

In addition, this function seems to rely on a third array, a parameter named $request_values:

function updatePost($request_values)

This is really confusing, and possibly an error.

But on to your actual problem, which is that when the $featured_image variable you're creating is empty, your SQL statement updates the column to blank:

$query = "UPDATE posts SET title='$title', slug='$post_slug', views=0, image='$featured_image', body='$body', published=$published, updated_at=now() WHERE id=$post_id";

To only update the image column when $featured_image is not blank, you can conditionally include image='$featured_image' in your $query:

$query = "UPDATE posts SET title='$title', slug='$post_slug', views=0, " . ( strlen($featured_image) ? "image='$featured_image', " : '' ) . "body='$body', published=$published, updated_at=now() WHERE id=$post_id";

In any case, you really need to change your code to use something like the PDO library along with parameterized queries to prevent SQL injection attacks.

Image file name deleted unintentionally from MySQL after PHP CRUD blog update

1 Answers1