SQL Injection
Enhance SQL Server With a CLR Function
Sometimes you can’t easily do what you need in T-SQL; fortunately you can create new functionality in a .NET project and invoke it from your stored procedures or SQL. You can build a Visual Studio project that you can publish to your SQL Server database and use like a normal function or stored proocedure.
In principal, it is not too hard to extend SQL Server, but there are several frustrating issues which I explain in the post below. You will save your own frustration if you learn from my efforts! For example, projects that won’t build but have no error messages, fixing apparent damage to Visual Studio, and explicitly turning-on CLR in SQL Server.
My Business Need: Enhanced String Comparison
I’m importing legacy data and discovered some data that should be identical but isn’t, due to similar punctuation marks! For example ASCII as at least three different single quote marks: a ‘straight’ single quote, an open single-quote and a close single-quote. Like the following:
‘`´
Also, there are a couple different ASCII hyphens: a normal version, and an extra-wide one. Presumably there are other punctuation marks that look similar to humans but aren’t the same to SQL Server
Names the Same Except for Punctuation
Normally I don’t care about the punctuation, but I have some key fields with different punctuation, such as:
- Joe’s Bar and Grill (straight quote)
- Joe´s Bar and Grill (close quote)
The problem arises when I try to prevent duplicates: the two names above are the same to me, but SQL Server says they are different, because of the differing punctuation.
At first, I thought I could avoid the issue by specifying a collate clause in my SQL, but that that is actually designed for comparisons in different languages and doesn’t help here.
.NET to the Rescue!
Fortunately, .NET has a CompareOptions object which allows you to compare strings with a ‘IgnoreSymbols’ setting. Not perfect, because it only ignores punctuation; it isn’t smart enough to compare the three kinds of single quotes against each other but not against other punctuation. However, I judged it good enough for my purposes. Now, I will use that capability to build a “SQL CLR C# User Defined Function in SQL SERVER”.
Start by creating a SQL Server Database Project
After starting your project, make sure you target the correct version of SQL Server, in my case, SQL Server 2012.
Now, choose the type of functionality you wish to add to SQL Server, in my case, a User-Defined Function. Add a new item to your project and choose the template indicated in the screen shot below.
Now, Write Some Code!
You will get a stub function; you should fill-in the functionality to meet your needs. In my case, I will return a 0 if two strings are equivalent, a -1 if the first string is smaller, and a 1 if the first string is bigger.
using System.Data.SqlTypes;
using System.Globalization;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true, SystemDataAccess = SystemDataAccessKind.None, IsPrecise = true)]
public static SqlInt16 PunctuationInsensitiveComparison(SqlString s1, SqlString s2)
{
try
{
if (s1.IsNull && s2.IsNull)
return 0;
else if (s1.IsNull)
return -1;
else if (s2.IsNull)
return 1;
else
{
CompareOptions myOpts = CompareOptions.IgnoreSymbols | CompareOptions.IgnoreWidth | CompareOptions.IgnoreCase | CompareOptions.IgnoreNonSpace;
CompareInfo ci = CompareInfo.GetCompareInfo("en-US");
SqlInt16 compareResult = (SqlInt16)ci.Compare((string)s1, (string)s2, myOpts);
return compareResult;
}
}
catch (System.Exception ex)
{
return -1;
}
}
Highlights
- The tag decorating the start of my function tells SQL Server what kind of usage to expect, I believe it is used for optimization
[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true, SystemDataAccess = SystemDataAccessKind.None, IsPrecise = true)]
- Note the use of SQL Server data types, such as SqlString and SqlInt16. This is important for optimzation
- Also note that I took pains to check for nulls, for example:
else if (s1.IsNull)
- This will work when String.IsNullOrEmpty fails!
Avoid this Gotcha!
At this point, you can try to build, but, even though the syntax is correct, it failed for me. In fact, it failed without an error message! After some research, I discovered I needed to install SQL Server Database Tooling, which you can get at this url: https://msdn.microsoft.com/en-us/dn864412.
Next Problem
Installing this extension may mess-up your instance of Visual Studio, it certainly did mine! You may get an error ‘No exports were found that match the constraint contract name‘ After some research, I found the solution was to rename or delete the following folder:
- C:\Users\YourUserName\AppData\Local\Microsoft\VisualStudio\12.0\ComponentModelCache
Now Publish Your Solution
Right-click on your project and choose ‘Publish’ from the context menu.
You will have to browse for your server/database by clicking the ‘edit’ button; afterwards, you can either generate a script or else click the publish button. If you generate a script, you can tweak it, afterwards you will need to run it by clicking the button indicated in the following screen shot.
After publishing, your new function will appear in SQL Server!
One Last Step Before You Can Use it
The last thing to do is enable CLR functionality in your database instance, by running the following two commands:
sp_configure 'clr enabled', 1 Reconfigure
Now, you can use your new function in SQL, like the following simple example:
if dbo.PunctuationInsensitiveCompare('Jim''s Bar', 'Jim´s Br') = 0
Print 'Same'
Else
Print 'Different'
Microsoft documentation states that CLR functions can be more efficient than the equivalent in a normal Scalar-valued function, and my test confirmed that statement. However, if you aren’t careful about null checking, use the wrong data types, or generate errors, it can be much slower!
Summary
If you have a task that is hard in SQL but easy in .NET, you can build a CLR function and publish it to SQL Server. Things like string manipulation come to mind. You will build a SQL Server Database project, add an item ‘SQL CLR C# User-Defined Function’ and publish it to your database. There are several gotchas involved, so be careful and be sure to use System Restore Points and other backup techniques.
Efficiently Protect Your Company from SQL Injection
Most places I work don’t want to be bothered protecting themselves from hackers, an attitude I find hard to believe given that, almost every day, another company looses mega-bucks after failing to protect themselves.
What you will learn: How to protect yourself against SQL Injection with a few lines of really cool code that runs six times faster than a naive implementation.
Why Don’t Most Folks Protect Themselves?
Most programmers think that hacker attacks are rare, and defending is work, so they’ll hope they get lucky. Sometimes I think they’re just burying their heads in the sand. The problem is, they aren’t thinking about how devastating one lucky hacker attack can be. A hacker attack can easily put a company out of business; just think what would happen if all your credit card info was stolen, or your database backups were destroyed!
The other mistake they make is assuming they are protected by technology X; usually they put all their trust in their firewall or https. Sorry, hackers have defeated those technologies plenty of times. Furthermore, most attacks from from disgruntled former employees or business associates. They already know your passwords, so they use one weak app to get through the fire wall, then exploit their loop hole to attack other apps. Bottom line: the best security is to never assume any technology is foolproof, always use defense-in-depth.
I’ll guarantee that the latest company who got hacked had that attitude; i.e. the guys who screwed-up assumed that they didn’t need to use precautions because technology X was enough.I hope they like sleeping in their cars! I know you don’t like sleeping in your car, so spend some time to prevent a catastrophe.
My Defense Task: Prevent SQL Injection
SQL Injection is when an attacker submits SQL in a string that you use to run as a query. Generally you are vulnerable when you build an ad hoc query and run it against your database. For example, if you append the contents of a textbox to your query, the attacker can turn part of your intended query into a comment and run his query after a truncated version of your query runs. Note: any string from the outside world is a potential threat, even XML from your corporate partners. They could have disgruntled employees, or they could be hacked, or someone could spoof their transmission.
Normally, you can protect yourself by using parameterized queries. My problem: our ERP controls access to our database, and I can’t force it to use parameterized queries. Solution: scrub all input to sanitize SQL key words. For example, if the user input contains the words ‘drop table foo’, you can inject a space, or other character, into either ‘drop’ or ‘table’. In other words, substitute ‘dr op table’ whenever you find ‘drop table’. That way, legitimate text is still readable, but your database won’t run the query.
Scrub Efficiently with Regular Expressions!
Your naive coworker will conduct six different search-and-replace operations. I assert that this is six times too slow. Instead, be smart, use regular expressions: then you can only make one pass through your text!
Naive Approach:
public string SlowScrub(string cleanMe) {
string result = cleanMe.Replace("Drop Table", "D rop Table")
.Replace("Select ", "S elect ")
.Replace("Update ", "U pdate ")
.Replace("Delete ", "D elete ")
.Replace("Insert ", "I nsert")
.Replace("Truncate Table ", "T runcate Table ");
return result;
}
There are three problems with the code above:
- It makes 6 passes through your string, that slows-down your app
- It is case sensitive (and the Replace method won’t allow you to change that problem)
- It won’t catch Truncate Table or Drop Table when split across two lines
Regular Expressions to the Rescue
Here’s how I used a regular expression to make 1 pass through my string. The regular expression is a pattern to replace in the string. I also used a lambda (anonymous function) that .NET executes on every match it finds – how cool is that!
public string ScrubSql(string cleanMe) {
string pattern = @"( #Start our capture group
(?:drop\stable) #Non-capturing group 1; the word 'drop', space, then 'table'
| #or
(?:select\s) #Non-capturing group 2: select' followed by whitespace
| #Or
(?:update\s) #Non-capturing group 3; update' followed by whitespace
| #or
(?:delete\s) #Non-capturing group 4; 'delete' followed by whitespace
| #or
(?:insert\s) #Non-capturing group; the word 'insert' followed by whitespace
|
(?:truncate\stable) #'truncate table'
)";
string result = Regex.Replace(cleanMe, pattern, (m) => {
//This anonymous function is run on every match
string danger = m.Groups[1].Value.ToLower();
return danger.StartsWith("dr") ? "d rop t able "
: danger.StartsWith("s") ? "s elect "
: danger.StartsWith("u") ? "u pdate "
: danger.StartsWith("de") ? "d elete "
: "i nsert";
},
RegexOptions.IgnoreCase | RegexOptions.IgnorePatternWhitespace
| RegexOptions.Multiline | RegexOptions.Singleline);
return result;
}
Notes:
- I include comments in my regular expression; that helps make it easier to maintain. # is the comment char
- This only works if I also specify ‘IgnorePatternWhitespace’ and ‘MultiLine’
- My Search Pattern consists of six different choices, the same choices I showed you in my naive implementation.
- The pipe character | means ‘or’ when used in a regular expression; it separates the for choices
- Note that I supply a function (a ‘MatchEvaluator’) that .NET runs for every match it finds
- My MatchEvaluator serves to identify which dangerous text it identified in the input string
- And then it returns an appropriate sanitized string
- Note that I also specified ‘IgnoreCase’
- Also: the RegexOption ‘SingleLine’ is a bit confusing. It basically means “match your text as if it were a single line”, with the effect that, when your pattern seeks 2 words, it will find them even if on separate lines.
Bottom Line
You can easily plug-in my little function to your code. Every string you get from the outside world, use my funciton to scrub that string. The job you save may be your own!
Randy's Rants, Ravings and Reflections 