Passing Enumerables to a SQL Stored Procedure

Passing Enumerables to a SQL Stored Procedure

I have been asked the question “Can you pass an enumerable to a procedure?” or “How do you pass a table to a stored procedure” several times in the past. The simple answer is “yes”, the slightly more complex answer is “yes, and this is how”!

How to pass an Enumerable to a SQL Stored Procedure in .NET

Although my example code here is in C# the same process applies to other .NET languages.
You can indeed pass an enumerable object to your Stored Procedure by using a special type of SQL object called a “User Defined Table Type”. UDTTs can be used to create tempory tables in a SQL Query without the need to fully write out the TABLE statement; in much the same way they can be used to pass a tempory table to a stored procedure. The only restriction is that the parameter passed into the query must be marked as read-only.
An example SQL statement to create a User Defined Table Type is as follows:

Read more

Obtaining the meta data from a SQL Query

Obtaining the meta data from a SQL Query

I recently had a need to find a way of obtaining just the column names returned by a SQL Server Query; the query in question is ad-hoc and entered into an application by the user (in this case my application is a WPF desktop application, and the query is Microsoft T-SQL).

One quick and simple way of obtaining the column names would be to execute the query; I will not go into the details of why executing arbitrary SQL statements pasted into a text box by your users is a bad idea. Suffice to say it is a very bad idea; especially if your application is run with higher privileges. In my case, these pasted queries are also persisted in the database if a user with low rights passed a dangerous query someone with higher rights could come across it in the future causing it to be executed.

I needed to find an alternative; I contemplated a couple of things:

  • A complicated Regular Expression (RegEx)
  • Wrapping the execution in a BEGIN TRANSACTION and ROLLBACK TRANSACTION.
  • Running the query under a low (db_reader only) privilege account

Whichever way I looked at it the options didn’t seem great, and I thought there must be an alternative. It turns out there is!

Read more

Splitting trusted and untrusted networks

Splitting trusted and untrusted networks

There have been a number of articles in the press in the last year about Internet of Things (IOT) devices being hacked and forming parts of botnets, or just exposing data from the networks they sit on. Most users just pick up a device and type in their wireless password or plug in a network cable; being the IT Professional (ha !) that I am I decided it was time to look into network segmentation.

For those reading who do not understand what this is: the two basic categories are Trusted and Untrusted devices; I trust my personal laptop and my server (I manage their security after all), the IOT thermostat and the bargain price CCTV system I bought years back, however, are a different story. So they need to be in seperate secured areas of my home network.

Following some guides from Troy Hunt and other Ubiquiti fans, I have succeeded in splitting these two sets of network clients using my Ubiquiti Unified Security Gateway (USG). The following is a rough guide to separating your networks using Ubiquiti equipment with an optional step of creating a captive guest wifi portal for visitors. I am sure that other network equipment manufacturers provide the ability to layout a network in this way, but I do not have their hardware to test!

Read more

Do you fizz buzz, buzz fizz, or just scratch your head?

Do you fizz buzz, buzz fizz, or just scratch your head?

Over the years I have interviewed a lot of developer candidates and given advice to a fair few potential developers who are looking for their first interviews. Again and again, the FizzBuzz test has cropped up, and I have seen both some fantastic and some not so fantastic answers to the problem. For those who are not aware of what the FizzBuzz test is, here is the standard question:

Write a function which prints all the numbers from 1 to 100. But when printing a number which is a multiple of three print the word “Fizz” instead of the number, when the number is a multiple of five print the word “Buzz”. If the number is a multiple of both three and five write the word “FizzBuzz” instead of the number.

FizzBuzz is a simple program, or at least it should be. I think that some people who are new to the industry find it hard because it does not fit into the standard coding styles taught in school/university, it isn’t just a simple for loop nor can you just implement an if then else. This is why it can be such a good tool to break the candidates apart from those who can code and those who can problem solve; that being said it only really works for the earlier stages of someone’s development career (although if a senior developer couldn’t solve this problem then they may need to seek alternative employment).

Read more