declare @inject nvarchar(4000) = NCHAR(0x02bc) + N'; select 1/0; select ' + nchar(0x02bc);
declare @safe nvarchar(4000) = REPLACE(@inject, N'''', N'''''');
declare @sql varchar(4000) = N'SELECT ''' + @safe + N'''';

print @inject;
print @safe;
print @sql;

exec(@sql);
go

declare @inject nvarchar(4000) = NCHAR(0x02bc) + N'; select 1/0; select ' + nchar(0x02bc);
declare @safe nvarchar(4000) = QUOTENAME(@inject, N'''');
declare @sql varchar(8000) = N'SELECT ' + @safe;

print @inject;
print @safe;
print @sql;

exec(@sql);
go

Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

In both examples above the SQL executed apparently should had been safe from SQL injection, but it isn’t. Neither REPLACE nor QUOTENAME were able to properly protect and the injected division by zero was executed. The problem is the Unicode MODIFIER LETTER APOSTROPHE (NCHAR(0x02bc)) character that I used in constructing the NVARCHAR value, which is then implicitly cast to VARCHAR. This cast is converting the special ‘modifier letter apostrophe’ character to a plain single quote. This introduces new single quotes in the value, after the supposedly safe escaping occurred. The result is plain old SQL Injection.

In order for this problem to occur, there has to exist an implicit or explicit conversion (a cast) from Unicode to ASCII (ie. NVARCHAR to VARCHAR) and this conversion must occur after the single quote escaping occurred.

Credit: Why doubling single quotes is not enough to protect from SQL Injection.

I recently seen a discussion on the merits of using SQL Server as an application lock manager. Application would start a transaction and acquire app locks, using sp_getapplock. No actual data would be queried or modified in this transaction. The question is whether using SQL Server as such, basiclaly as an application lock manager, can lead to problems in SQL Server.

sp_getapplock uses the same internals for acquiring and granting locks like SQL Server data locks

Locks are relatively cheap. The engine can acquire, grant and release millions of them per second. A lock cost a small amount of memory, but overall locking is such an important and critical part of SQL Server operations that everything about locks is highly optimized. sp_getapplock is basically just a shell around the lock manager features and as such it gets a ‘free ride’. Highly optimized, efficient, fast and low overhead locks. However, this is not the only cost associated with app locks. Transaction scoped app locks must also play by the rules of transaction rollback and recovery, and one such rule is that exclusive locks acquired by transactions must be logged, since they must be re-acquired during recovery. App locks are no different, and this is easy to test:

create database test;
go

alter database test set recovery  full;
go

backup database test to disk = 'nul:';
go

use test;
go

begin transaction
exec sp_getapplock 'foo', 'Exclusive', 'Transaction'
select * from sys.fn_dblog(null, null);

00000021:0000005b:0001	LOP_BEGIN_XACT   ... user_transaction
00000021:0000005b:0002	LOP_LOCK_XACT    ... ACQUIRE_LOCK_X APPLICATION: 5:0:[fo]:(ea53e177)

Sure enough our transaction that acquired an exclusive app lock has generated log records. This means that if the application is using SQL Server as a lock manager and holding long lived locks (for example while making a HTTP request) is also pinning the log, preventing truncation. Another side effect is log traffic, if the application is requesting lots of app locks it may result in noticeable log write throughput.

tempdb has a simplified logging model

I mentioned that the requirement to log the exclusive locks acquired in a transaction is a needed for recovery. Since tempdb is never recovered, tempdb has a more lax logging model. Specifically, it is not required to log exclusive locks acquired by transactions. So if you repeat the same experiment as above, but in tempdb you’ll see that no log record is generated:

use tempdb;
go

begin transaction
exec sp_getapplock 'foo', 'Exclusive', 'Transaction'
select * from sys.fn_dblog(null, null);

If you make sure the current context is set to tempdb when calling sp_getapplock then you get a high performance lock manager for your application, with little overhead for your SQL Server engine. This is actually trivial to achieve in practice:

use test
go

begin transaction
exec tempdb..sp_getapplock 'foo', 'Exclusive', 'Transaction'
select * from sys.fn_dblog(null, null)

By simply using the 3 part name tempdb..sp_getapplock I have effectively suppressed the logging of the app lock acquired! Of course a word of caution: please make sure that you are OK with app locks not being logged. The important thing to consider is how will your application behave when doing an online recovery. If app locks were not logged your app may acquire a an app lock while the data that was protected by that app lock is actually going through recovery. In practice this is seldom the case to worry, but you have to consider and acknowledge the trade offs.

The SQL Server transaction log contains the history of every action that modified anything in the database. Reading the log is often the last resort when investigating how certain changes occurred. It is one of the main forensic tools at your disposal when trying to identify the author of an unwanted change. Understanding the log and digging through it for information is pretty hard core and definitely not for the faint of heart. And the fact that the output of ::fn_dblog can easily go into millions of rows does not help either. But I’ll try to give some simple practical examples that can go a long way into helping sort through all the information and dig out what you’re interested in.

Read the rest of this entry »

If you are a developer writing applications that use SQL Server and you are wondering what exactly happens when you ‘run’ a query from your application, I hope this article will help you write better database code and will help you get started when you have to investigate performance problems.

Requests

SQL Server is a client-server platform. The only way to interact with the back-end database is by sending requests that contain commands for the database. The protocol used to communicate between your application and the database is called TDS (Tabular Data Sream) and is described on MSDN in the Technical Document [MS-TDS]: Tabular Data Stream Protocol. The application can use one of the several client-side implementations of the protocol: the CLR managed SqlClient, OleDB, ODBC, JDBC, PHP Driver for SQL Server or the open source FreeTDS implementation. The gist of it is that when your application whats the database to do anything it will send a request over the TDS protocol. The request itself can take several forms:

Read the rest of this entry »

SQL Server 2012 SP1 has shipped a great enhancement to XML: Selective XML Indexes. When properly used these indexes can speed up the searching of XML columns tremendously, at little disk/size cost:

The selective XML index feature lets you promote only certain paths from the XML documents to index. At index creation time, these paths are evaluated, and the nodes that they point to are shredded and stored inside a relational table in SQL Server. This feature uses an efficient mapping algorithm developed by Microsoft Research in collaboration with the SQL Server product team. This algorithm maps the XML nodes to a single relational table, and achieves exceptional performance while requiring only modest storage space.

Read the rest of this entry »

The SMO object model for SQL Server ServiceQueue does allow one to enable or disable a queue, but the property that modifies the queue status is not intuitive, it is IsEnqueueEnabled:

Gets or sets the Boolean property that specifies whether the queue is enabled.

This property matches the catalog view column is_enqueue_enabled in sys.service_queues but bears little resemblance to the T-SQL statement used to enable or disable a queue: ALTER QUEUE ... WITH STATUS = {ON|OFF}

Read the rest of this entry »

A recent inquiry from one of our front line CSS engineers had me look into how case sensitive collations decide the sort order. Consider a simple question like How should the values 'a 1', 'a 2', 'A 1' and 'A 2' sort?

create table [test] (
	[col] varchar(10) 
		collate Latin1_General_CS_AS);
go

insert into [test] ([col]) values 
	('a 1'),
	('a 2'),
	('A 1'),
	('A 2');
go

select [col]
from [test]
order by [col];

Here are two possible outputs:

Read the rest of this entry »

The typical SQL Server activation procedure is contains a WHILE (1=1) loop and exit conditions based on checking @@ROWCOUNT. Error handling is done via a BEGIN TRY ... BEGIN CATCH block. This pattern is present in many Service Broker articles on the web, including this web site, in books and in Microsoft samples:

Read the rest of this entry »

Columnar storage has established itself as the de-facto option for Business Intelligence (BI) storage. The traditional row-oriented storage of RDBMS was designed for fast single-row oriented OLTP workloads and it has problems handling the large volume range oriented analytical processing that characterizes BI workloads. But what is columnar storage and, more specifically, how does SQL Server 2012 implement columnar storage with the new COLUMNSTORE indexes?

Read the rest of this entry »

LSNs, or Log Sequence Numbers, are explained on MSDN at Introduction to Log Sequence Numbers:

Every record in the SQL Server transaction log is uniquely identified by a log sequence number (LSN). LSNs are ordered such that if LSN2 is greater than LSN1, the change described by the log record referred to by LSN2 occurred after the change described by the log record LSN.

There are several places where LSNs are exposed. For example sys.database_recovery_status has the columns last_log_backup_lsn and fork_point_lsn, sys.database_mirroring has the mirroring_failover_lsn column and the msdb table backupset contains first_lsn, last_lsn, checkpoint_lsn, database_backup_lsn, fork_point_lsn and differential_base_lsn. Not surprisingly all these places where LSNs are exposed are related to backup and recovery (mirroring is a form of recovery). The LSN is exposed a numeric(25,0) value that can be compared: a bigger LSN number value means a later log sequence number and therefore it can indicate if more log needs to be backed up or recovered.

Yet we can dig deeper. Look at the LSN as exposed by the fn_dblog function:

Read the rest of this entry »