storing binary data in mysql

Question

I have a PDF file on a local machine. I want to upload this file into a BINARY BLOB on a SQL database. Other approaches mentioned here [Binary Data in MySQL all use PHP. I want a simple clean way to upload this PDF file on the Linux command line. Unfortunately, I do not have access to the remote filesystem so cannot just store links to the file as mentioned elsewhere... I sort of need to use this MySQL database as a virtual filesystem for these PDF files..

From the PhP example, it seems all that is required is to escape the slashes before using the INSERT command? Is there a simple way to achieve that on a Linux command-line?

Answer 1

You could use the mysql function LOAD_FILE in conjunction with a small shellscript to do this I guess.

Untested code follows:

#!/bin/bash

if [ -z $1 ]
then 
    echo "usage: insert.sh <filename>"
else
    SQL="INSERT INTO file_table (blob_column, filename) VALUES(LOAD_FILE('$1'), '$1')"

    echo "$SQL" > /tmp/insert.sql
   cat /tmp/insert.sql | mysql -u user -p -h localhost db
fi

And you could use it like this:

<prompt>./insert.sh /full/path/to/file

Better implementation with error checking, proper tempfile creation, escaping and other niceties is left as an exercise to the reader. Note that use of LOAD_FILE() requires the FILE privilege in MySQL and a full path the file.

Answer 2

You could use the curl browser to submit the same POST that your GUI browser does. Sniff the request that your GUI browser sends, then replicate that with curl.