Rss

Archives for : development

Databases: How to securely store sensitive information

One problem that many people have is figuring out the best way to store sensitive information that needs to be retrievable, such as credit card numbers, securely in a database? The solution for passwords is simple: hash (and salt) them. That’s fine, because you don’t ever need to retrieve a password, the user has to input it and you compare the hashes. For credit card information, doing something like that would be, well, absolutely pointless, since it would defeat the purpose of actually storing the credit card information in the database.

How it’s already done

It’s already possible, to some extent, to encrypt credit card information. You could use some private/public key pair someway, or you could just encrypt everything with the same symmetric key. The problem with this is, the private key or encryption key needs to be stored somewhere, and the web server needs to be able to access it. So it may work if your database and web servers are different and your database server gets compromised, but if your web server gets compromised as well (remembering that many smaller websites will use the same server as the web server and database server,) then it’s pretty pointless.

Is there a way that the web server can decrypt it without having any human interaction (e.g. to type in the encryption key) except for the customer? I believe there is.

Alternate Way

So I got to thinking, how could this possibly work? Then I thought of this: take 2 hashes of the password (with 2 different salts,) then use one to store in the database for log in purposes, and then use the other one as a symmetric key for sensitive information and store it in a cookie.

It would go something like this:

  • Password = P
  • Salt1 = S1
  • Salt2 = S2
  • Hash1 = Hash(P & S1)
  • Hash2 = Hash(P & S2)
  • Hash1 and Hash2 are both generated when a user logs in.
  • Hash1 is stored in database, and is used to compare log in.
  • Hash2 is stored in a cookie on the user machine and accessed when needed.
  • Credit Card Info = CC
  • Secure Credit Card Info = Encrypt[Key: Hash2] (CC)
  • Store secure credit card info in the database.

Down Sides

There are of course down sides.

  • It’s extra work to have to do, in that you need to generate an extra hash each time the user logs in.
  • It won’t work  they forget their password. You can use a good password recovery method, but then they’ll need to re-enter all the information you have encrypted.
  • Cookies aren’t necessarily the most secure method of storing info, but at the same time someone would have to compromise your web server and database server whilst that user was logged in to be able to access their cookies, or manage to get into the customers system (but if that happens, it’s not your problem.) Also, make sure your web server doesn’t store a cache of cookies somewhere, or store them in a log file. That would also defeat the purpose (well, it would still make it harder for a hacker to figure out, but once they do it’s all gone.)
  • You won’t be able to do subscription payments, since the customer will need to be logged in to do it. I personally think this could be good, as I don’t like companies automatically charging my credit card, a solution would be for them to email me and have me login to approve each payment.

Also remember, do everything over HTTPs! There’s no excuse to not do everything over HTTPs these days. You should also never rely on the encryption to store data securely, rather you should focus on preventing data getting into the wrong hands… encryption is for if your database does get compromised, you can minimise the damage done.

Android: SQLite Database Upgrade

Note: I haven’t edited this since writing it, or fully tested the code that I present (that said, it’s heavily based on one of my apps, it’s just I’ve reduced the number of tables being made.) I will update the tutorial later on when I get a chance.

There are quite a few tutorials out on the web of how to make a SQLite database within an Android application, but not so many that deal with proper upgrading of the database.

Over time the requirements of the database within your application may change, this is almost inevitable if your application is in active development and you’re constantly adding new features. Properly upgrading the database in your app is important, because if something goes wrong your application will have unexpected behaviour (such as crashing) or you may loose all your user data and have to start over.

Version 1 of your database
If you have followed one of the numerous tutorials online on how to setup a SQLite database in your app, you most likely have some form of DatabaseHelper class that extends SQLiteOpenHelper. It may look something like this:

package com.example.sampledb;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import java.text.SimpleDateFormat;

public class DbHelper extends SQLiteOpenHelper
{
    private static final String DATABASE_NAME = "mysampledb";
    private static final int DATABASE_VERSION = 1;

    private static final String DATABASE_CREATE_SAMPLE_TABLE = "CREATE TABLE tblSample" +
        "(" +
        "   _id integer primary key autoincrement," +
        "   name varchar(32)" +
        ");";

    public static final String DATE_FORMAT = "yyyy-MM-dd HH:mm:ss.SSSS";
    public static final SimpleDateFormat SIMPLE_DATE_FORMAT = new SimpleDateFormat(DATE_FORMAT);

    public DbHelper(Context context)
    {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase database)
    {
        database.execSQL(DATABASE_CREATE_SAMPLE_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
    {
        // Do nothing for now
    }
}

Each time it is called, it will check to see if this database and version already exist. If the database doesn’t exist, it will create the database by calling onCreate(), and it will store the database name and version number with it. If it does exist, but the version of the current version is lower than what is defined in DATABASE_VERSION, the onUpgrade() method will be called.

Version 2 of your database
The question is, what is the best way to handle an upgrade? I had a think about the way it worked, and I decided the best way would be to loop through all the different versions and apply the required changes.

Let’s say you wanted to add a field to “tblSample” called “address”? The new statement to create the database table would look like:

CREATE TABLE tblSample
(
    _id integer primary key autoincrement,
    name varchar(32),
    address varchar(128)
);

Which you will obviously what you want to change your “DATABASE_CREATE_SAMPLE_TABLE” variable to, since you want all new creations of the database to be up to date. You also want to use the onUpgarade method(). The way I have implemented the onUpgrade method is as follows.

package com.example.sampledb;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import java.text.SimpleDateFormat;

public class DbHelper extends SQLiteOpenHelper
{
    private static final String DATABASE_NAME = "mysampledb";
    private static final int DATABASE_VERSION = 2;

    private static final String DATABASE_CREATE_SAMPLE_TABLE = "CREATE TABLE tblSample" +
        "(" +
        "   _id integer primary key autoincrement," +
        "   name varchar(32)," +
        "   address varchar(128)" +
        ");";

    public static final String DATE_FORMAT = "yyyy-MM-dd HH:mm:ss.SSSS";
    public static final SimpleDateFormat SIMPLE_DATE_FORMAT = new SimpleDateFormat(DATE_FORMAT);

    public DbHelper(Context context)
    {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase database)
    {
        database.execSQL(DATABASE_CREATE_SAMPLE_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
    {
        for (int i = oldVersion; i < newVersion; i++)
        {
            switch(i)
            {
                case 1:
                    db.execSQL("ALTER TABLE tblSample ADD address varchar(128)");
                    break;
            }
        }
    }
}

How will this work? Well, quite well from what I’ve found, since it will work for any upgrade… if you always use this method, it will work for upgrading from version 1 to 9, or from version 4 to 5. Basically, it will loop through all the previous versions starting at the current version.

So, if my phone is on version 1, and the app needs to upgrade to version 2, this loop will iterate one time, with the value of “i = 1.” The switch case statement should, for each version number, execute the required statements to upgrade from that version to the next. So, in this case, “case 1:” will execute the required statements to upgrade from version 1 to version 2.

Potential Problems
The biggest problem that you may face is if your “fresh” database creation statements get out of sync with all the update statements. What should always be the case is, if you start with version 1, and run all the update statements to get it to the newest version, the schema of the database that has been upgraded should match exactly the schema of the database if it were created from the newest version.

In short, the best way around it is only make small changes to your database at a time, and make sure you apply the changes to both the creation statements and the update statements such that they match. I would also suggest always using string literals in the update statements rather than refer to any variables. This would definitely be the case if you need to create a new table. It would be tempting to just refer to the DATABASE_CREATE_NAME_TABLE variable in the upgrade section, but remember that this variable should be creating the newest version of the database, and in your upgrade statement, you need to be aiming to move to the version after what is specified in the case statement. So whilst it will work at first, when you change that table, it may mess up.

A potential solution
One potential solution I have thought of that may work is to keep the create table statements the same such as they were in version 1. Then, when you create a fresh database in the onCreate method, you create all the tables as they were in version 1, you call the onUpgrade method with the variables (database, 1, DATABASE_VERSION). It would look something like the following:

package com.example.sampledb;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import java.text.SimpleDateFormat;

public class DbHelper extends SQLiteOpenHelper
{
    private static final String DATABASE_NAME = "mysampledb";
    private static final int DATABASE_VERSION = 2;

    private static final String DATABASE_CREATE_SAMPLE_TABLE = "CREATE TABLE tblSample" +
        "(" +
        "   _id integer primary key autoincrement," +
        "   name varchar(32)" +
        ");";

    public static final String DATE_FORMAT = "yyyy-MM-dd HH:mm:ss.SSSS";
    public static final SimpleDateFormat SIMPLE_DATE_FORMAT = new SimpleDateFormat(DATE_FORMAT);

    public DbHelper(Context context)
    {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase database)
    {
        database.execSQL(DATABASE_CREATE_SAMPLE_TABLE);
        onUpgrade(database, 1, DATABASE_VERSION);

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
    {
        for (int i = oldVersion; i < newVersion; i++)
        {
            switch(i)
            {
                case 1:
                    db.execSQL("ALTER TABLE tblSample ADD address varchar(128)");
                    break;
            }
        }
    }
}

I haven’t tried this, and I don’t think it’s the best way to go. If it does work, it would reduce the potential of a miss match between the same version if it were created fresh or upgraded, since it’s just creating the first version and upgrading it to the newest version.

The main problem with this I think is that it doesn’t force you to think about the structure of your database, and could easily turn you into a lazy programmer (like having a catch clause that doesn’t do anything.) The other problem I can see is that it’s hard to see at a glance the structure of your database in the newest version, since you will need to process all the upgrade statements to see what it really looks like.

It’s your choice. This way should work, and will pretty much eliminate any possibility of a miss match between a fresh and upgraded database, but at the same time you lose the ability to see the structure of your database, and it has the potential to make you lazy.

1000 downloads for Virgin Mobile Australia

Well, I’ve reached 1000 total downloads. Can’t say that I was expecting it to reach that, especially when there’s two on the market (as I’ve mentioned, I didn’t actually find the other one until I was half way through making my own version.)

Moving forward to 10000!

1 more Android app and improvements to Virgin Mobile app

In just a few days I will have finished all my exams for this semester, and will have a good week and bit before I go back to work for the holidays. This means I’ll have some time to work on both the Virgin Mobile app, and a new one. The new one’s a secret for now, but it is another Australian based one.

That said, I’m a firm believer in making quality apps, and there’s definitely a lot of work that I need to put into the Virgin Mobile app, so I’m going to have a good day fixing bugs and making general improvements before I make too much effort on the new app. I’d rather have a handful of quality and well supported apps rather than a large number of half-hearted attempts. So all the ones that I do publish, I do plan on continuing to improve them, both by fixing bugs (most important,) making a better user interface (a new one for Virgin Mobile is coming soon) and adding new features (after the other two things are done.)

PC Case Gear: Tracking Link Grease Monkey Script

PC Case Gear is one of the best, if not the best, specialist computer hardware and modding stores in Australia. I’m more than happy to buy something from there even if it’s slightly more expensive than other stores (though in my experience, most items I’ve looked at, PC Case Gear have always been in the top 5 cheapest, so it’s the most consistently cheap.)

Anyway, one thing that I’ve noticed, is that on the order tracking page, they give you the tracking number of your order, and a link to the website of the courier. The courier that they seem to be using the most these days (Australian Air Express) allows you to directly link to the order tracking page, so in theory it would be possible to link directly to a tracking page. PCCG doesn’t do this automatically, so I thought I’d have a go at my first grease monkey script to add a link in there. It isn’t terribly long, but it is my first one.

A quick before and after shot:

Before

You have a link to Australian Air Express where you can copy and paste your tracking number, but that just sounds like too much effort.

After

The tracking numbers now link directly to their tracking page!

Download the script here. It will work in Firefox (with the Grease Monkey extension installed) and Chrome. Since it’s only a small script, I’m not going to go ahead and put an open source licence on it, rather just say it’s public domain, though it would be nice if you left the credits there.

Edit 3 July 2011: Well, there is no longer a need for this script. When I wrote it, I also made a suggestion in the suggestions box that they implement this into their website, and it looks as if they have. Great work PCCG, can’t say many retailers actually listen to suggestions, never mind actually implement them.

The joys of developing software with no API

I’ve finally pushed out my first Android app, one that I’ve put a lot of time into (and still am, got to improve the UI experience and fix up a few bugs!) and one thing I realised in the development process is, it isn’t fun trying to develop for something that has no external API, or a company that even wants this to happen.

In the development process, I have been blocked from logging into Virgin Mobile’s website, where I just kept getting 404 Not Found or 405 Not Allowed errors (fun times.) That said, it was linked to my IP address, not my account, because it worked when I tried it at uni. And right now, I can no longer pay any bill by credit card, as I made a few mistakes in a yet unreleased feature (though I’m sure you can guess,) and whilst those mistakes have been fixed in the program (I think), I cannot make payments to my from my IP address (changed IP address, same problem), account (tried a different account, same problem) credit card, making it very hard to test.

Not to mention the lack of ability to simulate events that either don’t happen that often (turn of the month, to ensure that the app handles data and months properly,) or that you don’t ever want to happen (overdue bill.) I can also only test it on my own account, which severely limits the testing scope. I do have a friend from Uni who I get to do beta testing, but without many different accounts at your disposal and connected to your debugger, it does make finding and fixing bugs quite difficult. For example, I have had 2 bugs so far that I cannot simulate, I can’t work out why the data isn’t been fetched properly without an account that it happens on. One of them I’ve put in if and try…catch statements that prevents the error from happening, but it comes at a cost (the data usage for that user will not be displayed. That said, maybe Virgin Mobile still have postpaid accounts without data? If so, the bug should be fixed.) What I really need is users to bombard me with emails, screenshots of their account and the HTML source code of some of their account pages (I don’t need username and passwords, just the user to be willing to test it after I’ve made a few changes.)

It’s also annoying when you can only work with the information you’re given, but that information isn’t available. For example, I want to have a progress bar throughout the month of how much credit the person has used, but I can’t find anywhere on the accounts page a value that states how much credit the user has in their cap (it does for data, but not for cap credit.) I’ve tried to overcome this by detecting the type of cap (that information is available,) and storing the credit allowance and the amount for that cap in the code of the program, and matching it up. This isn’t the best way to do it (though it’s the only way I can,) as I have to manually keep it updated, and what if it’s wrong? To overcome it been wrong I’ve set it to alert the user when they login what the cap is detected as, and that they can change it if it’s wrong, but it’s better when the user has to input as few things as possible. I’m just getting away with username and password, I’m hoping when I implement a prepaid account it can automatically be detected (which reminds me, I need to run out tomorrow and get a prepaid SIM, I need an account to test it with!)

I haven’t yet heard from Virgin about unusual activity on my account, and I hope to keep it that way, but there’s only so much you can develop without actually testing it on your own account a few times, and things inevitably go wrong. It would be nice if Virgin Mobile released an official app, or an API, as it is what users want.