How To add media Files in a mysql database via PDO class?

[Genexys]

I have a website streaming project going on,but there's one thing that stops me from really starting it "HOW TO ADD FILE PATH INTO A MYSQL DB?"  since I know it will slow down the sgbd to directly add the file in a longblob field,I need some help on how to retrieve those file path(manually?) 

Let's say a user enters 'die hard 4' in the search form,Do I concatenate the user's input to a query like

 $query = 'SELECT * FROM T_DIRECTORIES WHERE DIRECTORIES LIKE 'user_input%'; 

Then do  'scandir($query); 

Since I will name my directories as the files it contains

[Genexys]

Forget that I get it now, I wanted to delete the topic but since someone else can face this issue one day I'll explain what to do:

You just store your files' path one by one in the the db

Ex: Lion.jpeg

INSERT INTO table_name (column_name)

VALUES ('/dir1/dir2/dir3/Lions.jpeg');

 

Then let's say a user make a search on your website to find this particular item

You just concatenate the user's entry to a select query

$query = $PDO->query('SELECT * FROM <table_name> WHERE 'File_path' LIKE '%userInput%');

 

Then there you go you can use this path ($query) to do whatever you want with

 

 

 

Edited September 1, 2022 by Genexys

[kicken]

12 hours ago, Genexys said:

You just concatenate the user's entry to a select query

You should not be putting user's input directly into your query.  Doing so opens you up to SQL Injection attacks.  Use parameter binding to create a safe query with the user's input.

[gizmola]

Another thing you have not thought through is what happens when you have 2 files with the same name.  What you have is a naive strategy, that as, kicken pointed out, can open you up to a variety of exploits.

This is what most people do:

• In your "media" table
  • have a column for the path to the stored file
    • Generate an internal name for the file.
      • MD5, SHA1 etc are good places to start
        • Provide a variety of input to the hash
          • example:  $internalName = sha1($orignalName . uniqid('something', true) . date(DATE_RFC2822) . $userid);
          • Make this the name of the file you store + mimetype extension
  • have a column for the original file name
    • Run hygiene functions on this name, that does things like:
      • remove any non-characters
      • remove any path characters
      • remove html 
      • remove spaces and replace with underscores or dashes
      • Exploiters will try things like names that included multiple extensions, and can in some cases fool your webserver into running code
        • examples:  foo.jpg.php,  foo.bar.cgi.jpeg
        • remove any periods other than the one at the end of the file name (.ext) and either replace those with underscore or dash, or remove.
  • have a column for the mime type of the media
    • Run function(s) on the file that determine if the mime type of the file matches what you will allow
    • Check that the extension of the filename matches the mimetype

When it comes time to display a link to the document, you will use your internal hash id to find the document.  Depending on what you are doing with the media you can use html markup like the alt attribute to display the "original" name, and/or for download purposes, you can specify that in the content-disposition header like so:

Content-Disposition: attachment; filename="filename.jpg"

So your download script might have something like this in it:

<?php
$fileId = $_GET['fid'];

// Db code looks up file in media table, returns $media row

header('Content-type: ' . $media['mimetype']);
header('Content-Disposition: attachment; filename="'. $media['original_name'] . '"');

// open file and return data