Load_file to blob in MySql

apps

#1

All,

I’m a dreamhost user who’s is really stumped. I have code that tries to load a PDF file into a blob field in a MySQL table. That code uses the LOAD_FILE function, which requires the FILE privilege, which dreamhost can’t grant due to security issues. I can’t believe I’m the first person to run into this kind of issue…surely there’s a way to populate blob fields on dreamhost, right? Can anyone enlighten me on how you were able to do it?

Many thanks,

  • David

#2

As you appear to already know, your MySQL accounts are never going to get file privilege access on a shared MySQL server due to security risks.

[quote]From: http://dev.mysql.com/doc/refman/5.0/en/privileges-provided.html
A user who has the FILE privilege can read any file on the server host that is either world-readable or readable by the MySQL server. (This implies the user can read any file in any database directory, because the server can access any of those files.) The FILE privilege also enables the user to create new files in any directory where the MySQL server has write access.
[/quote]
I’ve done projects where we decided to save files directly in the database before. This was before fiber arrays were commonly available and we had been saving files directly on the webserver. Not really a big push for database storage until we got a little bigger, but this is an awesome technique for scalability. At that time our only other options were to redundantly store the files across servers, or keep a user sticky to one webserver forever.

In our case we were restricted in our use of BLOBs due to a compatibility issue between Cold Fusion and the 3rd party Oracle native drivers that were available at the time. We ended up solving the problem by converting the files to BASE64 encoded text strings and storing them in CLOB fields. It worked perfectly fine and is the same method I still use today.

If you have worked with Oracle DBAs you already know that they are a different breed. Rather than question their wisdom, I take it on faith when they tell me that they would much rather I use CLOBs than BLOBs. Something related to their ability to maximize db optimization I believe.

Even though I wrote this code to connect ColdFusion and Oracle, all the functionality can be ported to PHP and MySQL with just a little effort. I’ve already verified that PHP has BASE64 encode/decode functions. I believe that this will go a pretty long way towards getting you started. Post back if you want some more input or anything. Good luck and have fun.

http://us2.php.net/manual/en/function.base64-encode.php
http://us2.php.net/manual/en/function.base64-decode.php

ENCODE FILE AS BASE64 CHAR DATA AND SAVE INTO CLOB FIELD

[code]

SELECT
FISCAL_YEAR
, FISCAL_MONTH
, AFFILIATE_CD
, BSC_FILE_NUM
, ORIGINAL_FILE_NAME
FROM GLOBAL_APPS.ODS_BSC_FILES


<cfset fname = tmpdir & getFiles.FISCAL_YEAR & “" & getFiles.FISCAL_MONTH & "” & getFiles.AFFILIATE_CD & “" & getFiles.BSC_FILE_NUM & "” & getFiles.original_file_name>
#fname#




UPDATE GLOBAL_APPS.ODS_BSC_FILES
SET file_content =
WHERE
FISCAL_YEAR = #getFiles.FISCAL_YEAR#
AND FISCAL_MONTH = #getFiles.FISCAL_MONTH#
AND AFFILIATE_CD = '#getFiles.AFFILIATE_CD#'
AND BSC_FILE_NUM = #getFiles.BSC_FILE_NUM#

[/code] GET BASE64 ENCODED DATA FROM DATABASE, CONVERT TO BINARY, OUTPUT FILE [code] SELECT FISCAL_YEAR , FISCAL_MONTH , AFFILIATE_CD , BSC_FILE_NUM FROM GLOBAL_APPS.ODS_BSC_FILES SELECT original_file_name, file_content FROM GLOBAL_APPS.ODS_BSC_FILES WHERE FISCAL_YEAR = #getFiles.FISCAL_YEAR# AND FISCAL_MONTH = #getFiles.FISCAL_MONTH# AND AFFILIATE_CD = '#getFiles.AFFILIATE_CD#' AND BSC_FILE_NUM = #getFiles.BSC_FILE_NUM# #fname# - SUCCEEDED
#fname# - FAILED
[/code]

#3

Thanks for the reply. I grok the security issues around file access, I’m suppose I’m just still surprised there’s not a workaround. The uuencoding option makes sense, in fact, the original pdf that I’m trying to store gets posted to my app as uuencoded text. It’s a ruby app and ruby’s library for uuencoding/decoding is simple and easy to use, so I’m good to go there.

Thanks again for the input.

  • David