Just Another BLOB Discussion Posted on : 8/22/2008 04:44:00 PM

Ini arsip diskusi internal gw dengan temen2. Silakan menikmati.

Kalo menurut artikel yg tomfreakz refer-kan : http://forums.site5.com/showthread.php?t=14370

yg gw lihat malah "sisi positif" nya makae blob :

Q1:

I read that the BLOB type in MYSQL can store files up to approx. 2 gigs in file size. I need to store pictures that are approx. 5 mb, much smaller than the allowed size. The problem is, I noticed that the BLOB column says it's size limit is only 65,536 B!
If this is the case then how can I store any pictures or any files of any significance?

Can this file size be raised to something useful?
If not, then why even allow BLOB types?

Jawaban gw : -- BLOB emang cuman max 64KB. karna emang aslinya di-optimize utk nyimpen file2 kecil2 kya avatar, smilies, favicon, yaaa, web-widget lah.. :-)
klo butuh yg lebih dari 64KB, coba pakae MEDIUMBLOB. itu sampe 16MB. ato mo pake LONGBLOB ? sampe 4GB. cuman utk lebih dari 16MB ke atas, butuh tuning di my.ini nya MySQL, dimana default value dari max_allowed_packet=16M ~> mgkn MySQL mikir, itu udah cukup lah utk nge-fetching 1 record dengan tipe mediumblob pada kapasitas maksimal...

Q2:
Since this tiny tiny limit for BLOB types exists and if it can't be raised, I assume I'll have to go with plan B and store the file path for my pictures and store the actual picture files in a directory via PHP upon upload?

Regarding this, the questions:

Q3:
Isn't this creating a security risk by allowing the uploading of files into a directory?

Q4:
Can't hackers try to upload malicious files and then try to execute them?
This was one of the main reasons why I wanted to store the pictures in the database. Well, this and that I figure it'd require less coding.

Q5:
Can anyone give some pointers on how to go about doing this via PHP and to do so securely? protected login/data transfer,etc.

Q6:
Can site5's shared SSL cert be used for this kind of thing?

nah, tu orang nanyain problematic Q2, karna dia berasumsi tipe BLOB yg disedia-in ngga mendukung kebutuhan data yg dia perlukan (hehehe, ngga mbaca MySQL Manual sih...)
dan alibi yang dia berikan dgn Q3 dan Q4 sebenarnya malah memberikan POIN PLUS kenapa perlu penggunaan BLOB.

Disamping itu, lebih mudah manajemen file/pencarian/sorting klo pake BLOB, soalnya MySQL "sementara ini" merupakan satu-satunya DBMS engine yg bisa memperlakukan BLOB/binary data type selayaknya static-data-type. bisa di SORT, bisa di GROUP BY...

pernah kepikiran ngga :
SELECT id, image_thumbs FROM photo_images GROUP BY image; --> utk nampilin thumbnail2 (kalo ada beberapa) berdasarkan image induknya.

pretty cool, huh?

dan (ini pandangan subyektif gw) >> jauh lebih good penggunaan BLOB ketimbang absolute-path karena :

1. mudah di-dump/restore
2. lebih hemat space.. whazzup?? --> file JPG kan ngga bs di-kompress tuh. udah ukuran kecil. klo BLOB? dia bisa dicompress makae syntax :
"INSERT INTO photo_image (id, image) VALUES (" . $id . " , COMPRESS(" . mysqli_escape_string($imagedata) . ")"

dan ketika select, lakukan UNCOMPRESS()
"SELECT id, UNCOMPRESS(image) FROM photo_image WHERE id = " . $id

3. hanya perlu concern ke maintain DB, ngga usah lagi mikirin security di level file-system (yang tentu utk masing2 OS bisa beda)
4. sementara ini 3 dulu deh... --> yg punya warnet udah datang. gw harus pertanggungjawabkan manajemen bandwith yg kmaren gw pasang. xixixixi....