CODEWITHSUNDEEP
mysqldatabasesecurity
Jose Lopez Garcia
Jose Lopez Garcia

Reputation: 982

Storing critical data into MySQL Database

This is not a programming question at all. Let me explain: I am creating a game, for which I need a Database that will store users registration data (e.g. username, email, password). If a user wins, he/she will earn Cash Points. The user will be able to exchange his/her Cash Points by real money, so I consider that the Cash Points is a very, very critical data. My question is: would you store the Cash Points data in the same "users" table? Or would you create a new table named "cash" (for instance) and then store it into? (from a security point of view)

Thanks

Upvotes: 1

Views: 309

Answers (4)

SQL.injection
SQL.injection

Reputation: 2647

Well you should create a database design that resembles a bank balance. That way you can keep track of all changes, this is

create table balance
(id int,
debit numeric (10,2),
credit numeric (10,2),
balance_before numeric(10,2),
balance_after numeric(10,2),
timestamp datetime,
user_id int,
description varchar(32),
...
);

Upvotes: 1

tadman
tadman

Reputation: 211590

It's best if you implement a simple ledger system whereby transactions are recorded against the user's account as credit or debits, and the account itself has a total that can be audited.

You must keep a record of transactions performed if you're involving cash or cash-like currency. If someone complains about missing money you need to be able to verify every transaction that affected their balance and uncover any discrepancies.

This also presumes you're making use of transactions to avoid committing incomplete transactions. The balance adjustment and transaction record should be part of the same transaction.

As always, test this as ruthlessly as you can.

Upvotes: 1

xionutz2k
xionutz2k

Reputation: 729

Cashpoints definitely in a separate table but not from security perspective. It's better from design perspective and will allow you to keep a log of CashPoint changes for each user.

Upvotes: 1

Jay Patel
Jay Patel

Reputation: 647

It is considered bad design if you store cash points in the users table. Tables should be normalized. You should store cash points in a separate table and use the userId as the foreign key in that table. You could look into encrypting Cash Points table data as well.

Upvotes: 1

Related Questions