Programming4us
         
 
 
SQL Server

SQL Azure : Securing Your Data (part 2) - Hashing

12/22/2010 5:37:04 PM

4.2.2. Hashing

Hashing isn't nearly as complicated as you've seen so far. And although you can store the values you've encrypted so far in the database, in this example you hash all the columns of the rows (except the ID value) to make sure they're unchanged. Why? The answer goes back to the integrity concern of the CIA triad discussed earlier. You want a way to tell whether your data has been modified outside of your code. Encrypting your secret value makes it virtually impossible to break the confidentiality aspect of the triad, but someone can still update the PropertyName column—or, worse, the Value column. Hashing doesn't prevent data from being modified, but you have a way to detect whether it was changed without your authorization.

To simplify the code, start by creating a couple of extension methods. Extension methods are a handy way to extend the methods available to a class (or data type) even if you don't have the original source code. Here you can see how to declare an extension method on the string and DateTime data types:

1.  public static class Extensions
2. {
3. public static byte[] GetBytes(this string value)
4. {
5. byte[] buffer = UTF8Encoding.UTF8.GetBytes(value);
6. return buffer;
7. }
8.
9. public static byte[] GetBytes(this DateTime value)
10. {
11. return value.ToString().GetBytes();
12. }
13. }

This code adds a GetBytes() method to the string and DateTime data types. You also create a utility class that allows you to create a hash value based on a collection of byte arrays. The following code shows that class:

1.  public class Util

2.  {
3. /// <summary>
4. /// Computes a hash value based on an array of byte arrays
5. /// </summary>
6. /// <param name="bytes">Array of byte arrays</param>
7. public static byte[] ComputeHash(params byte[][] bytes)
8. {
9. SHA256 sha = SHA256Managed.Create();
10. MemoryStream ms = new MemoryStream();
11.
12. for (int i = 0; i < bytes.Length; i++)
13. ms.Write(bytes[i], 0, bytes[i].Length);
14.
15. ms.Flush();
16. ms.Position = 0;
17.
18. return sha.ComputeHash(ms);
19. }
20. }

This Util class is very handy shortly. Note on line 7 the declaration of the variable as params byte[][]; this means each parameter passed to this method must be a byte array. You declare a memory stream, loop on each byte-array variable, and append it to the memory stream on line 13. Finally, you return the computed hash of the memory stream on line 18. You see how to call this method shortly.

The UserProperties class is next, in the following example, and makes the actual call to the SQL Azure database. It takes two input parameters: the property name to save and its encrypted value stored in the CipherText structure. On line 13, you retrieve the connection string from another class and open the database connection on line 15. You then create the command object, specifying a call to a stored procedure. The code for the stored procedure is provided later. The hash value is then created on line 39; as you can see, you call the ComputeHash method just reviewed by passing each stored procedure parameter as a byte array. This is where you use both the extension methods created earlier and the hashing method. After the hash result is calculated, you pass it into the last stored procedure parameter on line 45:

1.   using System.Data.SqlDbType;
2. public class UserProperties
3. {
4.
5. /// <summary>
6. /// Saves a property value in a SQL Azure database
7. /// </summary>
8. /// <param name="propertyName">The property name</param>
9. /// <param name="ct">The CipherText structure to save</param>
10. public static void Save(string propertyName, CipherText ct)
11. {
12. using (SqlConnection sqlConn =
13. new SqlConnection(CDatabase.ConnectionString))
14. {
15. sqlConn.Open();
16.
17. using (SqlCommand sqlCmd = new SqlCommand())
18. {
19.


20.                 DateTime dateUpdated = DateTime.Now;
21.
22. sqlCmd.Connection = sqlConn;
23. sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
24. sqlCmd.CommandText = "proc_SaveProperty";
25. sqlCmd.Parameters.Add("name", NVarChar, 255);
26. sqlCmd.Parameters.Add("value", VarBinary, int.MaxValue);
27. sqlCmd.Parameters.Add("vector", VarBinary, 16);
28. sqlCmd.Parameters.Add("lastUpdated", DateTime);
29. sqlCmd.Parameters.Add("hash", VarBinary, 32);
30. sqlCmd.Parameters[0].Value = propertyName;
31. sqlCmd.Parameters[1].Value = ct.cipher;
32. sqlCmd.Parameters[2].Value = ct.vector;
33. sqlCmd.Parameters[3].Value = dateUpdated;
34.
35. // Calculate the hash of this record...
36. // We pass the list of values that should be hashed
37. // If any of these values changes in the database,
38. // recalculating the hash would yield a different result
39. byte[] hash = Util.ComputeHash(
40. propertyName.GetBytes(),
41. ct.cipher,
42. ct.vector,
43. dateUpdated.GetBytes());
44.
45. sqlCmd.Parameters[4].Value = hash;
46.
47. int res = sqlCmd.ExecuteNonQuery();
48.
49. }
50.
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. 
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.

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 2 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.

Figure 2. Record with the encrypted value, a hash, and a vector

Last but not least, you should know that SQL Server and SQL Azure both support hashing natively. Unfortunately, support for hashing in both database platforms is limited to the MD5 and SHA-1 algorithms. The hashing method used in the C# code shown previously uses SHA-256 as its algorithm, which is much stronger. Here is a quick example of how to compute an SHA-1 hash in SQL:

SELECT HASHBYTES('sha1', 'MySecret')

The output of HASHBYTES() is a byte array as well:

0xEABBEC6F31804EB968E2FAEAAEF150546A595FC3

So far, you've seen a way to encrypt sensitive information for confidentiality, hashed certain columns of a record for increased integrity, and deployed in Azure for strong availability. As you can see, developing encryption and hashing routines can be very complex and requires a strong command of the programming language. You may find it beneficial to create a generic encryption library, like the one shown in the previous examples, that can be reused across projects.

Other -----------------
- SQL Azure : Security - Overview
- Setting Up a Full-Text Index (part 4) - Using the Full-Text Indexing Wizard to Build Full-Text Indexes and Catalogs
- Setting Up a Full-Text Index (part 3) - Diagnostics
- Setting Up a Full-Text Index (part 2) - Full-Text Indexing of BLOBs and XML
- Setting Up a Full-Text Index (part 1) - Using T-SQL Commands to Build Full-Text Indexes and Catalogs
- Implementing SQL Server 2008 Full-Text Catalogs
- How SQL Server FTS Works
- SQL Azure : Connecting to a SQL Azure Database (part 2) - Connecting from the Entity Framework
- SQL Azure : Connecting to a SQL Azure Database (part 1) - Connecting Using ADO.NET
- SQL Azure : Creating Databases, Logins, and Users (part 2)
- SQL Azure : Creating Databases, Logins, and Users (part 1)
- SQL Azure : Azure Server Administration (part 3) - Databases
- SQL Azure : Azure Server Administration (part 2) - Firewall Settings
- SQL Azure : Azure Server Administration (part 1) - Server Information
- SQL Azure : Managing Your Azure Projects
- SQL Azure : Creating Your Azure Account
- An OLAP Requirements Example: CompSales International (part 16) - Security and Roles
- An OLAP Requirements Example: CompSales International (part 15) - SSIS
- An OLAP Requirements Example: CompSales International (part 14) - Data Mining
- An OLAP Requirements Example: CompSales International (part 13) - Cube Perspectives
 
 
Most View
- Sharepoint 2010 : Creating a .NET Connector in Visual Studio 2010 (part 1)
- Exchange Server 2010 Mailbox Services Configuration (part 4) - Client Configuration
- Windows Firewall with Advanced Security in Windows Server 2008 (part 3)
- Configuring Exchange Server 2003 to Coexist with X.400-Compliant Messaging Systems
- Troubleshooting Windows Home Server 2011 : Troubleshooting Device Problems
- Windows Azure : Programming Access Control Service (part 9) - Configuring a Web Service Client to Acquire and Send SAML Tokens
- Deploying ASP.NET 4 Applications with Visual Studio (part 2) - Copying a Website and Publishing a Website
- Windows Small Business Server 2011 : Deploying Network Printers (part 1) - Creating a Printer - Installing a Local Printer Manually
- Microsoft Dynamics AX 2009 : Working with Forms - Storing last form values
- Windows Phone 7 : Synching Notes to the Web
Top 10
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 3) - Configuring Recipient Filtering
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 2)
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 1)
- Implementing Edge Services for an Exchange Server 2007 Environment : Installing and Configuring the Edge Transport Server Components
- What's New in SharePoint 2013 (part 7) - BCS
- What's New in SharePoint 2013 (part 6) - SEARCH
- What's New in SharePoint 2013 (part 6) - WEB CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 5) - ENTERPRISE CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 4) - WORKFLOWS
- What's New in SharePoint 2013 (part 3) - REMOTE EVENTS