Image upload to mysql

Question

My site is able to upload a file but I don't understand how to get the path to the file for the database query. When someone uploads an image, the path to the image should get directly inserted into the users table in the userpic field. How can I achieve this?

<?PHP
if(isset($_FILES['file'])) {
    move_uploaded_file($_FILES['file']['tmp_name'],'files/'.$_FILES['file']['name']);
    
    session_start();
    $username = $_SESSION['user'];
    $userpic = ???? // <-- what am i supposed to call here to put the path to my image file 
    
    include ("connect.php");
    $sql = $con->prepare('INSERT INTO users (username,userpic) VALUES (?,?)');
    $sql->bind_param("ss",$username,$userpic);
    $sql->execute(); 
    $sql->close();
    $con->close(); 
} else {
    echo "no files";
}
?>

Answer 1

If you store files using the name provided by the client when the file is uploaded, you will potentially overwrite images (e.g. if two users upload me.png) - it would be much better to use the username to store the images, and then you don't even need the mysql table to connect users to their pics..

<?php
session_start();
$username = $_SESSION['user'];
if(empty($username)){
   echo "Error: no username found";
}
else if(isset($_FILES['file']) ){
   //create a path to move file to  
   $newpath = 'files/'.$username;
    
   if (move_uploaded_file($_FILES['file']['tmp_name'], $newpath)) {
    echo "File is valid, and was successfully uploaded.\n";
   } else {
    echo "Error: Possible file upload attack!\n";
   }
}
else{
  echo "No Files to save";
}

In this code we use the username from the session, and check its not blank. We then use this to store the image in your files folder.

Note this ignores a number of security issues:

Including ../ in your username which would cause the file to be saved outside of the files directory. This may not be an issue if you have already validated the username, another solution would be to create a hash of the username and using this instead: $newpath = 'files/'.md5($username);

Not checking for errors, or verifying the file is indeed an image.

http://php.net/manual/en/features.file-upload.errors.php

PHP image upload security check list

How are these images going to be used after this? If the files directory is within your htdocs, the contents will be available for all - it would probably be better to store it outside of your htdocs e.g. $newpath = '/var/myappdata/userimages/'.md5($username);

You could then create another file userimage.php which reads the file:

<?php
session_start();
$username = $_SESSION['user'];
$path = '/var/myappdata/userimages/'.md5($username);
readfile($path);

This allows you to do additional checks e.g. that the user is allowed to see the image.

There is still a huge amount that could be covered here, hopefully this gives you enough to move forward, but do please read more about file upload security before putting this into production.

Your original question

If you did want to store information about the image in your database you could do something like this:

<?php
session_start();
include ("connect.php");

$username = $_SESSION['user'];
if(empty($username)){
   echo "Error: no username found";
}
else if(isset($_FILES['file']) ){
    //create a path to move file to  
    $filename = basename($_FILES['file']['name']);

    $newpath = 'files/'.$filename;
    if (move_uploaded_file($_FILES['file']['tmp_name'], $newpath)) {
        echo "File is valid, and was successfully uploaded.\n";
        $sql = $con->prepare('INSERT INTO users (username,userpic) VALUES (?,?)');
        $sql->bind_param("ss",$username,$filename);
        $sql->execute(); 
        $sql->close();
        $con->close();          
    } else {
        echo "Error: Possible file upload attack!\n";
    }
}
else{
    echo "No Files to save";
}

As I said though - you will run into conflicts if two users upload the same file.

Answer 2

You aren't going to want to store the entire URL in the database. You just need the path to where it is on the server. That is the part where you are moving the tmp file to a new location. In your case it would be the following.

$userpic = 'files/'.$_FILES['file']['name'];