Tuesday, May 31, 2011

Passwords: Longer, Not More Complex

I was going through a mandatory security training course for my company when I came across a slide about passwords. Its advice was that the password should be at least 7 characters long and include a good mixture of lower/upper case characters, plus numbers and special symbols. Sony is using the same tactics. I had to change my PSN password and it forced me to use at least one number and one letter in the password.

This is totally bunk advice. What you want is a long password, and it doesn't have to be complex.

Suppose there's a server with your credentials that has the bare essentials of good security (hash + salt). If someone compromises the server, they will have to crack your password by checking every single password against the hash in the database. Therefore, the more possible passwords your scheme contains, the longer it takes to crack.

You can calculate an approximation on number of attempted passwords a cracker will have to attempt with this simple formula: (size of character set)^(password length). Given a rudimentary understanding of exponents, it's pretty evident that increasing the password length will have a lot more impact than expanding the character set size.

Let's look at some numbers. On my keyboard, there are 95 characters I could insert into a password. Let's compare a length eight password using that character set to a slightly longer password using only lower case letters (a 26 size character set):

95^8 ~= 6 quadrillion (or ~10^15)
26^12 ~= 90 quadrillion (or ~10^16)
26^15 ~= 1 sextillion (or ~10^21)

With four extra characters, my simpler password is already harder to crack than the complex eight character password.

The best part about using a limited character set is that you can create a pass phrases instead of pass words. It's much easier to remember "please do not break into my account" than "x82@.Zij", and it's also far more resilient to cracking, too.

Unfortunately, not all sites allow for long passwords. In those cases, it's better to use a more complex character set. But when given the opportunity, a long and simple password is vastly superior to a short but complicated one.

Addendum: A friend pointed out that pass phrases are more vulnerable to dictionary attacks. This is true, but only if your pass phrase contains very few words. The approximate effort for pass phrases is (number of possible words)^(number of words). Given how many possible words there are, it only takes a phrase of a few words to be as secure as 15 simple characters of nonsense. So while "supercalifragilisticexpialidocious" is a crappy password despite how long it is, my example from above ("please do not break into my account") is still very secure.

Tuesday, May 24, 2011

Using Subquery Columns on Android with SQLite

Take a look at this (contrived) query, as might be passed to SQLiteDatabase.rawQuery():

SELECT *
FROM t1 A, (SELECT T2.id FROM t2 T2) B
WHERE A.id = B.id

On Android 2.1 and below, this query will cause your app to crash. You'll get an error like this:

Caused by: android.database.sqlite.SQLiteException: no such column: B.id: , while compiling: SELECT * FROM t1 A, (SELECT T2.id FROM t2 T2) B WHERE A.id = B.id
at android.database.sqlite.SQLiteProgram.native_compile(Native Method)
at android.database.sqlite.SQLiteProgram.compile(SQLiteProgram.java:110)
at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:59)
at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:49)
at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:49)
at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1220)
at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1193)
at com.mycompany.myapp.MyActivity.onCreate(QuickTestActivity.java:22)
at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1047)
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2459)
... 11 more

It turns out that SQLite throws this error if two conditions are met:
  1. You use a subquery (in this case, the select subquery from table t2).

  2. Your subquery has a table alias (in this case, referencing table t2 as "T2").

The solution is simple: alias the selected field names. This query will work:

SELECT *
FROM t1 A, (SELECT T2.id AS id FROM t2 T2) B
WHERE A.id = B.id

I'm hardly an expert on SQL or SQLite, so I don't know whether this is a bug or just me running into undefined behavior. I ran into this problem when doing JOINs on multiple tables, so as contrived as my example is, it can happen. Regardless, it is easy to work around.