cjhaas.com Basically a place that Chris can post solutions to problems so he can easily find them later

November 6, 2010

Generate INSERT statements from SQL Server

Filed under: Uncategorized — Tags: , — Chris Haas @ 5:23 pm

The original version of this code comes from here. That version, however, didn’t handle NULLs and just inserted empty strings or zeros. In the comments there was an updated version by spider_pat that supported NULL values but had a couple of problems, namely it only had one column to INSERT into (due to placing code inside of a loop instead of outside) and it didn’t enclose columns in brackets. The first was really easy to fix and the second most people probably don’t care about unless you have some system that automatically creates table names and you can’t rename them. Also, I took a little bit of time to clean up the code to make it more readable. Yes, I removed BEGIN/END blocks from single-statement IFs which I generally recommend against but this made it much easier to read.

CREATE PROC InsertGenerator
(
@tableName varchar(100)
)
AS
/*
Code from  http://www.codeproject.com/KB/database/InsertGeneratorPack.aspx
*/
--Declare a  cursor to retrieve column specific information for the specified table
DECLARE cursCol CURSOR FAST_FORWARD FOR
SELECT column_name,data_type,table_schema FROM information_schema.columns WHERE table_name = @tableName
OPEN cursCol
DECLARE @string         nvarchar(max) --for storing the first  half of INSERT statement
DECLARE @stringData     nvarchar(max) --for storing the data  (VALUES) related statement
DECLARE @dataType nvarchar(max) --data types returned for  respective columns
DECLARE @schemaType     nvarchar(max) --table schema value for  correct schema execution
SET @stringData=''
DECLARE @colName nvarchar(max)
FETCH NEXT FROM cursCol INTO @colName, @dataType, @schemaType
IF @@fetch_status<>0
BEGIN
      PRINT 'Table  '+@tableName+' not found, processing skipped.'
      CLOSE curscol
      DEALLOCATE curscol
      RETURN
END
SET @string='INSERT ['+@schemaType+'].['+@tableName+']('
WHILE @@FETCH_STATUS=0
BEGIN
      IF @dataType in ('varchar','char','nchar','nvarchar')
            SET @stringData = @stringData + ''''' + ISNULL('''''''' + REPLACE([' + @colName + '],'''''''','''''''''''') + '''''''', ''NULL'') + '',''+'
      ELSE IF @dataType in ('text','ntext')
            SET @stringData = @stringData + '''''''''+ISNULL(cast([' + @colName + '] as  varChar(max)),'''')+'''''',''+'
      ELSE IF @dataType = 'money'
            SET @stringData = @stringData+'''CONVERT(money,''''''+ISNULL(CAST([' + @colName + '] as varChar(max)),''0.0000'')+''''''),''+'
      ELSE IF @dataType='datetime'
            SET @stringData = @stringData + '''CONVERT(datetime,' + '''+ISNULL(''''' + '''''+CONVERT(varChar(max),[' + @colName + '],121)+''''' + ''''',''NULL'')+'',121),''+'
      ELSE IF @dataType='image'
            SET @stringData = @stringData + '''''''''+ISNULL(CAST(CONVERT(varBinary,[' + @colName + ']) as varChar(6)),''0'')+'''''',''+'
      ELSE
            SET @stringData = @stringData + '''' + '''+ISNULL(''''' + '''''+CONVERT(varChar(max),[' + @colName + '])+''''' + ''''',''NULL'')+'',''+'
      SET @string=@string + '[' + REPLACE(@colName,'''','''''') + '],'
      FETCH NEXT FROM cursCol INTO @colName,@dataType,@schemaType
END
DECLARE @Query nvarchar(4000)
SET @query ='SELECT '''+substring(@string,0,len(@string)) + ') VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')'' FROM '+@schemaType+'.'+@tableName
PRINT @query
EXEC sp_executesql @query
--select @query
CLOSE cursCol
DEALLOCATE cursCol
GO

UPDATE 12/21/2010

Fixed a bug where content with single quotes was breaking.

September 7, 2010

SQL Server Encryption Overview

Filed under: Uncategorized — Tags: , , — Chris Haas @ 11:14 am

I was doing some work that required that some parts of the database were encrypted and couldn’t find an all-in-one location for information. If that’s what you’re looking for, this ain’t it either. I will, however, briefly explain how to create symmetric and asymmetric keys that use passwords.

First off, symmetric keys. Symmetric keys are faster than asymmetric keys by a long shot but are (arguably) less secure since they use the same password to encrypt/decrypt things. To create a symmetric key by password you do not need a database master key, they are for asymmetric keys and certificates only. When you create the key you can choose between 10 different encryption algorithms, DES | TRIPLE_DES | TRIPLE_DES_3KEY | RC2 | RC4 | RC4_128 | DESX | AES_128 | AES_192 | AES_256. You can read more about those here. To create the key just simply use:

CREATE SYMMETRIC KEY YourKeynameHere WITH ALGORITHM = AES_256 ENCRYPTION BY PASSWORD = N'YourPasswordHere'

For a sample table we’ll use the following:

CREATE TABLE MyTable
(
Email varbinary(max)
)

To use the key to insert data, first open the key, then insert using the ENCRYPTBYKEY function and finally close the key:

OPEN SYMMETRIC KEY YourKeynameHere DECRYPTION BY PASSWORD = N'YourPasswordHere'
INSERT INTO MyTable (Email) VALUES (ENCRYPTBYKEY(KEY_GUID('YourKeynameHere'), 'test@example.com'))
CLOSE SYMMETRIC KEY YourkeynameHere

It’s very important that you open the key first. Failure to do so will not result in an error, NULL data will just be inserted instead.

To read the data you perform a similar pattern except you use the DECRYPTBYKEY function which already knows which key to decrypt with (but still needs to be opened first). Also remember that DECRYPTBYKEY returns binary data so its up to you to convert it properly.

OPEN SYMMETRIC KEY YourKeynameHere DECRYPTION BY PASSWORD = N'YourPasswordHere'
SELECT CONVERT(varchar(max), DECRYPTBYKEY(Email)) FROM MyTable
CLOSE SYMMETRIC KEY YourkeynameHere

Now, if you want to use a symmetric key with a stored procedure but you don’t want to store the password within the sproc you’ll run into an issue. DDL statements don’t allow variables as parameters. What this means is that if you try creating the following sproc you’ll receive the error “Incorrect syntax near '@EncPass'.”

CREATE PROC MyProc
(
@EncPass nvarchar(255)
)
AS
OPEN SYMMETRIC KEY YourKeynameHere DECRYPTION BY PASSWORD = @EncPass
SELECT CONVERT(varchar(max), DECRYPTBYKEY(Email)) FROM MyTable
CLOSE SYMMETRIC KEY YourKeynameHere

The only fix I’ve found is to use EXECUTE to run your open key for you. If you’re here, I assume (or hope) that you know the dangers of execute. So the above sproc becomes:

CREATE PROC MyProc
(
@EncPass nvarchar(255)
)
AS
EXEC ('OPEN SYMMETRIC KEY YourKeynameHere DECRYPTION BY PASSWORD = N''' + @EncPass + '''')
SELECT CONVERT(varchar(max), DECRYPTBYKEY(Email)) FROM MyTable
CLOSE SYMMETRIC KEY YourKeynameHere

More information:

Okay, I said I’d go into asymmetric keys here too but that will have to wait for another day.

August 6, 2010

SQL Server 2008 Express Asymmetric Encryption

Filed under: Uncategorized — Tags: , , — Chris Haas @ 2:54 pm

First off, if you’ve got SQL Server 2008 Enterprise Edition I’d recommend that you look at TDE. SQL Server 2008 Express Edition doesn’t support TDE so instead we need to find another way to encrypt our data. The method that I’m talking about here is asymmetric encryption using a password (instead of certificate). I’m just going to drop the code below any maybe elaborate at a later date.

UPDATED 2010-08-09
Found a slight problem, my original code worked great in the Management Studio but failed on the web with a Cannot insert NULL message. The problem was that the user that the user account listed in my web.config didn’t have permission to access the private key. The solution was simple, just GRANT that CONTROL permission on they key. The code below reflects this. I’m not 100% sure that there isn’t a permission with less privileges than CONTROL but it does work at least.

--Create a test database
CREATE DATABASE [Test]
GO

--Select the test database
USE [Test]
GO

--Create the database master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'really awesome password here'
GO

--Create a single asymmetric key, this password is one that we're going to be using later actually
CREATE ASYMMETRIC KEY KEY_SuperAwesomeKey WITH ALGORITHM = RSA_2048 ENCRYPTION BY PASSWORD = N'some other really awesome password'
GO

--Give the user account "YourUserAccountHere" permission to use the key
GRANT CONTROL ON ASYMMETRIC KEY::[KEY_SuperAwesomeKey] TO YourUserAccountHere

--Create a test table.
--Very important, the encryption and decryption functions max out at 8000 bytes
CREATE TABLE SampleTable
(
PlainText varChar(8000),
EncText varBinary(8000)
)
GO

DECLARE @PlainText varChar(8000)
DECLARE @EncText varBinary(8000)

--Plain text variable
SET @PlainText	= 'Hello world'
--EncryptByAsymKey(KeyId, PlainText), use AsymKey_Id(KeyName) to find the Id of the key
SET @EncText	= ENCRYPTBYASYMKEY(ASYMKEY_ID('KEY_SuperAwesomeKey'), @PlainText)

--Insert into our table
INSERT INTO SampleTable (PlainText, EncText) VALUES (@PlainText, @EncText)

--Show what the encrypted form looks like
SELECT * FROM SampleTable

--DecryptByAsymKey(KeyId, EncryptedText, AsymetricKeyPassword), returns byte array that needs to be re-cast to original type
--Also, very important, the password MUST be passed in as nvarchar
SELECT *, CONVERT(varchar, DECRYPTBYASYMKEY(ASYMKEY_ID('KEY_SuperAwesomeKey'), EncText, N'some other really awesome password')) FROM SampleTable

June 24, 2009

Make sure your SQL queries are using the proper index

Filed under: Uncategorized — Tags: , — Chris Haas @ 11:00 am

I’ve got a live table with 98 million rows that I’m normalizing. Unforuntately all full batch updates tend to lock the system for a very long so I wrote a VB.Net program to perform updates in smaller batches. The program pulls 10,000 records that haven’t been updated by using the standard SELECT TOP 10000… syntax. Unfortunately the WHERE portion of this query takes a really long time to run. I started digging into things and tried just SELECT TOP 1… and that returned results immediately. I then tried SELECT TOP 10… and that returned immediately, too. After playing around I found that SELECT TOP 63… was the most I could go before the query took a long time to execute. I have no idea what’s so special about 63 but it was bugging me. So I ran SELECT TOP 63… and SELECT TOP 64… side-by-side in SQL SMS with the execution plan turned on and found the 63 version was using an Index Seek on the index that was on the column in my WHERE clause. This is a good thing. The 64 version, however, was performing a Clustered Index Scan on the primary key which had nothing to do with my WHERE clause.

The fix for this is to force SQL to use the index of your choice and its so easy to do. In your query, before the WHERE clause just add WITH (INDEX(INDEX_NAME)).

So if this is your original query:

SELECT TOP 10000 Col1, Col2 FROM YourTable WHERE Col3 IS NULL

And you have an index on Col3 called IDX__YourTable__Col3 you’d execute this query instead:

SELECT TOP 10000 Col1, Col2 FROM YourTable WITH (INDEX(IDX__YourTable__Col3)) WHERE Col3 IS NULL

Normally you shouldn’t have to do this but if you’re doing one-off things like I’m doing it comes in handy.

–Update:

The forced index is running so fast that I’ve actually changed my batch size from 10,000 to 100,000. The prior 10,000 was processing about 400 records/sec, most of the time spent trying to get results from the SELECT TOP… but with the forced index and changing to 100,000 I’m now averaging 3,500 records/sec. Nice.

Powered by WordPress