What does the following C# code output?
using System;
namespace ConsoleApplication14
{
class Test
{
public void Display()
{
Console.WriteLine("Hello World");
Console.WriteLine(string.Intern("Hello World"));
}
}
class Program
{
const string X = "X";
const string X2 = X;
static readonly string Y = string.Intern("Hello World");
static void Main()
{
var t = new Test();
t.Display();
DoWork();
t.Display();
Console.WriteLine(true);
Console.WriteLine(false);
bool test = true;
Console.WriteLine(test);
bool test2 = false;
Console.WriteLine(test2);
Console.WriteLine(1==2);
Console.WriteLine(1!=1);
Console.WriteLine("X");
Console.WriteLine(X);
string s = "X";
Console.WriteLine(s);
string s2 = X;
Console.WriteLine(s2);
Console.WriteLine(X2);
Console.WriteLine(Y);
}
private static unsafe void DoWork()
{
fixed(char*p=X)*p='\u0059';
fixed (char* p = Boolean.FalseString)
{
*p = '\x0054';
*(p + 1) = (char)114;
*(p + 2) = '\u0075';
*(p + 3) = '\u0065';
*(p + 4) = '\0';
}
fixed (char* p = Y)
{
*p = *(p + 10);
*(p + 1) = *(p + 9);
*(p + 2) = *(p + 8);
*(p + 3) = *(p + 7);
*(p + 4) = *(p + 6);
*(p + 5) = *(p + 5);
*(p + 6) = *(p + 4);
*(p + 7) = *(p + 3);
*(p + 8) = *(p + 2);
*(p + 9) = *(p + 1);
}
}
}
}
Nandos has “silently” changed the recipe for their sauces. Mild is no longer mild, but “hotter” and the old mild no longer exists. Lemon and herb tastes slightly more like lemons – the dose of lemon in the “lemon and herb” sauce has apparently been increased. Apparently, due to inconsistencies across the stores, they have reverted to the “original” Nandos sauces.
There is often the need to parse delimited data in SQL Server. For instance, a comma separated values (CSV) list of Id's from a multi-select Reporting Services report parameter, or a data field that is stored as a pipe-separated list of strings.
Here are two T-SQL (2005 or later) user defined functions (UDF's) to generically parse any delimited list of values.
You supply the delimited list of values as an nvarchar(max) argument -- this caters for Unicode and the maximum possible length (@Values).
You also supply a delimiter string of one or more Unicode characters, also passed as an nvarchar(max) parameter (@Separator). For instance you supply ',' for CSV and '|' for pipe delimited.
The UDF_ParseArray UDF is a table valued function that returns a table that has the following definition:
RETURNS
@Array TABLE (
[Index] int NOT NULL IDENTITY(0, 1) PRIMARY KEY,
[Value] nvarchar(max) NOT NULL
)
The Index column is the 0-based array index (or field index). The Value column is the actual value of the array entry (field) as a Unicode string up to the maximum possible length.
The UDF_ParseArrayIndex UDF is a scalar valued function that returns a particular array entry given the array index. In addition to the two arguments described above (@Values and @Separator), you supply the array index argument (@Index, 0-based, int).
Source code:
CREATE FUNCTION [dbo].[UDF_ParseArray]
(
@Values nvarchar(max),
@Separator nvarchar(max)
)
RETURNS
@Array TABLE (
[Index] int NOT NULL IDENTITY(0, 1) PRIMARY KEY,
[Value] nvarchar(max) NOT NULL
)
AS
BEGIN
DECLARE @Pattern nvarchar(max);
DECLARE @Offset int;
DECLARE @PatIndex int;
SET @Values = @Values + @Separator;
SET @Pattern = N'%' + @Separator + N'%';
SET @Offset = LEN(@Separator) - 1;
SET @PatIndex = PATINDEX(@Pattern, @Values);
WHILE @PatIndex <> 0
BEGIN
INSERT INTO @Array ([Value]) VALUES (LEFT(@Values, @PatIndex - 1));
SET @Values = STUFF(@Values, 1, @PatIndex + @Offset, N'');
SET @PatIndex = PATINDEX(@Pattern, @Values);
END;
RETURN;
END;
GO
CREATE FUNCTION [dbo].[UDF_ParseArrayIndex]
(
@Values nvarchar(max),
@Separator nvarchar(max),
@Index int
)
RETURNS nvarchar(max)
AS
BEGIN
DECLARE @Pattern nvarchar(max);
DECLARE @Offset int;
DECLARE @ArrayIndex int;
DECLARE @PatIndex int;
SET @Values = @Values + @Separator;
SET @Pattern = N'%' + @Separator + N'%';
SET @Offset = LEN(@Separator) - 1;
SET @ArrayIndex = 0;
SET @PatIndex = PATINDEX(@Pattern, @Values);
WHILE @PatIndex <> 0
BEGIN
IF (@ArrayIndex = @Index)
RETURN LEFT(@Values, @PatIndex - 1);
SET @Values = STUFF(@Values, 1, @PatIndex + @Offset, N'');
SET @PatIndex = PATINDEX(@Pattern, @Values);
SET @ArrayIndex = @ArrayIndex + 1;
END;
RETURN NULL;
END;
GO
Examples:
SELECT * FROM [dbo].[UDF_ParseArray](N'The,Big,Brown,Fox,Jumped,Over,The,Cow', ',');
CREATE TABLE [dbo].[Data] (
[Id] int NOT NULL PRIMARY KEY,
[Value] nvarchar(max) NOT NULL,
[Array] nvarchar(max) NOT NULL
);
INSERT INTO [dbo].[Data] ([Id], [Value], [Array]) VALUES (1, 'Test1', 'Test1_Index0|Test1_Index1|Test1_Index2');
INSERT INTO [dbo].[Data] ([Id], [Value], [Array]) VALUES (2, 'Test2', 'Test2_Index0|Test2_Index1|Test2_Index2');
INSERT INTO [dbo].[Data] ([Id], [Value], [Array]) VALUES (3, 'Test3', 'Test3_Index0|Test3_Index1|Test3_Index2');
INSERT INTO [dbo].[Data] ([Id], [Value], [Array]) VALUES (4, 'Test4', 'Test4_Index0|Test4_Index1|Test4_Index2');
INSERT INTO [dbo].[Data] ([Id], [Value], [Array]) VALUES (5, 'Test5', 'Test5_Index0|Test5_Index1|Test5_Index2');
INSERT INTO [dbo].[Data] ([Id], [Value], [Array]) VALUES (6, 'Test6', 'Test6_Index0|Test6_Index1|Test6_Index2');
INSERT INTO [dbo].[Data] ([Id], [Value], [Array]) VALUES (7, 'Test7', 'Test7_Index0|Test7_Index1|Test7_Index2');
INSERT INTO [dbo].[Data] ([Id], [Value], [Array]) VALUES (8, 'Test8', 'Test8_Index0|Test8_Index1|Test8_Index2');
INSERT INTO [dbo].[Data] ([Id], [Value], [Array]) VALUES (9, 'Test9', 'Test9_Index0|Test9_Index1|Test9_Index2');
DECLARE @IdList nvarchar(max);
SET @IdList = N'1,3,5,7,9';
SELECT
[Data].[Id],
[Data].[Value],
[dbo].[UDF_ParseArrayIndex]([Data].[Array], N'|', 0) AS [ArrayIndex0],
[dbo].[UDF_ParseArrayIndex]([Data].[Array], N'|', 1) AS [ArrayIndex1],
[dbo].[UDF_ParseArrayIndex]([Data].[Array], N'|', 2) AS [ArrayIndex2]
FROM [dbo].[Data] AS [Data]
INNER JOIN [dbo].[UDF_ParseArray](@IdList, N',') AS [T]
ON [Data].[Id] = [T].[Value];
SELECT * FROM [dbo].[UDF_ParseArray](N'Field1<fs>Field2<fs>Field3', N'<fs>');
You can insert guidelines into the Visual Studio text editor to help hard wrapping code and code comments. A typical column wrap is at column 80.
1. Open the Registry Editor (Start, Run, regedit)
2. Open the registry key: HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\9.0\Text Editor
3. Add a new string value called "Guides" (right click, New, String Value)
4. Edit the string value (double click) and insert the text "RGB(192,192,192) 80" (excluding the quotes).
The format of this string is: RGB(<red>,<green>,<blue>) <column 1>[, <column n>] where:
<red>, <green> and <blue> are the red, green and blue components of the colour of the guideline you want, and <column 1> is the column number of the first guideline and <column n> is the column number of the nth guideline.
5. Restart Visual Studio.
I needed a way to test classes working with large data streams (greater than 4GB of data). I could've created a file greater than 4GB and used a FileStream. However, the performance and disk-space usage overhead was not acceptable. Thus I created the VirtualStream. The VirtualStream behaves like a NullStream (Stream.Null) in that it has no backing store, except that you can also read from it and therefore use it as a virtual source of data of any size. You can specify the initial length and position when constructing the VirtualStream and perform all stream operations, including Read, Seek, Write, Position, SetLength, etc. Another difference between VirtualStream and NullStream is that it validates the input parameters the same way that a FileStream or MemoryStream would, therefore being ideal for testing. In addition to validating all input parameters and allowing reads, you can specify that the VirtualStream simulate an actual read by filling the buffer passed to the Read method with random data. This is especially useful for testing classes that require a source of disparate data (not all zeros), for instance compression streams such as DeflateStream. I thought I'd share this class as it can save you a few minutes of development.
My specific scenario that I needed this for was testing my implementation of the Zip64 ZIP file specification. The Zip64 ZIP file specification adds additional 64-bit records to the ZIP local file header and central directory in order to cater for archives larger than 4GB or archives that contain files larger than 4GB.
using System;
using System.IO;
using System.IO.Compression;
namespace ConsoleApplication112
{
[Flags]
public enum VirtualStreamOptions
{
None = 0,
ReadRandomBytes = 1
}
internal static class VirtualStreamOptionsExtensions
{
internal static bool IsValid(this VirtualStreamOptions options)
{
return (options >= 0 && (int)options <= 1);
}
internal static bool IsSet(this VirtualStreamOptions options,
VirtualStreamOptions option)
{
return (options & option) != 0;
}
}
public class VirtualStream : Stream
{
private long length;
private long position;
private readonly VirtualStreamOptions options;
private readonly Random random;
public VirtualStream()
{ }
public VirtualStream(long initialLength)
: this(initialLength, 0, VirtualStreamOptions.None)
{ }
public VirtualStream(long initialLength, long initialPosition)
: this(initialLength, initialPosition, VirtualStreamOptions.None)
{ }
public VirtualStream(long initialLength, long initialPosition,
VirtualStreamOptions options)
{
if (initialLength < 0)
{
throw new ArgumentOutOfRangeException("initialLength");
}
if (initialPosition < 0)
{
throw new ArgumentOutOfRangeException("initialPosition");
}
if (!options.IsValid())
{
throw new ArgumentException("options");
}
length = initialLength;
position = initialPosition;
if (options.IsSet(VirtualStreamOptions.ReadRandomBytes))
{
random = new Random();
}
this.options = options;
}
public override bool CanRead
{
get { return true; }
}
public override bool CanSeek
{
get { return true; }
}
public override bool CanWrite
{
get { return true; }
}
public override void Flush()
{ }
public override long Length
{
get { return length; }
}
public override long Position
{
get { return position; }
set
{
if (value < 0)
{
throw new ArgumentOutOfRangeException("value");
}
position = value;
}
}
public override int Read(byte[] buffer, int offset, int count)
{
if (buffer == null)
{
throw new ArgumentNullException("buffer");
}
if (offset < 0)
{
throw new ArgumentOutOfRangeException("offset");
}
if (count < 0)
{
throw new ArgumentOutOfRangeException("count");
}
if ((buffer.Length - offset) < count)
{
throw new ArgumentException(
"offset subtracted from the buffer length is less than count.");
}
long bytesLeft = length - position;
if (bytesLeft <= 0)
{
return 0;
}
if (bytesLeft < count)
{
count = (int) bytesLeft;
}
if (options.IsSet(VirtualStreamOptions.ReadRandomBytes))
{
if (offset == 0 && count == buffer.Length)
{
random.NextBytes(buffer);
}
else
{
var tempBuffer = new byte[count];
random.NextBytes(tempBuffer);
Buffer.BlockCopy(tempBuffer, 0, buffer, offset, count);
}
}
position += count;
return count;
}
public override long Seek(long offset, SeekOrigin origin)
{
switch (origin)
{
case SeekOrigin.Begin:
if (offset < 0)
{
throw new ArgumentOutOfRangeException("offset",
"Seeking is attempted before the beginning of the stream.");
}
position = offset;
break;
case SeekOrigin.Current:
if ((position + offset) < 0)
{
throw new ArgumentOutOfRangeException("offset",
"Seeking is attempted before the beginning of the stream.");
}
position += offset;
break;
case SeekOrigin.End:
if ((length + offset) < 0)
{
throw new ArgumentOutOfRangeException("offset",
"Seeking is attempted before the beginning of the stream.");
}
position = length + offset;
break;
default:
throw new ArgumentException(
"There is an invalid SeekOrigin.", "origin");
}
return position;
}
public override void SetLength(long value)
{
if (value < 0)
{
throw new ArgumentOutOfRangeException("value");
}
length = value;
if (position > length)
{
position = length;
}
}
public override void Write(byte[] buffer, int offset, int count)
{
if (buffer == null)
{
throw new ArgumentNullException("buffer");
}
if (offset < 0)
{
throw new ArgumentOutOfRangeException("offset");
}
if (count < 0)
{
throw new ArgumentOutOfRangeException("count");
}
if ((buffer.Length - offset) < count)
{
throw new ArgumentException(
"offset subtracted from the buffer length is less than count.");
}
long num = position + count;
if (num < 0)
{
throw new IOException("Stream too long.");
}
if (num > length)
{
length = num;
}
position = num;
}
}
public static class StreamOperation
{
public static void Copy(Stream source, Stream target)
{
Copy(source, target, 0x1000);
}
public static void Copy(Stream source, Stream target, int bufferSize)
{
if (source == null)
{
throw new ArgumentNullException("source");
}
if (target == null)
{
throw new ArgumentNullException("target");
}
if (bufferSize < 0)
{
throw new ArgumentOutOfRangeException("bufferSize");
}
var buffer = new byte[bufferSize];
int bytesRead;
while ((bytesRead = source.Read(buffer, 0, bufferSize)) != 0)
{
target.Write(buffer, 0, bytesRead);
}
}
}
class Program
{
static void Main()
{
using (var source = new VirtualStream(int.MaxValue + 1L, 0,
VirtualStreamOptions.ReadRandomBytes))
using (var target = new DeflateStream(new FileStream(@"c:\out.bin",
FileMode.Create, FileAccess.Write), CompressionMode.Compress))
{
StreamOperation.Copy(source, target);
}
}
}
}
WARNING: DRAFT, WORK-IN-PROGRESS
You may or may not be aware that since .NET 2.0, the .NET System.Threading.ThreadPool class (thread pool) has supported I/O completion ports -- available on Windows 2000 and later. This addition to the thread pool means a significant boost in performance of asynchronous I/O operations. However, it is not immediately obvious how to go about using this new functionality in your own class libraries and applications.
Firstly, some background on the basic architecture of the .NET thread pool.
There is only ONE thread pool per process (not per AppDomain as may be thought). This actually improves performance of I/O operations as we shall see later, by channeling all .NET framework asynchronous I/O operations, for all AppDomains in a process, through the same point.
Why a thread pool? Creating and destroying threads, while pretty quick these days, is still not free in terms of performance. A thread pool recycles threads after they are used, instead of repeatedly creating and destroying threads. The thread pool controls the minimum and maximum number of threads in the pool and automatically grows and shrinks the thread pool as needed.
To give you an idea of what is involved in creating and destroying threads, when a thread is created, the Windows kernel allocates an internal object to represent the thread, allocates memory for the thread's stack and initialises the thread's stack memory to zero. If your process has any DLLs loaded (which it typically will have several), each DLL entry point is called specifying DLL_THREAD_ATTACH. This has two implications. Firstly, Windows must load pages from disk into memory in order to execute the DLL entry point code (resulting in page faults) and then the actual DLL entry point user code must be executed. The DLL entry point user code may incur significant overhead depending on what the code actually does. This is repeated for every DLL loaded in the process whether your thread will use that DLL or not!
To destroy a thread, the entry point to every DLL loaded in the process must again be called, specifying DLL_THREAD_DETACH. Again, Windows needs to page-fault pages from disk into memory and then execute the required user code, which may incur significant overhead. The thread's stack memory must be freed and the kernel thread object must be freed if there are no more references to it.
All of this overhead just to create and destroy a single thread, before it is actually even scheduled to perform work.
To get any kind of scalable performance, a thread pool is the only way to go. Further, using multiple thread pools concurrently DOES NOT improve performance through-put, contrary to popular belief, as we shall see when we look at how asynchronous I/O operations are handled. It is a common request from developers to be able to create additional thread pools. While this may seem appealing, there are performance downsides to doing this and this is why the .NET framework team has consciously excluded this functionality from .NET. You should almost always use the System.Threading.ThreadPool for asynchronous I/O. There are a few rare cases where it makes sense to have a separate thread pool, but this is certainly NOT the general case.
The thread pool has four types of threads. They are:
- Worker Thread
- Completion Port Thread
- Wait Thread
- Timer Manager Thread
The worker thread is the one we all should be immediately familiar with. You are able to run tasks on a thread pool worker thread using ThreadPool.QueueUserWorkItem. There is currently a default maximum of 500 worker threads, but this may change in the future. You can also explicitly set the maximum number of worker threads using ThreadPool.SetMaxThreads.
The completion port threads are threads that are specifically designed to be used for asynchronous I/O operations. These threads wait on a single I/O completion port (IOCP). The .NET thread pool has exactly ONE IOCP. There is currently a default maximum of 1,000 IOCP threads, but this may change in the future. You can also explicitly set the maximum number of IOCP threads using ThreadPool.SetMaxThreads. You cannot set a maximum value less than the number of CPUs in the machine -- we will see why below.
NOTE: Some hosts may prevent you from changing the thread pool min and max threads, e.g. IIS and SQL Server.
The worker threads and IOCP threads are the only two thread pool threads that actually can run user code. The wait threads and timer threads are used internally by the thread pool to manage waiting on a wait handle and timer based tasks respectively.
When you call ThreadPool.RegisterWaitForSingleObject, you specify a WaitHandle object, as well as a delegate (callback) to the method you want to execute, a timeout and if the delegate must be executed only once or multiple times. The thread pool allocates a wait thread and calls WaitHandle.WaitOne(timeout) on the wait handle, and for the timeout, you passed to RegisterWaitForSingleObject. When the wait handle is signaled, e.g. by another thread calling AutoResetEvent.Set(), or the timeout elapses (note, the timeout may be 0 -- immediate, or -1 -- infinite), the wait thread queues your delegate (callback) to an IOCP thread pool thread for execution. If you specified execute only once in the call to RegisterWaitForSingleObject, the wait thread exits and returns to the thread pool to be reused, otherwise, the wait thread again calls WaitHandle.WaitOne(timeout). Note that your callback is executed on an completion port thread and NOT a worker thread.
Individual wait threads are not actually allocated to every single call to RegisterWaitForSingleObject. Wait threads are shared. They achieve this by internally using WaitForMultipleObjects, waiting on many wait handles. This reduces the number of wait threads required to service signaled or timeout based tasks. Currently, a single wait thread can service 63 wait handles, but this may change in the future. So if you had 1,000 wait handles registered, the thread pool uses only 16 threads to wait on those 1,000 wait handles.
Firstly, we should identify when it is appropriate to use the thread pool IOCP threads instead of the thread pool worker threads.
If you have some task that you want to execute asynchronously (in the background), the typical approach is to call ThreadPool.QueueUserWorkItem. This is completely valid for tasks that run code that utilise the CPU intensely for a relatively short period to perform their work and then finish. Some tasks though, utilise the CPU to initiate a task and then wait while some "device" performs I/O. When the "device" completes the required I/O, the CPU is again used to process the result. The latter type of task is classified as an I/O operation. I/O operations are characterised by the initial use of the CPU to initiate the I/O (the I/O request), then a dead period where the CPU is idle while the I/O is taking place (I/O typically takes place on some hardware device), and then the use of the CPU to process the response after the I/O completes (the I/O response).
If you use a normal thread pool worker thread, using Thread.QueueUserWorkItem, to process an I/O operation, that thread is blocked for the entire duration of the I/O task. However, the thread could have been used to do something else while it was waiting for the I/O operation to complete, as it is essentially idle during this time.
This is where IO Completion Ports (IOCP's) come to the rescue.
----
Since this article is currently incomplete, here is the code to queue a work item to the thread pool IOCP threads. The explanation will follow later. There is also a more complicated version of this using a Queue to queue the state rather than packing the IAsyncResult object into the Overlapped object, which could be more efficient. I will post the second version later when I complete this article.
public static class ThreadPoolHelper
{
private sealed class CallbackState : IAsyncResult
{
private readonly WaitCallback callback;
private readonly object state;
internal CallbackState(WaitCallback callback, object state)
{
this.callback = callback;
this.state = state;
}
internal void InvokeCallback()
{
callback(state);
}
#region IAsyncResult Members
public object AsyncState
{
get { throw new NotSupportedException(); }
}
public WaitHandle AsyncWaitHandle
{
get { throw new NotSupportedException(); }
}
public bool CompletedSynchronously
{
get { throw new NotSupportedException(); }
}
public bool IsCompleted
{
get { throw new NotSupportedException(); }
}
#endregion
}
private static unsafe void IOCompletionCallback(uint errorCode, uint numBytes,
NativeOverlapped* pOVERLAP)
{
try
{
((CallbackState)Overlapped.Unpack(pOVERLAP).AsyncResult).InvokeCallback();
}
finally
{
Overlapped.Free(pOVERLAP);
}
}
public static bool UnsafeQueueIOCompletion(WaitCallback callback, object state)
{
if (callback == null)
{
throw new ArgumentNullException("callback");
}
unsafe
{
return ThreadPool.UnsafeQueueNativeOverlapped(
new Overlapped(0, 0, IntPtr.Zero,
new CallbackState(callback, state)).UnsafePack(
IOCompletionCallback, null));
}
}
}
Test console application:
class Program
{
static void DisplayThreadInfo()
{
int workerThreads, completionPortThreads;
ThreadPool.GetAvailableThreads(out workerThreads, out completionPortThreads);
Console.WriteLine("id={0},workerThreads={1},completionPortThreads={2}",
Thread.CurrentThread.ManagedThreadId, workerThreads, completionPortThreads);
}
static void IOCallback(object state)
{
Console.WriteLine((string)state);
DisplayThreadInfo();
}
static void Main()
{
DisplayThreadInfo();
ThreadPoolHelper.UnsafeQueueIOCompletion(IOCallback, "Hello World from IOCP thread pool thread.");
Thread.Sleep(1000);
Console.WriteLine("Press a key to exit...");
Console.ReadKey(true);
}
}
"Transparent data encryption (TDE) performs real-time I/O encryption and decryption of the data and log files. The encryption uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery. The DEK is a symmetric key secured by using a certificate stored in the master database of the server or an asymmetric key protected by an EKM module. TDE protects data "at rest", meaning the data and log files. It provides the ability to comply with many laws, regulations, and guidelines established in various industries. This enables software developers to encrypt data by using AES and 3DES encryption algorithms without changing existing applications."
Check List:
- Backup the SERVICE MASTER KEY. This is the main key for the whole SQL Server instance.
- Keep the backup file in a secure, offsite location.
- Keep the password for the backup file in a secure, offsite location.
BACKUP SERVICE MASTER KEY TO FILE = 'path_to_file'
ENCRYPTION BY PASSWORD = 'password';
GO
- Create the "master" database MASTER KEY encrypted by a password and the SERVICE MASTER KEY.
USE [master];
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'password';
GO
- Backup the "master" database MASTER KEY. This is the main key for the "master" database used to ultimately encrypt your database encryption key.
- Keep the backup file in a secure, offsite location.
- Keep the password for the backup file in a secure, offsite location.
USE [master];
GO
BACKUP MASTER KEY TO FILE = 'path_to_file'
ENCRYPTION BY PASSWORD = 'password';
GO
- Create the database encryption key (DEK) server certificate encrypted by the "master" database MASTER KEY.
USE [master];
GO
CREATE CERTIFICATE certificate_name
WITH SUBJECT = 'certificate_subject_name';
GO
- Backup the database encryption key (DEK) server certificate with the private key.
USE [master];
GO
BACKUP CERTIFICATE certificate_name
TO FILE = 'path_to_file'
WITH PRIVATE KEY (
FILE = 'path_to_private_key_file',
ENCRYPTION BY PASSWORD = 'encryption_password'
);
GO
- Create the database encryption key (DEK) encrypted by the server certificate.
USE [database_name];
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE certificate_name;
GO
- Enable Transparent Data Encryption (TDE) on your database.
ALTER DATABASE database_name
SET ENCRYPTION ON;
GO
The .NET ECMA specified memory model is weaker than the x86 hardware memory model. When you run your multi-threaded .NET applications on a multi-processor machine with a weak memory model, such as the Intel Itanium Architecture (IA64), your programs may not execute as expected.
This is because weaker memory models can reorder memory operations (loads and stores) out of program sequence in order to improve performance. This reordering is guaranteed to have no visible side-effects on a single thread of execution. However, if you are accessing memory across threads, and the stores to, and loads from that memory are reordered, this may produce undesirable side-effects, effectively breaking your well-crafted application.
However, if all access to memory shared across threads is completely protected by a synchronisation primitive such as the System.Threading.Monitor class using the C# lock keyword, then you will never experience any of these side-effects. This is because .NET does all the work under the hood to ensure that the appropriate memory barriers are in place. Acquiring a lock in C# for instance implements a read memory barrier on entry, while releasing a lock in C# implements a write memory barrier on exit.
There are however cases where performance is critical and we therefore opt for a low-lock synchronisation strategy. A low-lock synchronisation strategy tries to avoid locking wherever possible to improve performance. As soon as you enter this world though, you are treading in dangerous territory and you need to be aware of memory barriers -- see System.Threading.Thread.MemoryBarrier.
A memory barrier also known as a memory fence ensures that memory operations that precede the memory barrier do not get reordered to occur after the memory barrier and memory operations that occur after the memory barrier do not get reordered to occur before the memory barrier.
.NET only offers a full memory fence that is a read (load) and write (store) memory barrier (fence) in one, via System.Threading.Thread.MemoryBarrier. Some hardware platforms offer separate read and write memory barriers, which can further improve performance.
int a = 1; // store
int b = 2; // store
int c = 3; // store
In the code above, the program sequence for the three memory operations is as follows: first store 1 in the memory location represented by 'a', then store 2 in the memory location represented by 'b' and finally store 3 in the memory location represented by 'c'.
In a weak memory model (e.g. the .NET memory model on IA64), these memory store operations can be reordered in any order, e.g.
int b = 2; // store
int a = 1; // store
int c = 3; // store
This reordering has no effect on the outcome, on a single thread of execution, and therefore the program still proceeds as expected. However, if another thread (thread B) was relying on 'a' being set before 'b' gets set, then in the reordered example, because 'b' is set before 'a', thread B would see another value for 'a' (possibly 0) instead of 1.
To correct this program, we can either place a lock around all accesses to 'a' and 'b', thus implicitly inserting memory barriers, or we could explicitly insert a memory barrier between 'a' and 'b' as follows:
int a = 1; // store
Thread.MemoryBarrier();
int b = 2; // store
int c = 3; // store
Now, the store to 'a' cannot move down past the memory barrier and the stores to 'b' and 'c' cannot move up past the memory barrier. However, the stores to 'b' and 'c' can still be reordered after the memory barrier as follows:
int a = 1; // store
Thread.MemoryBarrier();
int c = 3; // store
int b = 2; // store
The C# volatile keyword implements acquire and release semantics, which implies a read memory barrier on read and a write memory barrier on write.
You can also use System.Threading.Thread.VolatileRead and System.Threading.Thread.VolatileWrite to implement acquire and release semantics respectively. However, these API's simply make a call to System.Threading.Thread.MemoryBarrier and are slower than simply calling System.Threading.Thread.MemoryBarrier yourself. Therefore, they have little practical application, since the reason you resort to memory barriers in the first place is performance.
The System.Threading.Interlocked class API's also implicitly apply the appropriate memory barriers on your behalf. These API's are relatively quick too.
Consider the following simple program that relies on a shared 'stop' flag to stop a thread from executing:
class Program
{
static bool stop = false;
static void Main()
{
Thread thread = new Thread(Job);
thread.Start();
Thread.Sleep(2000);
stop = true;
}
static void Job()
{
int counter = 0;
while (!stop)
{
Console.WriteLine(counter++);
}
}
}
On x86 hardware, this program will execute as expected. On multi-processor IA64 hardware, this application may never end. This is because the thread running 'Job' may decide to load the value of 'stop' (initially false) into the processor cache and never reload it -- blissfully unaware that another thread (the main application thread), running on another processor, has changed the value of stop to 'true'.
Likewise, the 'Job' method may never get to execute if the assignment of 'true' to 'stop' is moved before the call to Thread.Start().
To correct this program we need to lock around all accesses to 'stop'.
class Program
{
static bool stop = false;
static readonly object syncRoot = new object();
static bool Stop
{
get
{
lock (syncRoot)
{
return stop;
}
}
set
{
lock (syncRoot)
{
stop = value;
}
}
}
static void Main()
{
Thread thread = new Thread(Job);
thread.Start();
Thread.Sleep(2000);
Stop = true;
}
static void Job()
{
int counter = 0;
while (!Stop)
{
Console.WriteLine(counter++);
}
}
}
Alternatively we could mark the 'stop' field volatile:
class Program
{
static volatile bool stop = false;
static void Main()
{
Thread thread = new Thread(Job);
thread.Start();
Thread.Sleep(2000);
stop = true;
}
static void Job()
{
int counter = 0;
while (!stop)
{
Console.WriteLine(counter++);
}
}
}
Alternatively we could use System.Threading.Thread.VolatileRead and System.Threading.Thread.VolatileWrite:
class Program
{
static int stop = 0;
static bool Stop
{
get
{
return Thread.VolatileRead(ref stop) != 0;
}
set
{
Thread.VolatileWrite(ref stop, value ? 1 : 0);
}
}
static void Main()
{
Thread thread = new Thread(Job);
thread.Start();
Thread.Sleep(2000);
Stop = true;
}
static void Job()
{
int counter = 0;
while (!Stop)
{
Console.WriteLine(counter++);
}
}
}
Notice though that there are no overloads for System.Threading.Thread.VolatileRead and System.Threading.Thread.VolatileWrite that take a Boolean parameter, so we switched to using an int instead.
Alternatively we could use System.Threading.MemoryBarrier explicitly:
class Program
{
static bool stop = false;
static bool Stop
{
get
{
bool localStop = stop;
Thread.MemoryBarrier();
return localStop;
}
set
{
Thread.MemoryBarrier();
stop = value;
}
}
static void Main()
{
Thread thread = new Thread(Job);
thread.Start();
Thread.Sleep(2000);
Stop = true;
}
static void Job()
{
int counter = 0;
while (!Stop)
{
Console.WriteLine(counter++);
}
}
}
You can also creatively use the System.Threading.Interlocked class since all the interlocked operations have memory barriers as well -- this approach is significantly faster than using a System.Threading.Monitor class with the C# lock keyword:
class Program
{
static int stop = 0;
static bool Stop
{
get
{
return Interlocked.CompareExchange(ref stop, 0, 0);
}
set
{
Interlocked.CompareExchange(ref stop, value ? 1 : 0, value ? 0 : 1);
}
}
static void Main()
{
Thread thread = new Thread(Job);
thread.Start();
Thread.Sleep(2000);
Stop = true;
}
static void Job()
{
int counter = 0;
while (!Stop)
{
Console.WriteLine(counter++);
}
}
}
We've looked very briefly at the .NET memory model reordering and how you can combat it in your multi-threaded applications using the C# volatile keyword, the C# lock keyword, the System.Threading.Thread.MemoryBarrier API, the System.Threading.Thread.VolatileRead/VolatileWrite API's and the System.Threading.Interlocked class API's.
Here is a summary of all the possible reordering's with respect to one another in the .NET memory model:
In the first column is the first operation, while along the top row is the second operation. For example, a read (normal read -- NOT a volatile read) can reorder with respect to a read that follows it, but not with respect to a volatile write that follows it.
| |
Read |
Write |
Volatile Read |
Volatile Write |
Lock |
Unlock |
| Read |
Yes |
Yes |
Yes |
No |
Yes |
No |
| Write |
Yes |
Yes |
Yes |
No |
Yes |
No |
| Volatile Read |
No |
No |
No |
No |
No |
No |
| Volatile Write |
Yes |
Yes |
Yes |
No |
Yes |
No |
| Lock |
No |
No |
No |
No |
No |
No |
| Unlock |
Yes |
Yes |
Yes |
No |
No |
No |
- Normal reads and writes are freely reordered.
- Locks and unlocks are never reordered.
- Volatile reads and writes have acquire and release semantics. That is, operations after volatile-read cannot be moved to before the volatile-read and operations before volatile-write cannot be moved to after the volatile-write.
One classic low-lock strategy is the double-check lock pattern, for example:
public class Singleton
{
private static readonly object syncRoot = new object();
private static Singleton instance;
private readonly string message;
private Singleton(string message)
{
this.message = message;
}
public string Message
{
get { return message; }
}
public static Singleton Instance
{
if (instance == null)
{
lock (syncRoot)
{
if (instance == null)
{
instance = new Singleton("Hello World"); // memory barrier required
}
}
}
return instance;
}
}
class Program
{
static void Main()
{
Console.WriteLine(Singleton.Instance.Message);
}
}
This code uses the double-check lock pattern, to implement the lazy initialisation pattern to implement the singleton pattern.
In the 'Instance' property, the code prevents unnecessary locking by first checking if 'instance' is initialised or not. If it is initialised, it simply returns 'instance', otherwise it proceeds to initialise 'instance'. In order to ensure thread safety when initialising 'instance', a lock is taken and 'instance' is again checked for null before being initialised -- this is the double-check lock pattern.
Unfortunately, this code is broken on IA64.
The line that initialises 'instance' require two memory stores. The first (in program order) is to store the value "Hello World" to the memory location represented by the 'message' field of the 'Singleton' class. The second (in program order) is to store the newly constructed instance of the 'Singleton' class to the 'instance' field of the 'Singleton' class.
However, on IA64, these two memory store operations can be reordered, resulting in the instance of the 'Singleton' class being first assigned to the 'instance' field, before "Hello World" is assigned to the 'message' field. Thus another thread could see the 'instance' field as initialised and attempt to use it. However, the 'message' field may not yet have been set, thus the program could write out 'null' to the console.
To fix this, a memory barrier is required to force the order of the two inner memory store operations (store to 'message' and store to 'instance') to always occur in the correct order. The corrected program is as follows:
public static Singleton Instance
{
get
{
if (instance == null)
{
lock (syncRoot)
{
Singleton tempInstance = new Singleton("Hello World");
Thread.MemoryBarrier();
instance = tempInstance;
}
}
return instance;
}
}
Note that NOT all implementations of the double-check lock pattern require a memory barrier. It is only because the inner initialisation code had more than one memory store operation that must occur in order that a memory barrier is required. If your implementation of the 'Singleton' class did not have a store to the 'message' field, the memory barrier would not be necessary, since there would be only one store to the 'instance' field.
For example, the following is correct, without a memory barrier, even on IA64:
public class Singleton
{
private static readonly object syncRoot = new object();
private static Singleton instance;
private Singleton()
{ }
public string Message
{
get { return "Hello World"; }
}
public static Singleton Instance
{
if (instance == null)
{
lock (syncRoot)
{
if (instance == null)
{
instance = new Singleton();
}
}
}
return instance;
}
}
class Program
{
static void Main()
{
Console.WriteLine(Singleton.Instance.Message);
}
}
You could of course change the 'instance' field to volatile and then the memory barrier would not be required. However, this incurs the overhead of doing a volatile read (read memory barrier) every time the 'Instance' property is called and therefore defeats the purpose of the performance optimisation -- although volatile is very quick, it is not as quick as just incurring the performance penalty of the System.Threading.Thread.MemoryBarrier the first time you initialise the 'instance' field.
You should always use a System.Security.SecureString when creating .NET APIs that handle passwords. Unfortunately many of the .NET Framework APIs do not yet use System.Security.SecureString. This will change in the future, but for now, how do you handle the marshaling of a System.Security.SecureString to an API that uses System.String without leaving traces of the password all over memory. Here is one work-around for this problem.
First off, what is wrong with using System.String to represent passwords:
1. The GC is free to move the System.String around in memory (unknown to the developer). When moving the memory buffer around in memory, copies of the string are left all over the now free areas of the process memory. For efficiency, the GC does not zero the memory that it frees. So even though your code may only have 'one copy' of the string, in memory there could be thousands of copies of the string that are no longer accessible by a reference or pointer and will remain there until that memory is used for something else and gets overwritten.
2. Whenever you perform some System.String manipulation, a new System.String is created. This is because System.String is immutable. Thus, if I append characters onto a System.String in order to build up a password, pieces of the password are left all over the process memory by the temporary strings that are created.
An example of a .NET Framework API that should use a System.Security.SecureString to represent a password, but instead uses a System.String, is the WCF System.ServiceModel.Security.UserNamePasswordClientCredential.Password property. There are many examples of this in the .NET Framework. One in particular that relates to a post below about the SQL OPEN { MASTER | SYMMETRIC } KEY relates to ADO.NET not accepting a System.Security.SecureString as a mapable type to a string-based parameter. This means that all passwords sent to the database via ADO.NET must be System.String.
There is some good news though, the System.Diagnostics.Process class uses a System.Security.SecureString to represent the password.
So, how do we marshal a System.Security.SecureString to a System.String temporarily, and then guarantee that traces aren't left all over the process memory. We need to implement two things:
1. Guarantee that the .NET GC does not move the System.String around in memory.
2. Guarantee a deterministic freeing of the string, zeroing out the memory.
To accomplish the first one, we will pin the string in memory using the System.Runtime.InteropServices.GCHandle class.
To accomplish the second, we will use managed pointers (unsafe code) to mutate the System.String and the strong guarantees of the System.Runtime.CompilerServices.RuntimeHelpers.ExecuteCodeWithGuaranteedCleanup method.
Step 1: Capture a System.Security.SecureString password from the command line.
SecureString password = ConsoleUtility.CapturePassword("Enter password: ");
See my post below for the ConsoleUtility code. http://dotnet.org.za/markn/archive/2008/10/03/simple-console-password-capture-utility.aspx
Step 2: Allocate an "empty" System.String (filled with zeros '\0') of the correct length.
int length = password.Length;
var insecurePassword = new string('\0', length);
This creates a string filled with the '\0' character for the same length as the password. At this point, the GC is still free to move this string around in memory, leaving copies of it in memory as it goes. We are not concerned with this, because the string does not contain any sensitive information yet.
Step 3: Pin the System.String so that the GC cannot move it around in memory.
GCHandle gch = GCHandle.Alloc(insecurePassword, GCHandleType.Pinned);
Once pinned by a GC handle, the GC cannot move the memory until GCHandle.Free is called.
Step 4: Marshal the System.Security.SecureString to the pinned System.String.
1: IntPtr passwordPtr = Marshal.SecureStringToBSTR(password);
2: var pPassword = (char*)passwordPtr;
3: var pInsecurePassword = (char*)gch.AddrOfPinnedObject();
4: for (int index = 0; index < length; index++)
5: {
6: pInsecurePassword[index] = pPassword[index];
7: }
8: Marshal.ZeroFreeBSTR(passwordPtr);
Line 1 marshals the encrypted System.Security.SecureString password to an unmanaged buffer. Line 2 gets the managed pointer to the unmanaged buffer. Line 3 gets the managed pointer to the pinned managed System.String that we are going to copy the password into. Lines 4 to 7 copy the password into the System.String buffer (mutating the immutable string). Line 8 zeros and frees the unmanaged buffer holding the marshaled password.
NOTE: This code must be executed in an unsafe context. Place the unsafe keyword around the code or on the method. Enable your assembly to use unsafe code under Properties, Build, Allow unsafe code.
Step 5: Use the System.String password.
Console.WriteLine(insecurePassword);
Here we would pass the System.String to the .NET API that requires a System.String. In this case I just output the password to the console. In a real situation this would be a more useful call.
Step 6: Deterministically zero the pinned System.String password.
for (int index = 0; index < length; index++)
{
pInsecurePassword[index] = '\0';
}
This overwrites (mutates) the System.String memory with zeros '\0'.
Verify that the System.String memory has been overwritten.
Console.WriteLine(insecurePassword);
Step 7: Unpin the System.String password.
Now the System.String is free to be garbage collected by the GC, but the string does not contain any sensitive information, so we don't care that the string can now move around in memory or that there is a delay before the string is actually GC'ed.
That is the basic structure of the code. However, there are potential memory leaks in this code in the face of exceptions and asynchronous exceptions such as ThreadAbortException, OutOfMemoryException and StackOverflowException, etc.
In Step 3, we need to protect the GCHandle.Alloc to ensure that we do not leak a GC handle. An asynchronous exception, such as ThreadAbortException may be thrown after the GCHandle.Alloc method has allocated the handle, but before it is assigned to the variable 'gch', thus leaking the handle. To protect this allocation and the assignment to the variable we use a CER (Constrained Execution Region).
Step 3 changes to the following code:
1: GCHandle gch;
2: RuntimeHelpers.PrepareConstrainedRegions();
3: try {} finally
4: {
5: gch = GCHandle.Alloc(insecurePassword, GCHandleType.Pinned);
6: }
The try block is not a CER, only the finally block is a CER. You will often see this strange form of a try finally block with RuntimeHelpers.PrepareConstrainedRegions method call directly above the try. This declares a CER for the finally block. A CER cannot be interrupted once entered, even by asynchronous exceptions such as ThreadAbortException, OutOfMemoryException and StackOverflowException. This post is not about CERs, so I won't go into more detail here. NOTE: CERs should NOT be used in "normal, everyday" code. They should only be used when marshaling to and from unmanaged code or in this edge case, when using GCHandle, because GCHandle requires an explicit free.
In Step 4, line 1, the Marshal.SecureStringToBSTR method call is also not safe from asynchronous exceptions. Again we require a CER...
1: IntPtr passwordPtr;
2: RuntimeHelpers.PrepareConstrainedRegions();
3: try {} finally
4: {
5: passwordPtr = Marshal.SecureStringToBSTR(password);
6: }
Finally, we should ensure that the GC handle is freed with GCHandle.Free and the unmanaged pointer is freed with Marshal.ZeroFreeBSTR. To do this, we could use a try/finally, but this does not guarantee that the finally will be executed in the face of asynchronous exceptions. To harden this and ensure we don't leak GC handles or unmanaged memory, we again use CERs. We should also dispose the System.Security.SecureString to ensure good house-keeping with a using. Here is the final reworked code.
1: using (SecureString password = ConsoleUtility.CapturePassword("Enter password: "))
2: {
3: int length = password.Length;
4: var insecurePassword = new string('\0', length);
5:
6: var gch = new GCHandle();
7: RuntimeHelpers.PrepareConstrainedRegions();
8: try
9: {
10: RuntimeHelpers.PrepareConstrainedRegions();
11: try {} finally
12: {
13: gch = GCHandle.Alloc(insecurePassword, GCHandleType.Pinned);
14: }
15:
16: IntPtr passwordPtr = IntPtr.Zero;
17: try
18: {
19: RuntimeHelpers.PrepareConstrainedRegions();
20: try {} finally
21: {
22: passwordPtr = Marshal.SecureStringToBSTR(password);
23: }
24:
25: var pPassword = (char*)passwordPtr;
26: var pInsecurePassword = (char*)gch.AddrOfPinnedObject();
27: for (int index = 0; index < length; index++)
28: {
29: pInsecurePassword[index] = pPassword[index];
30: }
31:
32: // Use the password.
33: Console.WriteLine(insecurePassword);
34: }
35: finally
36: {
37: if (passwordPtr != IntPtr.Zero)
38: {
39: Marshal.ZeroFreeBSTR(passwordPtr);
40: }
41: }
42: }
43: finally
44: {
45: if (gch.IsAllocated)
46: {
47: // Zero the string.
48: var pInsecurePassword = (char*)gch.AddrOfPinnedObject();
49: for (int index = 0; index < length; index++)
50: {
51: pInsecurePassword[index] = '\0';
52: }
53: gch.Free();
54: }
55: }
56: }
[EDIT] There is an error in the code above. Between lines 16 and 17 there should be a RuntimeHelpers.PrepareConstrainedRegions(); method call.
[EDIT] Lines 32 and 33 should come after line 41 to ensure that the unmanaged buffer is freed as early as possible.
There is still a potential problem with this code. The final finally that zeros and unpins the managed System.String containing the unencrypted password, may never execute in the face of a StackOverflowException.
To harden this code further we can convert the outer try/finally (CER) to RuntimeHelpers.ExecuteCodeWithGauranteedCleanup method. This guarantees that the cleanup code ALWAYS executes even in the face of a StackOverflowException. Here is the reworked code...
1: using (SecureString password = ConsoleUtility.CapturePassword("Enter password: "))
2: {
3: int length = password.Length;
4: var insecurePassword = new string('\0', length);
5:
6: var gch = new GCHandle();
7: RuntimeHelpers.ExecuteCodeWithGuaranteedCleanup(
8: delegate
9: {
10: RuntimeHelpers.PrepareConstrainedRegions();
11: try {} finally
12: {
13: gch = GCHandle.Alloc(insecurePassword, GCHandleType.Pinned);
14: }
15:
16: IntPtr passwordPtr = IntPtr.Zero;
17: try
18: {
19: RuntimeHelpers.PrepareConstrainedRegions();
20: try {} finally
21: {
22: passwordPtr = Marshal.SecureStringToBSTR(password);
23: }
24:
25: var pPassword = (char*)passwordPtr;
26: var pInsecurePassword = (char*)gch.AddrOfPinnedObject();
27: for (int index = 0; index < length; index++)
28: {
29: pInsecurePassword[index] = pPassword[index];
30: }
31:
32: // Use the password.
33: Console.WriteLine(insecurePassword);
34: }
35: finally
36: {
37: if (passwordPtr != IntPtr.Zero)
38: {
39: Marshal.ZeroFreeBSTR(passwordPtr);
40: }
41: }
42: },
43: delegate
44: {
45: if (gch.IsAllocated)
46: {
47: // Zero the string.
48: var pInsecurePassword = (char*)gch.AddrOfPinnedObject();
49: for (int index = 0; index < length; index++)
50: {
51: pInsecurePassword[index] = '\0';
52: }
53: gch.Free();
54: }
55: },
56: null);
57: }
[EDIT] There is an error in the code above. Between lines 16 and 17 there should be a RuntimeHelpers.PrepareConstrainedRegions(); method call.
[EDIT] Lines 32 and 33 should come after line 41 to ensure that the unmanaged buffer is freed as early as possible.
We did this extra hardening step to ensure the safety of the password. That is, to ensure that the System.String containing the unencrypted password is ALWAYS zeroed deterministically.
However, there is still a potential memory leak in that the Marshal.ZeroFreeBSTR may never execute. We can further harden this code by replacing the inner try/finally with RuntimeHelpers.ExecuteCodeWithGuaranteedCleanup, although this is not necessary for the security of the password, only to avoid a memory leak.
This is the final code listing:
using (SecureString password = ConsoleUtility.CapturePassword("Enter password: "))
{
int length = password.Length;
var insecurePassword = new string('\0', length);
var gch = new GCHandle();
RuntimeHelpers.ExecuteCodeWithGuaranteedCleanup(
delegate
{
RuntimeHelpers.PrepareConstrainedRegions();
try {} finally
{
gch = GCHandle.Alloc(insecurePassword, GCHandleType.Pinned);
}
IntPtr passwordPtr = IntPtr.Zero;
RuntimeHelpers.ExecuteCodeWithGuaranteedCleanup(
delegate
{
RuntimeHelpers.PrepareConstrainedRegions();
try {} finally
{
passwordPtr = Marshal.SecureStringToBSTR(password);
}
var pPassword = (char*)passwordPtr;
var pInsecurePassword = (char*)gch.AddrOfPinnedObject();
for (int index = 0; index < length; index++)
{
pInsecurePassword[index] = pPassword[index];
}
},
delegate
{
if (passwordPtr != IntPtr.Zero)
{
Marshal.ZeroFreeBSTR(passwordPtr);
}
},
null);
// Use the password.
Console.WriteLine(insecurePassword);
},
delegate
{
if (gch.IsAllocated)
{
// Zero the string.
var pInsecurePassword = (char*)gch.AddrOfPinnedObject();
for (int index = 0; index < length; index++)
{
pInsecurePassword[index] = '\0';
}
gch.Free();
}
},
null);
}
For command line utilities, that require capturing a password, you may find this C# utility class to capture a password useful.
Features:
1. Allows you to specify a prompt, e.g. "Enter password: "
2. Caters for masking or unmasking the displayed password, e.g. abc123 or ******
3. Allows you to specify the masking character, e.g. - instead of *
4. Caters for a maximum length, or unlimited length passwords.
5. Caters for processing the backspace key so the user can make corrections.
6. Uses the System.Security.SecureString class (correctly) to ensure that the password is encrypted in memory.
Copy this code into a Visual Studio 2008,C# Windows Console Application Project, Program.cs file.
using System;
using System.Runtime.CompilerServices;
using System.Runtime.InteropServices;
using System.Security;
namespace TestConsoleUtility
{
/// <summary>
/// Extensions to the <see cref="Console"/> class.
/// </summary>
public static class ConsoleUtility
{
/// <summary>
/// Captures an encrypted password from the console.
/// </summary>
/// <remarks>
/// No prompt message is displayed to the user.
/// The password may be any length.
/// The characters entered by the user are replaced by the '*' character
/// for improved security.
/// The user may use the backspace key to make corrections while
/// capturing the password.
/// </remarks>
/// <returns>A <see cref="SecureString"/> containing the encrypted
/// password.</returns>
public static SecureString CapturePassword()
{
return CapturePassword(null, -1, true, '*');
}
/// <summary>
/// Captures an encrypted password from the console.
/// </summary>
/// <remarks>
/// The password may be any length.
/// The characters entered by the user are replaced by the '*' character
/// for improved security.
/// The user may use the backspace key to make corrections while
/// capturing the password.
/// </remarks>
/// <param name="prompt">The message to show to the user on the console.
/// </param>
/// <returns>A <see cref="SecureString"/> containing the encrypted
/// password.</returns>
public static SecureString CapturePassword(string prompt)
{
return CapturePassword(prompt, -1, true, '*');
}
/// <summary>
/// Captures an encrypted password from the console.
/// </summary>
/// <remarks>
/// The password may be any length.
/// If the <paramref name="useMask"/> parameter is true, the characters
/// entered by the user are replaced by the '*' character for improved
/// security.
/// The user may use the backspace key to make corrections while
/// capturing the password.
/// </remarks>
/// <param name="prompt">The message to show to the user on the console.
/// </param>
/// <param name="useMask">true, if the characters typed by the user
/// should be replaced by the '*' character, otherwise false.</param>
/// <returns>A <see cref="SecureString"/> containing the encrypted
/// password.</returns>
public static SecureString CapturePassword(string prompt, bool useMask)
{
return CapturePassword(prompt, -1, useMask, '*');
}
/// <summary>
/// Captures an encrypted password from the console.
/// </summary>
/// <remarks>
/// The password may be any length.
/// If the <paramref name="useMask"/> parameter is true, the characters
/// entered by the user are replaced by the <paramref name="mask"/>
/// character for improved security.
/// The user may use the backspace key to make corrections while
/// capturing the password.
/// </remarks>
/// <param name="prompt">The message to show to the user on the console.
/// </param>
/// <param name="useMask">true, if the characters typed by the user
/// should be replaced by the <paramref name="mask"/> character,
/// otherwise false.</param>
/// <param name="mask">The character to replace the characters typed by
/// the user with, if the <paramref name="useMask"/> parameter is true.
/// </param>
/// <returns>A <see cref="SecureString"/> containing the encrypted
/// password.</returns>
public static SecureString CapturePassword(string prompt,
bool useMask, char mask)
{
return CapturePassword(prompt, -1, useMask, mask);
}
/// <summary>
/// Captures an encrypted password from the console.
/// </summary>
/// <remarks>
/// If the <paramref name="maxLength"/> parameter is set to -1, the
/// password can be any length.
/// The characters entered by the user are replaced by the '*' character
/// for improved security.
/// The user may use the backspace key to make corrections while
/// capturing the password.
/// </remarks>
/// <exception cref="ArgumentOutOfRangeException">
/// <paramref name="maxLength"/> is less than -1.</exception>
/// <param name="prompt">The message to show to the user on the console.
/// </param>
/// <param name="maxLength">The maximum length of the password to
/// capture. Specify -1 for infinite.</param>
/// <returns>A <see cref="SecureString"/> containing the encrypted
/// password.</returns>
public static SecureString CapturePassword(string prompt, int maxLength)
{
return CapturePassword(prompt, maxLength, true, '*');
}
/// <summary>
/// Captures an encrypted password from the console.
/// </summary>
/// <remarks>
/// If the <paramref name="maxLength"/> parameter is set to -1, the
/// password can be any length.
/// If the <paramref name="useMask"/> parameter is true, the characters
/// entered by the user are replaced by the '*' character for improved
/// security.
/// The user may use the backspace key to make corrections while
/// capturing the password.
/// </remarks>
/// <exception cref="ArgumentOutOfRangeException">
/// <paramref name="maxLength"/> is less than -1.</exception>
/// <param name="prompt">The message to show to the user on the console.
/// </param>
/// <param name="maxLength">The maximum length of the password to
/// capture. Specify -1 for infinite.</param>
/// <param name="useMask">true, if the characters typed by the user
/// should be replaced by the '*' character, otherwise false.</param>
/// <returns>A <see cref="SecureString"/> containing the encrypted
/// password.</returns>
public static SecureString CapturePassword(string prompt, int maxLength,
bool useMask)
{
return CapturePassword(prompt, maxLength, useMask, '*');
}
/// <summary>
/// Captures an encrypted password from the console.
/// </summary>
/// <remarks>
/// If the <paramref name="maxLength"/> parameter is set to -1, the
/// password can be any length.
/// If the <paramref name="useMask"/> parameter is true, the characters
/// entered by the user are replaced by the <paramref name="mask"/>
/// character for improved security.
/// The user may use the backspace key to make corrections while
/// capturing the password.
/// </remarks>
/// <exception cref="ArgumentOutOfRangeException">
/// <paramref name="maxLength"/> is less than -1.</exception>
/// <param name="prompt">The message to show to the user on the console.
/// </param>
/// <param name="maxLength">The maximum length of the password to
/// capture. Specify -1 for infinite.</param>
/// <param name="useMask">true, if the characters typed by the user
/// should be replaced by the <paramref name="mask"/> character,
/// otherwise false.</param>
/// <param name="mask">The character to replace the characters typed by
/// the user with, if the <paramref name="useMask"/> parameter is true.
/// </param>
/// <returns>A <see cref="SecureString"/> containing the encrypted
/// password.</returns>
public static SecureString CapturePassword(string prompt, int maxLength,
bool useMask, char mask)
{
if (maxLength < -1)
{
throw new ArgumentOutOfRangeException("maxLength");
}
var password = new SecureString();
// Output the prompt message.
Console.Write(prompt);
// Read in the password character by character until <Enter> is hit.
ConsoleKeyInfo consoleKeyInfo;
while ((consoleKeyInfo = Console.ReadKey(true)).Key !=
ConsoleKey.Enter)
{
if (consoleKeyInfo.Key == ConsoleKey.Backspace)
{
// Process the backspace key.
if (password.Length > 0)
{
// Remove a character from the encrypted password.
password.RemoveAt(password.Length - 1);
// Remove the last mask character from the console.
Console.Write(consoleKeyInfo.KeyChar);
Console.Write(' ');
Console.Write(consoleKeyInfo.KeyChar);
}
}
else
{
// Process a password character.
if (maxLength == -1 || password.Length < maxLength)
{
password.AppendChar(consoleKeyInfo.KeyChar);
// Write out the masked character or actual character.
Console.Write(useMask ? mask : consoleKeyInfo.KeyChar);
}
}
}
Console.WriteLine();
// Prevent further changes to the encrypted password.
password.MakeReadOnly();
return password;
}
}
class Program
{
static void Main()
{
using (var password = ConsoleUtility.CapturePassword(
"Enter password: "))
{
IntPtr passwordPtr = IntPtr.Zero;
// Execute with guaranteed cleanup to ensure we do not leave
// a clear text password in the process memory, even in the face
// of an asynchronous exception such as ThreadAbortException,
// OutOfMemoryException or StackOverflowException.
RuntimeHelpers.ExecuteCodeWithGuaranteedCleanup(
delegate
{
// Execute in a CER to guarantee that we do not leak the
// pointer in the face of asynchronous exceptions such as
// ThreadAbortException.
RuntimeHelpers.PrepareConstrainedRegions();
try { }
finally
{
passwordPtr = Marshal.SecureStringToBSTR(password);
}
// TODO: Do something useful with the password.
},
delegate
{
// Zero and free the memory.
// This is guaranteed to always execute,
// even in the face of asynchronous exceptions.
if (passwordPtr != IntPtr.Zero)
{
Marshal.ZeroFreeBSTR(passwordPtr);
}
},
null);
}
}
}
}
Be careful when using passwords with OPEN MASTER KEY and OPEN SYMMETRIC KEY on SQL Server 2005 & 2008. The password is vulnerable to SQL injection attacks, unless you escape all single-quote characters ('), with two single quote characters. This is because the DDL syntax for the OPEN MASTER KEY and OPEN SYMMETRIC KEY SQL statements do not accept parameters for the password.
Consider the following SQL statement to open a symmetric key, decrypting it by a certificate that is protected by a password:
OPEN SYMMETRIC KEY [MyKey] DECRYPTION BY CERTIFICATE [MyCert] WITH PASSWORD = '<password>';
Since OPEN ... KEY SQL statements do not accept parameters, you need to formulate the SQL string as inline-SQL. Assuming your password is 'abc123', your SQL statement will look something like this:
OPEN SYMMETRIC KEY [MyKey] DECRYPTION BY CERTIFICATE [MyCert] WITH PASSWORD = 'abc123';
Nothing wrong there.
Now assume we select a password of [';SELECT 'SQL INJECTION';--] (excluding the square brackets []), your SQL statement will look as follows:
OPEN SYMMETRIC KEY [MyKey] DECRYPTION BY CERTIFICATE [MyCert] WITH PASSWORD = '';SELECT 'SQL INJECTION';--';
This does not open the SYMMETRIC KEY, but instead executes a SELECT statement that returns the value 'SQL INJECTION'.
Now imagine a somewhat worse choice of password such as [';DROP DATABASE pubs;'] (excluding the square brackets []).
Given that the primary use-case for specifying a password on OPEN ... KEY is to use a secret that is not stored in the database, for example to prevent dbo or sysadmin users from getting access to the data, this string would typically be executed from your application code. Since the statement does not accept parameters, you need to formulate this as an inline-SQL string in your code. Therefore you need to enforce that you escape all single-quotes in the password in your code. For example:
OPEN SYMMETRIC KEY [MyKey] DECRYPTION BY CERTIFICATE [MyCert] WITH PASSWORD = ''';SELECT ''SQL INJECTION'';--';
Now the password is the literal text [';SELECT 'SQL INJECTION';--] (excluding square brackets []) and the injected SELECT statement is not executed.
I've requested a feature from Microsoft to allow parameters on all DDL SQL statements that require passwords. Microsoft is currently considering adding this feature to improve security.
To vote on this feature request, please visit: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=369270
I was busy debugging an exception class that I am writing and could not for the life of me figure out why all the private fields were getting overwritten with incorrect values. I suspected the debugger had something to do with it, seeing as it runs fine in release mode. So I wrote a little test application, which proves that the debugger causes a side effect in Exeception classes.
The debugger calls the Exception.Message property before the Exception class is fully constructed. Obviously, since Exception.Message is a virtual property, this results in a virtual call during construction and depending on the implementation of the overridden Message property, this can wreak havoc on the internal state of your class. I thought that perhaps the Debugger was calling ToString(), which was in turn calling Message. However, I overrode ToString and hard-coded the return. This proves that the debugger explicitly calls Exception.Message before construction of the Exception object instance is complete.
Below is a sample application that demonstrates this debugger side-effect. If you run the application in Release mode, or in Debug mode without stepping into the code (F5), the expected output is displayed. However, if you step into the code (F11) the output is incorrect and the state of the class is permanently damaged. The StackTrace shows that the getter of the Message property (get_Message) is called from the constructor (.ctor), even though there is no code that does this.
This is completely evil! I can only wonder what other methods and properties are explicitly and arbitrarily called during a debug session without you knowing it. There could be serious consequences/side-effects to arbitrarily calling methods and properties at the inappropriate times during the execution of an application. What is worse is there is no documentation that says that Exception.Message will be called by the debugger before construction of the Exception object instance is complete.
Expected Output:
Error=1
Message=1
Trace= at TestExceptionMessageDebugSideEffect.TestException.get_Message()
at TestExceptionMessageDebugSideEffect.Program.Main()
at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
Output when stepping through (F11) code with Visual Studio 2008 Debugger:
Error=1
Message=0
Trace= at TestExceptionMessageDebugSideEffect.TestException.get_Message()
at TestExceptionMessageDebugSideEffect.TestException..ctor(Int32 error)
at TestExceptionMessageDebugSideEffect.Program.Main()
at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
Code:
using System;
using System.Diagnostics;
namespace TestExceptionMessageDebugSideEffect
{
public class TestException : Exception
{
private readonly object traceLock = new object();
private StackTrace traceCallStack;
private readonly int error;
private string message;
public TestException(int error)
{
this.error = error;
}
public string Trace
{
get
{
return traceCallStack == null ? string.Empty : traceCallStack.ToString();
}
}
public int Error
{
get { return error; }
}
public override string Message
{
get
{
if (message == null)
{
lock (traceLock)
{
if (message == null)
{
traceCallStack = new StackTrace();
message = error.ToString();
}
}
}
return message;
}
}
public override string ToString()
{
return "TestException.ToString()";
}
}
class Program
{
static void Main()
{
var ex = new TestException(1);
Console.WriteLine("Error={0}", ex.Error);
Console.WriteLine("Message={0}", ex.Message);
Console.WriteLine("Trace={0}", ex.Trace);
}
}
}
There is a lot of confusing documentation, opinion and debate when it comes to database terminology, database design dos and don'ts and best practices. This is especially prevalent when it comes to the subtle differences between seemingly similar relational database "key" terminology/constructs, such as candidate keys, super keys, surrogate keys, natural keys, primary keys, alternate keys, unique keys, unique constraints, unique indexes and foreign keys.
I will attempt to uncover some of the subtle, but very pertinent, differences between these confusing concepts and terminology.
Candidate, Primary, Alternate and Unique Keys
Very simply, any combination of one or more attributes (columns), whose combined value is unique among all tuples (rows) is a candidate key (CK) on a relation (table). That is, any unique column or unique combination of columns is a candidate key on a table.
The primary key (PK) of any table is any candidate key of that table which the database designer arbitrarily designates as "primary". The primary key may be selected for convenience, comprehension, performance, or any other reasons. It is entirely proper (albeit often inconvenient) to change the selection of primary key to another candidate key.
The alternate keys (AK) of any table are simply those candidate keys which are not currently selected as the primary key. Exactly one of those candidate keys is chosen as the primary key and the remainder, if any, are then called alternate keys. An alternate key is a function of all candidate keys minus the primary key.
There can be many candidate keys on a table. The terminology "candidate key" implies that one of these keys can be selected as the primary key -- there can be only one primary key on a table (more about primary keys below). That is, all of the candidate keys are potential primary keys, hence the name "candidate key". It is theoretically possible that a table has no candidate keys and therefore no primary key. This is a hot topic of debate, since many relational database purists insist that all tables must have a primary key, and therefore must have at least one candidate key.
Many data modelling products do not adequately represent the concept of a candidate key. This is unfortunate as much metadata in the model definition is lost as a result.
For example:
Lets say there is an Employee table, a Skill table and an EmployeeSkill association table associating skills with employees:
EmployeeSkill
In the table above, the primary key (PK) is across both the EmployeeId and the SkillId columns.
Lets say that later on we decide to add a database generated, numeric integer, surrogate key column called EmployeeSkillId and designate it as the new primary key:
EmployeeSkill
| PK | EmployeeSkillId |
| | EmployeeId |
| | SkillId |
By adding the EmployeeSkillId column and changing the primary key to it, the model has lost the notion that EmployeeId and SkillId together form a candidate key (alternate key) and are therefore unique. That is, their uniqueness is no longer automatically enforced by the model.
In the database model, the candidate keys (CK) should first be represented and then one of those candidate keys marked as the primary key, thus making the remaining candidate keys alternate keys (AK) as follows:
| CK1 | EmployeeSkillId |
| CK2 | EmployeeId |
| CK2 | SkillId |
The model metadata would now be as follows:
| PK (CK1) | EmployeeSkillId |
| AK1 (CK2) | EmployeeId |
| AK1 (CK2) | SkillId |
This ensures that the EmployeeId/SkillId composite, alternate key still enforces uniqueness in the model. If we change the primary key to the second candidate key, there is sufficient metadata to preserve the notion of a unique EmployeeSkillId alternate key.
| AK (CK1) | EmployeeSkillId |
| PK (CK2) | EmployeeId |
| PK (CK2) | SkillId |
A characteristic of a candidate key is that it should never allows NULLs. This is important, because a candidate key is a potential primary key, and a primary key must never be NULL because it is used to identify a row. Likewise an alternate key must never allow NULLs for the same reason.
A candidate key must be unique. That is, a candidate key identifies a row uniquely, and therefore so does a primary key and an alternate key.
A very important, but often overlooked, notion of a candidate key, and therefore a primary key and an alternate key as well, is the notion of immutability.
A candidate key must always be immutable!
There is a distinct difference between truly, uniquely identifying a row and simply distinguishing a row from other rows. For instance a company has a name and a registration number. Both are unique and "identifying". But, the name is not immutable, since a company can change its name, while the registration number is immutable as the registration number never changes. Thus, although the company name can be used as a unique key to identify the company at a point in time, it cannot TRUELY identify the company through its entire lifetime -- as the name can change over time, and thus is not a candidate key (or a primary or alternate key for that matter).
This is an important and probably the most significant distinction between a candidate key (and therefore a primary and alternate key as well) and a unique key.
By enforcing that all candidate keys (primary and alternate keys) are immutable, unique and not nullable, they are truly identifying. This is an important characteristic of a candidate key, since a primary key and/or alternate keys can be used to persist references to an entity outside of the database model -- perhaps in another database. For this reason, primary and alternate keys should never be updated. Even if you allow cascaded updates within your data model, you cannot know who is holding a reference to your primary or alternate keys outside of your data model.
If you did allow updating of a primary or alternate key, it would imply that you had some knowledge outside of the data model, that the row before the update and after the update represents the same entity. This implies that your data model is wrong, because this knowledge is not represented in the data model. In fact there would be no distinction between an update and a delete followed by an insert.
In summary, a candidate key must have the following properties:
1. Must NOT be NULL. You must not have the notion of a missing identity for an entity.
2. Must be unique.
3. Must be immutable.
One candidate key must be selected as the primary key -- all other candidate keys become the alternate keys, which adhere to the same rules as a candidate key.
A unique key, that is not a candidate key (primary key or alternate key), is mutable.
Unique Index versus Unique Constraint
To define a unique key (not a primary key or an alternate key), SQL Server offers two implementations, a unique index and a unique constraint. Although these are both implemented in SQL Server as unique indexes under the hood, there are important differences between the two.
I have seen many posts touting that unique constraints and unique indexes are essentially the same. Many posts even highlight the implementation differences between the two concepts, such as FILLFACTOR that can be specified on a unique index, but not on a unique constraint. However, none of the posts I have read, have ever highlighted the most important and significant difference between these two concepts.
Conceptually, a unique constraint is a schema enforcing construct. That is, it enforces uniqueness on the column or columns to which it applies. A unique index implies something completely different from a modelling point of view. An index implies something added in addition to the schema definition, to improve performance.
Conceptually a constraint is a real schema modelling construct, which enforces integrity in the model, while an index is created to improve query performance. Thus I might decide to drop a unique index and create another index, while performance tuning, and inadvertently break the constraints of the data model schema.
If something is truly unique with respect to the conceptual/logical data model, then it must be defined as a unique constraint to adequately represent the notion of the constraint. However, if you define an index across one or more columns to improve performance, and that index happens to include a column or columns, which make it unique, then you should define this as a unique index, in addition to the unique constraint on the unique columns to preserve the conceptual/logical data model.
For example:
Employee
If IdNumber in the Employee table above is unique, it should be defined as a unique constraint in the data model.
If you often query on FirstName and return the IdNumber, you may choose to create an index, to improve performance, on FirstName and IdNumber. This index is unique because it included the IdNumber column which is unique and should be created as a unique index in addition to the unique constraint.
I often run into small, annoying little problems as I'm sure most people working in IT do at some point, so I decided to start blogging the solutions. No matter how insignificant they may seem, they may save someone an hour or two of Google'ing for the answer.
How to: Reindex an entire database on SQL Server 2000 or SQL Server 2005 in a single statement:
EXEC [sp_MSforeachtable] @command1="DBCC DBREINDEX('?')"
If you want to receive progress messages while it is running, try this version:
EXEC [sp_MSforeachtable] @command1="RAISERROR('DBCC DBREINDEX(''?'') ...',10,1) WITH NOWAIT DBCC DBREINDEX('?')"
More Posts
Next page »