Multiuser atomic SQL; day one

Recently we've taken over support for a new, externally developed application that sends text messages to mobiles in response to shortcode requests for more information about cars, houses etc for sale.

Under development and testing everything was fine. However, when it went live the registration process occasionally failed. After a brief examination I noticed this method for inserting records in the database class:

$newId = $this->conn->nextID($tableName . "_id");

I suspected that this did something a little unusual.

It had been written in Php5 backed by MySQL and was using the Pear DB package. It rapidly became clear the book "Professional PHP5" had been used a little too religiously. The book suggests this facade method for generating and getting the next ID for a new record that hides the operational differences between MySQL, PostreSQL, Oracle and other RDBMS.

Under the hood, Pear DB creates an id tracking table of the same name suffixed with "_id_seq" for each table storing data with unique ids. It gets the value from this tracking table, adds one to the value, stores it back, and returns this value as the next id to be used in the insert statement for the new record.

So I heard websites and the internet was a concurrent multiuser enviroment? Can anyone guess what might be the problem generating unique ids this way?

MySQL gives the us the auto_increment feature. It also gives us the mysql_insert_id method. This allows us to get the last auto generated id if we need it:

$newId = mysql_insert_id($this->conn->connection);

"But surely, when inserting new records in a concurrent multiuser environment, we need actions like this to be as atomic as possible?" I hear you ask. The force (read SQL) is strong with this one. Correct.

MySQL allows us to provide a value for an auto_increment field, or we can supply 'null' and allow MySQL to do it for us.

So to fix this intermittent registration failure, we replace the line:

$newId = $this->conn->nextID($tableName . "_id");

with:

$newId = 'null';

And LO, the bug is gone.
Needless to say Pear DB has been replaced by something else. And interestingly if you read on a little in the book you'll see "we strongly recommend... ideally, allow your RDBMS to [generate the id]"

So what? Read books carefully. Know the technology you're using. Concurrency requires atomic superness.

Securing web logins against dictionary attacks

It's good to learn from your mistakes; it's even better to learn from other people's. So, when Twitter got hacked by a simple dictionary attack, we, the development team at Compsoft, thought it a good time to review our password security model.

Because pretty much everyone wants password protection for at least some part of their web site, password security is something we've implemented quite a few times already. And as nobody enjoys re-inventing the wheel, this already lives in our well-established reusable code libraries - meaning that if we ever identify a weakness we can fix it in one place. At the very least, all our future customers get this improvement out of the box; where appropriate, the upgrades get rolled out to our existing customers too.

Our password handling was already pretty tight - we use some pretty aggressive salting and hashing to ensure that passwords are held so securely that even we can't crack them. Even though I have full access to the database, I don't know your password; I can't tell anything about your password; I can't even tell if two people have the same password. If I copy the password details from my user record to yours, I still won't be able to log in as you. It's got to the point that it's quite difficult for us to set up the first user record in a new database - even copying password details from one database to another won't work.

Anyway, storing the password securely is all well and good, but doesn't help if your users use passwords that are easily guessable, as Twitter found to their embarrassment. The key feature they'd failed to implement (and that we realised we'd immediately have to add to our arsenal) was preventing unlimited password attempts.

Though dictionary attacks are always going to be slow when executed across the internet, they're still entirely possible - especially given that the only limit on a typical hacker's time is how quickly he gets bored.

The easiest solution is to say "if you get your password wrong five times, your account gets blocked". This is quick and easy to implement, but what does it mean for our users? Sure, they'd be protected against having their account hacked, but could they not legitimately take five attempts before they remember which password they used for this site? At times I've taken a lot more than that, going through more and more possible passwords that I think I might have used, before finding that the one I tried first was right - I'd just typed it wrong.

Increasing the number of attempts allowed doesn't really solve the problem - if they breach it, they're locked out completely. That means we'd have to add some mechanism whereby they can contact an administrator and convince them that they are who they say they are, so that the admin will re-enable their account. Not only would this alienate users, but this isn't really practical for a solution we can reuse across many intra- and internet sites.

What's worse, it's easy for one malicious user to lock other users out of their accounts, simply by repeatedly trying to log in as them.

We need something that is entirely invisible to as many genuine users as possible, but that is insurmountable to a dictionary attack in any reasonable amount of time. Also, should we start suspecting a legitimate user might be a hacker, they mustn't feel like we've just slammed the door in their face.

The solution we finally opted for goes like this:

• You get ten attempts to try to remember your password.
• On the eleventh attempt, your account is locked for one minute.
• Each subsequent attempt locks your account for twice as long: two minutes, four minutes, eight minutes...
• If you make no log in attempts for twenty-four hours, we reset the counter to zero again.

Almost all users will never see that this security exists. Those who break the ten-guess limit see a polite message informing them of why we think something's wrong ("a large number of login attempts using the wrong password") and how long they new have left to wait before they can try again.

The first few times they see this limit they'll not have to wait long, and in the meantime we're gently teaching them to think a little more carefully about what password they might have used.

On the other hand, a hacker using a dictionary attack would have to attack the same user id persistently for a full 24 hours to get through just the first 21 words in his attack dictionary.

The experience of the attacked user isn't too bad either - they'll only know there's even been a problem if they try to log in shortly after the hacker (or malicious user) has been trying to access their account. The worst that could happen would be that they wouldn't be able to log in until tomorrow.

For us, this is a nice solution. We've implemented it as part of our reusable code framework, and included it in the template used by our code generation tool. This means that even though we haven't started work making your next website for you yet, your next website is already more secure than Twitter was.

Implementing visual inheritance in windows mobile

We've been working on a Windows Mobile 6 PDA application and one requirement is to have a status bar that shows internet connectivity and updates.

In Windows programming, a common way to do this is to create a base form which has a status bar and a mechanism to update it, this form is then used as a template for other forms. This means that you have a single point of failure, update and maintenance. This is a good thing as if you added that status bar to all your forms, you'd increase your maintenance headache with each new form.

On Windows Mobile, there is support for the same visual templating mechanism, but there are a few pitfalls. If for example on your base form you wanted to add a popup keyboard, you'd need to add a reference to Microsoft.WindowsCE.Forms. This unfortunately breaks the visual inheritance in Visual Studio's form designer saying "Visual inheritance is currently disabled", this also occurs when using Platform Invoke (a method of calling methods in the base operating system) in the base form.

Luckily there are a few workarounds for this:

Add a class diagram, select your base form, add a custom attribute of DesktopCompatible(true), this adds an XML Metadata Attribute file (.xmta).The xmta file can cause a genasm.exe error, ensure the build action is set to None and the copy to output directory is set to Do not copy, this should now build fine.


Job done, we have visual inheritance visible in the designer.

Creating an Installer for Windows Mobile Applications

We recently had a requirement come up to create a windows installer for a mobile application. So a couple of minutes of googling gets me a great MSDN article on step by step instructions on creating an installer.

However, great as MSDN is, the article had some flaws, it didn't work! The application installed on the local windows machine but didn't trigger the mobile install on the device.

After some debugging of the installer code, I found that the article refers to Context.Parameters["InstallDirectory"] however it asks you to place targetdir="[TARGETDIR]\" in the CustomActionData property. This meant the install directory was not being transfered to the DLL.

To make the project work you need to get those two to match. Update the code to use Context.Parameters["TargetDir"] and everything works a treat.

Remember to set both Commit and Install custom actions to the new DLL otherwise it won't install correctly.

The final code looks like:

///

/// Code implemented from: http://msdn.microsoft.com/en-us/library/bb158529.aspx
///

[RunInstaller(true)]
public partial class CustomInstaller : Installer
{
public CustomInstaller()
{
InitializeComponent();
}

public override void Commit(System.Collections.IDictionary savedState)
{
// Call the Commit method of the base class
base.Commit(savedState);

// Open the registry key containing the path to the Application Manager
Microsoft.Win32.RegistryKey key = null;
key = Microsoft.Win32.Registry.LocalMachine.OpenSubKey("Software\\microsoft\\windows\\currentversion\\app paths\\ceappmgr.exe");

// If the key is not null, then ActiveSync is installed on the user's desktop computer
if (key != null)
{
// Get the path to the Application Manager from the registry value
string appPath = null;
appPath = key.GetValue(null).ToString();

// Get the target directory where the .ini file is installed.
// This is sent from the Setup application
string strIniFilePath = "\"" + Context.Parameters["TargetDir"] + "ObScan.ini\"";
if (appPath != null)
{
// Now launch the Application Manager
System.Diagnostics.Process process = new System.Diagnostics.Process();
process.StartInfo.FileName = appPath;
process.StartInfo.Arguments = strIniFilePath;
process.Start();
}
}
else
{
throw new Exception("No Active sync installed");
}
}
}