Security - Pro SQL Azure - page 73

@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.
Running the Save() method on the UserProperties class creates a record in the UserProperties
table. The following code shows how to call the Save method:
1. class Program
2. {
3. static void Main(string[] args)
4. {
5. // Declare the encryption object and encrypt our secret value
6. Encryption e = new Encryption();
7. CipherText ct = e.EncryptAES("secret value goes here...");
8.
9. UserProperties.Save("MySecret", ct);
10.
11. }
12. }
Figure 4-3 shows the content of the table. The Value column is your encrypted value, the Vector is
the @vector variable from the stored procedure, and the Token column is the calculated hash passed as
the @hash variable.