Upload image directly through mySQL Command Line

Question

I have a certain table in mySQL which has a field called "image" with a datatype of "BLOB". I was wondering if it is possible to upload an image in that field directly from the Command Line Client rather than doing it through php...If it is possible, then where exactly should I place my image files?

Bremser

Answer 1

Try using the LOAD_FILE() function.

UPDATE `certain_table`
SET image = LOAD_FILE('/full/path/to/new/image.jpg')
WHERE id = 1234;

See the manual for requirements about the path to the filename, privileges, etc.

Dessiedessma

Answer 2

LOAD_FILE works only with certain privileges and if the file is on the server. I've found out a way to make it work completely client side:

mysql -e "update mytable set image=FROM_BASE64('`base64 -i image.png`')" DBNAME

The idea is to encode the image to base64 on the fly and let then MySql decode it.

Rood

Answer 3

This is a variation on Teudimundo's answer that works with older MySQL versions, where Base64 functions are not available:

mysql -e "update mytable set col = x'$(xxd -p image.png | tr -d \\n)' where ..."

The trick is to use xxd -p to convert a binary file to a plain hexdump:

$ xxd -p /usr/share/font-manager/data/blank.png
89504e470d0a1a0a0000000d4948445200000040000000400806000000aa
6971de000000274944415478daedc1010d000000c220fba77e0e37600000
00000000000000000000000000c0bd0040400001ee1dbb2f000000004945
4e44ae426082

then using tr -d \\n to remove the newlines, and finally embedding the result into a MySQL-specific hexdump string literal: x'...'

Pennyworth

Answer 4

I recommend you to never upload images directly in a database, it's quite inefficient. It's better to simply store the location and name of the image and store those images in a folder somewhere.

and if you want to "upload" via the commandline, you can just do an:

insert into table(image_loc) values('/images/random/cool.jpg') where id=1;

and depending on your environment you can use shell access to move images around. I'm not quite sure what you are trying to do with these images or how your system is setup. You'll probably need to clarify on that.

Inaccurate

I have read a lot of online discussions regarding this issue...and I do support your views against it, but quite frankly I did not find enough documentation on how this process works....so I was kind of reluctant to approach it. If you have any links in mind, it would be great if you can post them here! :) – Bremser

@Bremser what language do you use with mysql? It's as simple as executing a sql command to get the location string from the database and opening this file with your language. Personally I think using load_file is much more troubling. – Inaccurate

Yes, load_file() didnot work in my case so I am now planning on retrieving images from the system. I will be creating a web service in C# which will retrieve the location of the file from the mysql database...This is just the backend though....the front end is entirely in HTML5 + CSS3 + JavaScript. So I guess i will have to look for resources which will help me load an image from a given address in JavaScript maybe? Thanks for your help anyways... – Bremser

Answer 5

It is more preferable to build a sample application and then insert the values in the database. For instance this method could be used to enter a BLOB datatype into the database...

[WebMethod]
                public string sendDataToMySql(string get_name, byte[] buffer)
                {
                    string MyConString = "SERVER=localhost;" +
                          "DATABASE=test;" +
                          "UID=root;" +
                          "PASSWORD=admin;";


                    MySqlConnection connection = new MySqlConnection(MyConString);
                    connection.Open();
                    MySqlCommand command = new MySqlCommand("", connection);
                    command.CommandText = "insert into testImage(name, image) values(@name, @image);";


                    MySqlParameter oParam1 = command.Parameters.Add("@name", MySqlDbType.VarChar, 50);
                    oParam1.Value = get_name;


                    MySqlParameter oParam2 = command.Parameters.Add("@image", MySqlDbType.Blob);
                    oParam2.Value = buffer;

                    command.ExecuteNonQuery();

                    connection.Close();
                    return "Data was inserted successfully!";
                   }

Bremser

Answer 6

Sometimes we try to upload file using loadfile but file is not loaded or file path in formatted text is stored in BLOB field. This is because of access issues. If you are facing such condition, instead of loading file from any location, try to load it from data path of mysql preferably like :

INSERT INTO `srms`.`images` (`ID`, `Image`) VALUES ('5', load_file('C:\ProgramData\MySQL\MySQL Server 5.7\Uploads\test.jpg'));

Lateral