views:

28

answers:

2

I'm trying to write a Stored Procedure which'll get a string, hash it with SHA1 and then return the hash. I can't seem to make it return @hashedString. I'll admit I'm a total beginner with T-SQL.

I'm writing the T-SQL directly in the db.

This is what I've gotten up to now:

ALTER PROCEDURE dbo.ConvertToHash

 (
 @stringToHash nvarchar(256),
 @hashedString nvarchar(256) OUTPUT
 )

AS
 DECLARE @HashThis nvarchar(256);
 SELECT @HashThis = CONVERT(nvarchar(256), @stringToHash);
 SELECT @hashedString = HashBytes('SHA1', @HashThis);

+3  A: 

HashBytes returns a VARBINARY result, ie. a byte array. I strongly suggest you keep this type, don't convert it to a string. If you have to convert it, use Base64 encoding.

Right now your procedure casts the hash to an Unicode string, but this is incorrect because not all byte combinations are valid in Unicode and the hash may well hit on invalid Unicode characters.

ALTER PROCEDURE dbo.ConvertToHash
 (
 @stringToHash nvarchar(256),
 @hashedString binary(20) OUTPUT
 )
AS
 SET @hashedString = HashBytes('SHA1', @stringToHash);

Make sure you preserve the hashed value as BINARY(20) everywhere in your code, and use it as byte[] in your client. Never convert the hash to a string (this applies to every language and platform out there).

Remus Rusanu
Thanks for the heads up. I'll remember this.
Phil
This stored procedure that you provided doesn't return a value, at least not when I run it from the editor. Am I supposed to append anything?
Phil
Stored procedure don't 'return' values. They use `OUTPUT` parameters, so you need to provide an output parameter: `set @hash binary(20); exec dbo.ConvertToHash('somestring', @hash OUTPUT); select @hash;`
Remus Rusanu
+1  A: 

Your final select statement is assigning the value rather than returning it. You can either issue another select statement on the relevant variable (modified code included below for your reference) or you can incorporate OUTPUT parameters into your Stored Procedure design, as suggested by Remus.

ALTER PROCEDURE dbo.ConvertToHash

 (
 @stringToHash nvarchar(256),
 @hashedString nvarchar(256) OUTPUT
 )

AS
 DECLARE @HashThis nvarchar(256);
 SELECT @HashThis = CONVERT(nvarchar(256), @stringToHash);
 SELECT @hashedString = HashBytes('SHA1', @HashThis);
 SELECT @hashedString 
John Sansom