Insert Multiple Image Paths Into Multiple Columns

Question

On an Intranet site, I am moving quite a few images from the file system into MySQL and have written a little function to assist. There are no errors but neither are the images being inserted so I need to help to figure out how to do it without LOAD_FILE() as it requires folder and file permissions changes that I cannot do.

Note that it is necessary to query the albums table in order that the Primary Key from the albums table can be inserted with the image so that the programming that displays the album will know which image to load.

function uploadFolder($path,$thumbpath) {
    // Use form to submit path (keeps process from running automatically when loaded)
    if (isset($_POST['create']) && isset($path)) :
        global $siteDB;

        // Fetch image names from existing albums
        $sqlAlbum = "SELECT ID, ImageName FROM albums ORDER BY ImageName";
        $sqlRow = DBConnect($sqlAlbum, "Multiple", $siteDB, "assoc");

        // Build queries to insert all images to table
        foreach ($sqlRow as $row) :
            $filename = basename($row['ImageName']);
            $albumID = $row['ID'];
            $pathname = $path.$filename; 
            $sqlUpload = "INSERT INTO images (`ImageName`,`AlbumID`,`ImageFull`) VALUES ('$filename', $albumID, LOAD_FILE('$pathname'))";
            // Run the query for each file
            DBConnect($sqlUpload,"Run", $siteDB);
        endforeach;

        // FOR TEST PURPOSES ONLY
        $_SESSION['testSQL'] = $sqlUpload;
    endif;
}

it is being called with:

if (isset($_POST)) :
    $path = (isset($_POST['path'])) ? $_POST['path'] : "";
    uploadFolder($path,"thumbnails");
endif;

Not much to the images table but here it is for reference:

CREATE TABLE IF NOT EXISTS `images` (
  `ID` int unsigned NOT NULL AUTO_INCREMENT,
  `ImageName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `AlbumID` int unsigned DEFAULT NULL,
  `ImageFull` blob,
  `ImageThumb` blob,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=228 DEFAULT CHARSET=utf8;

LOAD_FILE() problems aside, there are two ways I could have done this. One is to read all the files and insert them IF they exist in the albums table by querying it. The other as I showed above is to get a list of files from the albums table by querying it, then find and insert those images from the file system. Doing the latter (in theory) prevents it from inserting stray photos that are not supposed to be in the album.

Although I didn't show it here because I realized it after posting the question, doing the latter also lets me grab the Description column from the albums table so that I could tell which image was which.

See about sql injection and the importance of prepared and bound queries - i appreciate that's it's an intranet site, but the fact remains that code gets copied and pasted from place to place, and there's really no excuse for writing insecure code — Strawberry, May 03 '20 at 02:14
Are there rows in the images table with the non thumbnail versions of the images already or is this supposed to load just the thumbnail? — gview, May 03 '20 at 02:49

gview · Answer 1 · 2020-05-04T18:28:20.697

It's hard to tell for sure, because you are using some sort of database wrapper with some odd parameter conventions. Typically you would not be using LOAD_FILE but rather, reading the file with php into a variable and using the mysql client api to save each row.

LOAD_FILE can work, but it must be the exact path to the file AND the effective webserver user (the user the webserver is running as) must have permissions to the directories you are trying to read from.

Assuming that is true, something like this should work:

    foreach ($sqlRow as $row) :
        $filename = basename($row['ImageName']);
        $albumID = $row['ID'];
        $pathname = $path.$filename;
        $thumbPathname = $path . $thumbpath . DIRECTORY_SEPARATOR . $filename
        $sqlUpload = "INSERT INTO images (`ImageName`,`AlbumID`,`ImageFull`,`ImageThumb`) VALUES ('$filename', $albumID, LOAD_FILE('$pathname'), LOAD_FILE('$thumbPathname'));\n";
        // Run the query for each file
        DBConnect($sqlUpload,"Run", $siteDB);
    endforeach;

This code assumes that thumbNail directory is a child of the path directory. The path directory needs to be entered with a trailing slash.

Potential issues:

The SQL statement has a semi colon and a newline. I have never seen a database client call that would include those. The MySQL client library doesn't use those, so I am not sure why they would be part of the DB wrapper.
There is no error checking code here. If something goes wrong in the DBConnect() function, how will you know?
The path variable must be complete and must have an end separator.

ok

/somepath/to/directory/

won't work

/somepath/to/directory

I adjusted the code given your comments. The error checking needs to come from the database wrapper code. If there are errors coming from MySQL you need to know what those are, or you won't be able to know for sure if that is the problem. — gview, May 04 '20 at 18:32
There are many possible reasons for this. Go through the answers in [this question](https://stackoverflow.com/questions/18069054/mysql-load-file-loads-null-values) — gview, May 05 '20 at 23:10
That feature works fine. It has security controls around it so that people can't use it as an exploit. So long as you understand permissions and mysql configuration it works fine. You never even established what OS you are working with, btw. — gview, May 07 '20 at 03:12

Insert Multiple Image Paths Into Multiple Columns

1 Answers1