What is the best place for storing uploaded images SQL database or disk file system

Question

I m writing an application that allows users to upload images onto the server  I expect about 20 images per day all jpeg and probably not edited resized   This is another question  how to resize the images on the server side before storing  Maybe someone can please drop a  NET resource for that in the comment or so    I wonder now what the best place for storing uploaded images is    Store the images as a file in the file system and create a record in a table with the exact path to that image  Or  store the image itself in a table using an  image  or  binary data  data type of the database server    I see advantages and disadvantages in both   I like a  because I can easily relocate the files and just have to change the table entry  On the other hand I don t like storing business data on the web server and I don t really want to connect the web server to any other datasource that holds business data  for security reasons   I like b  because all the information is in one place and easily accessible by a query  On the other hand the database will get very big very soon  Outsourcing that data could be more difficult

User · Answer

We have had clients insist on option B  database storage  a few times on a few different backends  and we always ended up going back to option A  filesystem storage  eventually    Large BLOBs like that just have not been handled well enough even by SQL Server 2005  which is the latest one we tried it on    Specifically  we saw serious bloat and I think maybe locking problems   One other note  if you are using NTFS based storage  windows server  etc  you might consider finding a way around putting thousands and thousands of files in one directory  I am not sure why  but sometimes the file system does not cope well with that situation  If anyone knows more about this I would love to hear it   But I always try to use subdirectories to break things up a bit  Creation date often works well for this    Images 2008 12 17  jpg     This provides a decent level of separation  and also helps a bit during debugging  Explorer and FTP clients alike can choke a bit when there are truly huge directories   EDIT  Just a quick note for 2017  in more recent versions of SQL Server  there are new options for handling lots of BLOBs that are supposed to avoid the drawbacks I discussed    EDIT  Quick note for 2020  Blob Storage in AWS Azure etc has also been an option for years now  This is a great fit for many web-based projects since it s cheap and it can often simplify certain issues around deployment  scaling to multiple servers  debugging other environments when necessary  etc

User · Answer

We use A   I would put it on a shared drive  unless you don t plan on running more than one server       If the time comes when this won t scale for you then you can investigate caching mechanisms

User · Answer

I use uploaded images on my website and I would definitely say option a    One other thing I d highly recommend is immediately changing the file name from what the user has named the photo  to something more manageable  For example something with the date and time to uniquely identify each picture    It also helps to strip the user s file name of any strange characters to avoid future complications

User · Answer

Most implementations are option A   With option B  you open a whole big can of whoop4ss when you marshall those bits from the database into something that can be displayed on a browser     Also  if the db is down  the images are not available   I don t think that space is too much of an issue     Terabyte drives are a couple hundred bucks now     We are implementing with option A because we don t have the time or resources to do option B

User · Answer

Option A   Once the image is loaded you can verify the format and resize it before saving  There a number of  Net code samples to resize images on http   www codeproject com  For instance   http   www codeproject com KB cs Photo Resize aspx

User · Answer

It depends on your requirements  specially volume  users and frequency of search  But  for small or medium office  the best option is to use an application like Apple Photos or Adobe Lighroom  They are specialized to store  catalog  index  and organize this kind of resource  But  for large organizations  with strong requirements of storage and high number of users  it is recommend instantiate an Content Management plataform with a Digital Asset Management  like Nuxeo or Alfresco  both offers very good resources do manage very large volumes of data with simplified methods to retrive them  And  very important  there is an free  open source  option for both platforms

User · Answer

I have recently created a PHP MySQL app which stores PDFs Word files in a MySQL table  as big as 40MB per file so far    Pros    Uploaded files are replicated to backup server along with everything else  no separate backup strategy is needed  peace of mind   Setting up the web server is slightly simpler because I don t need to have an uploads  folder and tell all my applications where it is  I get to use transactions for edits to improve data integrity - I don t have to worry about orphaned and missing files   Cons    mysqldump now takes a looooong time because there is 500MB of file data in one of the tables  Overall not very memory cpu efficient when compared to filesystem   I d call my implementation a success  it takes care of backup requirements and simplifies the layout of the project  The performance is fine for the 20-30 people who use the app

User · Answer

We have had clients insist on option B  database storage  a few times on a few different backends  and we always ended up going back to option A  filesystem storage  eventually    Large BLOBs like that just have not been handled well enough even by SQL Server 2005  which is the latest one we tried it on    Specifically  we saw serious bloat and I think maybe locking problems   One other note  if you are using NTFS based storage  windows server  etc  you might consider finding a way around putting thousands and thousands of files in one directory  I am not sure why  but sometimes the file system does not cope well with that situation  If anyone knows more about this I would love to hear it   But I always try to use subdirectories to break things up a bit  Creation date often works well for this    Images 2008 12 17  jpg     This provides a decent level of separation  and also helps a bit during debugging  Explorer and FTP clients alike can choke a bit when there are truly huge directories   EDIT  Just a quick note for 2017  in more recent versions of SQL Server  there are new options for handling lots of BLOBs that are supposed to avoid the drawbacks I discussed    EDIT  Quick note for 2020  Blob Storage in AWS Azure etc has also been an option for years now  This is a great fit for many web-based projects since it s cheap and it can often simplify certain issues around deployment  scaling to multiple servers  debugging other environments when necessary  etc

User · Answer

We use A   I would put it on a shared drive  unless you don t plan on running more than one server       If the time comes when this won t scale for you then you can investigate caching mechanisms

User · Answer

I know this is an old post  But many visitors to this page are getting nothing related to the question  Especially for a newbie   How to upload and store images or file in our website   For a static website there maybe no problem since the file storage for some share hosting still adequate  The problem comes from a dynamic website when it gets bigger  Bigger in the database can be handled  but bigger in file such as images is becomes a problem  There are two type of images in a website    Images come from the administrator for dynamic blog  Usually  these images have been optimized before upload  Images from users in case of users is allowed to upload images such as avatar  Or users can create blog content and put some images from text editor  This kind of images is difficult to predict the size  Users can upload big images just for small content by resize the view size but not resize the image size     By ignoring item no  1 above  quick solution for item no  2 can be temporary solved by the following tips if we don t have image optimizer functionality in our website     Do not allow users to directly upload from text editor by redirecting them to image gallery  On this page users must upload file in advance before they can embedded in the content  This method is called as a File Manager  Use a crop image function for users to upload images  This will limit the image size even users upload very big file  The final image is the result of the cropped image  We can define the size in server side and accept only for example 500Kb or lower    Now  that is only temporary  For final solution  the question is repeated     How to handle a big images storage   Resize or change the extension   How a big or medium website or e-commerce handle the file storage for their images    What we can do then     Migrate from share hosting VPS  Not enough  Then more higher by upgrading to Dedicated  Create your own server for file storage  Googling to do it  This is not as difficult as you think  Some people do it for their website  The easy way is use the CDN file storage service    Okay  1 and 2 is little bit expensive  But no 3 I think is the best solution   Some CDN services allow you to store as many web files as you want    Question   how to upload file to CDN from our website    Don t worry  once you register  usually free  you will get guidance how to upload file and get their link from to your website  You will get an API and more  It s easy    Some providers give us a free service for 14 days with limited storage and bandwidth  But that will be okay for starting point  The only problem is because  people never try    Hope it will help for newbie

User · Answer

We have had clients insist on option B  database storage  a few times on a few different backends  and we always ended up going back to option A  filesystem storage  eventually    Large BLOBs like that just have not been handled well enough even by SQL Server 2005  which is the latest one we tried it on    Specifically  we saw serious bloat and I think maybe locking problems   One other note  if you are using NTFS based storage  windows server  etc  you might consider finding a way around putting thousands and thousands of files in one directory  I am not sure why  but sometimes the file system does not cope well with that situation  If anyone knows more about this I would love to hear it   But I always try to use subdirectories to break things up a bit  Creation date often works well for this    Images 2008 12 17  jpg     This provides a decent level of separation  and also helps a bit during debugging  Explorer and FTP clients alike can choke a bit when there are truly huge directories   EDIT  Just a quick note for 2017  in more recent versions of SQL Server  there are new options for handling lots of BLOBs that are supposed to avoid the drawbacks I discussed    EDIT  Quick note for 2020  Blob Storage in AWS Azure etc has also been an option for years now  This is a great fit for many web-based projects since it s cheap and it can often simplify certain issues around deployment  scaling to multiple servers  debugging other environments when necessary  etc

User · Answer

This is basically I do    Store an uploaded image in temporary directory or memory  Process that image before permanently storing it  2 1  Color corrections 2 2  Compress 2 3  Create several copies based on image dimensions 2 4  Rename with  xl   lg   md   sm etc  suffixes Pack all processed image files  from a single file  inside a folder with folder name as id which will be stored in database for any row document along with image file name  or may be random name as image name   Create yyyy mm d path folder if doesn t exist  For example 2016 08 21  Remember that path and store in database for same document and row  Move image id folder to path folder   Path folder may be located in  var web-content folder   Flush memory buffer or delete temporary file      When you need to access any image mentioned in a document  you have the path and id of the folder than contains images  For example  var web-content   path     id   image-file-name sm jpg  This way if you have to delete all processed image files  just delete the folder and it s content recursively

User · Answer

We have had clients insist on option B  database storage  a few times on a few different backends  and we always ended up going back to option A  filesystem storage  eventually    Large BLOBs like that just have not been handled well enough even by SQL Server 2005  which is the latest one we tried it on    Specifically  we saw serious bloat and I think maybe locking problems   One other note  if you are using NTFS based storage  windows server  etc  you might consider finding a way around putting thousands and thousands of files in one directory  I am not sure why  but sometimes the file system does not cope well with that situation  If anyone knows more about this I would love to hear it   But I always try to use subdirectories to break things up a bit  Creation date often works well for this    Images 2008 12 17  jpg     This provides a decent level of separation  and also helps a bit during debugging  Explorer and FTP clients alike can choke a bit when there are truly huge directories   EDIT  Just a quick note for 2017  in more recent versions of SQL Server  there are new options for handling lots of BLOBs that are supposed to avoid the drawbacks I discussed    EDIT  Quick note for 2020  Blob Storage in AWS Azure etc has also been an option for years now  This is a great fit for many web-based projects since it s cheap and it can often simplify certain issues around deployment  scaling to multiple servers  debugging other environments when necessary  etc

User · Answer

Most implementations are option A   With option B  you open a whole big can of whoop4ss when you marshall those bits from the database into something that can be displayed on a browser     Also  if the db is down  the images are not available   I don t think that space is too much of an issue     Terabyte drives are a couple hundred bucks now     We are implementing with option A because we don t have the time or resources to do option B

User · Answer

I generally store files on the file-system  since that s what its there for  though there are exceptions  For files  the file-system is the most flexible and performant solution  usually    There are a few problems with storing files on a database - files are generally much larger than your average row - result-sets containing many large files will consume a lot of memory  Also  if you use a storage engine that employs table-locks for writes  ISAM for example   your files table might be locked often depending on the size   rate of files you are storing there    Regarding security - I usually store the files in a directory that is outside of the document root  not accessible through an http request  and serve them through a script that checks for the proper authorization first

User · Answer

I have recently created a PHP MySQL app which stores PDFs Word files in a MySQL table  as big as 40MB per file so far    Pros    Uploaded files are replicated to backup server along with everything else  no separate backup strategy is needed  peace of mind   Setting up the web server is slightly simpler because I don t need to have an uploads  folder and tell all my applications where it is  I get to use transactions for edits to improve data integrity - I don t have to worry about orphaned and missing files   Cons    mysqldump now takes a looooong time because there is 500MB of file data in one of the tables  Overall not very memory cpu efficient when compared to filesystem   I d call my implementation a success  it takes care of backup requirements and simplifies the layout of the project  The performance is fine for the 20-30 people who use the app

User · Answer

I use uploaded images on my website and I would definitely say option a    One other thing I d highly recommend is immediately changing the file name from what the user has named the photo  to something more manageable  For example something with the date and time to uniquely identify each picture    It also helps to strip the user s file name of any strange characters to avoid future complications

User · Answer

For auto resizing  try imagemagick    it is used for many major open source content photo management systems    and I believe that there are some  net extensions for it

User · Answer

We use A   I would put it on a shared drive  unless you don t plan on running more than one server       If the time comes when this won t scale for you then you can investigate caching mechanisms

User · Answer

Absolutely  positively option A   Others have mentioned that databases generally don t deal well with BLOBs  whether they re designed to do so or not   Filesystems  on the other hand  live for this stuff   You have the option of using RAID striping  spreading images across multiple drives  even spreading them across geographically disparate servers   Another advantage is your database backups replication would be monstrous

User · Answer

If they are small files that will not need to be edited then option B is not a bad option   I prefer this to writing logic to store files and deal with crazy directory structure issues   Having a lot of files in one directory is bad  emkay   If the files are large or require constant editing  especially from programs like office  then option A is your best bet   For most cases  it s a matter of preference  but if you go option A  just make re the directories don t have too many files in them   If you choose option B  then make the table with the BLOBed data be in it s own database and or file group   This will help with maintenance  especially backups restores   Your regular data is probably fairly small  while your image data will be huge over time

User · Answer

I know this is an old post  But many visitors to this page are getting nothing related to the question  Especially for a newbie   How to upload and store images or file in our website   For a static website there maybe no problem since the file storage for some share hosting still adequate  The problem comes from a dynamic website when it gets bigger  Bigger in the database can be handled  but bigger in file such as images is becomes a problem  There are two type of images in a website    Images come from the administrator for dynamic blog  Usually  these images have been optimized before upload  Images from users in case of users is allowed to upload images such as avatar  Or users can create blog content and put some images from text editor  This kind of images is difficult to predict the size  Users can upload big images just for small content by resize the view size but not resize the image size     By ignoring item no  1 above  quick solution for item no  2 can be temporary solved by the following tips if we don t have image optimizer functionality in our website     Do not allow users to directly upload from text editor by redirecting them to image gallery  On this page users must upload file in advance before they can embedded in the content  This method is called as a File Manager  Use a crop image function for users to upload images  This will limit the image size even users upload very big file  The final image is the result of the cropped image  We can define the size in server side and accept only for example 500Kb or lower    Now  that is only temporary  For final solution  the question is repeated     How to handle a big images storage   Resize or change the extension   How a big or medium website or e-commerce handle the file storage for their images    What we can do then     Migrate from share hosting VPS  Not enough  Then more higher by upgrading to Dedicated  Create your own server for file storage  Googling to do it  This is not as difficult as you think  Some people do it for their website  The easy way is use the CDN file storage service    Okay  1 and 2 is little bit expensive  But no 3 I think is the best solution   Some CDN services allow you to store as many web files as you want    Question   how to upload file to CDN from our website    Don t worry  once you register  usually free  you will get guidance how to upload file and get their link from to your website  You will get an API and more  It s easy    Some providers give us a free service for 14 days with limited storage and bandwidth  But that will be okay for starting point  The only problem is because  people never try    Hope it will help for newbie

User · Answer

Definitely resize the image  and check it s format if you can  There have been cases of malicious files being uploaded and served by unwitting hosts- for instance  the GIFAR vulnerability allowed you to hide a malicious java applet in a GIF file  which would then be able to read cookies in the current context and send them to another site for a cross-site scripting attack  Resizing the images usually prevents this  as it munges the embedded code  While this attack has been fixed by JVM patches  naively serving up binary files without scrubbing them opens you up to a whole range of vulnerabilities    Remember  most virus scanners can only run against the filesystem- if you store your binaries in the DB  you won t be able to run a scanner against them very easily

User · Answer

I generally store files on the file-system  since that s what its there for  though there are exceptions  For files  the file-system is the most flexible and performant solution  usually    There are a few problems with storing files on a database - files are generally much larger than your average row - result-sets containing many large files will consume a lot of memory  Also  if you use a storage engine that employs table-locks for writes  ISAM for example   your files table might be locked often depending on the size   rate of files you are storing there    Regarding security - I usually store the files in a directory that is outside of the document root  not accessible through an http request  and serve them through a script that checks for the proper authorization first

User · Answer

For auto resizing  try imagemagick    it is used for many major open source content photo management systems    and I believe that there are some  net extensions for it

User · Answer

There s sort of a hybrid approach in SQL Server 2008 called the filestream datatype that was talked about on RunAs Radio  74  which is sort of like the best of both worlds   Most people don t have the 2008 otion  but if you do  this option looks pretty cool

User · Answer

Most implementations are option A   With option B  you open a whole big can of whoop4ss when you marshall those bits from the database into something that can be displayed on a browser     Also  if the db is down  the images are not available   I don t think that space is too much of an issue     Terabyte drives are a couple hundred bucks now     We are implementing with option A because we don t have the time or resources to do option B

User · Answer

Definitely resize the image  and check it s format if you can  There have been cases of malicious files being uploaded and served by unwitting hosts- for instance  the GIFAR vulnerability allowed you to hide a malicious java applet in a GIF file  which would then be able to read cookies in the current context and send them to another site for a cross-site scripting attack  Resizing the images usually prevents this  as it munges the embedded code  While this attack has been fixed by JVM patches  naively serving up binary files without scrubbing them opens you up to a whole range of vulnerabilities    Remember  most virus scanners can only run against the filesystem- if you store your binaries in the DB  you won t be able to run a scanner against them very easily

User · Answer

I have recently created a PHP MySQL app which stores PDFs Word files in a MySQL table  as big as 40MB per file so far    Pros    Uploaded files are replicated to backup server along with everything else  no separate backup strategy is needed  peace of mind   Setting up the web server is slightly simpler because I don t need to have an uploads  folder and tell all my applications where it is  I get to use transactions for edits to improve data integrity - I don t have to worry about orphaned and missing files   Cons    mysqldump now takes a looooong time because there is 500MB of file data in one of the tables  Overall not very memory cpu efficient when compared to filesystem   I d call my implementation a success  it takes care of backup requirements and simplifies the layout of the project  The performance is fine for the 20-30 people who use the app

User · Answer

For security reasons  it is also best practise to avoid problems caused by IE s Content Sniffing which can allow attackers to upload JavaScript inside image files  which might get executed in the context of your site  So you might want to transform the images  crop resize them  somehow before storing them to prevent this sort of attack  This answer has some other ideas

User · Answer

The only benefit for the option B is having all the data in one system  yet it s a false benefit  You may argue that your code is also a form of data  and therefore also can be stored in database - how would you like it   Unless you have some unique case    Business logic belongs in code  Structured data belongs in database  relational or non-relational   Bulk data belongs in storage  filesystem or other       It is not necessary to use filesystem to keep files  Instead you may use cloud storage  such as Amazon S3  or Infrastructure-as-a-service on top of it  such as Uploadcare    https   uploadcare com upload-api-cloud-storage-and-cdn   But storing files in the database is a bad idea

User · Answer

There s sort of a hybrid approach in SQL Server 2008 called the filestream datatype that was talked about on RunAs Radio  74  which is sort of like the best of both worlds   Most people don t have the 2008 otion  but if you do  this option looks pretty cool

User · Answer

There s sort of a hybrid approach in SQL Server 2008 called the filestream datatype that was talked about on RunAs Radio  74  which is sort of like the best of both worlds   Most people don t have the 2008 otion  but if you do  this option looks pretty cool

User · Answer

For security reasons  it is also best practise to avoid problems caused by IE s Content Sniffing which can allow attackers to upload JavaScript inside image files  which might get executed in the context of your site  So you might want to transform the images  crop resize them  somehow before storing them to prevent this sort of attack  This answer has some other ideas

User · Answer

I generally store files on the file-system  since that s what its there for  though there are exceptions  For files  the file-system is the most flexible and performant solution  usually    There are a few problems with storing files on a database - files are generally much larger than your average row - result-sets containing many large files will consume a lot of memory  Also  if you use a storage engine that employs table-locks for writes  ISAM for example   your files table might be locked often depending on the size   rate of files you are storing there    Regarding security - I usually store the files in a directory that is outside of the document root  not accessible through an http request  and serve them through a script that checks for the proper authorization first

User · Answer

If they are small files that will not need to be edited then option B is not a bad option   I prefer this to writing logic to store files and deal with crazy directory structure issues   Having a lot of files in one directory is bad  emkay   If the files are large or require constant editing  especially from programs like office  then option A is your best bet   For most cases  it s a matter of preference  but if you go option A  just make re the directories don t have too many files in them   If you choose option B  then make the table with the BLOBed data be in it s own database and or file group   This will help with maintenance  especially backups restores   Your regular data is probably fairly small  while your image data will be huge over time

User · Answer

The only benefit for the option B is having all the data in one system  yet it s a false benefit  You may argue that your code is also a form of data  and therefore also can be stored in database - how would you like it   Unless you have some unique case    Business logic belongs in code  Structured data belongs in database  relational or non-relational   Bulk data belongs in storage  filesystem or other       It is not necessary to use filesystem to keep files  Instead you may use cloud storage  such as Amazon S3  or Infrastructure-as-a-service on top of it  such as Uploadcare    https   uploadcare com upload-api-cloud-storage-and-cdn   But storing files in the database is a bad idea

User · Answer

Definitely resize the image  and check it s format if you can  There have been cases of malicious files being uploaded and served by unwitting hosts- for instance  the GIFAR vulnerability allowed you to hide a malicious java applet in a GIF file  which would then be able to read cookies in the current context and send them to another site for a cross-site scripting attack  Resizing the images usually prevents this  as it munges the embedded code  While this attack has been fixed by JVM patches  naively serving up binary files without scrubbing them opens you up to a whole range of vulnerabilities    Remember  most virus scanners can only run against the filesystem- if you store your binaries in the DB  you won t be able to run a scanner against them very easily

User · Answer

Absolutely  positively option A   Others have mentioned that databases generally don t deal well with BLOBs  whether they re designed to do so or not   Filesystems  on the other hand  live for this stuff   You have the option of using RAID striping  spreading images across multiple drives  even spreading them across geographically disparate servers   Another advantage is your database backups replication would be monstrous

User · Answer

Well  I have a similar project where users upload files onto the server  Under my point of view  option a  is the best solution due to it s more flexible  What you must do is storing images in a protected folder classified by subdirectories  The main directory must be set up by the administrator as the content must no run scripts  very important  and  read  write  protected for not be accesible in http request   I hope this helps you

User · Answer

For auto resizing  try imagemagick    it is used for many major open source content photo management systems    and I believe that there are some  net extensions for it

User · Answer

We use A   I would put it on a shared drive  unless you don t plan on running more than one server       If the time comes when this won t scale for you then you can investigate caching mechanisms

User · Answer

Option A   Once the image is loaded you can verify the format and resize it before saving  There a number of  Net code samples to resize images on http   www codeproject com  For instance   http   www codeproject com KB cs Photo Resize aspx

User · Answer

Option A   Once the image is loaded you can verify the format and resize it before saving  There a number of  Net code samples to resize images on http   www codeproject com  For instance   http   www codeproject com KB cs Photo Resize aspx

User · Answer

It depends on your requirements  specially volume  users and frequency of search  But  for small or medium office  the best option is to use an application like Apple Photos or Adobe Lighroom  They are specialized to store  catalog  index  and organize this kind of resource  But  for large organizations  with strong requirements of storage and high number of users  it is recommend instantiate an Content Management plataform with a Digital Asset Management  like Nuxeo or Alfresco  both offers very good resources do manage very large volumes of data with simplified methods to retrive them  And  very important  there is an free  open source  option for both platforms

User · Answer

This is basically I do    Store an uploaded image in temporary directory or memory  Process that image before permanently storing it  2 1  Color corrections 2 2  Compress 2 3  Create several copies based on image dimensions 2 4  Rename with  xl   lg   md   sm etc  suffixes Pack all processed image files  from a single file  inside a folder with folder name as id which will be stored in database for any row document along with image file name  or may be random name as image name   Create yyyy mm d path folder if doesn t exist  For example 2016 08 21  Remember that path and store in database for same document and row  Move image id folder to path folder   Path folder may be located in  var web-content folder   Flush memory buffer or delete temporary file      When you need to access any image mentioned in a document  you have the path and id of the folder than contains images  For example  var web-content   path     id   image-file-name sm jpg  This way if you have to delete all processed image files  just delete the folder and it s content recursively

User · Answer

I use uploaded images on my website and I would definitely say option a    One other thing I d highly recommend is immediately changing the file name from what the user has named the photo  to something more manageable  For example something with the date and time to uniquely identify each picture    It also helps to strip the user s file name of any strange characters to avoid future complications

User · Answer

If they are small files that will not need to be edited then option B is not a bad option   I prefer this to writing logic to store files and deal with crazy directory structure issues   Having a lot of files in one directory is bad  emkay   If the files are large or require constant editing  especially from programs like office  then option A is your best bet   For most cases  it s a matter of preference  but if you go option A  just make re the directories don t have too many files in them   If you choose option B  then make the table with the BLOBed data be in it s own database and or file group   This will help with maintenance  especially backups restores   Your regular data is probably fairly small  while your image data will be huge over time

User · Answer

There s sort of a hybrid approach in SQL Server 2008 called the filestream datatype that was talked about on RunAs Radio  74  which is sort of like the best of both worlds   Most people don t have the 2008 otion  but if you do  this option looks pretty cool

User · Answer

Absolutely  positively option A   Others have mentioned that databases generally don t deal well with BLOBs  whether they re designed to do so or not   Filesystems  on the other hand  live for this stuff   You have the option of using RAID striping  spreading images across multiple drives  even spreading them across geographically disparate servers   Another advantage is your database backups replication would be monstrous

User · Answer

Well  I have a similar project where users upload files onto the server  Under my point of view  option a  is the best solution due to it s more flexible  What you must do is storing images in a protected folder classified by subdirectories  The main directory must be set up by the administrator as the content must no run scripts  very important  and  read  write  protected for not be accesible in http request   I hope this helps you

User · Answer

For auto resizing  try imagemagick    it is used for many major open source content photo management systems    and I believe that there are some  net extensions for it

User · Answer

If they are small files that will not need to be edited then option B is not a bad option   I prefer this to writing logic to store files and deal with crazy directory structure issues   Having a lot of files in one directory is bad  emkay   If the files are large or require constant editing  especially from programs like office  then option A is your best bet   For most cases  it s a matter of preference  but if you go option A  just make re the directories don t have too many files in them   If you choose option B  then make the table with the BLOBed data be in it s own database and or file group   This will help with maintenance  especially backups restores   Your regular data is probably fairly small  while your image data will be huge over time

User · Answer

Option A   Once the image is loaded you can verify the format and resize it before saving  There a number of  Net code samples to resize images on http   www codeproject com  For instance   http   www codeproject com KB cs Photo Resize aspx

User · Answer

I have recently created a PHP MySQL app which stores PDFs Word files in a MySQL table  as big as 40MB per file so far    Pros    Uploaded files are replicated to backup server along with everything else  no separate backup strategy is needed  peace of mind   Setting up the web server is slightly simpler because I don t need to have an uploads  folder and tell all my applications where it is  I get to use transactions for edits to improve data integrity - I don t have to worry about orphaned and missing files   Cons    mysqldump now takes a looooong time because there is 500MB of file data in one of the tables  Overall not very memory cpu efficient when compared to filesystem   I d call my implementation a success  it takes care of backup requirements and simplifies the layout of the project  The performance is fine for the 20-30 people who use the app

User · Answer

I generally store files on the file-system  since that s what its there for  though there are exceptions  For files  the file-system is the most flexible and performant solution  usually    There are a few problems with storing files on a database - files are generally much larger than your average row - result-sets containing many large files will consume a lot of memory  Also  if you use a storage engine that employs table-locks for writes  ISAM for example   your files table might be locked often depending on the size   rate of files you are storing there    Regarding security - I usually store the files in a directory that is outside of the document root  not accessible through an http request  and serve them through a script that checks for the proper authorization first

User · Answer

Absolutely  positively option A   Others have mentioned that databases generally don t deal well with BLOBs  whether they re designed to do so or not   Filesystems  on the other hand  live for this stuff   You have the option of using RAID striping  spreading images across multiple drives  even spreading them across geographically disparate servers   Another advantage is your database backups replication would be monstrous

User · Answer

Definitely resize the image  and check it s format if you can  There have been cases of malicious files being uploaded and served by unwitting hosts- for instance  the GIFAR vulnerability allowed you to hide a malicious java applet in a GIF file  which would then be able to read cookies in the current context and send them to another site for a cross-site scripting attack  Resizing the images usually prevents this  as it munges the embedded code  While this attack has been fixed by JVM patches  naively serving up binary files without scrubbing them opens you up to a whole range of vulnerabilities    Remember  most virus scanners can only run against the filesystem- if you store your binaries in the DB  you won t be able to run a scanner against them very easily

User · Answer

Most implementations are option A   With option B  you open a whole big can of whoop4ss when you marshall those bits from the database into something that can be displayed on a browser     Also  if the db is down  the images are not available   I don t think that space is too much of an issue     Terabyte drives are a couple hundred bucks now     We are implementing with option A because we don t have the time or resources to do option B

User · Answer

I use uploaded images on my website and I would definitely say option a    One other thing I d highly recommend is immediately changing the file name from what the user has named the photo  to something more manageable  For example something with the date and time to uniquely identify each picture    It also helps to strip the user s file name of any strange characters to avoid future complications

[database] What is the best place for storing uploaded images, SQL database or disk file system?

Examples related to database

Examples related to filesystems

Examples related to image-uploading

Examples related to binaryfiles