Separation of public and private data on the web

August 28, 2007 3:12 PM

How to do separation of public and private data on the web with PHP/MySQL? I'm creating a website for a business, with 2 sections - public and admin (staff). The admin section is secured with .htaccess and is intended to be used by office staff to enter customer requirements as they come in, and edit product listings. However, I worry about the security and suspect there is probably a better way.

The intent is that customers can browse the website to get our latest information, that staff can use the website like an app and use it to add/edit/remove products and enter inbound customer enquiries.

This means there would be some data in the db that we wouldn't necessarily want to be public. With sql injection and such to worry about, I turn to the hive for advice. I'm not so worried about maximum security from theoretical attacks (it's not that important), but certainly would like to cover my bases w.r.t. realistic security threats.

It's currently a paper based office, and information is seriously in need of some centralised consolidation - which is why I'm trying to do it using one interface which does a lot of simple but useful things automatically. It's web based because I don't have the GUI skills to program a custom app, and certainly not in Windows.

Two thoughts that occur are below, but other suggestions are more than welcome as I'd really like to get this done!

Store private data in separate databases, with separate users. With the htaccess restriction on the staff side, would this provide enough security for most business needs? Would it work, and if so how much would it complicate the programming?

Keep private data off the web entirely, have the computer running XAMPP or something and a copy of the website (with staff pages). Set apache to deny all incoming connections etc. To update the public database, allow connections outbound to the hosted web server and allow only inbound connections from localhost and our IP on the host. (Or any other straightforward way to sync 2 databases in this situation.)

posted by BishopsLoveScifi to computers & internet (8 comments total)

GRANTs and views in the database. Tie the GRANTs to (at least) two different database users ("logins"). Allow database connections to the internal user only from internal hostnames. Allow database connections to the external login from internal hostnames and the DMZ. Tables that are only for internal use are only GRANTed to teh onternal user. Tables with some columns that are external-access and some with internal only access have corresponding external-facing views that containing only external columns.
posted by orthogonality at 3:19 PM on August 28, 2007

Oh, and do the network stuff too. Suspenders and belt. But copy the db is a loss, and partitioning it almost certainly is a loss.
posted by orthogonality at 3:28 PM on August 28, 2007

Many thanks for the info Orthogonality! I had no idea you could do different views for different users on the same table. It would certainly simplify matters and thinking about it, could make a couple of other features much easier to implement....
posted by BishopsLoveScifi at 5:36 PM on August 28, 2007

It's hard to say what is the best solution without knowing what your skill level is, and what sort of structure your app and code use.

In general .htaccess-based restrictions are actually pretty good as they are implemented at a server level and it's far less likely that you will leave gaps.

With SQL permissions you could easily limit access to tables, between SQL users, but that will require that your admin and client interface apps use seperate database logins, which shouldn't be too hard.

Make sure you use basic anti-injection stuff with all your SQL queries. And separate your code so that the client-facing code simply doesn't have any queries or functions that operate on data you don't want them to have access to.

Throw a little 'security by obscurity' into the mix as well, if there are tables you don't want people to find, don't name them in the most obvious way - so instead of 'creditcards' have 'cc_data' - that way, if someone does manage to inject SQL they are less likely to be find things by just by trying the obvious things.

Another technique I have seen involves a db query handler that take an arguement telling it what to expect (ie, what columns should be returned) if what it gets doesn't match that, then it errors. So if an additional statement were injected, it wouldn't be returned anyway.

It depends how private this data is I guess. All the application security in the world isn't going to help if someone roots the box. I would possibly think twice about storing sensitive financial records online.

If you want to go for the offline/online model with a local app updating a remote site, you can look at SQL replication to share tables, or XMLRPC functionality in PHP to update the remote site from your internal site. If you do go that way, I'd think about putting a dedicate Linux install on a spare PC somewhere for it, as PHP/Apache/MySQL in Windows can be ever-so-slightly different at times and catch you out unexpectedly - if you're used to the way it operates in Linux, it might be worth keeping it that way.
posted by sycophant at 5:41 PM on August 28, 2007

Excellent advice offered already, but I'd add one thing. Write two separate apps. Write your customer-facing app, use a login that allows only viewing of appropriate data. Design only customer-facing features into this app.

Write an entirely separate application to access administrative functions. Don't share code, don't share logins, don't share state or sessions. Be diligent (dare I say religious) about this separation. In addition to being the Right Way to handle the security, it'll make eventually splitting up the systems' hosting much easier should that come to pass.

We use symfony here which makes starting a second project very easy, but any framework will help you keep apps separate.
Full disclosure: some of my co-workers have contributed code to the symfony project
posted by Skorgu at 6:00 PM on August 28, 2007

Hey Sycophant,

Don't worry - no financial data would be stored online, I always steer clear of that particular minefield....

At this point I am leaning towards Ortho's suggestion, with public and admin sides implementing different users as you mentioned, plus the htaccess. It seems pretty secure, and shouldn't involve many code changes at all from the way it is currently structured.

I'm not likely to go for the online/offline model now but thanks for the tips - I'm sure they'll be useful if I do :)

The db query handler sounds interesting - something to think about when I'm making my reusable code snippets.
posted by BishopsLoveScifi at 6:13 PM on August 28, 2007

If you end up making two apps, it can be worth writing base classes and extending the logic for the two apps from them - that way you can share some code (that is best shared) and maintain them easily.
posted by sycophant at 7:08 PM on August 28, 2007

sycophant writes "Throw a little 'security by obscurity' into the mix as well, if there are tables you don't want people to find, don't name them in the most obvious way - so instead of 'creditcards' have 'cc_data' - that way, if someone does manage to inject SQL they are less likely to be find things by just by trying the obvious things."

Better (this is untested, so test it thoroughly before using, but shold in principle do the trick):

create view cc_data as select * from creditcards;

revoke all on creditcards from public_user, private_user;

Now they can only access the (obscurely named) view, but maintainers of the schema don't have to play guessing games.

Even better:

create table creditcard ( id int not null primary key auto_increment, card_number char(16), card_number_hash) ;

grant select( id, card_number_hash) on creditcard to 'private_user'@'localhost';
/* only private user, only those two columns, only if logged in locally */

revoke select(card_number) on creditcard from 'private_user'@'locahost';

delimiter ;;

create trigger creditcard_before_insert before insert on creditcard
foreachrow begin
new.card_number_hash = one_way_hash( new.card_number) ;
end;;

delimiter ;

Now, to verify a card number matches one in your db, you hash the candidate, and compare it against card_number_hash. No one (except root) can see the actual card number. Writing one_way_hash is left as an exercise; in real life, you'd consult a crypto/security expert and use his.

(Oh, your card member agreement probably mandates you not card numbers anyway. But the same technique can and should be used to secure passwords, names, addresses, and phone numbers.)
posted by orthogonality at 11:50 PM on August 28, 2007