SQLite.Net.Cipher: Secure your data on all mobile platforms

SQLite database have become the first choice for storing data on mobile devices. SQLite databases are just files that are stored on the file system. Other apps, or processes can read/write data to this database file. This is true for almost all platforms, you could root/jailbreak the device and get the database file to do with it whatever you like. That’s why it is very important that you start looking into securing your data as much as possible.

In this post you show how you can use SQLite.Net.Cipher to encrypt/decrypt data when stored/accessed in/from your database. This library helps you secure the data and do all the work for you seamlessly. All you need to do it annotate the columns that you want to encrypt with one attribute. The library will do the rest for you.

The Model

 public class SampleUser : IModel
 {
  public string Id { get; set; }

  public string Name { get; set; }

  [Secure] 
  public string Password { get; set; }
 }

Notice above that we have decorated our Password property with [Secure] attribute. This will tell the SQLite.Net.Cipher to encrypt the password property whenever storing data into the database, and it will decrypt it when reading out of the database.

The model needs to implement IModel, which enforces the contract of having a property with the name Id as a primary key. This is a common standard, and you could use other columns for PrimaryKey if you want and use backing properties to satisfy this requirement if you like.

The Connection

Your database connection entity needs to extend the SecureDatabase, which is provided to you by the SQLite.Net.Cipher as below:

 public class MyDatabase : SecureDatabase
 {
  public MyDatabase(ISQLitePlatform platform, string dbfile) : base(platform, dbfile)
  {
  }

  protected override void CreateTables()
  {
   CreateTable<SampleUser>();
  }
 }

You can use the CreateTable() method to create whatever tables you need. There is also another constructor that allows you to pass your own implementation of the ICryptoService if you like. This is the entity that is responsible for all encryption and decryption tasks.

See it in Action

Now to see the library in action, you could establish a connection to the database, insert some data and retrieve it:

 var dbFilePath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "mysequredb.db3");
 var platform = new SQLite.Net.Platform.XamarinIOS.SQLitePlatformIOS();
 ISecureDatabase database = new MyDatabase(platform, dbFilePath);
 var keySeed = "my very very secure key seed. You should use PCLCrypt strong random generator";

 var user = new SampleUser()
 {
  Name = "Has AlTaiar", 
  Password = "very secure password :)", 
  Id = Guid.NewGuid().ToString()
 };

 var inserted = database.SecureInsert<SampleUser>(user, keySeed);
  
 // you could use any desktop to inspect the database and you will find the Password column encrypted (and converted base64)

 var userFromDb = database.SecureGet<SampleUser>(user.Id, keySeed);

And that’s all  Assuming that you have installed the Nuget Package.

Dependencies

Please note that this library relies on the following great projects:
SQLite.Net-PCL
PCLCrypto

Both of these projects are really great and they support all major platforms, including builds for PCL libraries, so I would highly encourage your to look into them if you have not seen them before.

jQuery keyup event to table rows

How to bind keyup / keydown event to table?

Have you ever tried to bind a keyup/keydown event of jQuery? What you try it wont trigger. However it is possible with a minor change. By adding the tabindex=0 to the table if you bind to the table, or tabindex=0 to the tr when you bind to the table rows, jQuery will trigger the event and you can handle the eventbinding. So you will be able to use tab in table cells, tabbing on table rows or create own shortcuts.

Happy coding!

Enumeration types, switch statements and extra data and logic

An enum is basicly a collection of related choices or states. For example you can have an enum State { active, inactive }.
One of the main strenghts of an enum is that it can be used in a switch statement, thus eliminating the use of multiple if statements:
switch(state)
{
    case State.active: deactivate(); break;
    case State.inactive: activate(); break;
}

A switch statement only work with constant expressions, meaning code that is always compiled the same way, like numbers, chars and enums.

In most languages an enum is basicly nothing more than a single int. You can't specify that the State.active always has the color green, and the State.inactive the color red, or add methods to specify how comparison should work.

C#

In C# you can add some extra information to enums using attributes, and you can use extension methods and reflection to get some extra information, but even with these possibilities the enum still is quite dumb.

We can use an enumeration class to add extra information to our enum types (like described here: https://github.com/HeadspringLabs/Enumeration), but you can't use this enumeration class in a switch statement since the enumeration types are not constant expression.

C++

C++ has basicly the same problem. Since the newest version of C++ (C++11) typed enums are added, which makes the enum work like it does in C#, but it is still not possible to add logic or extra data to them.
An other nice addition to the newest C++ version is the constexpr keyword.
Using this keyword you can make your own types work in a switch statement, as long as they can convert to an int type:
struct State
{
    const int state;
    constexpr operator int() { return state; }
};
constexpr State active= { 1 };
switch(state)
{
    case active: ..
}
Unfortunatly  it is still not possible to do the following:
struct State
{
    static constexpr State Active = { };
};
So we still can't create the same type of enumeration classes as in C#, but we can come far with the following workaround I found:
struct StateType { /* the implementation of the type */ };
struct State
{
    static constexpr StateType Active = { };
    static constexpr StateType Inactive = { };
};

Conclusion

In conclusion, enums will not really change, but hopefully the creators/designers of programming languages will make the languages soon better to make it possible to use enumeration classes in switch statements like you can use enums.

Removing all Foreign keys from MSSQL

Today I had some problems with removing tables.
To clear all test data from a database we can use the TRUNCATE method to cleanup the table. Other solution is to clear all the foreign keys and then drop the database.


My database contains tables and a schema. When we are developing we want to reset our database. So we use a sql script where we drop all the tables of a schema and then the database itself.


When I run the query I got this error message:


fk is not a constraint. could not drop constraint. see previous errors

And after some changes in my SQL:


could not drop object because it is referenced by a foreign key constraint


So why you wont delete. The table order seems to be right but it doesnt work?
We can get all the constraints from the INFORMATION_SCHEMA.TABLE_CONSTRAINTS table.


For example you see here the Northwind database:





Ok, we can remove each constraint easy by adding in our SQL a DROP CONSTRAINT for each constraint. However, not easy when the database grows.


An other easy solution is check in the database, if found, we create a query and executes the query. Our solution will be:

WHILE(exists(SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='FOREIGN KEY'))
BEGIN
 DECLARE @sql nvarchar(2000)
 SELECT TOP 1 @sql=('ALTER TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME
 + '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']')
 FROM information_schema.table_constraints
 WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
 EXEC (@sql)
END

In my case I want to remove only constraints from the given schemaname, so add an extra WHERE for TABLE_SCHEMA and we are done!

Method not found: 'Void Newtonsoft.Json.Serialization.DefaultContractResolver.set_IgnoreSerializableAttribute(Boolean)'.

I am using MVC4 for while. Together with NuGet it works very well. Last week I updated my libraries for my solution. Running my code and BAM! Exception was thrown:

Method not found: 'Void Newtonsoft.Json.Serialization.DefaultContractResolver.set_IgnoreSerializableAttribute(Boolean)'.

Method not found? So I start searching the internet. Tried multiple solutions but nothing seems to work. In my steps to reproduce, I removed some references, like EntityFramework and OAuth.

To keep the solution short, NuGet seems to be the problem. In my steps to reproduce the problem I downloaded once the latest libraries. However, when NuGet finds out you have the library already, the solution won't be updated. So the version of my DLL was just 4.0.1 instead my latest library had version 5.0.6. After changing my projectfile to use the version of 5.0.6 everything works fine!

Ps. I updated WebGrease and Newtonsoft.Json

Happy coding!

Disable Password Expiry in Windows Server 2008 r2

We have some servers for testing purpose. We don't want to change the password all the time. So we disabled the Password Expiry policy.

How to disable the Password Expiry Policy?
- Go to Start -> Run -> gpedit.msc, this will load the Group Policy Editor
- Expand sections to Password Policy
      = Computer Configuration --> Windows Settings --> Security Settings --> Account Policies --> Password Policy
- Set 'Maximum password age' to 0 to totally disable expiry

Now your password expiry policy is disabled

OOP with JavaScript

Object Oriented Programming, short called OOP, can be done on different levels. JavaScript can be made OOP too. Normally you write based on the function what you need. For example:

$(".btn").click(function(){
    alert("Button is clicked");
});

For creating a new class with JavaScript you need to start with this:

var MessageBox = function() {
   //Methods and stuff
};

Now we have created a new class. The name of the class is MessageBox. Now we want to add a public method to the class. Lets call the method AlertDefault. The AlertDefault method will call the alert with a static text: "I am a default message"
For defining a public method you need to add this to the method name. So this will be:

this.AlertDefault = function() {
   alert("I am a default message");
};

But maybe we want to have our custom message to give to the alert. So we want to add an argument to our second method. Lets call this method AlertMessage. The implementation will be almost the same, however the argument is put in the alert.

this.AlertMessage = function(message) {
   alert(message);
};

The complete code will be:

var MessageBox = function() {
   this.AlertDefault = function() {
       alert("I am a default message");
   };
 
   this.AlertMessage = function(message) {
       alert(message);
   };
};

Now we have created our class we want to check if everything is working fine.

First we need to create a instance of the MessageBox. Then we can call the AlertDefault and the AlertMessage.

var messageBox = new MessageBox();
messageBox.AlertDefault();
messageBox.AlertMessage("I am a custom message");