Databases and backups

[sunama]

We have to remember that a DB can be hacked/attacked from different angles.
Without spending huge sums of money, we will never be able to keep out the most determined and inventive hacker.
What we can do though, is create a system which will thwart all, but the best hackers.

Initially, my business/idea/service will be quite low key. While I am a small entity the best hackers are unlikely to attack me. As the entity grows in size and transactions increase in number and in value, I shall upgrade the protection/security (which will obviously be increasingly expensive, the stronger the system).

What I can say is that at present there are many SMEs who use the most basic and simple data storage systems. You can even buy off the shelf online shops, which use little to no security protection.

So, my aim is to release the first version of the DB system, followed by an upgraded 2nd version AFTER the amounts of money running through the system has increased in value.

 

[Pho]

Firstly, I'm not a DBA so the following might be rubbish advise.

We use the same method as AshenShugar1873 to backup our systems.

You really want to be doing all of your replication within your database product, and not rolling your own code for it - it's only going to end in tears. Fortunately you're using MySQL and so don't have to fork out £6k for SQL Server Standard to do replication .

Why not look at creating some scripts (hosted away from the live server if you want to be paranoid) which checks transactions made on your payment gateway against your database and checks for mismatches? This would work from both a fraud point of view (e.g. someone incremented their balance without actually paying) and if your systems went down you could use it to fill in the missing transactions again bringing your system up to date.

How are you systems hosted anyway, are they hosted on your own dedicated machines or is it with shared hosting?

 

[sunama]

Hi Pho,

Initially, both DBs shall be hosted on shared hosting.

Today, I have been thinking further of improvements.
Rather than creating the 2nd DB, by using a separate program, we shall update both DB1 and DB2, at the same time. In theory, both DBs should be created/updated equally and should be identical at all times.

What I shall then do is create a program which checks that both DBs are identical. If not, then more checks shall be carried out until the offending transaction(s) have been weeded out.

The account which belongs to those particular transactions are then automatically "locked" and an email is automatically generated for the admin to deal with.

I'm actually going to use a re-mix of sniffy's algorithm, to create a check-key system, so that even if someone alters the transaction "amount" after the initial transaction has already taken place (which would be the most tempting thing to do), the check-key should highlight this.

The other obvious thing which a hacker would do is add an extra (fake deposit) transaction. The hacker would need to update both DBs, otherwise my program will spot that there is an extra transaction in 1 DB. Again, this would "lock" the account which that transaction belongs to, until an admin unlocks the account.

Ideally though, I would like another way to ensure that the latest transaction which has been added to the DB, is correct/valid. What if the hacker manages to hack both DBs, at the same time and add the fake transaction in, while also knowing the check-key system? He can successfully add whatever amount, to his account balance.

 

[ZombieFan]

Firstly, I'm not a DBA so the following might be rubbish advise.

We use the same method as AshenShugar1873 to backup our systems.

You really want to be doing all of your replication within your database product, and not rolling your own code for it - it's only going to end in tears. Fortunately you're using MySQL and so don't have to fork out £6k for SQL Server Standard to do replication .

[edit] just read your latest post above which makes a lot of my stuff below void




I agree with this. You are only creating work for yourself if you try to roll your own system - and the chances are that it won't be as good as the replication system already built into mysql.

However, if you do want to give this a go (good luck!) then please don't rely on e-mail to transmit transactions. E-mails:

a) Are not a reliable way of transmitting information between computers - emails can not be received without the sender ever knowing.
b) Are not secure - a hacker will be able to easily intercept and spoof emails.
c) Cannot be guaranteed to be received in the order they are sent - this would be a disaster for your system, since a deposit transation followed by a withdraw could be received as a withdraw followed by a deposit. This would flag up a false entry.
d) Are slow - In a large database system, it's not uncommon to see hundreds of transactions per second being processed. Emails just won't handle this.

Also, your system is attempting to catch differences between the live database and a backup database. However, if someone has accessed the live database, then surely any updates they make will be e-mailed over to the backup database automatically just the same as a valid transaction? How can you determine the difference between a valid and non-valid database update?

 

[ZombieFan]

Hi Pho,

Initially, both DBs shall be hosted on shared hosting.

Today, I have been thinking further of improvements.
Rather than creating the 2nd DB, by using a separate program, we shall update both DB1 and DB2, at the same time. In theory, both DBs should be created/updated equally and should be identical at all times.

What I shall then do is create a program which checks that both DBs are identical. If not, then more checks shall be carried out until the offending transaction(s) have been weeded out.

The account which belongs to those particular transactions are then automatically "locked" and an email is automatically generated for the admin to deal with.

I'm actually going to use a re-mix of sniffy's algorithm, to create a check-key system, so that even if someone alters the transaction "amount" after the initial transaction has already taken place (which would be the most tempting thing to do), the check-key should highlight this.

The other obvious thing which a hacker would do is add an extra (fake deposit) transaction. The hacker would need to update both DBs, otherwise my program will spot that there is an extra transaction in 1 DB. Again, this would "lock" the account which that transaction belongs to, until an admin unlocks the account.

Ideally though, I would like another way to ensure that the latest transaction which has been added to the DB, is correct/valid. What if the hacker manages to hack both DBs, at the same time and add the fake transaction in, while also knowing the check-key system? He can successfully add whatever amount, to his account balance.

Now you've just got to make sure security is tight enough to prevent a hacker from calling the routine which updates both databases

 

[sunama]

[edit] just read your latest post above which makes a lot of my stuff below void

Indeed. But those points you made about emails not being a great medium on which to build a database on, are still valid and have taught me a few things.

What I do need is a way to verify each deposit. I'm not too fussed about the withdrawals (yet), but I am fussed about the deposits.

I want to think of a way to verify that each deposit is actually genuine, just in case a hacker managers to successfully add a fake deposit transaction, to both databases (eg. if he gets hold of the method which adds a new transaction (deposit), to the database).

 

[Pho]

Hi Pho,

Initially, both DBs shall be hosted on shared hosting.

See, I'd hazard a guess that shared hosting is going to be a much easier attack vector. If someone roots the hosting server you're screwed either way.

Rather than creating the 2nd DB, by using a separate program, we shall update both DB1 and DB2, at the same time. In theory, both DBs should be created/updated equally and should be identical at all times.

What I shall then do is create a program which checks that both DBs are identical. If not, then more checks shall be carried out until the offending transaction(s) have been weeded out.

The account which belongs to those particular transactions are then automatically "locked" and an email is automatically generated for the admin to deal with.

This just sounds like pure replication so just get MySQL to do that for you - I've never used replication in MySQL but I imagine it's pretty resilient to outages and things like that, things which would be a pain for you to code manually (e.g. you need to update both DBs but one is offline - you have a problem).

Replication should also mean you can scale it out to 100 DB servers if you want more easily, otherwise you're doing this in code:

for ($i=0; $i<100; $i++)
	mysql_query("UPDATE...", $conn[$i]);

...and that's not going to be quick, especially if some servers are offline.

I'm actually going to use a re-mix of sniffy's algorithm, to create a check-key system, so that even if someone alters the transaction "amount" after the initial transaction has already taken place (which would be the most tempting thing to do), the check-key should highlight this.

The other obvious thing which a hacker would do is add an extra (fake deposit) transaction. The hacker would need to update both DBs, otherwise my program will spot that there is an extra transaction in 1 DB. Again, this would "lock" the account which that transaction belongs to, until an admin unlocks the account.

Ideally though, I would like another way to ensure that the latest transaction which has been added to the DB, is correct/valid. What if the hacker manages to hack both DBs, at the same time and add the fake transaction in, while also knowing the check-key system? He can successfully add whatever amount, to his account balance.

To me this all seems to point to periodically auditing transactions against your payment gateways, of course, if that gets hacked you and a lot of other people have bigger problems.

Why not implement something like Benford's Law into your site to detect fraud? Apparently the banks use it so it must work .

More information and handy video

Indeed. But those points you made about emails not being a great medium on which to build a database on, are still valid and have taught me a few things.

MSSQL can do transaction log shipping (I'm sure MySQL can but I have no idea what they call it) which is kind of like your email idea - it dumps the transaction details into a file which another server can read and apply to itself. You could have them dumped into a read-only VPN/FTP folder which the other server picks up and processes.

 

[sunama]

See, I'd hazard a guess that shared hosting is going to be a much easier attack vector. If someone roots the hosting server you're screwed either way.

Indeed. I spoke with TSOHost who said that the only way to improve security on the DB is by having a dedicated hosting service. This way, they can add a firewall. However, we are talking about increasing costs here. And if we assume that we are dealing with 5-10 transactions per month, initially, this just doesn't seem worth it.

This just sounds like pure replication so just get MySQL to do that for you -

This not pure replication. Repication is good redundancy but useless when it comes to fraud detection.

By using the system which I am proposing, we have 2 DBs which in theory should contain identical data, at all times. If they do not then the admin is flagged.

Why might the 2 DBs have different data?
1. if 1 DB is "down", while the other is "online", only 1 DB will get updated.
2. if a hacker updates or alters 1 DB

In both cases, the admin shall be alerted. In case 1, the admin can make an "adjustment" to correct the difference, to prevent the same "error" from alerting the admin. In case2, the admin can lock the user's account and investigate what has gone on.

To clarify, if we use simple replcation, if the hacker decides to alter DB1, DB2 is automatically altered as well, thus defeating the ability to detect differences between the 2 DBs (as they are always identical).

To me this all seems to point to periodically auditing transactions against your payment gateways, of course, if that gets hacked you and a lot of other people have bigger problems.

I'm looking into this right now. I am going to use Paypal. Now paypal have an API to get transactions which have occurred. The easiest way is to have a program periodically get the transaction ids of transactions and then check the DB against them. If there are erroneous transactions, then we can alert the admin and lock the account. HOWEVER, this is only available on the Pro Account and seeing as I am going to be using Paypal Standard, this facility is not available.

Now, I have created a program which is able to read a webpage, so its still possible that I might get my program to load up Paypal's account page and then extract all the transactions which have occurred.

MSSQL can do transaction log shipping (I'm sure MySQL can but I have no idea what they call it) which is kind of like your email idea - it dumps the transaction details into a file which another server can read and apply to itself. You could have them dumped into a read-only VPN/FTP folder which the other server picks up and processes.

I may use emails to validate the transactions, but the problem here is that we are involving too many servers.
Server1. web host and DB1 host
Server2. DB2 host
Server3. email server
Server4. local server running my program which receives the email

The more servers we have the greater the chance of a point of failure. If somebody has paid money into their account, if any of the above 4 servers are out, their transaction will show as pending = not good. The only way that a transaction is considered valid, if is all the servers involved are working.

The best balance is to have 2 separate databases on Server1 (DB & web host) and Server2 (DB2).

Also consider that if somebody wants to withdraw money, there are additional checks which occur. So, lets assume that a hacker successfully bypasses all the security measures before withdrawal. He may still be caught by a human, at the time of withdrawal.

I shall also be using Paypal's IPN system (ie. when a transaction is successful or cancelled, paypal automatically generates a message which is sent to a url/server). This is another way to get hold of the details of the transaction and perhaps hold this information on a separate server.

 

[Goksly]

I don't get that one statement... If we've got more servers there are more points of failure? The idea of more servers is failure of one or more points should have less of an impact.

At work we have 4 servers, but on each server we have redundant services that are on the other servers, so if the primary one goes down, were alerted but the second one kicks in for the meantime.

 

[Goksly]

Just looking at some of the solutions and they seem all a bit... Megh? There is what looks like manual implementations of replication, then talks of security concerns and then comments of emailing information?

If hackers *really* want to get onto your system and they are good, then they will (dod just said its a war that cannot be won).

Personally I'd probably find off the shelf solution (payment / encryption software - someone will have this out there, PayPal like someone else suggested?). Follow normal / typical hardening (strong passwords, firewalls etc if looking after your own servers). Have one db server with a view to cluster if demand requires it... But you'll be surprised at how much volume one server would take.

 

[sunama]

I think I did state earlier that any security system can be compromised. You just have to look at as many different ways that this may happen and close the doors. Some doors will never be closed.

Emails are not going to be used. This was just an idea thrown up.

Paypal (as far as I am aware), do not have any software which updates your DB. Their part ends once the user has made payment and they have redirected the user back to your website. My problem is 'what happens if the user never makes any payment to Paypal and directly manipulates the DB on which the account data is stored'? In this scenario Paypal are not actually involved.

Over the last hour, I've been looking at PayPal API (in conjunction with their express checkout service, which has no monthly fees). And I am now looking to use their transaction search facility (API) to verify the transactions which are in the DB.

In an earlier post I stated that the big problem will be if someone gets hold of the method which updates the DBs. By using an auditing program to check the transactions in the DB, against the list held on Paypal servers, we shall be able to find any fake transactions. This goes some way to solving that problem.

The auditing program shall check the 2 DBs at regular intervals and check is everything is "ok" or if an admin needs to be notified (and if the account should be locked).

An additional (transactions history) check also occurs when a user wishes to withdraw money.

For sure there are companies who have come up with much better ways to avoid fraud, but I think for my purposes I have come up with a simple solution which is ample (involving 2 DBs, held on separate web servers, an auditing/DB-verifying program, which is held on a 3rd server and then a final check when the user wishes to withdraw money).

 

[Goksly]

Still sounds over complicated. Personally I'd just have a single transaction table which is audited to a table that cannot be updated / deleted; only inserted.

Every time a transaction is placed, it must be done through predefined web servers and it's own unique account(s). When rows are updated you can use in build commands (MSSQL has them, as does oracle, so I'd assume mysql does too) to get the users adding that row.

For a hacker to successful alter their balance they'd have to compromise your system (as db wouldn't be directly accessible from outside), and get the username / password for the web server users accounts. To help combat this, you could use encryption between the web server and the db server (and if the account on the web server was a service only account... the hacker would need to have broken multiple accounts).

All transactions would be atomic and have the right business logic build around them to ensure non-duplicate transactions, can't over draw etc.

Just my 2p

 

[sunama]

Hi, thanks for your input.

Personally I'd just have a single transaction table which is audited to a table that cannot be updated / deleted; only inserted

"Which is audited to a table that cannot be updated". By this do you mean that the auditing program will have to "extract" new transactions from the main DB and then add them to the audited DB?

When rows are updated you can use in built commands to get the users adding that row.

What do you mean by "get the users adding that row"?

For a hacker to successful alter their balance they'd have to compromise your system (as db wouldn't be directly accessible from outside), and get the username / password for the web server users accounts. To help combat this, you could use encryption between the web server and the db server (and if the account on the web server was a service only account... the hacker would need to have broken multiple accounts).

The DB(s) must be kept on a web server (I don't want to bring it in-house), due to the reliability of the internet connection. Once I can afford to host the DB on a dedicated server (off-site, with a hosting company), then this is viable, but until then I do not want to host the DB myself.

All DBs are held on a shared web host, with no Firewall.

 

[Goksly]

Firstly, let me just be clear (although its probably already apparent) - I'm by no means a security expert. I'd also like to take this opportunity to state I've only been in a position of complete control of servers (i.e. hosted internally with the network guys sitting right next to me).

By audited I mean a trigger which places any information into another table. For the purpose of this thread, those might just be updates / deletes to the main table (which should never happen? updates would be done by inserting a new row to correct the balance I'd assume, even in the event of a mistaken original value?).

By the user I mean system_user (sql server)... this is the user that is logged on / executing the command. As it's not a value sent to the server, it would be difficult to spoof (i.e. a hacker couldn't just set a value in a table - or if they did (editing a row) it would be picked up by our audit table).

Ultimately I guess I'm trying to say - keep it simple! Avoid multiple databases, instances etc. Avoid external programs to replicate data. The inbuild security of operating systems / databases is pretty good. Most security breaches you hear of are stupid ones (garry mckinnon / nasa - someone had a password of 'password' iirc).

As you're storing transactions just make sure your data is encrypted so if someone gets at it it's going to be a nightmare. Make sure all your passwords are strong. Make sure your business logic is solid and everything is atmoic when it comes to writing to the database.

Finally, especially in the initial stages... make sure you've given the administrators the tools to easily view data to authorise transactions (i.e. withdrawls... quick history check etc).

 

[sunama]

By audited I mean a trigger which places any information into another table.

This would surely be done by an external program, right?
And if so, we are back to the original issue of having a 2nd DB/table which is duplicated using an external program.

Or are you thinking of an automatic replication, performed by SQL (without any auditing/checking)?

Avoid multiple databases, instances etc. Avoid external programs to replicate data.

Without having a second DB, we have no redundancy, though? That was the initial problem, right at the start (before we introduced the problem of security). If we were to replicate a database in another location (I mean perfect copy), then this has the problem that if a hacker somehow manages to update the main DB and changes a deposit amount, this will automatically be replicated and change the 2nd DB. This is not good as when the auditing program checks differences between the 2 DBs, no differences will be found.

The inbuild security of operating systems / databases is pretty good.

Agreed. I want to use as many in-built features as possible, without having to custom-code myself (which is time-consuming).

As you're storing transactions just make sure your data is encrypted

Agreed.

 

[Goksly]

Triggers are internal to the database - built in (or at least they are by SQL Server). They happen low down so are guarenteed (if they fail, the initial data action also fails) and also decent performance.

By using what I described above (user accounts and triggers), if a hacker adjusted a record you'd see that in the audit table (which couldn't be updated by anyone). For redundancy I'd use a build in feature (sql server has clustering, mirroring, snapshots, log shipping etc). The main reason for using these in build types is it does what it was designed for; high availability... nothing more nothing less. You shouldnt (imo) try and have a solution that you use for HA and for auditing (you'd implement two separate solutions - one for each problem).

 

[sunama]

Ok. I understand. I shall certainly look into this method.
Much appreciated.