T-SQL equality operator ignores trailing spaces

T-SQL equality operator ignores trailing spaces

Today I discovered something new about SQL Server while debugging an application. T-SQL’s equality operator ignores any trailing spaces when comparing strings. Thus, these two statements are functionally equivalent:

SELECT * FROM Territories WHERE TerritoryDescription = 'Savannah'SELECT * FROM Territories WHERE TerritoryDescription = 'Savannah         '

When executed against the Northwind database included with SQL Server they both return the same row, which has no trailing spaces after its TerritoryDescription.

TerritoryID          TerritoryDescription                               RegionID    -------------------- -------------------------------------------------- ----------- 31406                Savannah                                           4(1 row(s) affected)

This behaviour isn’t immediately obvious from the offset, and isn’t mentioned on the MSDN entry.

To avoid this problem, you should use LIKE instead:

SELECT * FROM Territories WHERE TerritoryDescription LIKE 'Savannah         '

When comparing strings with LIKE all characters are significant, including trailing spaces.

Update: a co-worker discovered yesterday that using LIKE in T-SQL JOINs doesn’t use indices in the same way that the equals operator does. This can have a significant impact on performance. Be warned!

Implementing access control in a tiered application

Implementing access control in a tiered application

Recently I have been working on a reasonably-large ERP system that supports around 2,000 users. The system has approximately forty access control groups to which individual users can belong. Each of these groups grants or restricts access to parts of the system. For example, a user may be allowed to edit the price of a product if they are in the SalesManagers group. If they are in the InventoryManagers group, they can edit the number of items in stock. If they are in the Admins group they can do everything.

public class User{        // A list of groups the user belongs to.        public List<string> MembershipGroups { get; };         // ... etc}

To handle all these access groups and their associated rules, a form page’s load method contained several screenfulls of business logic to determine which elements should be editable by the user and which should not.

Here’s a simple example of what it might look like on an EditProduct page:

public class EditProductPage : Page{        // Form elements.        private TextBox ProductName;        private TextBox Price;        private TextBox NumberInStock;         public void InitializeForm()        {                if (CurrentUser.MembershipGroups.Contains("Admins"))                {                        // Admins can change anything.                        ProductName.Enabled = true;                        Price.Enabled = true;                        NumberInStock.Enabled = true;                }                else if (CurrentUser.MembershipGroups.Contains("SalesManagers"))                {                        // SalesManagers can change the product's price.                        ProductName.Enabled = false;                        Price.Enabled = true;                        NumberInStock.Enabled = false;                }                else if (CurrentUser.MembershipGroups.Contains("InventoryManagers"))                {                        // InventoryManagers can change the inventory levels.                        ProductName.Enabled = false;                        Price.Enabled = false;                        NumberInStock.Enabled = true;                }                else                {                        // Regular users can't do anything.                        ProductName.Enabled = false;                        Price.Enabled = false;                        NumberInStock.Enabled = false;                }                 // ... other complex business logic        }}

Now this is all good and well, but what happens if someone is a member of SalesManagers and InventoryManagers? Or if we add rules related to editing certain types of products? What happens if we need to make another form? This code will grow exponentially and start to spaghetti out of control.

I believe there are two fundamental problems with this design. Firstly, it is not clear what the different access groups mean in the context of the form. For example, if you’re a member of the InventoryManagers group, which fields should be editable and which should be locked? These rules are probably defined clearly in a requirements document somewhere, but are difficult to understand from reading the source code.

The second problem is that these rules are implemented at each point they are consumed, i.e. in the presentation layer. Each form is free to access and interpret what each group means. This logic clearly don’t belong here: a form page should only know how to hide and show elements! On the other hand, it seems this responsibility doesn’t lies exclusively with the business layer either, as it relates to form-specific elements. So where should it go?

In a well-designed application, the relationship between group membership and access privileges would not be exposed to the UI. Instead, we should define a list of easily-understood permission flags that have direct meaning to consumers of our model. Each of these flags will explicitly define what a user can and cannot do. They must be as simple to understand as possible.

public class AccessPermissions{        // Correct: these flags can be applied directly to a form.        public bool CanEditName { get; };        public bool CanEditPrice { get; };        public bool CanEditNumberInStock { get; };         // Incorrect: this flag is too vague.        bool IsAdmin { get; };}

We can then add these permissions to our User class:

public class User{        // A list of groups the user belongs to.        public List<string> MembershipGroups { get; };         // The user's access permissions.        public AccessPermissions Permissions { get; };         // ... etc}

Setting these flags may involve complex and conditional business logic, which is now removed from the presentation layer. These simplified flags can then be directly used by the form’s Supervising Controller:

public class ProductEditFormController : ISupervisingController         // The passive view for the current form.        private IEditFormView Form;         // Set up form state.        public void InitializeForm()        {                AccessPermissions permissions = CurrentUser.Permissions;                 if (permissions.CanEditName)                        Form.ProductName.Editable = true;                 if (permissions.CanEditPrice)                        Form.Price.Editable = true;                 if (permissions.CanEditNumberInStock)                        Form.NumberInStock.Editable = true;                 // ... etc        }}

Privileges should be separated amongst multiple classes, specific to different areas of functionality. For example, you might provide a UserPermissions class for a user management form:

// Permissions related to user management.public class UserPermissions{        bool CanEditName;        bool CanEditMembershipGroups;        bool CanDeleteUser;}

By removing access level-related logic from the point of consumption and declaring them as explicit, immediately-usable flags, we can make it much easier for developers to consistently apply access control. Removing potentially complex business logic from the presentation layer cuts down on code duplication (and bugs). The overall quality of the system improves.