Laravel with postgresql, store and get binary data

Question

How to store and get data from postgres bytea field with Laravel ? I want to update binary data and download them. File types is jpg, excel, txt and so on. Can I do?

currently my code to store.

    public function store_db( $file, $file_name, $user_id ) {

    $file_path = $file->getRealPath();

    $new_attachment = Attachment::create([
        'name' => $file_name,
        'mime' => $file->getClientMimeType(),
        'size' => $file->getClientSize(),
        'uploaded_data' => pg_escape_bytea(file_get_contents($file_path)),
        'created_by' => $user_id,
        'updated_by'=> $user_id,
        'created_at' => Carbon::now(),
        'updated_at' => Carbon::now()
    ]);

    return $new_attachment->id;

next to get and download data (jpg, excel, and so on)

    public function get_attachment( $id ) {
    $file = $this->attachmentRepository->findWithoutFail($id);

    $dbh = DB::connection()->getPdo();

    $stmt = $dbh->prepare("SELECT name, mime, trim(trailing from encode(uploaded_data,'escape')) AS encode_data FROM attachments WHERE attachments.id = :atid");
    $stmt->bindParam(':atid', $id);
    $stmt->execute();
    $result = $stmt->fetch($dbh::FETCH_ASSOC);

    $name = $result['name'];
    $mime = $result['mime'];
    $headers = array(
        "Content-Type: {$mime}",
    );
    $fileData = $result['encode_data'];

    $ext = substr($name, strrpos($name, '.') + 1);
    file_put_contents($ext , pg_unescape_bytea($fileData));


    return response()->download($ext, $name, $headers);

You're doing too much manual work. Does eloquent not take care of the prepared statements for you ? — apokryfos
– apokryfos, Commented Apr 6, 2018 at 21:26
you are right. but how do i get bytea data from postgresql by laravel eloquent? — hanaka
– hanaka, Commented Apr 7, 2018 at 11:11
Seems that laravel doesn't support it out of the box but you could probably use a mutator like in e.g. laravel.com/docs/5.6/eloquent-mutators — apokryfos
– apokryfos, Commented Apr 7, 2018 at 11:37

trane77 · Accepted Answer · 2025-04-23 08:42:03Z

Part of your code was useful for myself with Laravel 7. Here my solution 100% functional for me. I'm still waiting that it will be of help you. Sorry for my english isn't my mother language.

$dbh   = DB::connection()->getPdo();
$query = "SELECT *, TRIM(TRAILING FROM encode(uploaded_data,'base64')) AS encode_data FROM esq_inversion.view_documento_proyecto WHERE id = :id";
$stmt  = $dbh->prepare($query);

$id = 123;
$stmt->bindParam(':id', $id);

$data = $stmt->execute();

// Convert to binary and send to the browser
$stream = base64_decode($data['encode_data']);

$callback = function () use ($stream) {
    $file = fopen('php://output', 'wb');
    fwrite($file, $stream);
    fclose($file);
};

$headers = [
    'Content-Type'        => $mime, // define the mime/type content of the binary data (ideally from another DB column)
    'Cache-Control'       => 'must-revalidate, post-check=0, pre-check=0',
    'Content-Disposition' => "attachment;filename*=UTF-8''$fileName;", // filename = same as mime/type
    'Expires'             => '0',
    'Pragma'              => 'public',
];

return response()->streamDownload(
    $callback,
    $fileName,
    $headers,
    'attachment'
);

Best regards, Migue

ILiyan Dimitrov · Accepted Answer · 2022-11-01 09:48:30Z

0

For me, all the files I was restoring from the database was corrupted. Then I found that the pg_escape_bytea() is returning everywhere instead of one apostrophe -> two. So I use str_replace when storing the binary data and that solve the problem:

File::create([
 'report_id' => $report->id,
 'name' => $file->getClientOriginalName(),
 'mime' => $file->getClientMimeType(),
 'size' => $file->getSize(),
 'data' =>  str_replace("''", "'", pg_escape_bytea(file_get_contents($file_path))),
 'created_at' => Carbon::now(),
 'updated_at' => Carbon::now()
]);

Then my method for downloading the files:

public function fileDownload(File $file)
    {
        file_put_contents($file->name , stream_get_contents($file->data));

        $headers = array(
            "Content-Type: {$file->mime}",
            "Content-Length: {$file->size}"
        );

        return response()->download($file->name, $file->name, $headers)->deleteFileAfterSend(true);
    }

answered Nov 1, 2022 at 9:48

ILiyan Dimitrov

611 silver badge7 bronze badges

1 Comment

trane77 Apr 3 at 22:03

You can safely avoid curly braces when interpolating variables/object variables with strings, like this: "Content-Type: $file->mime", (the variable itself must be safe, but that's another talk :-)

Collectives™ on Stack Overflow

Laravel with postgresql, store and get binary data

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related