Hey-O! How’s it going?
Today’s case-study is about a subject we’ve never discussed before (or maybe a little bit) – proper & secure Database management.
So Databases, we all use them. SQL-based or not, we need some sort of non-volatile mechanism to save our data.
Whether you like it or not, currently, the SQL-based databases (MySQL, MS-SQL etc.) are still the most used databases in the world, and a lot of companies use them as their main storage mechanism. Long live the Structured Query Language! (no;-)
So- properly managing & controlling the database. I know, you’re thinking: “What the hell does this guy want? Its so obvious to manage and control my DB!”. Shut up and read!
First, let’s talk business: I have seen “more than a few” companies that don’t know how to control their own database(s):
a. The database connection string is known to a lot of other mechanism.
b. There is only one user – the root one – and every mechanism use it.
c. Even if there are a few users – one for each mechanism – all of the users have basically the same permissions set.
d. There are no DB backups. EVER!
e. And more horrifying things that I won’t say, because there might be children reading these lines, and it’s bed time.
The database is one of the most holy mechanisms in the application. It doesn’t matter the type of data it stores – it should be well treated.
A well-treated DB (Database)
First, let’s set things straight – “well-treated DB” does not mean a “suffering from obesity DB”. This case-study will not discuss the type of DB collection that your application should use, rules to not flood your DB and the advantages and disadvantages of using an SQL-based DB.
This article will highlight the risks of improperly handling your DB by showing you a real life example, and will supply some fundamental guidelines to keep your application more safe.
A very known Real Estate company, which it’s name we cannot disclose (and we respect their decision) suffered from some of the horrifying cases I described above: Their connection string was known to a lot of mechanisms, they had only one, fully-privileged root user and they didn’t have automatically periodically backups.
They had a main production DB which had a few tables. The main table was ‘user’ – a table which, among other stuff, held user Id, username (which was an email address) and salted password.
The email address was the users main identifier, and it could have been changed/replaced by the user. The change took place immediately, and until the user entered a confirmation link in the new email address he supplied, he wasn’t able to execute any “massive” action on the application, accept for information fetches. Which means – the user was still able to see his own object and data on the application.
So far so good- although the lack of awareness to the mentioned horrors (same CS, root user, no backups) – no SQL injection was possible, no CSRF was found, and the code was pretty much secured. Accept for one thing – It was not possible to supply an already existing email address when signing up, but it was possible to change email address to an existing one.
“So what?”, “What is the impact”, you say
Well, first I also thought: Meh, not much. But I was wrong. Very wrong.
When the DB had 2 rows with the same email address in the main table- it went crazy. Actions and data which was relevant to one email was relevant and visible to the other!
For example, the query to view all private assets which are related to that email looked very simple, like:
SELECT * FROM Assests WHERE EmailAddress = ‘<EMAIL_ADDRESS>’;
And resulted with private assets related to that TWO emails. An attacker could have changed his email to a victim’s one and then leak highly valued, private data.
When the company & us examined the code, we understood that another mechanism was responsible for changing the email address – and there were no existing checks at all. A simple mistake which could have led to a major disaster
So… give me your f-ing guidelines already!
This issue could have been easily prevented. The company agreed that this is a simple logic flaw. Maybe the programmer was tired. And the code reviewer(s). And the QA. I don’t know…
0. So the first guideline is to always drink coffee while writing such sensitive features. Or coke. Definitely not beer. Don’t ask.
1. The second one is to always have one and only DB managing mechanism. Write a simple, public & shared DB wrapping mechanism that every other mechanism in your application will have access to. Don’t have a DB util to each feature, and certainly don’t allow non-related mechanisms to supply you the SQL query.
2. Don’t be naive. Check each given user data for malicious characters. Integrate your existing sanitation engine to your DB managing mechanism.
3. If you can – never delete something from the DB. Remember: restoring is harder than resetting. It is best to simply have an indication that a row is ‘inactive’ instead of deleting it from your DB. Don’t be cheap on space.
4. This one is pretty obvious: Don’t allow non-certified users to execute requests that influence the DB.
5. Have a periodically, 3rd party service that backs up your DB every x hours. Provide this service a different user with only SELECT privileges.
Those 5 “gold” guidelines (and #5 is the most important, to my opinion) will assure you won’t have a heart attack when things will go wrong.
We’ll talk about having a Defibrillator later.