talk about "Common Issues with MySQL and PHP"

I believe that this tutorial should be linked in the "read first" section

Just did that btw ;)

Also has anyone tested the option of saving images in the DB as BLOB and the performance of that

Performance is acceptable in most cases, but you cannot take advantage of the caching mechanism you have with files.

Hi jkon - very pleased you took the time to read the tutorial and as I mentioned in it - any questions or criticisms are extremely welcome. I'll try to address you questions one at a time. We may have a difference of opinion on some things, but it may be that I didn't explain myself clearly:

I find storing filenames much easier in general than using BLOB fields, but this has its dangers.". Are you referring to a repository system where the file doesn't lies under the public_html ? What are those dangers ? . Also has anyone tested the option of saving images in the DB as BLOB and the performance of that ? I have long time ago and the results were not to do so , has this changed ? As more people tend to use DB as file storage and they don't quite get what a repository is about I would suggest to anyone following such an option to make a lot of tests before decide to go that way.

I was making the point about uploading a file to a physical directory and then writing the details to the DB. With a BLOB system, the whole thing is done within the transaction - all the data held together nicely. A simple 'physical file' system however has two components - the upload and storage and then the inserting the location (or just filename) to the DB. I've had cases where the file has been uploaded but the DB write failed - happened a lot on a phpBB board I helped to administer.

I do round up the section by suggesting that benchmark tests should be run. My undertsanding is that BLOB fields really slow things down, however, I've also read reports to the contrary, or at least saying that the problem isn't as bad as once thought.

Note that the "simple upload method" was meant to be to a public_html directory, as that's what most members have asked for in the past. A repository above the public root would obviously require a little more jiggery pokery to display within a simple <img> tag.

My second question has to do with the "Adjacency List " that you refer in 8 , the problem there is that the parent uri exists in the child uri. What happens when you change the parent uri ? As you mention that is based in the self referenced or "snake" data model. If each entry has its own uri components without its parents would be possible for PHP to make sense out of these without big effort? . The answer is yes if you use the indexed list model and app scope caching but this is something that is not the scope of this question. So in 8 the main question is if the admin changes the uri / URL of a parent category what happens to tree down ?

I'm not sure that the parent uri has to exist in the child uri - but it is certainly a model I've seen and I do agree that it does make considerable sense, especially in the happy world of Apache mod_rewrite. I'll certainly take another look at this section jkon - if you have any pointers, I'd be happy to follow them up.

In 11 take a look at CRYPT_BLOWFISH also would be a good idea never to transmit a password unhashed. E.g. you can md5 client side a password before getting it and crypt it.

I agree, using PASSWORD_BCRYPT constant (currently CRYPT_BLOWFISH), would have been better. With regard to transmitting unhashed passwords - yes this is a good point. Heh heh, security is so sticky isn't it? I can think of more things to implement such as an SSL certificate, second challenge hashing, at which point the beginner gets a nosebleed. As you're aware MD5 is a little vulnerable these days, and it won't stop an active Man in the Middle. But better than nothing. I'll have a little think about this too. :)

Ok, hopefully I'll have some ammunition to improve the tutorial further and to move it from a my personal tutorial into a community one. I think this thread is a really good idea jkon - thanks very much. Very useful to be able to discuss the technical issues without having to involve (or scare!) total beginners perusing the tute.

Not sure if I answered everything to your satisfaction jkon - get back to me if not.

Hello,

In both cases (repository and the indexed list model) I am thinking to write a tutorial long time now. The problem is that I should write those without the use of any framework , in order to be understood without any other knowledge and be easy implemented.

In the repository implementation I am talking about you have a main repository table with auto increment id of the file its extentsion , its folder (under the repository physical folder that is above public_html) and you can also have several other infos about the file (e.g. the md5 of its blob content , its size or the dimensions if it is an image , those info can be in linked tables with the repository id based in the type / extension of the file as well).

When you upload a file first you make all the validations needed (or even resize it if it is an image) and save in the folder under the repository of its section (e.g. if it is a product image you save it under the Product folder), with a temp name. Then you add a row to the main repository table knowing all the other details of the file (or add also each info to the other linked tables) . You take the id of the added row and you just rename the uploaded file with it + the extension. That way you will not have a problem of inconsistence , if the DB fails (I can't see how but lets suppose it will) the file will be retained with the temp name and will replaced with the next uploaded file, so it will be like it never uploaded.

Each file we upload in order to be exposed to the public must have a purpose. E.g. an image of the product with unique URL segment e.g. wirelesses-pc-microphone-500w so we could have a table with the productId , the repositoryId , status (active inactive) or even a type field (e.g. 1 for catalogue image) lets call it shop_product_images for now. In the simplest implementation you have an Image controller in such way that when you have the URL {root}/Image/Product/wirelesses-pc-microphone-500w_324.jpg it will keep the last part (different frameworks do it in different way so I will not get into this right now.

Spiting e.g. by _ you have in the first part the product segment URL and in the second part the repository id and the extension. You get the productId using the URL segment from the products table and you just check using that this productId has that repositoryId as you want (e.g. with status active) in the table shop_product_images and that this repositoryId has this folder and that extension in the repository table (the Product folder in our example). This is a single join that if you use correct indexes is really fast.

After that you just use the readfile that PHP that is almost as fast as reeding directly from the file location.

You can do many more with that , e.g. add Etag to the header , Content-type , Cache-Control or even decide if you will just serve HTTP/1.1 304 Not Modified based on HTTP_IF_MODIFIED_SINCE or / and HTTP_IF_NONE_MATCH).

This is a simple implementation of the repository way I described , and sure I should make a tutorial to make it clearer and with an example.

Sounds good jkon, perhaps aimed more at the Intermediate user? :) Would like to see it.

@jkon

Looked into the PASSWORD_BCRYPT vs PASSWORD_DEFAULT issue. From what I can see it's the same (at the moment) - both are producing the $2y$cost... hash.

DEFAULT may actually have an advantage if at some future time BCRYPT is found to be compromised, PHP could use a new more secure DEFAULT so the dev wouldn't have to change the source code. S/he may have to change the DB field length if not set to something big enough (255?) though.

What do you think?

This is an issue that have troubled also me in the past. I am pro to choose the way you crypt the password yourself and not let PHP decide by default , but if PHP decide one day to abandon blowfish as default in a new version , this will be a major issue that you couldn't miss before upgrading. So using the password_hash with PASSWORD_BCRYPT or crypt with proper salt and cost does the work. I understand why PHP has PASSWORD_DEFAULT since it tries to avoid low quality hashing algorithms so the choice to go with blowfish is almost irreversible , or at least if it changes we will hear about it.

OK, I think you've convinced me. Thanks - nice to have some clear thinking :)