It’s OK, My eval is Sandboxed (No It’s Not)

The idea of using eval has always been in interesting debate. Instead of writing logic which accounts for possibly hundreds of different scenarios, creating a string with the correct JavaScript and then executing it dynamically is a much simpler solution. This isn’t a new approach to programming and is commonly seen in languages such as SQL (stored procedures vs. dynamically generating statements). On one hand it can save a developer an immense amount of time writing and debugging code. On the other, it’s power is something which can be abused because of it’s high execution privileges in the browser.

The question is, “should ever be used?” It technically would be safe if there is a way of securing all the code it evaluates, but this limits its effectiveness and goes against its dynamic nature. So with this, is there a balance point where using it is secure, but also flexible enough to warrant the risk?

For example purposes, we’ll use the following piece of code to show the browser has been successfully exploited: alert(‘Be sure to drink your Ovaltine.’); If the browser is able to execute that code, then restricting the use of eval failed.

In the most obvious example where nothing is sanitized executing the alert is trivial:

eval will treat any input as code and execute it. So what if eval is restricted to only execute which will correctly evaluate to a complete statement?

Nope, this still successfully executes. In JavaScript all functions return something, so calling alert and assigning undefined to total is perfectly valid.

What about forcing a conversion to a number?

This still executes also, because the alert function fires when it is parsed and its return value is converted to a string and then parsed.

The following does stop the alert from firing,

But this is rather pointless, because eval isn’t necessary. It’s much easier to assign the value to the total variable directly.

What about overriding the global function alert with a local function?

This does work for the current scenario. It overrides the global alert function with the local one but doesn’t solve the problem. The alert function can still be called explicitly from the window object itself.

With this in mind, it is possible to remove any reference to window (or alert for that matter) in the code string before executing.

This works when the word ‘window’ is together, but the following code executes successfully:

Since ‘win’ and ‘dow’ are separated, the replacement does not find it. The code works by using the first eval to join the execution code together while the second executes it. Since replace is used to remove the window code, it’s also possible to do the same thing to eval like so:

That stops the code from working, but it doesn’t stop this:

It is possible to keep accounting for different scenarios whittling down the different attack vectors, but this gets extremely complicated and cumbersome. Further more, using eval opens up other scenarios besides direct execution which may not be accounted for. Take the following example:

This code bypasses the replace sanitations, and it’s goal wasn’t to execute malicious code. It’s goal is to replace the JSON.parse with eval and depending on the application might assume that malicious code is blocked, because JSON.parse doesn’t natively execute rogue code.

Take the following example:

The code does throw an exception at the end due to invalid parsing, but that isn’t a problem for the attacker, because eval already executed the rogue code. The eval statement was used to perform a lateral attack against the functions which are assumed not to execute harmful instructions.

Server Side Validation

A great extent of the time, systems validate user input on the server trying to ensure harmful information is never stored in the system. This is a smart idea, because removing before storing it tries to ensure everything accessing potentially harmful code doesn’t need to make certain it isn’t executing something it shouldn’t (you really shouldn’t and can’t make this assumption, but it is a good start in protecting against attacks). With eval, this causes a false sense of security, because code like C# does not handle strings the same way that JavaScript does. For example:

In the first example, the C# code successfully removed the word ‘window’, but in the second, it was unable to interpret this when presented with Unicode characters which JavaScript interprets as executable instructions. (In order to test the unicode characters, you need to place an @ symbol in front of the string so that it will treat it exactly as it is written. Without it, the C# compiler will convert it.) Worse yet, JavaScript can interpret strings which are a mixture of text and Unicode values making it more difficult to search and replace potentially harmful values.

Assuming the dynamic code passed into eval is completely sanitized, and there is no possibility of executing rogue code, it should be safe to use. The problem is that it’s most likely not sanitized, and at best it’s completely sanitized for now.

Project Oxford – Image Text Detection

Microsoft has a new set of services which use machine learning to extrapolate data from images and speech called Project Oxford. Each service has a public facing REST api allowing any program capable of communicating through HTTP to utilize it, and for smaller amounts of data, Project Oxford is free to use.

Microsoft classifies these services into three categories: vision, speech, and language. The vision section contains several different capabilities including facial recognition, facial emotion detection, video stabilization, and optical character recognition (OCR). It’s OCR api allows a system to send an image URL and in turn will return the text it detected. Since the service is system agnostic, it’s possible to create an HTML page (with a little help from a service like Imgur) which can upload an image and extract the text without using server side resources. (The completed example can be found here.)

How to create

Uploading the Image

The first step is uploading the image to a publicity accessible store. Imgur works well for this, because it’s easy to use and free. It requires an account and registering the “application”, but this is relatively quick and painless. The page to do so is found here, and with the api key, all that is left is to retrieve the image from the input and upload the file.

Uploading the File

HTML 5 has the input type: file. To upload the file when the user selects it, add an onchange event to the input.

Now the function uploadImage fires whenever the user select a new file.

With the help of the FileReader, the webpage can load the image into memory to send to the server. It has several methods for retrieving files such as readAsText and readAsBinaryString, but the one which allows the image to upload correctly is readAsDataURL as this uploads the image in the correct encoded format.

Reading the file is an asynchronous operation and requires a method to call when loading. There is a property in the FileReader object named onload and has event parameter, but it’s not necessary in this context, because the reader’s result object is available through closure, so omitting it is fine.

Sending the File to Imgur

Whether using JQuery, another framework, or plain JavaScript, the upload process is straight forward. This example is in JQuery.

The Imgur api exposes the https://api.imgur.com/3/image URL, and to upload an image, use POST and add the HTTP header Authorization with the client id: Client-ID application_client_id. The trickiest part concerns the file upload from the FileReader. The readAsDataURL returns more data than necessary and the contents look like

The comma and everything before it is extraneous and will cause an error, so it’s necessary to remove it. Using String.Split(‘,’) and picking the second item in the array is the easiest approach.

Posting the image to Imgur returns several pieces of data about the object, height, width, size, etc., but the key pieces are, link (which is the URL to the image), and deletehash (this will be used to remove the image once completed. The api documentation says in some cases the id can be used, but since this is an anonymous posting, the delete hash is necessary).

Data OCR

Project Oxford requires a login to grant the necessary api key. After signing up and logging in, the service portal lists a page with access to the various API keys (there is one for each service). The once necessary for OCR looks like:

get api key for ocr

To retrieve the image text information, POST to: https://api.projectoxford.ai/vision/v1/ocr. It requires the HTTP Header Ocp-Apim-Subscription-Key with the value being the client id retrieved from the key dashboard. The data contains the URL as the key, and there are two optional querystring parameters which the request can have: language and detectOrientation.

language

The parameter language specifies which language to use when performing the OCR. If it’s not provided, the service attempts a determination based on the image and return what it thinks is correct. This can be helpful, if the language is unknown, and goal of the OCR process is to extract the text and then translate it. (Unfortunately, Microsoft has disabled the features, mainly Cross Origin Request Sharing, for retrieving the authentication token necessary to create an application not having any server side processing in it’s Microsoft Translator API). Not including leaves the service up to guess and can provide incorrect results.

The parameter is the BCP-47 language code which follows ISO 639-1. (A lowercase two character code representing each language e.g. en for English, es for Spanish, etc.) At this time the API only supports about 20 languages, but Microsoft has said it is working to expand the list. The API documentation lists the supported languages under the language parameter.

detectOrientation

This is a boolean parameter indicating it should attempt to recognize the text and orient it to be parallel with the top of image’s bounding box. Sometimes this can help in how it groups the returned text.

Response

The service returns an object looking like this:

language

The language property is what the service thinks the language in the image is, and if there are multiple languages in the text, it still only returns one. The service makes its best determination and won’t necessarily pick the first it encounters.

TextAngle

TextAngle is the tilt the service thinks the image is set to, and orientation is the direction the top text is facing. If the text is facing 45 degrees to the right, the text angle would be “-45.000” and the orientation would be “Right”.

Regions

multilanguage The found text in the image is not lumped together when returned. There are separate entries for each section of text found. In the example to the right, Hola and Hello are found in two different areas, so there are two regions returned in the array. Each region has a property boundingBox which is a list of comma separated coordinates where the text region exists on the page. In each region there is a lines property which is an array of objects each with their own boundingBox and each line has a words property object array separating each word and also containing the boundingBox property.

Removing the Image

Removing the image is similar to the initial POST action. This time the action is DELETE and the URL has the deletehash appended to the end.

The Javascript code can be found here.

Minutes and Seconds

When dealing with dates in .NET, most applications use the System.DateTime struct to store and manipulate dates. It has a natural conversion from MSSQL Server’s DateTime datatype, and has little difficulty in translating into different data formats (JSON, XML, etc.)

For a while now JSON has been a standard in communicating between applications, web clients and servers, to and from database and so on, and serializing a DateTime object looks like this converting it into the ISO 8601 format:

and converting it back works exactly as expected:

Serializing the date and parsing it with JavaScript on some browsers (such as Chrome for Mac and Windows, and Safari) yields different results. The browser has moved the date 4 hours into the past, because JavaScript has assumed the time it parsed was in UTC.

Secondly, JavaScript based its interpretation off of an incorrect timezone assumption. In 1923, the majority of the United States stayed on standard time the entire year, and those that moved their clocks forward did so on April 29. Daylight Savings Time was not in effect, and throughout history, the use of it, and when it was applied, has varied. Even if the passed in date was meant to be in UTC, the conversion should have made it Mon March 19, 1923 19:00:00 GMT -0500 (EST).

This also means when serializing the date to JSON and sending it back to the server, it sends it as it’s assumed UTC date.

At first glance, this looks correct, but changing the date to 1923-05-20T00:00:00.000Z (adding the Z at the end, indicating the date is in UTC) yields 3/19/1923 7:00:00 PM showing that the .NET DateTime object is trying to apply the Time Zone Offset. Now the question is, “Why is .NET deserializing the date as 3/19/1923 7:00:00 PM and not 3/19/1923 8:00:00 PM like JavaScript does especially since the serialized date came from JavaScript after it interpreted the original date as UTC?” The answer is because JavaScript only interpreted the date as UTC and displayed it as the timezone the machine is set in. The date it had didn’t change, so when it serialized it to the ISO 8601 format, JavaScript only appended the Z thinking the date was already in UTC.

Sending the Time Zone Offset when serializing from .NET, fixes the inconsistencies in some instances, but not all of them.

Since the time zone wasn’t specified, .NET assumes it is the local time of the server and produces 2016-03-11T19:00:00-05:00. Loading that string into a JavaScript Date object produces the same results although in UTC:

One of the issues with creating DateTimes in .NET and not explicitly setting the timezone is that it can make incorrect assumptions. Setting the timezone to Indiana (East) and parsing the following date 1989-05-12T00:00:00.000Z yields 1989-05-11T20:00:00-04:00 showing the UTC conversion to Eastern Daylight Time. Indiana didn’t follow Daylight Savings Time until 2006, meaning the conversion is incorrect. (Even if it did do the conversion correctly, setting the Set Timezone Automatically flag in Windows 10 forces it to be Eastern Time)

Explicitly setting the timezone using the DateTimeOffset corrects the problem in .NET, but it doesn’t guarantee correctness across systems.

When the browser parses the date with the correct offset, it attempts to display the date with the offset it thinks it should be.

The data in the Date object is still correct, and exporting it will not alter it in any way. Its attempt to display the date in a local time is the issue. In the United States, the only dates that are affected are prior to 2007, because that is when the last time a change to the timezones occurred. (The start of Daylight Savings Time was moved ahead to the second Sunday in March, and then end extended to the first Sunday in November. If you load a date from the year 2004 into JavaScript in Chrome, the date will incorrectly show for all dates between March 14 and April 4.)

The real solution to handling dates is to use libraries specifically designed to handle timezones and Daylight Savings Time (MomentJs for JavaScript and NodaTime for .NET). These significantly reduce the inconsistencies found in using the built in date and time conversions found in .NET and in Browsers, but even these can have mistakes in them.

Configuring Logic

This question talks about removing a switch statement so that every time the business logic changes concerning a multiplier value, the C# code itself doesn’t have to be changed and the application recompiled. I proposed loading the keys and multiplier values from a configuration file into a dictionary and accessing the data when needed. (The following example shows it loaded in the constructor for brevity.)

A comment in the answer mentioned the benefits of creating extra classes, and how the dictionary approach could not handle more advanced calculations should the need arise. With a slight modification, and some additional code, this no longer becomes a hinderance. Expression Trees allow the program to dynamically create functions and execute them as it would with compiled code.

Based on the question and the example above, the current equation has two parts, the travelModifier (which is determined by the mode of transportation) and the DistanceToDestination. These are multiplied together, and return a decimal. Completely abstracting this out into its own function (which then becomes the model to base the configurable functions from), would make the method look like:

Since the travel modifier already comes from the configuration file, it is unnecessary to pass that into the function, because when the application reads the configuration and creates the method, each entry will have the travelModifier value already coded into the function so that parameter can be removed, and an example function in C# would look like:

To accomplish this, each entry in the configuration file would need to have two parts, the method of travel (bicycle, bus, car, etc.), and the equation. The latter is a combination of the travelModifier constant, the distanceToDestination and operators (+,-,/,*). An entry in the file would look like this:

Before loading the configuration file, the dictionary which will hold the function and retrieve it based on the selected method of travel will need to be changed. Currently it has a string as the key and a double as the value:

Instead, it needs a function as the value.

Loading the contents from the configuration file has a few different steps. Retrieving and separating the parts, parsing the equation, and creating the method at runtime.

Loading the Configuration File and Separating the Parts

Parsing the Equation

It would be possible to parse the equation and immediately convert it to an Expression, but it’s normally easier to load it into an intermediate structure so data can be transformed and grouped into a usable structure first. The equation has three parts, and an enum can help distinguish between them.

and the class to hold the equation parts

In order to parse the equation, the program needs to determine what is an operator and what is a variable or constant and its execution order.

A Note About Math things

Execution Order

Consider the following: 2 + 4 / 2. At first glance, it looks like the answer is three, but that is incorrect. The multiplication and division have a higher operator order precedence and their calculations occur before addition and subtraction. This makes the actual answer 4. The C# compiler knows about order of operations and which happens first. When building the expression tree, the runtime doesn’t take this into account, and will execute each operation strictly from left to right. It is important to note this when creating and grouping the intermediate objects to form a tree with the execution order, so it is correct.

Making the Expression

The System.LINQ.Expressions.Expression is the class used to create the lambda expressions. The actual method to create the function is Expression.Lambda<T> and then call its compile function to turn it into a callable method.

The Lambda function requires two parameters, an Expression, and a ParameterExpression[]. The entries in the ParameterExpression[] are the parameters to the function and they are made by calling Expression.Parameter.

Expression Body

Each Expression object is a tree of Expression objects. The four methods used to create the operator functions (Expression.Add, Expression.Subtract, Expression.Multiply, and Expression.Divide) all take two Expression parameters (the left term and the right term), and each Expression can be one of three things, a constant (Expression.Constant), the supplied parameter (ParameterExpression), or another Expression.

With this, all that is necessary is to convert the EquationPart tree into an expression.

Additional Actions

It might be necessary to do additional actions in the expression, for example method’s output could be logged to the console. To do this, the Lambda Expression would now need to:

1. Calculate the result of the equation (calling the created equation).
2. Assign that value to a variable.
3. Write the variable contents out to the console.
4. Return the result stored in the variable.

Right now, the body of the Lambda Expression is the result of a single Expression object. All the actions culminate to a single result, but when adding logging, this changes. Calculating the result and logging it are separate unrelated actions. The Expression.Block groups Expressions together, and returns the value from the last executed Expression.

The first step is creating a variable using Expression.Variable it takes a Type and optionally a variable name.

Then assign the results of the body Expression to it:

Now the system can log the result, by using Expression.Call.

The Expression.Block method takes Expressions to be executed in the entered order. The only exception to this is the creation of the variable which much be passed into the method by a ParameterExpression[].

The full method with the console output looks like this:

If/Then

The methods use the double type resulting in the impossibility of a DivideByZeroException. Per the C# specification, it returns the value infinity.

To create a conditional statement use the Expression.Condition method which has three parameters (the Expression for the test, the true block, and the false block).

Test Condition

The test condition is an Expression, and the double type has a static method for checking for the infinity value. To use it, the Expression.Call method works just like it did with writing data to the Console.WriteLine.

True Block

If the condition is true (meaning that the value is infinity, then it should throw an exception indicating a problem. Expression has a method for throwing exceptions, Expression.Throw

Empty False Statement

A false statement isn’t necessary, because if the condition is false, it will continue to the next statement outside of the condition. The Expression.Condition will not allow null as the third parameter, so to have an empty false statement use Expression.Empty instead.

Try Catch

Instead of passing the exception to the calling method, a second option would be to log it first by wrapping the method contents in a try-catch block. The Expression.TryCatch method has two parameters: the expression which contains the body information in the try statement, and the CatchBlock. Expression.MakeCatchBlock has three parameters: the type of Exception the catch block is for, the ParameterExpression which allows the Expression to bind the Exception to a variable for use, and the Expression code inside the catch statement.

Expression.Rethrow

Expression.Rethrow has two method signatures. The first has not parameters, and the second has a parameter of type of Type. In this example, since it is the last statement in the catch block (the the statement in a block determines what is returned from the block), if you use Expression.Throw(), the application will return with this error: Body of catch must have the same type as body of try. This is saying that the the try and catch blocks must have the same return type. In the example, the try block returns type double, so the catch block must do the same. The overload for Expression.Throw(Type), tells the runtime “This catch statement will return this type if necessary.” Since it’s throwing the exception, it won’t ever return a value, but this tells the Expression generator this will be the intended behavior if an exception doesn’t occur.

Here are all the code examples.

Pushing Data

Consider the following two pieces of code:

and

Although they look like they are roughly the same, they produce two very different results. The first returns an enumeration of string and the second throw’s an InvalidOperationException.

The difference stems from how C# handles the yield statement. Both methods promise to return an object which implements IEnumerable<string>. In .NET 1 and 1.1, there was no yield statement, and to return a custom object (not an array) which implemented it, you had to create an object which satisfied the IEnumerable requirements. (Remember it’s not generic, because it was created before generics were added.)

When it was added in .NET 2.0, in order to satisfy the requirements of returning an object, the C# compiler turns the method which uses yield into an object with the IEnumerable<T> interface.

(Entire generated class).

The important thing to note is that the generated class implements IDispoable, and it is responsible for disposing of the SqlConnection and SqlCommand, not the original method. This means that as the IEnumerable<string> object returns through the calling methods, nothing will dispose them until the enumeration object itself is disposed. The other method does not do this, because the SqlConnection is injected and the using statement is outside of the GetNamesWithConnection’s control (and hence it is not included in the generated class the GetNamesWithConnection converts to). Once the enumerator returns from the method responsible for disposing the SqlConnection (in the above example InjectSqlConnection), the using statement’s scope exits and the SqlConnection’s Dispose method fires.

How to fix the InjectSqlConnection method?

The easiest solution is to ensure that all operations performed on the IEnumerable object which it retrieves should occur before the using statement completes and SqlConnection.Dispose fires. In many scenarios this isn’t and issue, but in the following example this isn’t a possibility.

This method is from a class which inherits the API Controller where the method’s job isn’t to act upon the data, but return the it so the invoking method can serialize and send it out the response stream.

In this scenario, the first option would be to take the the SqlConnection out of the using statement, and let the Garbage Collector (eventually) dispose of it. This is a bad idea, and really isn’t a viable solution. Not disposing of SqlConnections will eventually use up all the available ones in the connection pool.

The second option would be to manually enumerate through the returned enumeration.
change this:

to this:

This is a possible solution, but it is a lot more work on the part of writing the code (since you would have to do this to each method returning data) and more work on the part of the system (keeping track multiple enumerators (both the original and the new), etc.). It’s a viable option, but it’s not ideal.

A third option would be to convert the returned enumeration to an object which doesn’t require a SqlConnection to operate such as an Array or a List.

This works, because the ToList() method creates a List object which implements IEnumerable and the ToList method loops through the contents of the enumeration adding each item to the list. Lists (and Arrays) exist purely in memory and is why the SqlConnectionyield return statement which only loads the record it needs at that moment (this is referred to as Lazy Loading).

Request.RegiserForDispose

The ApiController object has a property named Request which returns an object of type HttpRequestMessage. This object has a method named RegisterForDispose which returns void and takes in an IDisposable object. Any object passed into this method will be disposed once the current request finishes which is after the contents have been added to the response stream.

Encasing the SqlConnection in a using statement no longer becomes necessary to ensure it gets disposed. This allows the SqlConnection’s creation and registration to dispose to be abstracted into it’s own method. As long as the system retrieves all connections from this method, it will be impossible to leave a connection undisposed.

Just write it here, I’ll handle the rest

It’s pretty common knowledge in a .NET console application using the following command will produce the following result.

Console output

On the surface, this doesn’t look like it’s that helpful. Console applications can immediately output a message, but most applications don’t run in the console, and those that do, run in a process not visible to the user. However, System.Console has a method which makes it ideal for logging: Console.SetOut. Passing in a TextWriter object changes the system’s default output behavior and pushes the entries to a different location.

The following example outputs the contents to “C:\temp\logfile.txt” instead of the default console itself.

Text output

This allows the application to change the destination based on the need, and the destination can be anywhere. OverrideThe output class must inherit the TextWriter class, but this doesn’t mean that it has to write to a file, screen, or something which ultimately outputs to a stream. Override the appropriate methods in the TextWriter and handle the data in any manner necessary. (Here are two examples: 1. XmlLogWriter, 2. DbLogWriter)

The real advantage comes when writing larger applications where the solution consists of multiple projects requiring a way to communicate information about the system. When using a standard logging framework, every project must have knowledge of the framework to use it. This becomes cumbersome if core projects are used across multiple solutions, or if you want to change the logging framework at some point in time in the future. System.Console is available to all .NET assemblies by default, and by using it (and Console.Error.Write(Line)), the system components do not need to reference anything additional.

Along with this, the Console allows SetOut to be called at any time in the application’s lifetime, so it’s possible to change the output stream while the program is executing. This allows for the output to be changed on the fly should the need arise.

Quick and Dirty Data Auditing

A lot of times a project, especially as it progresses, lacks proper requirements, time, or the resources, and tracking changes to data is often an afterthought. Rapid changes to the the database schema and code occur on a frequent basis, and with a moving trend to create software with fewer checks and balances (less quality assurance staff, less emphasis from management to allow proper time for code development), errors in saved data becomes more and more prevalent.

With all of this in mind, the ability to track changes in data is a powerful tool for both development and production issue remediation. However, one of the biggest issues faced is maintaining auditing tables when the database schema changes, and trying to ensure they don’t cause a system failure on their own.

Take for example the following table:

and with this a common scenario is to create an auditing table like so:

and a trigger which fires on updates:

With this scenario there are some problems. The most major being that if the live table gets updated and not the audit table, the trigger fails and stops the insert. (This is easily fixed in development, not so much in production.) Hopefully if this is the case, the calling application is verifying the database update succeeds.

This problem can be solved by specifying the columns specifically,

but this leaves the problem that if columns are added to the live table, and either the audit table or the trigger aren’t also updated, new fields may slip through the cracks and won’t be noticed until someone actively checks the table for information. Unfortunately, this is a commonly occurring worse case scenario, because most of the time you only look in a audit table when you need to track down something that might be wrong.

There is actually a fairly simple solution to this problem. Store the updated data as a single text entry. Furthermore it’s just as easy to store it as an XML entry which most runtime environments have a convenient way of parsing. To accomplish this, create an auditing table with the appropriate fields:

The TableId is the sys.tables.object_id so that when a trigger enters data into the table, it is easily discernible which data came from where. The action field says whether it was an insert or a delete. (Updates to a table do a delete of the previous data and then an insert.) The TransactionEntryId links multiple entries into the audit table together to show which insert and deletes are the result of an update.

Next, create the trigger and apply it to the appropriate table or tables.

In truth, this doesn’t work for all scenarios. Tables with a high transaction volume, or ones with large amounts of data for each record may suffer problems, but it works well enough in many scenarios to be a viable solution even if it isn’t one for the long term. (Like any solution, before jumping to conclusions about performance, the change should be tested.) Also, by keeping the trigger contents generic, this same code can be applied to multiple places without needing to change it alleviating the possibility of accidentally making a mistake in the setup process.

Depending on the scenario, it can beneficial to be able to enable auditing at will. In an ideal scenario, it would be as easy as applying the trigger to the table when necessary, but in many organizations, this isn’t possibly without a formal deployment process, and when looking into a problem normally auditing needs to be added quickly. By adding another table which tells triggers when to record data, auditing can be turned on by adding an entry.

and edit the trigger template

The drawback to this approach is that the triggers will still fire on each data change in the table, but it does alleviate some of the overhead of the second insert statement and the audit tables growing.

Compile Time Stored Procedures

(code for this project can be found here)

One of the largest problems with interacting with databases is the lack of compile time assurance that the application code and the SQL code interact correctly. Testing helps with finding the errors, but as a system grows in size and complexity it becomes both difficult and costly to find every mistake. There are pre-made solutions such as F# Type Providers or Entity Framework, but these present their own challenges such as not having the project in F# or avoiding the heavy handedness of Entity Framework.

Fortunately SQL server provides enough information about Stored Procedures in the Metadata tables that makes it possible to create a from scratch compile time safe mapping between stored procedures and application code. Combining this with
T4 Text Templates it’s possible to automatically generate all the code necessary to handle stored procedure inputs and returns.

The first part is to pull all the stored procedure data from the sys.procedures view.

I remove all the procedures starting with sp_ as this prefix is meant to designate that it is a system procedure for use by SQL Server. The three important pieces from the table are the Procedure Name (p.name), Object Identifier (Object_Id), and the Schema Name (s.name).

Select Sys Procedures

Armed with the object id it's now possible to pull all the data for a procedures parameters from the sys.parameters view.

With this and the procedure's name it's possible to construct code to call SQL Server stored procedures which is compile time safe. Simply generate a method which takes in the appropriate parameters to call the stored procedure. The last step is to generate a lookup table which translates the SQL datatypes to ones which are valid C# syntax.

Idenfitfing which parameters have a default value and be not passed to the procedure

Since stored procedure parameters can have default values and therefore aren't necessary to populate, it would be nice to be able to know which one's are available but aren't required. This is a little trickier. There is a field has_default_value which looks like it would tell you if the parameter has a default value and therefore is optional, but there is a problem in the SQL Server metadata table, and it is only correct for CLR parameters, and not SQL ones. In order to actually find out if parameter has a default value, there are two options.

  1. Use Sql Management Objects (SMO)
  2. Pull the stored procedure code using something like SELECT OBJECT_DEFINITION (PROCEDURE_OBJECT_ID)

Using SMO to pull the definition is actually relatively simple assuming you have access to the appropriate dlls from Sql Server Management Studio.

Code:

If you don’t have access to the SMO dlls, you can still use something akin to a regular expression (or some other parsing method) to pull the parameter information out from the Object_Definition. As with parsing anything, there are a number of gotchas to watch out for, but it is possible with a little bit of work.

User Defined Tables

Starting in SQL Server 2008 it is possible to pass tables as parameters into stored procedures. There is view called sys.table_types which lists all of the user defined tables and has the necessary columns to link it back to which stored procedures use it as a parameter, and retrieve its column definition.

Stored Procedure Output

To automatically generate C# code for SQL Server output, you’ll really need SQL Server 2012 and later for it to be effective. In that edition they added, the dm_exec_describe_first_result_set_for_object procedure

This procedure takes a stored procedure Object_Id and returns the column information concerning the first result set it finds.

Result Set

The great thing about this procedure is that not only can you generate classes which can automatically update their types and names based on a result set that changes during development, but the procedure will return nothing if the stored procedure is non functional because of a changes in the database. By running this this and having it map all the result sets, the ones which come up blank which should return data indicate the procedure will not work with the current schema and needs to be fixed. It quickly helps eliminate the difficulties of knowing which procedures will break during program execution.

Putting It All Together

With these procedure, all that is really necessary is something that opens a connection to the database, pulls the necessary data and create the appropriate C# files.

As an example and a quick starter I’ve created an example solution. The SQL metadata retrieval is in the Classes.tt file, and all the code C# generation is in the StoredProcedures.tt file. The database I used to generate the example code is found here, and uses the Adventure Works Database.

I thought you were the new method?

In C#, the concept of a virtual method is not new. It’s a simple concept in which a child class may change the functionality of certain parent methods which have the keyword virtual applied. Anytime the child class is instantiated and this method accessed (either in its children or parents), it calls the overridden method by default and not the original. What about methods without the virtual keyword?

On occasion it is necessary to change the functionality of a method already defined in a parent class in it’s children without affecting the parent class’ functionality. The new keyword allows a child class to ignore (or hide) the method of the parent and implement its own anytime the child or its children are explicitly used (this is different than overloading as the new keyword allows a child to re-implement a method with the same signature). At first it seems it would be simple to determine if the parent or the child method would be called, but the method invocation changes depending on scenarios surrounding variable types making it tricky in complex situations.

Take the following piece of code:

The following two scenarios yield the results you would expect:

parentclassayinghello

Child class with new method saying hello world

Now what if you implement a method on the parent class which calls the method the child class hides in its implementation?

child class calling helper method in parent

This makes sense. The new keyword only hides the previous implementation of the method from objects accessing itself and its children in the inheritance chain. The parent is unaffected by this as it assumes certain actions will be taken when it calls this method.

Type declaration
When dealing with the new keyword, instantiating an object and assigning a variable with a specific type can alter the effects of how the objects methods’ function.

Even though the object being created is of type ChildClassNew, declaring the variable as its parent type forces the object to ignore the new method and use the one in the parent.

explicit declaration

This means that it is possible to create an object and change its behavior based on the calling code. Although this looks strange, in truth, this should be the expected. It follows the same principles as method overloading between parent and child classes. If a child class has an overloaded method which is less restrictive than the parent’s, the child’s method will be called even if the parameter being passed to the method satisfies the more restrictive condition of the parent method if the variable is of type child. (If the parent has void Foo(string s) and the child has void Foo (Object x). The child’s method is less restrictive because System.String can satisfy the parameter of System.Object. Look at the project here for examples.)

declare variable and change type

Implicit type declaration
With the following code what happens?

With implicit type declaration, you’re not telling the compiler what you want the variable type to be. You are asking it to figure out what it should be based on the information provided. In the above example var childType = parentType;, if you haven’t read the C# specification on how it works, you could possibly assume it determines the type in one of two different ways. Does it look at what has been explicitly declared as the parentType’s variable type, or does it look at the actual object which is assigned to that variable.

The specification is explicitly clear in how it works:

When the local-variable-type is specified as var and no type named var is in scope, the declaration is an implicitly typed local variable declaration, whose type is inferred from the type of the associated initializer expression.

This means that it looks at the type of the variable and not the type of the object when it is being assigned, so you must explicitly declare the type to change the behavior.

implicit type declaration

IL code that has been converted by to C# from ILSpy:
implicit type declaration from ILSpy

Raw IL Code:

Il Code

Interfaces
Interfaces work in a similar manner to declaring a variable as a parent or a child. If the interface is located on the parent, then it calls the parent’s method:

explicit interface

If you reattach the interface to the child class, the behavior of the types declared with that interface change. They now call the child’s method and not the parent.

Child Implements Interface

Although this makes sense, it leaves the possibility for unintended side effects to happen. Interfaces can require the implementation of other interfaces, and so by attaching a new one to a child class, it is possible to accidentally reattach an existing interface and hide the parent’s behavior.

Explicit interface through other interface

Using Dynamic
The dynamic keyword is a divergence from how the of the situations resolve the method. With dynamic there is no compile time resolution and so at runtime the program must determine which method it needs to call.

Using Dynamic

Even though the initial variable type was declared as the parent, and if it was called without the dynamic type being used, the parent method would execute. With the dynamic keyword, the behavior changes from the other scenarios where the variable type comes into play.

In certain scenarios, the new keyword can be helpful. Its use should be limited, however, because of it’s complicated rules for which method is called and certain edge case scenarios (such as using dynamic) where actual behavior potentially deviates from prediction.

The code for this post can be found here.