Creating Secure PM's??

[doubledee]

Well some people would look at a 'large' site and split their database into several smaller databases to handle each part of the site.

 

For example, and online store with a forum might have:

 

User Info: Containing user information like usernames, real names, contact information, personal details, etc.

Shop: Containing products, product info, prices, comments, etc.

Forums: Containing forum topics, posts and replies, etc.

Purchases: Purchase history, invoices, orders, etc.

 

Following?

 

Again, I don't know if this is widely used or not.. Maybe someone who worries about security more than I can chime in?

 

Interesting idea.  Yes, I follow you.

 

 

Debbie

 

[l0gic]

Come to think of it, I actually have this implemented in a game. One database handles all the logins. And then there's a database for each 'game world'/'realm' after that and you can hop between them. And each of those game worlds are populated with items from an item database.

 

However I didn't do it on purpose to be more secure, that was just they way I could see it working when I developed it.

[AyKay47]

It's called referential integrity, and it should be practiced.

[l0gic]

It's called referential integrity, and it should be practiced.

 

Well, that's relationships between tables.. I'm talking complete different databases.

[AyKay47]

It's called referential integrity, and it should be practiced.

 

Well, that's relationships between tables.. I'm talking complete different databases.

 

you think the concept is any different?

[doubledee]

It's called referential integrity, and it should be practiced.

 

Well, that's relationships between tables.. I'm talking complete different databases.

 

you think the concept is any different?

 

Referential Integrity has absolutely NOTHING to do with multiple databases.

 

From IBM...

Referential integrity is the relational property that each foreign key value in a table exists as a primary key in the referenced table.

 

 

Debbie

 

[l0gic]

It's called referential integrity, and it should be practiced.

 

Well, that's relationships between tables.. I'm talking complete different databases.

 

you think the concept is any different?

 

I would imagine it would be slightly different, same idea though.

 

As I understand it for referential integrity to hold in a relational database, any field in a table that is declared a foreign key can contain only values from a parent table's primary key or a candidate key. I may have missed it but I have never seen a way to establish PK/FK relationships between different databases, not natively anyway.

[MasterACE14]

you could of course, make sure you have everything nice and secure so it is unlikely(but not impossible) that you will be hacked in the first place. As for DB admins viewing stuff they shouldn't... if they can't be trusted, why would they be a DB admin in the first place?

 

My two cents.

 

-Ace

[kicken]

I may have missed it but I have never seen a way to establish PK/FK relationships between different databases, not natively anyway.

 

You can reference a table in another db using dbName.tableName syntax.  eg:

 

mysql> create database t1;
mysql> use t1;
mysql> create table items (id int auto_increment primary key, name varchar(20)) engine=innodb;
mysql> create database t2;
mysql> use t2;
mysql> create table users (id int auto_increment primary key, name varchar(20)) engine=innodb;
mysql> create database t3;
mysql> use t3;
mysql> create table inventory (userid int not null, itemid int not null, foreign key (userid) references t2.users (id), foreign key (itemid) references t1.items (id));
mysql> insert into inventory values (1,2);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`t3`.`inventory`, CONSTRAINT `inventory_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `t2`.`users` (`id`))

 

 

Separating out the tables into separate DB's wouldn't help too much with keeping the data safe though unless you also setup separate user accounts for each db with restricted access.  If someone is able to steal the data from the database, it's likely they did so by using sql injection to run arbitrary select queries or found another way into the DB.  In either case they could get the data from another db by just doing

SELECT * FROM db2.whatever;

 

If you want to encrypt your messages, then look into mcrypt and setup your key in a file somewhere, then just encrypt before storing and decrypt before displaying.  I don't think it's really necessary of worth-while in the context given but if you want to do it for fun and learning sakes go ahead.

 

[cpd]

After reading everything and arriving at kicken's I've noted he's written pretty much what I wanted to say so I won't go on but will emphasise a few of his points.

 

There is no purpose to splitting your data across multiple databases - other than perhaps organisation issues which in my opinion is not a satisfactory reason - unless you create specific user accounts for each database and even enforce permissions on specific tables. Having previously juggled many databases in a single system, I ended up with around 7 or 8 databases all of which had their individual user accounts. It can be seen as an added layer of security however, if someone can inject using one account they may be able to increase their permissions by logging in as a higher level user through injection. For this reason, you should start at the first point of contact the "hacker" will have with your application and ensure its fully secure before moving on to "securing your data"; in your case, any input should be processed appropriately.

 

You can then consider encrypting the data inside your database. If you want small steps Debbie, start at the front and work your way back. Don't start at the back and work your way forward if that makes sense? SQL Injection is a more pressing issue in my opinion.

 

All that said and done you may actually already have the best damn security on the planet but the point still stands.

 

P.s. Referential integrity can't be applied across databases. Furthermore, if data A and data B have a relationship which requires referential integrity, they shouldn't be in separate databases, the databases should be merged or data transferred.

[AyKay47]

It's called referential integrity, and it should be practiced.

 

Well, that's relationships between tables.. I'm talking complete different databases.

 

you think the concept is any different?

 

Referential Integrity has absolutely NOTHING to do with multiple databases.

 

From IBM...

Referential integrity is the relational property that each foreign key value in a table exists as a primary key in the referenced table.

 

 

Debbie

 

As noted, you can most certainly reference tables between databases, and if the databases are to relate in any specific way, you need to.

Please do not read a quick article on a certain subject and pretend that you know what you are talking about, you don't.

I was responding to a suggestion of using separate databases, which in this case would not benefit you in any sort of way.

To your question, encrypting data to be inserted into a db table is a waste of resources and db space.

Simply prevent SQL injection in the first place using the methods that you have already been given.

[doubledee]

If you want small steps Debbie, start at the front and work your way back. Don't start at the back and work your way forward if that makes sense? SQL Injection is a more pressing issue in my opinion.

 

I think that is the best advice so far.  And simply stated as well!

 

I have been working very hard to build a secure website, but considering that I am a newbie, there is a lot to learn!!

 

I think my php code is pretty solid, and my next focus is on learning as much as I can about Linux and Server Administration.  (I know that is a big hole in my security!)

 

Then as I get those under handle, maybe I can come back to this encrypted data issue.

 

Thanks,

 

 

Debbie

 

[KevinM1]

As a headstart for server security, look into iptables.