Storing Image in Database Directly or as Base64 Data

Storing image in database directly or as base64 data?

• Pro base64: the encoded representation you handle is a pretty safe string. It contains neither control chars nor quotes. The latter point helps against SQL injection attempts. I wouldn't expect any problem to just add the value to a "hand coded" SQL query string.

• Pro BLOB: the database manager software knows what type of data it has to expect. It can optimize for that. If you'd store base64 in a TEXT field it might try to build some index or other data structure for it, which would be really nice and useful for "real" text data but pointless and a waste of time and space for image data. And it is the smaller, as in number of bytes, representation.

Storing profile images as file urls or Base64?

Base64 images can be statically used in limited purpose by converting image to Base64. The generated code of Base64 is large.

1. If the images are limited then you can store to file and assess them
   
   
   
   
   2. But images are dynamic then you can use image name. And store to the database. And access them.
   3. In case of Base64 the size is large of the code but cannot store to the database.

Base64 encoding increases the storage requirement by 33% over a raw binary format. It also increases the amount of data that must be read from persistent storage, which is still generally the largest bottleneck in computing. It's generally faster to read less bytes and encode them on the fly. However, if your system is CPU bound instead of IO bound, then consider storing in base64. Also, inline images are a bottleneck themselves--you're sending 33% more data over the wire, and doing it serially. Whatever you do, make sure you don't store base64 encoded data as UTF8

Is it a good practice to save a base64 string on the Database?

To answer your question, I think it's not good practice.

It depends largely upon how you will use these images you will probably load. Then, remember (base64 encoding makes file sizes roughly 33% larger than their original binary representations).
The best way would be writing the image and then saving its location in the database.

Check out this post as it maybe help.

Storing Base64 PNG in MySQL

There's no need to base64 encode the image. MySQL's perfectly capable of storing binary data. Just make sure you use a 'blob' field type, and not 'text'. text fields are subject to character set translation, which could trash your .png data. blob fields are not translated.

As well, base64 encoding increases the size of text by around 35%, so you'd be wasting a large chunk of space for no benefit whatsoever.

However, it's generally a bad idea to store images in the database. You do have the advantage of the image being "right there" always, but makes for absolutely huge dumps at backup time and all kinds of fun trying to get the image out and displayed in your app/web page.

it's invariably better to store it externally in a file named after the record's primary key for ease of access/verfication.

What is the correct way of displaying base64 image from database to php page?

I would store the image directly encoded in base64 an also its extension for proper display.

IMPORTANT
The content field has to be TEXT type in your database, otherwise you won't store the data properly and you won't be able to get it as you should

<?php

$dsn = 'mysql:host=localhost;dbname=website;charset=utf8mb4';
$pdo = new PDO($dsn, 'root', '');

if (isset($_POST['upload'])) {
    // Get the image and convert into string
    $img = file_get_contents($_FILES['image']['tmp_name']);

    // Encode the image string data into base64
    $content = base64_encode($img);
    // Get the extension
    $extension = strtolower(end(explode('.', $_FILES['image']['name'])));
    if (!empty($content)) {
        $sql = " INSERT INTO images(content, extension) VALUES (:content, :extension) ";
        $pdo->prepare($sql)->execute(array('content' => $content, 'extension' => $extension));
    }
}

// Later in your code
?>
<div class="images" id="Images"></div>
    <?php
    $sql = " SELECT * FROM images ";
    $result = $pdo->query($sql);
    if ($result->rowCount() > 0) {
        while ($row = $result->fetch()) {
            echo "<img src='data:image/{$row['extension']};charset=utf-8;base64,{$row['content']}' alt='Binary Image'/>";
        }
    }
    ?>
</div>

insert base64 image to a mysql table in php

In general you don't want to store images in a relational database.

But as you want.

here's the code to store a blob using MySQLi(mysqli manual):

first: Create a table to store your images.

CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `content` blob NOT NULL
)

then:

<?php
$host = 'localhost';
$user = 'root';
$pass = 'root';
$db_name = 'test';

$base64_data = $_POST['base64_data'];

$link = mysqli_connect($host, $user, $pass, $db_name);

$stmt = mysqli_prepare($link, "INSERT INTO test (id, content) VALUES (?, ?)");

$stmt->bind_param('ib', $id, $content);

$id = 1;

$content = null;

//$base64_data = base64_decode(base64_encode(file_get_contents('3413983627135443.jpg')));
//$stmt->send_long_data(1, $base64_data);
$stmt->send_long_data(1, base64_decode($base64_data));

mysqli_stmt_execute($stmt);
printf("%d Row inserted.\n", mysqli_stmt_affected_rows($stmt));
mysqli_stmt_close($stmt);

Do not use the MySQL (Original)Extensions : intro.mysql

---

Related Topics