Coding Grounds

Sunday, 14 January 2024

Generating repeated data into variable in SQL Server in T-SQL

Let's see how we can create repeated data into variable of SQL Server in T-SQL. Use the REPLICATE function to create repeated data like this:



DECLARE @myVariable NVARCHAR(MAX)
SET @myVariable = REPLICATE('.', 10)
PRINT @myVariable
PRINT len(@myVariable)

In case you want to set the variable to data which is longer than 8000 characters, you must convert the argument to NVARCHAR(MAX).



DECLARE @myVariable NVARCHAR(MAX)
SET @myVariable = REPLICATE(CONVERT(NVARCHAR(MAX),'.'), 1024*1024*2)
PRINT len(@myVariable)

Creating random content is also easy in T-SQL:


DECLARE @myVariable NVARCHAR(MAX)
SET @myVariable = REPLICATE(CONVERT(NVARCHAR(MAX),REPLACE(NEWID(),'-', '')), 4)
PRINT len(@myVariable)
PRINT @myVariable

NEWID() creates a new guid, and we strip away the '-' letter, giving 32 chars which we replicate above four times. Since we were below 8000 chars, we chould have skipped using convert to nvarchar(max).

Sunday, 31 December 2023

Password hashing in .NET

This article will look on different ways to hash a password in .NET. MD5 was developed by Ron Rivest in 1991 and was used a lot in the 90s, but in 2005 it was revealed it contains collisions. MD5 and SHA-1 is not advised to used in sensitive hashing related to security anymore. Instead, a PBKDF or Password Derived Key-derivation function algorithm will be used. A PBKDF2-based method in Rfc2898DeriveBytes will be used. It has been available since .NET 6. Users of Asp.net Core Identity are recommended to use PasswordHasher instead : https://andrewlock.net/exploring-the-asp-net-core-identity-passwordhasher/ An overview of the arithmetic flow of PBKDF2 is shown below. In the diagram, SHA-512 is indicated, but the code shown in this article
uses SHA-256.

First off, to do a MD5 hash we can use the following :

 
 
 static string Md5(string input){
	using (var md5 = MD5.Create()){
		var byteHash = md5.ComputeHash(Encoding.UTF8.GetBytes(input)); 
		var hash = BitConverter.ToString(byteHash).Replace("-", "");
		return hash;
	}
}

And to test it out we can run the following:

 
 
 void Md5Demo()
{
	string inputPassword = "abc123";
	string md5Hash = Md5(inputPassword);
	Console.WriteLine("MD5 Demonstration in .NET");
	Console.WriteLine("-------------------------");
	Console.WriteLine($"Password to hash: {inputPassword}");
	Console.WriteLine($"MD5 hashed password: {md5Hash}");
	Console.WriteLine();
}


  
MD5 Demonstration in .NET
-------------------------
Password to hash: abc123
MD5 hashed password: E99A18C428CB38D5F260853678922E03

The MD5 hash above agrees with the online MD5 hash here: https://www.md5hashgenerator.com/ MD5 method here does not mention any salt, but this could be concatenated with the password to prevent against rainbow table attacks, that is dictionary attacks. Next, to perform PDKDF2 hashing, the code below can be used. Note that this algorithm will be run iteratively to generate a hash value that is increasingly more computationally expensive to calculate the hash of compared to the number of iterations and includes a salt, making it scalable
to be more and more difficult for attacks.



static byte[] _salt = RandomNumberGenerator.GetBytes(32);

static void HashPassword(string passwordToHash, int numberOfRounds)
{
	var sw = Stopwatch.StartNew();
	var hashedPassword = Rfc2898DeriveBytes.Pbkdf2(
		passwordToHash,
		_salt,
		numberOfRounds,
		HashAlgorithmName.SHA256,
		32);
	sw.Stop();

	Console.WriteLine();
	Console.WriteLine("Password to hash : " + passwordToHash);
	Console.WriteLine("Hashed Password : " + Convert.ToBase64String(hashedPassword));
	Console.WriteLine("Iterations < " + numberOfRounds + "> Elapsed Time: " + sw.ElapsedMilliseconds + " ms");
}

The value 32 here is the desired output length of the hash, we can decide how long the hash we get out of the call to the method. We can then test out the Pbkdf2 method using an increasing number of iterations.

 
 
 void RunPbkdf2HashDemo()
{
	const string passwordToHash = "abc123";

	Console.WriteLine("Password Based Key Derivation Function Demonstration in .NET");
	Console.WriteLine("------------------------------------------------------------");
	Console.WriteLine();
	Console.WriteLine("PBKDF2 Hashes using Rfc2898DeriveBytes");
	Console.WriteLine();

	HashPassword(passwordToHash, 1);
	HashPassword(passwordToHash, 10);
	HashPassword(passwordToHash, 100);
	HashPassword(passwordToHash, 1000);
	HashPassword(passwordToHash, 10000);
	HashPassword(passwordToHash, 100000);
	HashPassword(passwordToHash, 1000000);
	HashPassword(passwordToHash, 5000000);
}

This gives the following output:

 
 
Password Based Key Derivation Function Demonstration in .NET
------------------------------------------------------------

PBKDF2 Hashes using Rfc2898DeriveBytes

Password to hash : abc123
Hashed Password : eqeul5z7l2dPrOo8WjH/oTt0RYHvlZ2lvk8SUoTjZq4=
Iterations (1) Elapsed Time: 0 ms

Password to hash : abc123
Hashed Password : wfd8qQobzBPZvdemqrtZczqctFe0JeAkKjU3IJ48cms=
Iterations (10) Elapsed Time: 0 ms

Password to hash : abc123
Hashed Password : VY45SxzhqjYronha0kt1mQx+JRDVlXj82prX3H7kjII=
Iterations (100) Elapsed Time: 0 ms

Password to hash : abc123
Hashed Password : B0LfHgRSslG/lWe7hbp4jb8dEqQ/bZwNtxsaqbVBZ2I=
Iterations (1000) Elapsed Time: 0 ms

Password to hash : abc123
Hashed Password : LAHwpS4bnbO7CQ1r7buYgUTrp10FyaRyeK6hCwGwv20=
Iterations (10000) Elapsed Time: 1 ms

Password to hash : abc123
Hashed Password : WDjyPySpULXtVOVmSR9cYlzAY4LWeJqDBhszKAfIaPc=
Iterations (100000) Elapsed Time: 13 ms

Password to hash : abc123
Hashed Password : sDx6sOrTl2b7cNZGUAecg7YO4Md/g3eAtfQSvh/vxpM=
Iterations (1000000) Elapsed Time: 127 ms

Password to hash : abc123
Hashed Password : ruywLaR0QApOU5bkqE/x2AAhYJzBj5y6D3P3IxlIF2I=
Iterations (5000000) Elapsed Time: 643 ms

Note that it takes many iterations before the computation takes significant time. Sources / links :

OWASP recommends using 600 000 iterations when using SHA256 as noted here: https://cheatsheetseries.owasp.org/cheatsheets/Password_Storage_Cheat_Sheet.html#pbkdf2
Hashing passwords using PBKDF2 are explained by Stephen Haunts here: Hashing Passwords Safely using a Password Based Key Derivation Function (PBKDF2)

AES Encryption with Galois Counter Mode (GCM) in C#

This article presents some helper methods for performing AES Encryption using Galois Counter Mode (GCM). AES or Advanced Encryption Standard is the most used encryption algorithm used today, having overtaken DES and Triple DES since 2001. We will look into the GCM mode of AES in this article. AES-GCM class AesGcm is supported in .NET Core 3.0 and newer .NET versions, plus in .NET Standard 2.1. AES-GCM is authenticated encryption, compared to default AES-CBC (Cipher Block Chaining). Benefits of using GCM mode of AES is the following:

Data authenticity / integrity. This is provided via a tag that is outputted by the encryption and used while decrypting
Provides support for sending additional data, used for example in newer TLS implementations to provide both encryption and a non-encrypted payload. This is called additional metadata

Here is a helper class to perform encryption and decryption using AES-GCM.

 
 public static class AesGcmEncryption {


	public static (byte[], byte[]) Encrypt(byte[] dataToEncrypt, byte[] key, byte[] nonce, byte[] associatedData = null)
	{
		using var aesGcm = new AesGcm(key);
		//tag and ciphertext will be filled during encryption
		var tag = new byte[16]; //tag is a hmac (hash-based message authentication code) to check that information has not been tampered with
	    var cipherText = new byte[dataToEncrypt.Length];
		aesGcm.Encrypt(nonce, dataToEncrypt, cipherText, tag, associatedData);
		return (cipherText, tag);
	}

	public static byte[] Decrypt(byte[] cipherText, byte[] key, byte[] nonce, byte[] tag, byte[] associatedData = null)
	{
		using var aesGcm = new AesGcm(key);
		//tag and ciphertext will be filled during encryption
		var decryptedData = new byte[cipherText.Length];
		aesGcm.Decrypt(nonce, cipherText, tag, decryptedData, associatedData);
		return decryptedData;
	}
	
}

In the code above, the encrypt method returns a tuple with the ciperText and the tag. These are the encrypted data and the tag, both must be used while decrypting and the tag provides as mentioned a means of checking the integrity of data, i.e. that data has not been tampered with. Note that the 16-byte tag and the ciphertext is filled after running the Encrypt method of the AesGcm class. The cipherText array must be the same length as the dataToEncrypt array inputted. Here is sample code to use AES-GCM. Note that the metadata used here, while optional, must match in case it is set in the encryption and decryption. The nonce must be 12 bytes - 96 bits in length.The nonce is similar to a initialization vector, although it is used once for the particular encryption and decryption, it is used to protect against replay attacks.

 
 
 void TestAesGCM()
{
	const string original = "Text to encrypt";
	var key = RandomNumberGenerator.GetBytes(32); //256 bits key
	var nonce = RandomNumberGenerator.GetBytes(12); //96 bits nonce
	
	(byte[] cipherText, byte[] tag) result = AesGcmEncryption.Encrypt(Encoding.UTF8.GetBytes(original),
	 key, nonce, Encoding.UTF8.GetBytes("some metadata 123"));
	 byte[] decryptedText = AesGcmEncryption.Decrypt(result.cipherText, key, nonce, result.tag, Encoding.UTF8.GetBytes("some metadata 123")); 
		
	Console.WriteLine("AES Encryption demo GCM - Galois Counter Mode:");
	Console.WriteLine("--------------");
	Console.WriteLine("Original Text = " + original);
	Console.WriteLine("Encrypted Text = " + Convert.ToBase64String(result.cipherText));
	Console.WriteLine("Tag = " + Convert.ToBase64String(result.tag));
	Console.WriteLine("Decrypted Text = " + Encoding.UTF8.GetString(decryptedText));
}


  
AES Encryption demo GCM - Galois Counter Mode:
--------------
Original Text = Text to encrypt
Encrypted Text = 9+2x0kctnRwiDDHBm0/H
Tag = sSDxsg17HFdjE4cuqRlroQ==
Decrypted Text = Text to encrypt

Use AES-GCM to provide integrity checking and allowing to send in metadata if desired to encrypt and decrypting with the AES algorithm. We can protect the AES key using different methods, for example using the Data Protection API, this is only supported in Windows. Let's look at a helper class for using Data Protection API.

 
 
 public static class DataProtectionUtil {

	public static byte[] Protect(byte[] dataToEncrypt, byte[] optionalEntropy, DataProtectionScope scope)
	{
		var encryptedData = ProtectedData.Protect(dataToEncrypt, optionalEntropy, scope);
		return encryptedData;
	}
	
	public static byte[] Unprotect(byte[] encryptedData, byte[] optionalEntropy, DataProtectionScope scope){
		var decryptedData = ProtectedData.Unprotect(encryptedData, optionalEntropy, scope);
		return decryptedData;
	}

	public static string Protect(string dataToEncrypt, string optionalEntropy, DataProtectionScope scope)
	{
		var encryptedData = ProtectedData.Protect(Encoding.UTF8.GetBytes(dataToEncrypt), optionalEntropy != null ? Encoding.UTF8.GetBytes(optionalEntropy) : null, scope);
		return Convert.ToBase64String(encryptedData);
	}

	public static string Unprotect(string encryptedData, string optionalEntropy, DataProtectionScope scope)
	{
		var decryptedData = ProtectedData.Unprotect(Convert.FromBase64String(encryptedData), optionalEntropy != null ? Encoding.UTF8.GetBytes(optionalEntropy) : null, scope);
		return Encoding.UTF8.GetString(decryptedData);
	}

}

An example how to protect your AES key:

 
 
void EncryptAndDecryptWithProtectedKey(){
	var original = "Text to encrypt";
	Console.WriteLine($"Original Text = {original}");
	
	//Create key and nnoce . Encrypt our text with AES 
	var gcmKey = RandomNumberGenerator.GetBytes(32);
	var nonce = RandomNumberGenerator.GetBytes(12); 
	
	var result = EncryptText(original, gcmKey, nonce); 
	
	//Create some entropy and protect AES key
	var entropy = RandomNumberGenerator.GetBytes(16); 
	var protectedKey = ProtectedData.Protect(gcmKey, entropy, DataProtectionScope.CurrentUser);

	Console.WriteLine($"gcmKey = {Convert.ToBase64String(gcmKey)}, protectedKey = {Convert.ToBase64String(protectedKey)}");
	
	// Decrypt the text with AES. the AES key has to be retrieved with DPAPI.
	var decryptedText = DecryptText(result.encrypted, nonce, result.tag, protectedKey, entropy);

	Console.WriteLine($"Decrypted Text using AES GCM with key retrieved via Data Protection API = {decryptedText}");

}

private static (byte[] encrypted, byte[] tag) EncryptText(string original, byte[] gcmKey, byte[] nonce){
	return AesGcmEncryption.Encrypt(Encoding.UTF8.GetBytes(original), gcmKey, nonce, Encoding.UTF8.GetBytes("some meta"));
}

private static string DecryptText(byte[] encrypted, byte[] nonce, byte[] tag, byte[] protectedKey, byte[] entropy){
	
	var key = DataProtectionUtil.Unprotect(protectedKey, entropy, DataProtectionScope.CurrentUser);

	Console.WriteLine($"Inside DecryptText: gcmKey = {Convert.ToBase64String(key)}, protectedKey = {Convert.ToBase64String(protectedKey)}");

	var decryptedText = AesGcmEncryption.Decrypt(encrypted, key, nonce, tag, Encoding.UTF8.GetBytes("some meta"));
	return Encoding.UTF8.GetString(decryptedText);
}

Data Protection API is only supported on Windows platform, there are more possibilities to protect AES key but protecting your key is always a challenge when dealing with symmetric encryption algorithms such as AES. Some more links:

What is a Nonce ? https://www.youtube.com/watch?v=EOgpr73-pgc
AES Explained - https://www.youtube.com/watch?v=O4xNJsjtN6E
Galois/Counter Mode (GCM) - https://www.youtube.com/watch?v=V2TlG3JbGp0
Authenticated Encryption in .NET with AES-GCM - https://www.scottbrady91.com/c-sharp/aes-gcm-dotnet