Andy Crouch - Code, Technology & Obfuscation ...

Encoding Html Strings For SQL Storage

Photo: Daria Nepriakhina - Unsplash

Recently I reviewed some code that contained a problem that I hadn’t considered for a long time. Storing Html strings in a database.

The basic rule is that you should not store unencoded Html in the database for a couple of clear reasons:

  • Security. If the strings stored in the database are going to be inserted to the DOM or a <script> tag then it should be escaped. You should be following the security guidelines (as a minimum) from the OWASP Project. Doing so will help you prevent Cross-Site Scripting (XSS). Not encoding your Html (or XML/JSON) strings facilitates XSS.
  • It will break your pages layouts. Users have a knack for saving data you won’t have had the chance to think about or test. If a user creates a snippet in your database that includes </body> then you will get a broken page when it is rendered.

Most languages and stacks have inbuilt utilities and libraries for preventing XSS. They can handle the act of encoding so you do not have to write your implementation. In C# you can use the HtpUtility classes HtmlEncode and HtmlDecode methods. The sample code below shows how this works.

using System;
using System.Web;

public class Program
  public static void Main()
    var htmlString = @"<!DOCTYPE html> <html> <head> <meta charset='UTF-8'> <title>Example HTML Encoding Page</title> </head> <body> <p>This Is An Example File</p></body> </html>";
    Console.WriteLine("Non Encoded\n{0}", htmlString);
    var encodedString = HttpUtility.HtmlEncode(htmlString);
    Console.WriteLine("Encoded\n{0}", encodedString);
    var decodedString = HttpUtility.HtmlDecode(encodedString);
    Console.WriteLine("Dencoded\n{0}", decodedString);

In my projects, I tend to wrap the HttpUtility calls into extension methods. This means that if I have to change or add something to the implementation going forward I have one place to change.

public static class StringExtensions
  public static string HtmlEncode(this string s)
    return HttpUtility.HtmlEncode(s);
  public static string HtmlDecode(this string s)
    return HttpUtility.HtmlDecode(s);

If you have any comments or thoughts around this post then please contact me via twitter or email.

SQL Foreign Keys Should Only Relate To One Table

Photo: Caspar Camille Rubin

In my last post, I covered why storing enumerations in your database was the wrong thing to do. The piece of work that came to me as a Pull Request that generated that post was a PR that kept giving. This post is based on a second issue I encountered in that review and why it is also the wrong thing to do.

In a database schema, a Foreign Key should only connect two tables.

The change in the PR was implementing an audit table for some notifications that are sent. The audit table was to store an Id for the sent notification, an Id for the Subscriber that viewed the notification and an Id for some content sent with the notification. All very simple. Only I could see that there was no Foreign Key defined for the SubscriberId Column. I tried to create a simple query to join to the subscriber’s table from the SuscriberId. It turned out that a subscriber table had not been created. Instead, the SubscriberId was either:

  • The Id for a fully signed up User (the projects user data is split between Customer and Supplier user data)
  • The Id for an Unknown Subscriber, someone that was not a customer but who could receive the notification.

I asked the original developer to write a query that would allow me to view the opened notification audit data. I did this to illustrate that following this approach of not using a Foreign Key would make for a very hard to maintain data schema. The reason they thought this was a good idea was that they are storing the SubscriberType which was an enumeration in the database. The query I was sent back was like the following:

FROM      AuditTable Join To CustomerTable
WHERE     SubscriberType = 1
FROM      AuditTable Join To SupplierTable
WHERE     SubscriberType = 2
FROM      AuditTable Join To UnknownSubscriberTable
WHERE     SubscriberType = 3

Wow. So much confusion and hard work. I always have found that if I have a UNION in a query I have almost certainly got my schema or design wrong somewhere.

There was a clear need to clean this schema up. Remember

A foreign key is a column or group of columns in a relational database table that provides a link between data in two tables. It acts as a cross-reference between tables because it references the primary key of another table, thereby establishing a link between them.

By creating a Subscription table and accumulating known and unknown subscriber data we can then add a Foreign Key for the audit table. The table contains data specific to the subscriber and is now managed through the subscription process. While there are some duplicate data fields at present this is a temporary step until we clean up the customer and supplier data into a single user data table. At that point, we will remove the duplicate fields and use further Foreign Keys.

Thinking about your schema is vital as you develop your application. While you may find you adapt it as you build functionality, seeing clear relationships and dependencies is key to underpinning clean code. I have taken on projects in which the database has no Foreign Keys and they are always more difficult to maintain and enhance. You will benefit from not trying to save time in developing one feature as it will almost always make dependant changes or requirements harder to implement.

If you have any comments or thoughts around this post then please contact me via twitter or email.

Storing Enumerations In SQL Server


SQL Server does not support Enumerated data types so please stop trying to force them into it.

Enumerations are used to declare a type that groups named constants. Most languages have them in a given form going as far back as Ada. In C# you declare an enumeration as follows:

public enum Colour

A common use for enumerations is to apply a label for grouped constant values. So the above may be written as:

public enum Colour
    Red = 1, 
    Green = 2, 
    Blue = 3, 
    Yellow = 4

There are lots of articles on the wider web about using enumerations in your language. This post is about the misuse of enumerations which is not only bad design but has a major impact on your project.

Storing an enumeration value in the database may seem appealing. You define your Colour enumeration and define your entity class.

public class Entity
    public Colour EntityColour{get; set;}

You define your SQL table column to be an int and add the scaffolding code to handle your crud operations. It all works and you move on.

Only a little while later someone else has to write a query on your table containing your Colour values. They have no idea what the values represent. Is 1 red or green or blue? Who knows. They start the process of tracking down who wrote the code. Soon they arrive at your desk but it is 4 months since you last looked at the Colour code. So you have to open the code and find the enumeration declaration and provide them with the details. Now they have the details they end up with a nasty CASE block in their query like

  WHEN Colour = 1 THEN 'Red'
END AS Colour

Removing magic numbers in our code by using enumerations, we have complicated the data schema. The better approach is to use a lookup table in your database. We then create a relationship via a Foreign Key with the table that stores our Colour data. Again there are plenty of articles on defining database tables and relationships. The point of this post is to show that storing enumeration values in the database is wrong. It leads to a confusing data schema and difficult to maintain code. Using a well-defined database schema will result with an extra Colour type in your code and an easy to follow validated dataset that is built on a standard join.

This article has made enumerations sound like they shouldn’t be used and that’s not that case at all. In a future post I will cover how and when to use them effectively.

If you have any thoughts around this post then please contact me via twitter or email.

180-Degree Review Experiment - Part 1

Photo: Charles 🇵🇭 - Unsplash

I decided to try an experiment this week.

I have asked my team, juniors through to seniors I have worked with for over 10 years, to each write a full review, on me.

Why? Well, I undertake reviews of each of my team every three months. I have written before that this is a useful technique when running remote teams. It ensures that issues do not get to brew for too long and I can hear what’s going well and what needs improving. It also means that the team get quality one on one time with me. The reviews are simply an informal chat. We can cover anything from particular work to development techniques and training plans. I make the effort to write up the conversations and the employee gets to comment and sign them off. We can then review progress on each next review.

During reflection on the first half of the year and how its been going it occurred to me that those reviews are generally one way. I gave thought to why this was the case and how much impact doing the reviews via video chat can have. Does my team have feedback that they would not be comfortable to share in person? Does the time limit we try to stick to mean that we never get to 180-degree feedback? Have I just never asked the questions?

So I have sent the whole team a blank review form. What should I be doing? What have I done well and not done well? What should I think about focusing on? They can write as much as they like and I have made it clear that the aim is to give me direction. There is no comeback and if they don’t want to discuss points with me they don’t have to.

They have two weeks to complete them and send them back. I will follow up in a few weeks time on the results and if it was a useful exercise.

To be honest, I have never felt quite so exposed.

If you have any comments around this post then please contact me via twitter or email.

Timbuk2 Command Laptop Backpack

Photo: Andy Crouch

I recently brought a new laptop bag for day to day use. I had previously switched to a slimline messenger bag as I have been trying to carry less. My commute has extended by 40 minutes over the last year on days I go to the office. I have found that I actually need to carry slightly more to keep me amused and to see me through one or two days. For these reasons and the fact Sam, our cat, decided to pee on my previous bag it was time for a new one.

For a long time, I have been looking at Timbuk2 bags. Started in 1989, Timbuk2 initially started by selling quality messenger bags for cycle couriers. These days they have widened their range. I have heard many people that I know (and some I don’t) recommend these bags. They have a reputation of lasting and being hard wearing while retaining a unique look. They also have a reputation for lots of pockets.

To be honest I missed a good backpack after a year with a slim messenger. After a little browsing, I opted to order a Command Backpack in Jet Black.

I have now been using the bag for a little over 2 months and it’s fair to say it’s worth every penny. The bag states it can hold up to 30 litres but I have yet to fill it to capacity. There is a main compartment which I use for packing a hoodie, 2-litre bottle of water, project books, reading books and protein bottles. There is still a lot of room left. There is a second main compartment with several organiser pockets and pen and cable organisers. On the front, there is a small easy access pouch and a Tricot lined glasses compartment.

The main laptop compartment is placed on the back of the bag and unzips on three sides. It is Tricot lined and will hold up to a 15” laptop. I suspect this really means a 15” Mac but my current daily driver which is a 15.6” PC Specialist laptop does just fit. It was tight though. When you unzip the compartment there is also a small section for a tablet or a small notebook. To be honest this is where one of the negative aspects of the bag is to be found. I get that the design is TSA compliant but that is little comfort when trying to get your laptop out in a crowded train. Due to the three-sided zip and the weight of the main bag, it can be awkward to get the compartment shut. This is especially true when you have only one hand or if you have a travel mug or bottle in the side pouch. I have learned to master it but it took time.

The other negative point is the compression straps on the bottom of the bag. They are there to “pull in” the bag if you are not using all the available space. Unfortunately, this means that there is no bottom for the bag to stand on. Not a big issue but it took a little getting used to and a few bag tips.

This Command is comfortable. The shoulder straps are wide and so should fit most body shapes. Even fully loaded I don’t feel the bag is heavy. I really like this bag. It has more than enough space while being practical and it comes with a lifetime warranty. Barring the need to get used to the different positioning and zip system of the laptop compartment I would recommend this bag.

Have you got a recommendation for laptop bag brands or questions about the Command Backpack? If so then please contact me via twitter or email.