Security: flat files VS databases

Vulnerabilities to hacking

[explorador]

The first thing most people think of when talking about flat files VS databases is a ghost discussion about speed & features (mysql, postgreSQL, etc). The intention of this thread is not that, it's security. The main difference about flat file databases is one has to build the system and features, but let's focus on security.

Mysql/Postgresql-Databases and alike:

• Code and data are in separate places, it's too much bad luck to have them both exposed.
  
• It's not like "set up and go" you need to secure both areas.
  
• You might have great code security while having your database failing on that, or just exposed.
  
• It can be the opposite: having your data altered while your code is intact.
  
• It sucks trying to work with git or whatever version management, you have full control of your code but data needs extra steps.
  
• And how much time you need to recover from failure (backups and restoring it).

Flat file databases:

• Code and data are files you can see, copy, etc. You can have them on the same place, disk or wherever you want to.
  
• Amazingly nice to manage versions and backups.
  
• So, the same file access & security implementation works on both areas (code and data). No need to secure extra areas.

Let's not forget the many CMS and commercial software solutions relying on databases (like WP, Joomla, etc.) being hacked. Drupal (my fav choice) has better history and reputation because at the end a lot depends on code implementations, anyway if your file security is not good, even Drupal will fail.

My situation:
I coded my own CMS (several) and have been quite happy on security and specially performance. Critical applications were in "competition" against WP and other custom enterprise solutions, I didn't have any problems while can't say the same about the other options. Over the years I think it all comes down to how secure your server is, file permissions, shared hosting and your code implementations (filtering code, cleaning ALL data input, etc).

While I'm happy with my CMS's I'm now working on my full Perl framework focusing on the key points and challenges I see at work every day. This will solve a lot of problems at the office in no time. Some frameworks allow you changing one line of preffs to switch between mysql and postgresql. I will try to do the same but aiming database and flat file databases.

Anyway I'm researching on security, any comments will be appreciated. Been doing fine with security for years regarding websites 100% in Perl and getting 4K daily uniques visits.

Thanks in advance.

(if any doubts about speed, my systems have won the race over the other options being used, so it's not on the table for discussion).

[JohnAtWork]

As a long time perl coder myself, I wouldn't use flat files for anything but the smallest of sites or maybe read-only sites.
- Pick up a copy of the PostgreSQL printed manuals, they are very good references.

From a data integrity viewpoint a database system gives you transactions, constraints, triggers, foreign keys, backup consistency, roles and privileges etc.
- A very good programmer might be able to get those functions using flat files, but then its the same as actually having a database system.
- Editing flat file data on a live system is not a good idea.

From a security standpoint you should 1) ensure the database is not listening on the internet, 2) use least privilege as much as possible, 3) use a proper abstraction layer to prevent SQL attacks/injection.
- Successful attacks are almost always due to those rules being skipped.

The other attack vectors don't change much between the two choices:
1) operating system vulnerability - if they can get a login they've got your flat files as well as your database
2) your own code - a serious bug could be trouble for flat files as well as a database.

The benefits fall on the database.

I can't recommend WebmasterWorld itself as a bastion of best practices; my recent registration showed that they deliver passwords in plain text via email.

[phranque]

welcome to WebmasterWorld, JohnAtWork!

[explorador]

Thanks for the contributions, there are many valid points.

It's been a while since I posted on this thread and now that i re read it I see the title it's not the best for what I had in mind but still the posts, flat files and database comments are very useful.

I guess a better way of discussing this would be "what about a flat file framework" instead of bringing into discussion the "VS databases".

Thanks

[graeme_p]

If you ask that question, what would the framework do?

If its designed the wrong way, it would end up reinventing the database.

A good use case for flat files would be:

1) You want a hierarchical structure
2) You do not need references, tranactions etc.

Some wiki architectures are a good fit for this.

If you want just the speed and security characteristics of flat files and you have a static site, then you can generate files from a database, and expose those publicly with a database driven CMS that can only be accessed from the local network or certain IPs. The best of both worlds.

[explorador]

If you ask that question, what would the framework do?

I don't know all the ways to do everything but I've seen myself (and on other people work) it is very common to create the data structure then the database, the model, the class etc. Sure it helps a lot to create the CRUD and speeds up the process BUT right after the magic all of them faced validating data input. A lot of this happens on the server side but it really lacks automatic features.

From where I'm standing: a lot of those extra rules could be created on the document-0 (the data structure) and used all along from that like a data wizard. I came up with some interesting ideas to speed up the processes, shared it with a friend and sure he found a lot of "but..." until I showed him the magic. I know a lot of people rely on server validation allowing any kind of data entry on the forms. My experience shows that while Javascript validation is SURE-not-safe, it's welcome and not only helps but also speeds up the processes, yes if the client shuts down JS my framework would still validate the data. I'm a big usability interface designer so I really believe on how useful this is (and the final user thanks me at the end) while I also avoid unneeded server posts.

Why?: Speed. It's a long story and I wasn't that convinced myself at first. My last CMS/kinda-framework allowed me faster systems creation but I saw other needs. Sure I was criticized by many for using flat files but there are nice threads around where Brett explains the benefits on performance and speed, from reading/writing on the same file (multiple clients) to searching only where the data is or might be, sure, this involves designing the data storage for those purposes.

If its designed the wrong way, it would end up reinventing the database.

Sure but that's not my intention. So far I'm not interested (anymore) on implementing my ideas over an existing framework, I sure tried with Symfony because a lot of tasks were repetitive and boring (with LONG lines) so I builg my own libraries allowing me to do more with oneliners. Later I decided to step away from existing frameworks.

Sure databases do their work but I've managed to work on some stuff allowing me to move really fast on disasters, recovering, testing and moving data from server to server, it's a personal choice at the end.

But there is more involved on my idea (framework) than using the flat files. My primary ideas can be constructed with any code (php, asp, perl) I choosed perl, but the data structure and safety while having flat files involves questions of security.

[explorador]

Yes but...

Frameworks do make security a lot easier and more reliable.

Yes: existing frameworks but many fail and what they offer (to some extent): avoiding repetitive tasks. Yes they save time but there are still many repetitive tasks left on the table, the efficiency and time saving can still be raised.

As I mentioned before the thread title is not the best one because the flat file question comes as a conclusion of creating a framework. It usually goes like this:

• Coder begins coding
  
• Coder notices repetitive tasks
  
• Coder starts building libraries and functions
  
• Coder starts reusing
  
• Coder switches to frameworks
  
• Then starts building more code and notices repetitive tasks
  
• Coder starts reusing code
  
• Then notices frameworks don't make this exactly easy... because many times a single change erases your already implemented code, coder then has to find another way to avoid the console commands to overwrite code that has additions
  
• Finally some notice how much original code they have and think "what if..."
  

So a lot of old time coders already have lots of efficient, small, useful code they can reuse and combine into a framework (in fact many frameworks were born this way), some decide to build something for specific needs (that's the key). There is no perfect framework for every task and while some might want to address this from the database side, yes some use nosql, some use sql and some allows you to switch from diff options to others only needing small code changes or preferences. A lot of projects won't use all the features sql database offer. I'm considering doing this (implementing the flat-file/sql feature so users can select what they want).

Thanks for the comments and suggestions, taking care of data input is really important.

[Kendo]

One advantage of flat files is that they can at any time be made read only so that they can never be exploited. Try doing that to select rows in any database.

[explorador]

Read only, that has been amazingly helpful many times!

[graeme_p]

because many times a single change erases your already implemented code,

I do not understand that. How?

Why?: Speed.

Cache. As you scale pay some attention to efficient queries. Very few people need to be faster than, for example, Disqus, who store everything in PostgreSQL (their blog posts on scaling Postgres are interesting).

I do not believe that needing to be very fast, and not being able to cache, and not needing the data integrity of databases can be a common case.

Finally some notice how much original code they have and think "what if...

I hope they are thinking, "what if I tidied this up add contributed it to the framework I used or package it as a resuable app/plugin/whatever)"

One advantage of flat files is that they can at any time be made read only so that they can never be exploited. Try doing that to select rows in any database.

Write a trigger that prevents updates on those rows. Usually, those rows have some existing property that the trigger than check (a booking with a past date, final vs draft etc.).

My experience shows that while Javascript validation is SURE-not-safe, it's welcome and not only helps but also speeds up the processes, yes if the client shuts down JS my framework would still validate the data

A framework would make this easier. Write an extension that adds the validation you want. IN fact, you may find that someone had already done it, like this: [github.com...] - although HTML5 form fields do a lot of validation anyway. A few lines of code, and its done.

[JohnAtWork]

"Try doing that to select rows in any database."

All my database designs include a user which can write and user which is read-only.

To stop writing I could change the writable user into the readable user. One change in a config file.

Though I admit, I've never had a reason to make that flip.

[explorador]

I do not understand that. How?

Frameworks build stuff from the data definition, that doesn't include special validations so the coder must add this himself. There are other stuff an app might need so the work starts right after creating the cruds. Then the client wants a change, an extra field... so the data structure changes and the files need to be re-created overwriting some or lots of code.

In theory one designs the data structure and move on, then design, validate, then move on. In many cases (ugly cases) the first step has changes and the coder suffers, time is wasted.

Been working on Symfony and a friend (very skilled) on Ruby on Rails. We experienced lots of issues due to sudden changes, he is still in love with RoR, I'm not, I'm not even in love with Symfony even it saved the day many times BUT... at the end I see a lot of stuff we are repeating and it's not due to lack of experience: those frameworks didn't include those features. Some changes demand to recreate some files and it-sucks. Sure he refuses to change but I don't refuse to build.

There is a framework for almost every taste and need. Some special needs don't have a special tool so that's what I'm aiming at.

I hope they are thinking, "what if I tidied this up add contributed it to the framework I used or package it as a reusable app/plugin/whatever)"

Contributions will end up as plugins or libraries but not really implemented on the frameworks. I wrote some beautiful time saving ones for Symfony but I just hate to create a project, then copy, then enable, then whatever... it's nice to have such basic stuff ready out of the box.

I guess a lot of coders try frameworks and their own tools, some stick to their own. Cache? yes, I already have that in diff ways. Besides I hate the gazillion files Symfony creates for one simple thing and the so many hours people spend configuring their frameworks or how updating breaks the code (more often than not).

[incrediBILL]

FWIW, I noticed you mentioned data definitions being a lynchpin and you can add that to flat files as I did it for a product I developed many years ago for exactly that reason, I needed my framework to know the structure of the files.

Databases already have it built-in, but it doesn't go far enough for my purposes such as fields being typed based on UI elements, not data elements.

Basically, I did a mapping layer from the data format to the UI format, so you could do cool tools like automatic forms and such which know the fields for drop down lists, the data validation for other fields, etc. as part of the package.

I'm surprised more people don't do it that way as there's really no reason programming isn't mostly plug and play objects that you simply connect the inputs and outputs together to make most of what a program does work without truly programming.

Most of us that build websites for instance do the same tasks over and over. Most tasks are based around lists and a CMS, BBS, FORUM, etc. are nothing but lists.

You need a method to add data into the list, display and paginate the list, but in the end, whether it's a WebmasterWorld forum, an ecommerce site, or a WordPress blog, the same list manager could work for both.

In each example it's always hierarchical normalized drill down data lists. Once you've defined the data relationships, telling the list manager how to query that data should be simple, and then you have to define a view for each list to display. So if you grasp where I was going, the difference between a forum and an only store is only the data feed and the view, all the stuff in the middle could be 100% the same.

So what if your 'store' shows lists of suggested items based on the currently displayed item. It's just another view of another list embedded in the previous product view.

Electronics works that way, there are only so many components that you have which can be assembled in a myriad of ways to make damn near anything, and it's all plug 'n play.

More to the point, most web technologies don't even push developers to use common libraries like Windows does with a DLL. If you have 3 different programs installed on your site that use the same HTML editor in PHP or Perl you would install the same code in 3 different places.

It's insanity which a framework does reign in somewhat but nobody has put their foot down, drawn a line in the sand, and started forcing simple things like common shared code and versioning for supporting more than one version of the same thing at a time.

That's why websites get hacked 6 ways from Sunday because everybody is doing their own thing and not reaping the benefits from those that have already done it and did it better.

Instead we'll have yet another framework when your code could likewise share the same code from other frameworks and you could be enriching the common code we all use instead of starting from scratch and we'll debug it from scratch, again.

Sorry if I soapboxed a bit but it's a real pet peeve of mine as software development has sadly taken 10 steps back from where we were in the 90s because of poor practices all the way around.

BTW, how's your code been coming along?
Got anything to play with yet?

[Auctioneer]

Beeing playing with relativly simple Flatfile type Perl based auction Software for a long time, I started using PHP by use of the OpenCart Software a Year ago. I can therefore more than just agree with your statement:

...as software development has sadly taken 10 steps back from where we were in the 90s...

12 Years ago, it would have killed any shared Server, trying to handle this kind of Software, I bet...

Ernie

[edited by: phranque at 12:01 pm (utc) on Jan 21, 2015]
[edit reason] sig url snipped [/edit]

[explorador]

Incredibill: Exactly. I focused on data properties, a model and views with a diff more practical approach. The data properties define everything so really a lot of repetitive tasks on frameworks are avoided here, in fact just by creating the data definition the forms are ready to work out of the box including data validation, uniqueness, etc. Now it has automatic out of the box views and cruds, a lot of things can be achieved using single config files for each module and you can clone a module just by cloning the folder.

Just added language support very easy to configure. I agree with your descriptions. Yes a lot of people tell me "well, databases do this and that automatically" sure they are right, but I can't understand why they don't handle other tasks, or at least via frameworks you have to do a lot of things.

I understand many have problems getting the concept, discussed it with a friend and started talking about other frameworks until I showed him a partial alpha doing things from scratch, he loved it, open source it? (he suggested) nope, will no go that way for many reasons by now, it's been too much work for one man band so far.

It's understandable why many say "why not contributing to existing frameworks?, mod the code and there you go", well, why isn't anybody doing this already with the things they don't like? funny huh? so far most people will adapt to frameworks instead of working on their own ideas, both paths are acceptable. Exactly, poor practices. When one reads (deeply) the story and origins of some frameworks then is evident why there are forks and why it became a monster.


I'm playing with the first semi beta on an official website of mine, it works pretty well.

BTW, how's your code been coming along?

Thanks for asking, thousands of lines so far, clean code, commented, under 3MB with all the basic required assets, validation and while it works from zero CSS and HTML, it builds pretty nice things with Bootstrap. Avoided any special modules to keep it "average" and able to run on most servers without any problems, it needs low resources and just finished two levels of cache. With this framework just created another CMS in a matter of days (less than a week). Will go after other features.

Focused on keeping things clear and simple if I ever decide to release it for public use, or at least for myself because it's very diff when one is working than when one needs to mod the code after 2 years or using it but not coding it.

Got anything to play with yet?

Yes, I'm on it. Not a public release or demo but eventually will post something.

I messed up with the title of the thread, but it was my initial concern (the data). So far many frameworks work pretty well but many demand lots of hours doing repetitive coding, things that don't really make any sense. Besides some famous options demand a lot of resources and are a pain in the butt to configure or get them running without problems on servers or local servers.

[explorador]

auctioneer: ...as software development has sadly taken 10 steps back from where we were in the 90s...

12 Years ago, it would have killed any shared Server, trying to handle this kind of Software, I bet...

I know discussing frameworks or technologies people use brings defenses, you know they like what they like but what you say it's true, a lot of options are "pretty" but the requirements look like a complex dish to make, besides check the forums: a lot of those don't work as expected.

To the point: a lot of things can be done on low resources, but many tools are eating the shared servers without need. I have built some nice stuff running on shared servers beating stuff colleagues have built on specialized servers.

[graeme_p]

Frameworks build stuff from the data definition, that doesn't include special validations so the coder must add this himself. There are other stuff an app might need so the work starts right after creating the cruds. Then the client wants a change, an extra field... so the data structure changes and the files need to be re-created overwriting some or lots of code.

It sounds as though you are talking about frameworks that use code generation or something similar? I did not think that either RoR or Symfony requires regenerating code when you make a change, so I am a bit puzzled - its a while since I looked at either though.

In my experience a framework makes dealing with a relational database easier as it has tools to generate schema migrations and help generate data migrations which makes SQL more attractive relative to NoSQL and flat files.

[Auctioneer]

>> a lot of things can be done on low resources, but many tools are eating the shared servers without need. <<

I am aware of this. Just to give you an example. A very popular Add-On-Method in the Opencart Scene is to 'overwrite' Source-Code, before Pages are beeing sent to Visitors. SInce it does that, Mod by Mod, unselected, and it results, in some cases, that Servers have to easy build 50 and much more of internal Page-Temp-Files, one after the other, before one single Page is finaly ready to be sent. It's called VqMod.

It's the immense Server Power, that makes such crazy 'Doing' possible. And it even works, at least, up to a certain extend. But since I am not familiar with other PHP-driven multi/multi-Gizmos like OC, I could not judge.

But, by nature of things, we would have been glad to have such 'Options' like VqMod's, a good decade ago. It yould have saved a lot of peoples a lot of time, trying to 'implement' Perl coded modifications in such an easy form and way...

But, as I mentioned, it would probably not have worked out well..., on 2 to 4 MB of shared RAM

[explorador]

Graeme: It sounds as though you are talking about frameworks that use code generation or something similar? I did not think that either RoR or Symfony requires regenerating code when you make a change, so I am a bit puzzled - its a while since I looked at either though.

I've experienced several issues (repetitive) with clients including working on a big company (small or big clients showed the same over the years) and it's "ohhh let's add another field". It can be a pain in the butt to keep adding tables and fields to already planned schemas and generated models, not to mention validations.

I addressed this directly on my tool for my own benefit, now it's very easy for me to keep adding tables and fields on the master model file, the validations are specified there too so it won't affect anything, not even the order or visibility of the fields (that can be changed at any time).

@Auctioneer: exactly, lots of resources. Call me crazy but I also believe on running on low resources, it's even eco friendly. In my case I'm very happy on the kind of stuff I can run on shared servers.

[graeme_p]

@Auctioneer, that is crazy! Why is it needed? A lack of an decent API for addons? Does it do this on every page load, rather than doing it once and then running the modified files until something is update?

@explorardor, are we talking about the same thing? You are talking about frameworks that generate code that you modify, so that if you change something you have to reapply all your modifications to regenerated code?

[explorador]

@graeme: yes. Frameworks create diff files from one thing and depending the change you do to that one thing, several files might need updating or mods.

[graeme_p]

@explorardor, not all frameworks do this - Django (which is what I usually use) for example does not. It does generate files for database migrations, and it does generate a project skeleton but neither of these will need to be regenerated later.

I looked at a lot of frameworks in several languages a few years ago to pick what I wanted to use for new projects, and I ruled out a lot of well known frameworks (Yii, for example) because they relied on modifying generated code. I cannot remember that being a problem with Rails though.

[graeme_p]

Just to clarify as I do not think I have made it clear, Django creates CRUD interfaces dynamically rather than through code generation.

This is mostly done through subclassing - so to create a form to edit a particular model (an object that maps to a database table), you subclass the ModelForm class specifying that model (one line of code) and listing which fields should be in the form (another line of code). You can add more code to customise form fields, and you can add validation by adding methods to the class.

If you add a field to the model, you need to add the field name to the list of fields in the form, and it will appear. Validation can be added with either a method on the form class, or in the model.

[explorador]

Graeme: thanks you are right on code generation. My experience ended on: this can be done in other better ways. Django sounds good, never actually worked with it, my experience was trying to help coworkers with Edex (involving Django) it was a pain, even contacting the support revealed a lot but I take it as more related to Edex, not Django.

I finally did something like you said there with my framework/toy:

Database structure and relations are defined on a model
Forms depend on a preference/index file
You can change the order/visibility of the fields anytime
Uniqueness & diff kinds of validations are applied according to the model
The framework builds javascript (client side) and server side validation
The javascript validation helps the user experience and saves data exchange
the server side validation makes sure everything is fine
The forms are built on the fly, dynamically
They can also be cached
There is even validation and resizing for images
It's very easy, not a bunch of files or code
The Crude generation is very easy and fast
Adding extra fields is no pain


I know a lot of people hear it and think... several things. Coworkers didn't agree with the concepts until they saw it on beta. The thing is, if one can build something useful, they why not?

[Kendo]

Most interesting but I need to unsubscribe

[graeme_p]

I am not suggesting you should not do it, just that there are other frameworks that avoid the generated code problem. I think you said that this is in Perl? You should seriously consider open sourcing it as it could take off - it certainly looks better than the Perl frameworks I have heard of like Catalyst.

You should probably take a look at Django for ideas - also what what the Django developers have changed or deprecated over the years as a list of mistakes to avoid.

If have slightly lost track of whether you decided to go with flat files or an RDMBS. Does it create database tables from the models? If so, how do you handle applying changes to the model to the database? Django and Rails have database migration functionality.

[explorador]

@Kendo: It sounds as a bug I got, contact a moderator, I subscribed to a thread couldn't unsubscribe later.

@Graeme: thanks, yes it is in Perl, pretty fast, not heavy at all. I would consider open sourcing it on the long term, in the mid time I know such tool needs a lot of testing, not to mention I fear receiving a lot of critics, I mean it's pretty easy to attack some tool while not creating a single line of code, my interest was to help me to develop faster not to impress people, I guess you get what I mean, internet can be evil sometimes. Besides I also fear releasing something I use because it can compromise security (at least for a while). I need to research about open source because while money is not important in this case, just invested a lot of man hours there.

Django sounds very good now that you expand the subject. Will do.

Yes I stayed with flat files, yes it creates flat-database files from the models. The benefit is, a lot is defined in the model and code-handled, so the rest is just data. Some things are kept under the same flat-file-database, some are split into a few files to protect the data. Why? while there are routines to avoid collisions I wouldn't sleep well just trusting it... read a lot about it regarding perl, even here.

I came up with ideas making it easier to make more changes to the models but involve splitting more and more into more files, while this can be good (just consider some searches) I also try to avoid multiple opening-closing files again and again, disk access is also a challenge, it doesn't matter if those are small files vs one big file.


While I've been doing things like this for years, I still consider myself very inexperienced, when I was using Symfony couldn't believe how complex they made some easy tasks, built some libraries for my own use but that doesn't mean I feel confident on creating something for public use