Android, Self-Signed Certificates and OkHttp

Just recently I have been writing a private Android application using the latest freshness that is Kotlin.

Of course, like all mobile applications, it makes heavy use of HTTP requests, sending data back and forth between my app and the back-end API. Because this app is private, it will never be released in the Google Play store and the back-end server will not be publicly available on the internet. The server is a private home web server on an internal network (think intranet) and the application will only contact it over my home WiFi.

Did you know that Sensitive Data Exposure is still on the 2017 OSWASP top 10 list? It's currently sitting at the #3 spot. The OSWASP folks say:

"Over the last few years, this has been the most common impactful attack. The most common flaw is simply not encrypting sensitive data."

So, as a good developer, my next job was to switch my project to use HTTPS/TLS. That's when I remembered Let’s Encrypt which is a free, automated, and open Certificate Authority. What a great opportunity to try them out!

But I quickly hit a brick wall because Let’s Encrypt is only for public facing web sites that are available on the public internet and the service can only issue certificates for valid DNS names. See here, here, here and here.

As I mentioned, I don't have a valid DNS name (just a static IP address) and this server is not reachable via the internet. So unfortunately, playing with lets Encrypt was out!

...That's when I turned to creating my own self signed certificate.

Creating a Self-Signed Certificate

Now, you can create a self-signed certificate right inside IIS. Here is a handy guide on how to do that. But we are not going to take that route. We are going to use OpenSSL. The reason is, for the Android application to trust our own self-signed certificate we need a .crt file (the public key) to be loaded in to the Android app and IIS only creates .pfx files. You can read up on the differences between .pfx files and .crt files here. Another benefit of using OpenSSL is that you can configure how long the certificate is valid for. By defaut, IIS will only allow you to create a self-signed certificate that is valid for 1 year.

I downloaded the Win32 version of OpenSSL from here but thinking about it, I guess I could have used WSL. With OpenSSL downloaded you can create a certificate with the following command:

openssl req -x509 -sha256 -nodes -days 3650 -newkey rsa:2048 -keyout oceanairdrop_private.key -out oceanairdrop.crt

The above command is all we need to create our self-signed certificate.

When I ran the command, I requested that the certificate be valid for 3,650 days which is 10 years.

To use this certificate with IIS, you need to convert it to a certificate to a .pfx file because IIS only accepts .pfx certs. You can think of the .pfx file as a zip file that contains both the public & private key as well as password to protect it. Next run the following OpenSSL command to generate the .pfx key.

set RANDFILE=C:\OceanAirdrop\.rnd
openssl pkcs12 -export -out oceanairdrop.pfx -inkey oceanairdrop_private.key -in oceanairdrop.crt

You will be asked to provide a password for the file. Choose one and when your finished we will now have the .pfx file on disk

IIS API Host

Now that we have created the certificate, we can now go to IIS and import it. Nice and easy.

As a side-note, initially, when I tested my endpoint I got this error message: Error 0x80070020 when you try to start a Web site in IIS 7.0. It turns out I couldnt start the website because a clash of ports. So, I moved my port number to avoid any clashes.

Now, when I test this all in a browser over https, I get the expected warning that my certificate is unknown ( as it is from an invalid authority ) but it works!

Of course, this will not be a problem as I won't be calling my API endpoint from a browser. My endpoints are going to be called from my android application. This is what needs to trust my new certificate, not a browser.

Android and OkHttp

Now that my self-signed certificate has been created, I need to get it working in my Android app.

I am using the OkHttp library in Android for my applications HTTP requests. It's nice and simple to use. Before making any changes to the code, OkHttp errors with the exception: CertPathValidatorException when I attempt to call one of my endpoints. Now lets fix the code by getting the app to trust our new certificate.

This is where we need to load our self-signed certificate file and add it to the Android keystore. Open Android Studio, and copy your .crt file into the Android projects res\raw folder.

With the public key available, we can load the key into our android app and add it as a CA certificate. This ensures that application can trust this certificate.

// Load CAs from an InputStream
val certificateFactory = CertificateFactory.getInstance("X.509")

// Load self-signed certificate (*.crt file)
val inputStream =  App.context.getResources().openRawResource(R.raw.oceanairdrop)
val certificate = certificateFactory.generateCertificate(inputStream)
inputStream.close()

In the code above the variable certificate is our public key. Now we need to tell Android to trust that certificate by adding it to the key store and creating a TrustManager that trusts the CAs in our KeyStore.

// Create a KeyStore containing our trusted CAs
val keyStoreType = KeyStore.getDefaultType()
val keyStore = KeyStore.getInstance(keyStoreType)
keyStore.load(null, null)
keyStore.setCertificateEntry("ca", certificate)

// Create a TrustManager that trusts the CAs in our KeyStore.
val tmfAlgorithm = TrustManagerFactory.getDefaultAlgorithm()
val trustManagerFactory = TrustManagerFactory.getInstance(tmfAlgorithm)
trustManagerFactory.init(keyStore)

Once we have the loaded the key successfully we can create an instance of OkHttpClient passing in the trust manager.

Here is the full code sample below:

fun GetOkHttpClient() : OkHttpClient
{
    // Load CAs from an InputStream
    val certificateFactory = CertificateFactory.getInstance("X.509")

    // Load self-signed certificate (*.crt file)
    val inputStream =  App.context.getResources().openRawResource(R.raw.oceanairdrop)
    val certificate = certificateFactory.generateCertificate(inputStream)
    inputStream.close()

    // Create a KeyStore containing our trusted CAs
    val keyStoreType = KeyStore.getDefaultType()
    val keyStore = KeyStore.getInstance(keyStoreType)
    keyStore.load(null, null)
    keyStore.setCertificateEntry("ca", certificate)

    // Create a TrustManager that trusts the CAs in our KeyStore.
    val tmfAlgorithm = TrustManagerFactory.getDefaultAlgorithm()
    val trustManagerFactory = TrustManagerFactory.getInstance(tmfAlgorithm)
    trustManagerFactory.init(keyStore)

    val trustManagers = trustManagerFactory.trustManagers
    val x509TrustManager = trustManagers[0] as X509TrustManager

    // Create an SSLSocketFactory that uses our TrustManager
    val sslContext = SSLContext.getInstance("SSL")
    sslContext.init(null, arrayOf(x509TrustManager), null)
    var sslSocketFactory = sslContext.socketFactory

    // Create an instance of OkHttpClient
    m_client = OkHttpClient.Builder()
            .sslSocketFactory(sslSocketFactory, x509TrustManager)
            .hostnameVerifier(myHostNameVerifier())
            .build()

    return m_client
}

private fun myHostNameVerifier(): HostnameVerifier {
    return object : HostnameVerifier {

        override fun verify(hostname: String, session: SSLSession): Boolean {

            if (hostname == "192.168.0.18") {
                return true
            }

            return false
        }
    }
}

Wrapping Up

That's it.... All works. If you are like me and have moved the port numbers of your API, then don't forget to open the firewall to those ports!

Read More

Excel Interop & C#

If you've ever developed even a small line-of-business application, sooner or later, you know your users are going to ask:

"Erm... This is great and everything but can we get this data exported to excel?"

Now, of course, exporting data to Excel has always been possible and back in the day, you would use COM Interop to create an Excel file using C# but... how do I put this? - Let's just say it wasn't pretty!

Take this small snip of code for example:

private void ExcelTest()
{
     object mMissingValue = System.Reflection.Missing.Value;

     Application excelApp = new Application();

     // Open the file
     Workbook workBook = excelApp.Workbooks.Open(templatePath,
              mMissingValue, mMissingValue, mMissingValue, mMissingValue, mMissingValue,
              mMissingValue, mMissingValue, mMissingValue, mMissingValue, mMissingValue,
              mMissingValue, mMissingValue, mMissingValue, mMissingValue);

     Worksheet sheet = (Worksheet)workBook .Worksheets[1];
}

Notice all those missing values? Your code would be littered with them. Yuk!. - Those missingValue definitions represented "optional parameters" that can be passed through to the API, but even though we wasn't using any of them, we still had to specify all of them.

Of course, that was before the dynamic keyword was introduced with .NET 4.0. Then everything changed.... The above code could be written like this:

private void ExcelTest()
{
   var excelApp = new Application();

   var workBook = excelApp .Workbooks.Open(templatePath);

   var sheet = (Worksheet)workBook.Worksheets[1];
}

That's much better and easier on the eye. The dynamic keyword made interop with COM libraries so much simpler. For all places where an API had took an object the new interop library itself could replace that object with a dynamic.

...Of course that was then. This is now and things are always changing. - Today, if someone asks me to create an Excel file, I head off to NuGet and reach for "EPPlus".

EPPlus

EPPlus allows you to create advanced Excel spreadsheets using .NET, without the need of interop and its very feature rich. What I particularly like about the library is the documentation and samples. It's very well documented and is chock full of example code.

To install it via Nuget, type: the command: Install-Package EPPlus

As a quick example, if your working in C# there is a good chance you already have the app data held in a DataTable. In that scenario, here is a full example of outputting a DataTable to an excel file using EPPlus:

public static void WriteToExcel(DataTable tbl)
{
    var saveFileDialog = new SaveFileDialog();
    saveFileDialog.Filter = "Excel Workbook (*.xlsx)|*.xlsx|All files (*.*)|*.*";
    saveFileDialog.DefaultExt = "xlsx";
    saveFileDialog.Title = "Save As Excel Report";
    saveFileDialog.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Personal);

    if (saveFileDialog.ShowDialog() == DialogResult.OK)
    {
        FileInfo outputFile = new FileInfo(saveFileDialog.FileName);
                       
        using (ExcelPackage package = new ExcelPackage())
        {
            //Create the worksheet
            ExcelWorksheet ws = package.Workbook.Worksheets.Add("AppTab");

            // Write the datatable into the sheet, starting from cell A1. 
            ws.Cells["A1"].LoadFromDataTable(tbl, true);

            // save our new workbook and we are done!
            package.Save();

            // open the excel file
            System.Diagnostics.Process.Start(outputFile.FullName);
        }
    }
}

Just, so we are on the same page this one line is the main work-horse of this function:

// Write the datatable into the sheet, starting from cell A1. 
ws.Cells["A1"].LoadFromDataTable(tbl, true);

How easy is that? Very nice! There is so much that the EPPlus library can do, and I have only scratched the surface with this teeny-tiny example.

ExcelDNA

Okay, that's all cool but what if you want to go deeper? What if you need to create a bespoke excel function that your users can use to run a business specific formula?

Thats where ExcelDna comes in. I have only found out about this recently but it is a fast and easy way to create Excel AddIns in C# (again, something that used to be a pain in the what-now!)

Again, the library is available via Nuget via the command: : Install-Package ExcelDna.AddIn

Using this library, creating Excel functions is as easy as this:

public static class MyFunctions
{
    [ExcelFunction(Description = "Say Hello to Ocean Airdop")]
    public static string SayHello(string name)
    {
        return "Hello " + name;
    }

    [ExcelFunction(Description = "Lookup From Database")]
    public static int OceanAirdrop(string nameToFind)
    {
        var dbConn = new DBConnectionSqlSvr(@"127.0.0.1", "sa", "password", "database");

        var sql = "select count(*) as [count] from [some_table] where name = " + nameToFind;

        var result = dbConn.ExecSqlCommandScalar(sql);

        return result;
    }      
}

As you can see above, you have the full power of C# and the .NET framework using this library and it is easy-peasy!

I have not really explored ExcelDNA but the documenation for the project can be found here and the github page for the project can be found here

Wrapping Up

As EPPlus has saved my bacon on a number of occasions, I have added to my programmers toolkit!. If you're in a pinch, and need to export some business data into excel I would reach for EPPlus. It's a nice and simple library!

If you have other needs that extend beyond simply creating excel documents from C# perhaps ExcelDna could be the ticket for you.

Read More