-1

I have created an data base in my sql and decided to add some picture but the problem is that the picture that should be loaded currently returns an empty string. So where the correct image should appear on the website is just an image icon

Here you can see what the current website looks like and where the image icons are.

CREATE TABLE IF NOT EXISTS permissions(
typ int NOT NULL,
dvd_hinzufügen BIT NOT NULL,
dvd_löschen BIT NOT NULL,
dvd_bearbeiten BIT NOT NULL,
dvd_ausgeben BIT NOT NULL,
kunden_anlegen BIT NOT NULL,
kunden_bearbeiten BIT NOT NULL,
FOREIGN KEY (typ) REFERENCES benutzer(typ)
);

I have filled this table with some data. Here you can only see some of the movies I added

INSERT INTO dvd (titel, genre, fsk, laenge, darsteller, regisseur, anzahl, bild) VALUES
('Pulp Fiction', 'Krimi', 16, 154, 'John Travolta', 'Quentin Tarantino', 5, 
LOAD_FILE('/Bilder/pulp_fiction.jpg')),
('The Shawshank Redemption', 'Drama', 12, 142, 'Tim Robbins', 'Frank Darabont', 5, 
LOAD_FILE('/Bilder/the_shawshank_redemption.jpg')),
('The Godfather', 'Krimi', 16, 175, 'Marlon Brando', 'Francis Ford Coppola', 5, 
LOAD_FILE('/Bilder/the_godfather.jpg')),
('Fight Club', 'Drama', 18, 139, 'Brad Pitt', 'David Fincher', 5, 
LOAD_FILE('/Bilder/fight_club.webp')),
('Forrest Gump', 'Drama', 6, 142, 'Tom Hanks', 'Robert Zemeckis', 5, 
LOAD_FILE('/Bilder/forrest_gump.jpg')),
('Inception', 'Sci-Fi', 12, 148, 'Leonardo DiCaprio', 'Christopher Nolan', 5, 
LOAD_FILE('/Bilder/inception.png'));

The path to the images should be correct. My databank.sql file is in the same ordner as the ordner bilder which contains the images.

Then I requested the images in my php part.

if (!isset($_GET['search']) || ($_GET['search'] == '')) {
    //abfrage aller Filme aus Datenbank
    $sql = "SELECT titel, bild FROM dvd ORDER BY $selectedCategory $selectedSort" ;
    $res = $con->query($sql);
    if ($res->num_rows > 0){
        while($i = $res->fetch_assoc()){
            $bild = base64_encode($i['bild']);
            echo "<p>" .$i["titel"]."</p>";
            echo '<img src="data:image/jpeg;base64,'.$bild.'" />';
        }
    }
}
else if (isset($_GET['search'])) {
    //abfrage Filme aus Datenbank
    $searchTerm = $con->real_escape_string($_GET['search']);
    $sql = "SELECT titel, bild FROM dvd WHERE titel LIKE '%$searchTerm%' or genre LIKE '%$searchTerm%' or fsk LIKE '%$searchTerm%' or darsteller LIKE '%$searchTerm%' or regisseur LIKE '%$searchTerm%' ORDER BY $selectedCategory $selectedSort" ;
    $res = $con->query($sql);
    if ($res->num_rows > 0){
        while($i = $res->fetch_assoc()){
            $bild = base64_encode($i['bild']);
            echo "<p>" .$i["titel"]."</p>";
            echo '<img src="data:image/jpeg;base64,'.$bild.'" />';
        }
    }
}

I tried with echo "<p>Length: " . strlen($bild) . "</p>"; to see if the length of the varaible $bild is 0, which actully is. I also right clicked the image icons and opend them in the editor where I could see <p>A Beautiful Mind</p><img src="data:image/jpeg;base64," /><p>Eternal Sunshine of the Spotless Mind</p><img src="data:image/jpeg;base64," /><p>Gladiator</p><img src="data:image/jpeg;base64," /><p>Jurassic Park</p><img src="data:image/jpeg;base64," /><p>Toy Story</p><img src="data:image/jpeg;base64," /> </div>. In conclusion the problem should be that the images arent loaded properly with the load file function which makes no sense. I hope I could provide enough sample. Otherwise comment this and I will provide more.

Barmar
  • 741,623
  • 53
  • 500
  • 612
Taminator
  • 7
  • 1
  • I think [LOAD_FILE()](https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_load-file) must receive an absolute path, not a relative path. MySQL knows nothing about your web root. – KIKO Software May 18 '23 at 22:55
  • 1
    Please, do not put file content into database. It's enough to store file link – lezhni May 18 '23 at 22:55
  • Image should be stored in a 'BLOB'. Images must be base64 encoded before storing them in the DB. Images must be base64 de-coded to show them in a web page. – CharlesEF May 18 '23 at 23:07
  • 1
    **Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/32391315) – Dharman May 18 '23 at 23:11
  • _"Image should be stored in a 'BLOB'. Images must be base64 encoded before storing them"_... @CharlesEF these two statements are at odds. There is no need to base64 encode anything for storage – Phil May 18 '23 at 23:23
  • @Phil, I've never stored images in a database, but that's how I remember the process based on many other posts I've read. – CharlesEF May 19 '23 at 08:53

1 Answers1

0

The problem was in the sql file where I insert the picture with LOAD_FILE('/Bilder/inception.png'). The only problem was, that the path was not correct it is not enough to give the path from your projekt to the images. The correct answer would be LOAD_FILE('/xampp/htdocs/Bilder/inception.png').

Taminator
  • 7
  • 1