Sign a SQL Stored Procedure to track changes.

Several times I've run into situations where I need to deploy a SQL stored procedure to clients that maybe complex and critical to correct functioning of the database.

Things go along fine and then I get a call/Email after about 6 months with a bug or performance issue. After a little detective work I think I detect that a critical procedure as been changed either by the client or consultant working on their site. Verifying that the stored procedure is the orginal unaltered version I placed on the server can lead to some long conversations.

So I came up with a solution that allows me to "sign" or finger print any SP after I've installed it. The technique uses SQL 2005 and above ability to hash data with the HashBytes function. The hash contains date information, object definition, object IDs, and a "secret" word.

The secret word allows you to publish the signing procedures without fear that the end-user will fake a signature. At least as long as you don't tell them the secret word.

The resulting binary hash signature is stored in the extended properties of the procedure and cannot be modified using SSMS.

I've found this very helpful with some of my clients that love to tinker with critical procs.

Dave Winters
Terminus
--------------------------------------------------------------------------
--
--
if object_id('USP_Sign_Object') is not NULL
drop procedure USP_Sign_Object
GO
--------------------------------------------------------------
create procedure USP_Sign_Object
@objectName sysname
,@secretWord nvarchar(20)='My Software'
as
--
-- This procedure builds a hash of the contents of a stored procedure for "finger printing"
-- and detecting changes. The hash/signature is stored in the extended properties of the object.
-- Here are some implementatio details:
-- A "secret" word should be supplied to prevent spoofing.
-- The has includes the object ID, create date, modify date, and first 4000 bytes of the DDL
-- To resign the procedure, the "SIGNED" property must be dropped via SSMS
--
-- Dave Winters, January 2010
-- TheSeldonVault.blogspot.com
--
SET NOCOUNT ON
Begin --Top

declare @objid nvarchar(20),
@crdate nvarchar(20),
@modate nvarchar(20),
@signHash sql_variant

select @objid=cast(object_id as nvarchar(20))
,@crdate=cast(create_date as nvarchar(20))
,@modate=cast(modify_date as nvarchar(20)) from sys.objects where [name]=@objectName

if @@rowcount = 0 return -1

SELECT @signHash = cast( HashBytes(
'SHA1',isnull(substring(
@secretWord+@objid+@crdate+@modate
+OBJECT_DEFINITION(OBJECT_ID (@objectName))
,1,4000 ),'EnCrYpTeD' )
) as sql_variant)

EXEC sys.sp_addextendedproperty @name=N'SIGNED'
,@value=@signHash
,@level0type=N'SCHEMA'
,@level0name=N'dbo'
,@level1type=N'PROCEDURE'
,@level1name=@objectName

End --Bottom
GO
--
--exec USP_Sign_Object 'asp_benefit_warnings','MySeCrEtE'
--
------------------------------------------------------------------------
if object_id('USP_Validate_Object') is not NULL
drop procedure USP_Validate_Object
GO
--
create procedure USP_Validate_Object
@objectName sysname
,@secretWord nvarchar(20)='My Software'
as
SET NOCOUNT ON
Begin --Top

declare @objid nvarchar(20),
@crdate nvarchar(20),
@modate nvarchar(20),
@signHash sql_variant,
@currHash sql_variant

select @objid=cast(object_id as nvarchar(20))
,@crdate=cast(create_date as nvarchar(20))
,@modate=cast(modify_date as nvarchar(20)) from sys.objects where [name]=@objectName

if @@rowcount = 0 return -1

SELECT @signHash = cast( HashBytes('SHA1',isnull(substring(
@secretWord+@objid+@crdate+@modate
+OBJECT_DEFINITION(OBJECT_ID (@objectName))
,1,4000 ),'EnCrYpTeD' )
)
as sql_variant)
--
SELECT @currHash = p.value
FROM sys.all_objects AS sp
INNER JOIN sys.extended_properties AS p ON p.major_id=sp.object_id AND p.minor_id=0 AND p.class=1
where sp.name = @objectName--N'asp_benefit_warnings'
and p.name = 'SIGNED'

--select @currHash as CurrHash, @signHash as CompHash

if @currHash <> @signHash
begin
print '!!!Signature Invalid!!!'
return -1
end

Print '---Signature Valid---'
return 0

End --Bottom

--
--
--exec USP_Validate_Object 'asp_benefit_warnings','MySeCrEtE'
--
--

California accepts Open Source

A policy shift occurred January 7th with the California IT czar.

The CIO has issued a new policy directive that may make it easier for open source platform to compete for the scarce contract dollars available for IT projects.

Oracle and Microsoft dominating the California platform purchase pipeline may still be the norm. My hope is that with this new policy there is some opening of opportunity for good open source offerings and consulting.

Here is the link to the official PDF policy letter:
http://bit.ly/6XTifr

Simple XML query and looping

Working with a client's SQL 2005 stored procedure this week I came across a very complicated series of T-SQL. The jest of the procedure is to loop through a series of rows inside of an XML variable passed into the SP. The implmentation was built using OPENXML and Select statements with patterns. I spoke with the maintainers of the SP and it became clear that the functionality was overly complex and that using XML XQuery and XPath was a new skill set.

So I devised this simple XML sniplet that will run and demonstrates some of the basics available when dealing with XML data. This should click with most first time XML database folks accustomed to working with T-SQL. They can then explore the rich feature set available.

Dave W
--
-- Simple example of using XML query.
-- Build to shold some basics of "looping" through a set of XML "rows"
-- Dave Winters 6 Jan 2010
--
declare @x xml
set @x='
<grills franchise="1" regionname="West">
<location locationid="Sacramento">
<listing>MenuItem Pizza </listing>
<listing>MenuItem Salad </listing>
<listing>MenuItem Sushi </listing>
</location>
<location locationid="Auburn">
<listing>MenuItem french fries </listing>
<listing>MenuItem hamburgres </listing>
<listing>MenuItem coke </listing>
</location>
</grills>'
--
-- Get a specific location menu
--
SELECT @x.query('
for $listItem in /grills/location
where $listItem/@locationid="Auburn"
return $listItem
') as Result
--
-- XML fragments for the location ID attribute, not proper form.
-- To make it regular you'll need to put inside one root element
--
SELECT @x.query('
for $listItem in /grills/location
return <t>{$listItem/@locationid}</t>
')
--
-- Chunking some locations blobs
--
SELECT T.c.query('(.)[1]') as LocXML
FROM @x.nodes('/grills/location') T(c)
--
-- Build the master menu list
--
SELECT T.c.value('(./text())[1]', 'varchar(256)') as Master_Menu_List
FROM @x.nodes('/grills/location/listing') T(c)
--
-- XML is much more prowerful than this simple example.
-- This was to show how you can go through an XML object in a fashion similar to
-- processing records in SQL tables.
--

NULL in SQL databases!!!!!!! What a pain!

I am very passionate about the whole issue surrounding NULL in databases. It is a simple yet exceedingly difficult item.

Let me pass along some ideas and one scenario that I use as guides that has help keep me out of trouble.

1) NULL is NEVER a value! It is the absence of value! Or as EF Codd originally stated: “missing information and inapplicable information”
See: http://en.wikipedia.org/wiki/Null_(SQL)
2) NULL should NEVER EVER be used as a flag in decision logic. Testing for NULL is fine with a narrow constraint that we are checking for NULL state.
3) Setting anything to NULL is not the same as “deleting information.”

Here is a scenario I use to illustrate a simple practical application of these ideas. Assume we have a simple order entry SQL application that has a shipping address form coupled to an address table. Something like:
Create table ShipAddr( CustName varchar(40) null, Street1 varchar(30) null, City varchar(30) null, State varchar(20) null, ZipCode varchar(9) null)

Here is the scenario:

1- Customer orders a product and customer service calls up the shipping address form to fill out the information.
2- The customer supplies all of the information but doesn’t have their Zip code. This field is not updated and the form is saved. The ZipCode field has the original “state” NULL.
3- The next day the customer calls back and supplies the value “35123” as their Zip code. The form is updated and saved. The ZipCode field now has the value “35123”
4- Order fulfillment department runs a check and determines that “35123” is an invalid Zip. The ship address form is updated by deleting all of the values in this field. The form is saved.
IMPORTANT NOTE: Now the ZipCode field has the value “”, or in other words a zero length string!!! Not NULL!

Why not just set it back to NULL? The most important reason is that doing this completely loses the “transitional state” of the field in this record! If you look at classic 3VL and tuple calculus set operations it negates the ability to segregate subsets of the data based on if the value in the field has ever been “touched.” If the field is set back to NULL as a “value” the segregation of records that have never had an address value is lost. How do we also determine that a field in the general sense may actually have a correct value of “”?

Ok at this point you are probably asking yourself … who cares?
Having seen this problem repeated over and over in many SQL implementations I would suggest that this issue is important for several reasons:
1)Maintainability, confusion around NULLs is costly
2) Accuracy, loss of state or ability to validate because of NULL “values” can be expensive
3) Interoperability, NULLs used as values in one table or application may not have the same meaning in another application.

Regards,

Dave W