Security - Pro SQL Database for Windows Azure - page 52

51. sqlConn.Close();
52.
53. }
54. }
55.
56. }
As promised, following is the code for the stored procedure. You create a stored procedure because it allows you
to provide additional security from an access-control standpoint. As you see later, you create a schema that contains
the tables and a separate schema for the stored procedures that access the tables. This provides greater control over
your database security. You'll review schemas later in this chapter.
IF (Exists(SELECT * FROM sys.sysobjects WHERE Name = 'proc_SaveProperty' AND Type = 'P'))
DROP PROC proc_SaveProperty
GO
-- SELECT * FROM UserProperties
CREATE PROC proc_SaveProperty
@name nvarchar(255),
@value varbinary(max),
@vector binary(16),
@lastUpdated datetime,
@hash binary(32)
AS
IF (Exists(SELECT * FROM UserProperties WHERE PropertyName = @name))
BEGIN
UPDATE UserProperties SET
Value = @value,
Vector = @vector,
LastUpdated = @lastUpdated,
Token = @hash
WHERE
PropertyName = @name
END
ELSE
BEGIN
INSERT INTO UserProperties
(PropertyName, Value, Vector, LastUpdated, Token)
VALUES (
@name,
@value,
@vector,
@lastUpdated,
@hash )
END
This stored procedure performs both updates and inserts depending on the property name. Note the use of
varbinary(max) ; because you don't know how long the encrypted value will be, you allow large but variable binary
objects to be stored. However, the vector is always 16 bytes in length and the hash 32.