Doppelgänger

Over the last few years I’ve had various people come up to me, asking me if I was Hugh Dennis. ūüėĪ I’m clearly not, but I’ll take it ūüôā Have you ever had that experience, that someone mistakes or thinks you resemble a celebrity? It’s odd, and I can only apologise to the man himself, he has no need for a doppelg√§nger. ūüėé

Advertisements

Windows Insider Programme

I’ve been part of the Windows Insider programme for quite a while now (January 14, 2015), and I have generally been very pleased with my participation. Today I installed the¬†Windows 10 Insider Preview 18272.1000 (rs_prerelease), from my favourite Pr√©t coffee joint in Watford.

It took a while to download, but the actual installation was very quick, and it seems MS has been working on speeding up new OS upgrades over at least the last year. I’ve been using my trusted Lenovo Flex-2 15 for most of the builds. It is nearly 5 years old, and rarely had any issues with drivers etc, so I’m well pleased with the Windows OS, in particularly the Windows Insider builds. I’m now on the Active development of Windows preview build, on the Fast ring. I am running my development tools, Visual Studio 2017, Visual Studio Code etc, on these builds and rarely have any issues. Keep it up MS!

SQL Server Cell/Column/Field level encryption handled from your application(s)

SQL Server supports a number of encryption mechanisms, including Always Encrypted. This is automatic encryption handled by the database engine, which is pretty cool. However, what if you want your applications to handle the encryption and decryption when needed? This is where cell level encryption comes in, and it is supported by SQL Server which is a good thing.

I’ve used the AdventureWorks2017 example database for the encryption shown later, which you can find here, or using a direct download link. I have enabled SQL Server and Windows Authentication on the server, and I’m using SQL Server Authentication for this post. I have a login named enc, as well as a user named enc, using the dbo schema.

SQL Server has a number of Transact-SQL commands, which you can read more about here. Now, why am I writing about this, seeing as you can just use what is shown on those pages? Using Transact-SQL is very simple, but how can you use it from your own applications? Well, I needed to use the encryption from a C# application using Entity Framework 5.0. The latter was a requirement!

Right, let’s start by enabling the database to handle the encryption/decryption. In the Sales.CreditCard table, there is a CardNumber column, which I want encrypted, to keep prying eyes from reading it. So in a query window (I’m using SSMS, SQL Server Management Studio), run the following query:

-- Connect to the right database
USE AdventureWorks2017
GO

-- Create master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SomeSillyPassword@2018'
GO

-- Create certificate
CREATE CERTIFICATE CreditCardNoCert WITH SUBJECT = 'Credit card Numbers'
GO

-- Create symmetric key
CREATE SYMMETRIC KEY CreditCardNoKey WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE CreditCardNoCert
GO

-- Grant permissions
GRANT VIEW DEFINITION ON CERTIFICATE::CreditCardNoCert TO "enc"
GO

GRANT VIEW DEFINITION ON SYMMETRIC KEY::CreditCardNoKey TO "enc"
GO

GRANT CONTROL ON CERTIFICATE::CreditCardNoCert TO "enc"
GO

-- Add new column to hold encrypted value
ALTER TABLE Sales.CreditCard
ADD CardNumberEncrypted varbinary(8000)
GO

You can encrypt the CardNumber column for all existing rows, using the below T-SQL and the symmetric key. Notice how the encrypted value is copied from the original column, CardNumber, to the newly created column, CardNumberEncrypted. Notice how the symmetric key is being opened and closed.

OPEN SYMMETRIC KEY CreditCardNoKey
DECRYPTION BY CERTIFICATE CreditCardNoCert

UPDATE Sales.CreditCard
SET CardNumberEncrypted = EncryptByKey(Key_GUID('CreditCardNoKey'), CardNumber, 1, HashBytes('SHA1', CONVERT(varbinary, CreditCardID)));
GO

CLOSE SYMMETRIC KEY CreditCardNoKey
GO

As a means of protection, you could set the CardNumber column to an empty string, or you can delete it, and rename the CardNumberEncrypted column to CardNumber.

Now that we have the encryption in place, we need to look at how you can decrypt CardNumberEncrypted.

OPEN SYMMETRIC KEY CreditCardNoKey
DECRYPTION BY CERTIFICATE CreditCardNoCert

SELECT CardNumberEncrypted AS 'CardNumberEncrypted', CONVERT(nvarchar, DecryptByKey(CardNumberEncrypted, 1, HASHBYTES('SHA1', CONVERT(varbinary, CreditCardID)))) AS 'CardNumberDecrypted' FROM Sales.CreditCard;

CLOSE SYMMETRIC KEY CreditCardNoKey
GO

So far so good, now we need to handle the encryption/decryption from an application using C#. I have created an Operations class, shown below.

using System;
using System.Linq;
using System.Text;

namespace EF-SqlEncryption {
  public class Operations {
    private AdventureWorks2017Entities _adventureWorksContext = null;

    public void EnsureEncryptedCreditCardNoColExists() {
      using (_adventureWorksContext = new AdventureWorks2017Entities()){
        try {
          _adventureWorksContext.Database.ExecuteSqlCommand("ALTER TABLE Sales.CreditCard ADD CardNumberEncrypted varbinary(128)");
          Console.WriteLine("Column CardNumberEncrypted created");
        }
        catch (Exception){
          Console.WriteLine("Column CardNumberEncrypted already exists");
        }
      }
    }

    public string GetFirstCreditCardNo() {
      string result = string.Empty;

      using (_adventureWorksContext = new AdventureWorks2017Entities()) {
        var ccs = _adventureWorksContext.CreditCards.FirstOrDefault();
        result = ccs.CardNumber + " - ";
        if (ccs.CardNumberEncrypted != null)
          result += ByteArrayStringRepresentation(ccs.CardNumberEncrypted);
      }

      return result;
    }

    public byte[] EncryptCreditCardNo(string cardNo) {
      var result = new byte[0];

      try {
        _adventureWorksContext = new AdventureWorks2017Entities();
        var dmlOpenCert = "OPEN SYMMETRIC KEY CreditCardNoKey DECRYPTION BY CERTIFICATE CreditCardNoCert;";
        var dmlCloseCert = "CLOSE SYMMETRIC KEY CreditCardNoKey;";
        var dmlEncrypt = "SELECT CardNumberEncrypted = EncryptByKey(Key_GUID('CreditCardNoKey'), CardNumber, 1, HashBytes('SHA1', CONVERT(varbinary, CreditCardID))) FROM Sales.CreditCard WHERE CardNumber = '" + cardNo + "';";
        result = _adventureWorksContext.Database.SqlQuery<byte[]>(dmlOpenCert + dmlEncrypt + dmlCloseCert).FirstOrDefault();
      }
      catch (Exception e) {
        Console.WriteLine(e.Message);
      }
      finally {
        _adventureWorksContext.Dispose();
      }

      return result;
    }

    public byte[] UpdateEncryptedCreditCardNo(string cardNo) {
      byte[] result = new byte[0];

      try {
        EnsureEncryptedCreditCardNoColExists();
        _adventureWorksContext = new AdventureWorks2017Entities();
        var dmlOpenCert = "OPEN SYMMETRIC KEY CreditCardNoKey DECRYPTION BY CERTIFICATE CreditCardNoCert;";
        var dmlCloseCert = "CLOSE SYMMETRIC KEY CreditCardNoKey;";
        var dmlUpdate = "UPDATE Sales.CreditCard SET CardNumberEncrypted = EncryptByKey(Key_GUID('CreditCardNoKey'), CardNumber, 1, HashBytes('SHA1', CONVERT(varbinary, CreditCardID))) WHERE CardNumber = '" + cardNo + "';";
        var sqlSelect = "SELECT CardNumberEncrypted FROM Sales.CreditCard WHERE CardNumber = '" + cardNo + "';";
        result = _adventureWorksContext.Database.SqlQuery<byte[]>(dmlOpenCert + dmlUpdate + sqlSelect + dmlCloseCert).FirstOrDefault();
      }
      catch (Exception e) {
        Console.WriteLine(e.Message);
      }
      finally {
        _adventureWorksContext.Dispose();
      }

      return result;
    }

    public string DecryptFirstCreditCardNo() {
      var result = string.Empty;

      try {
        EnsureEncryptedCreditCardNoColExists();

        _adventureWorksContext = new AdventureWorks2017Entities();
        var res = _adventureWorksContext.Database.SqlQuery("SELECT TOP 1 * FROM Sales.CreditCard WHERE CardNumberEncrypted IS NOT NULL");

        var cardNo = res.FirstOrDefault().CardNumber;
        var dmlOpenCert = "OPEN SYMMETRIC KEY CreditCardNoKey DECRYPTION BY CERTIFICATE CreditCardNoCert;";
        var dmlDecrypt = "SELECT CONVERT(nvarchar, DecryptByKey(CardNumberEncrypted, 1, HashBytes('SHA1', " +
"CONVERT(varbinary, CreditCardID)))) AS 'CardNumberDec' FROM Sales.CreditCard WHERE CardNumber = '" + cardNo + "';";
        var dmlCloseCert = "CLOSE SYMMETRIC KEY CreditCardNoKey;";

        // Carsten Thomsen: This works, with the combination of DML and a query. EF is closing the connection after each query or DML, meaning the Cert would be closed too, effectively preventing encryption and decryption.
        result = _adventureWorksContext.Database.SqlQuery(dmlOpenCert + dmlDecrypt + dmlCloseCert).FirstOrDefault();
      }
      catch (Exception e) {
        Console.WriteLine(e.Message);
      }
      finally {
        _adventureWorksContext.Dispose();
      }

      return result;
    }

    public string DecryptCreditCardNo(string cardNo) {
      var result = string.Empty;

      try {
        EnsureEncryptedCreditCardNoColExists();
        _adventureWorksContext = new AdventureWorks2017Entities();
        var dmlOpenCert = "OPEN SYMMETRIC KEY CreditCardNoKey DECRYPTION BY CERTIFICATE CreditCardNoCert;";
        var dmlDecrypt = "SELECT CONVERT(nvarchar, DecryptByKey(CardNumberEncrypted, 1, HashBytes('SHA1', " +
"CONVERT(varbinary, CreditCardID)))) AS 'CardNumberDec' FROM Sales.CreditCard WHERE CardNumber = '" + cardNo + "';";
        var dmlCloseCert = "CLOSE SYMMETRIC KEY CreditCardNoKey;";
        result = _adventureWorksContext.Database.SqlQuery(dmlOpenCert + dmlDecrypt + dmlCloseCert).FirstOrDefault();
      }
      catch (Exception e) {
        Console.WriteLine(e.Message);
      }
      finally {
        _adventureWorksContext.Dispose();
      }

      return result;
    }

    public static string ByteArrayStringRepresentation(byte[] ba) {
      var bytesStringRepresentation = new StringBuilder(ba.Length * 2);
      bytesStringRepresentation.Append("0x");

      foreach (byte b in ba) {
        bytesStringRepresentation.AppendFormat("{0:x2}", b);
      }

      return bytesStringRepresentation.ToString().ToUpper();
    }
  }
}

Here are the AdventureWorks2017 model and code first classes.

Bitbucket Extension for Visual Studio

I started working in a new company recently, one that uses the Atlassian Bitbucket repository for source control. I\m not a big fan, but it’s Git. Anyway, I needed to integrate it with Visual Studio 2017, so I fired up the Extensions and Updates dialog from the Tools menu. I typed in ‘bitbucket’ in the search field, and found one extension, Visual Studio Bitbucket Extension by NextIteration v. 1.37.1 at the of writing this, which I then downloaded and installed. Unfortunately I couldn’t find in the Source Control section of the Options dialog box, found on the Tools menu; it just wasn’t shown in the Current source control plug-in dropdown list. I closed the Options dialog, and switched to the Team Explorer window which didn’t show the extension either. So, I went to the Visual Studio market place and found another extension by MistyK,¬†https://marketplace.visualstudio.com/items?itemName=MistyK.VisualStudioBitbucketExtension. Installing that, did the trick, even if this extension was also version 1.37.1, go figure…

Visual Studio 2017 Editor new line wrapping

We all have our favourite settings when it comes to setting up Visual Studio, and the editor is quite often set up differently for a group of developers working together. I like having the opening brace ({) on the same line as the class, struct or whatever the brace signals the beginning of.

I like this:

class {

as compared to this:

class 
{

I frequently use the key combination Ctrl+K, Ctrl+D to format my document after having typed some code. I dislike it, if the formatting is not to my liking. It should therefore not come as a surprise, if I tell you that while working on a new UWP app, in which I had copied across some code from a Template10 project template, the key combo seemingly didn’t work anymore. After some investigation, including opening an existing Console app, in which the key combo did work, I took a look at this code:

namespace Template10.Views
{
    public sealed partial class SettingsPage : Page
    {
        readonly Template10.Services.SerializationService.ISerializationService _serializationService;
 
        public SettingsPage()
        {
            InitializeComponent();
            NavigationCacheMode = NavigationCacheMode.Required;
            _serializationService = Template10.Services.SerializationService.SerializationService.Json;
        }
 
        protected override void OnNavigatedTo(NavigationEventArgs e)
        {
            var index = int.Parse(_serializationService.Deserialize(e.Parameter?.ToString()).ToString());
            MyPivot.SelectedIndex = index;
        }
    }
}

Because of the Page class, from which the class shown, SettingsPage, is derived has a notification stating “Base type ‘Page’ is already specified in other parts”, the class/type name Page is greyed out. This effectively blocks for the document formatting placing the opening brace { on the same line as the class declaration. If I remove : Page, and press the key combination Ctrl+K, Ctrl+D, this is what I get.

namespace Template10.Views {
    public sealed partial class SettingsPage {
        readonly Template10.Services.SerializationService.ISerializationService _serializationService;
 
        public SettingsPage() {
            InitializeComponent();
            NavigationCacheMode = NavigationCacheMode.Required;
            _serializationService = Template10.Services.SerializationService.SerializationService.Json;
        }
 
        protected override void OnNavigatedTo(NavigationEventArgs e) {
            var index = int.Parse(_serializationService.Deserialize(e.Parameter?.ToString()).ToString());
            MyPivot.SelectedIndex = index;
        }
    }
}

 

You should only open projects from a trustworthy source prompt

In Visual Studio 2017 and previous versions, if you attempt to open a project on a network share, such as one located on a Mac, that helpful, but rather annoying message prompts you. There’s a quick fix for that;

  • Open Visual Studio, on the¬†Tools ¬†menu, click Options.
  • In the Options dialogue box, on the list on the left, expand Projects and Solutions and click General.
  • Uncheck Warn user when the project location is not trusted, and click OK.

Simple…

Visual Studio 2017 Create Strong Name Key File

When trying to create a strong name key file for your project, in this case a UWP project, in Visual Studio 2017, and probably previous versions too, you may get an error message, once you click OK in the Create Strong Name Key dialogue box.

CreateStrongNameKeyWindows-VS2017

The operation could not be completed message box.

TheOperationCouldNotBeCompletedDialog

It turns out that all I needed to do, was to run Visual Studio as an administrator, and it worked. It’s the actual signing with the password that requires this, as you can create a key file without a password, and the signing will work without running VS as an administrator.

MS Band 2 battery dead

I’ve been using MS Band for a few years, initially the original Band and then the MS Band 2. I’ve had the latter replaced twice, but now that device is no longer able to be charged, i.e. the battery is fucked, and of course I’m out of warranty. I love my Band and I wear it all the time, it’s a fantastic little device and I pray to the MS Gods they will bring out a new and even better one sometime soon.

However, what do I do in the meantime, I really need a device for when I go running or walking… Any ideas?

C# Comments

This is another of my pet peeves when looking at other people’s code. If you add a comment, using the double forward-slash //, then add a freaking space after // to make it stand out from code that has been commented out. It pisses me off right, left and center to see this:

//TODO blah, blah

…or this:

//Here is where you add...

Why not just do this, so flipping simple:

// TODO:

This will avoid any potential mistake when glancing over the code, where the code below has is clearly commented out code, and the first examples above are not:

//var numDays = int.Parse(Console.ReadLine());
//var numPeople = 5;
//var numPeopleShared = 0;

Do feel free to share your opinion and experience on the same.

OutOfMemoryException in online test

I did an online test the other day, and while the code I had put together did work and passed 3 of the 4 testcases, the failing testcase throw an OutOfMemoryException. I was writing the code in Visual Studio 2017 and copying it to the web-based IDE on TestDome. Now, since I don’t know what the testcase did, I can only assume that it added a very large number of int’s to an array, for which I was to find the kth most common integer in the array. This is my code:

public static int KthMostCommon(int[] a, int k) {
    return a.GroupBy(v => v)
        .OrderByDescending(g => g.Count()).ElementAt(k - 1).Key;
}

I only got a 66% score for that task, but it dawned on me, that I should have tried using a byte array instead. Trying it with the following call to the method and obviously changing the method signature to accept a byte array instead of a an int array, worked a charm.

var t = Enumerable.Repeat<byte>(0, 1000000000)
    .Select(i => (byte) randNum.Next(minValue, maxValue)).ToArray();
var x = KthMostCommon(t, 2);

What would you have done, not knowing the number of integers that was in the array?

UPDATE:

I obviously couldn’t have changed the method signature as suggested in the actual test, as that would have caused all unit tests to fail. Another option is to pass the int array and convert to a byte array, and then disposing of the int array before processing the byte array.

var bytes = new byte[a.Length * 4]; 
for (var i = 0; i < a.Length; i++)
     Array.Copy(BitConverter.GetBytes(a[i]), 0, bytes, i * 4, 4);
a = null; 
GC.Collect();

Mind you, it would probably be better changing the processing alltogether… ūüôā