SQL Stored function Return table

 

Scenario:

Return table from SQL function

Solution:

 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
CREATE FUNCTION [schema].FnGetUserId()
RETURNS @Users TABLE (
  UserID                      INT,
  [Name]                      VARCHAR(50))
AS
  BEGIN
          INSERT INTO @Users VALUES (1, 'Ram')
	  INSERT INTO @Users VALUES (2, 'Shyam')

      RETURN
  END

GO

       

Sql scrub data

 

Scenario:

Scrub data through SQL

Solution:

1
2
3
4
5
6
7
8
9
DECLARE @data NVARCHAR(MAX) = 'This check #1 was for $200.', @i INT, @Pattern NVARCHAR(20) = '.$#', @newData NVARCHAR(MAX)

WHILE PATINDEX('%[^' + @Pattern + ']%', @data) <> 0
  BEGIN
      SET @newData = CONCAT(@newData, SUBSTRING(@data,  PATINDEX('%[^' + @Pattern + ']%', @data), 1))
      SET @data = STUFF(@data,  PATINDEX('%[^' + @Pattern + ']%', @data), 1, '')
  END

PRINT @newData

Reset password

 

Scenario:

Encryption using Asymmetric keys

Solution:

 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
private static string[] GetOldPasswords(string userName, int count)
        {
            //Get from history table
        }

        public class PasswordManagement
        {
            public void Enqueue(string email)
            {
                var newUserPassexpiry = DateTime.UtcNow.AddHours(72);
                var oldUserPassexpiry = DateTime.UtcNow.AddHours(48);

                var token = $"{email}:-:{newUserPassexpiry:oldUserPassexpiry}:-:{PassResetCnt}";

                var url = $"{url}?{token}";

                //enqueue email
            }
        }

         

 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
        static void Main(string[] args)
        {
            //passwordHistory = 10 (count)
            string userName = "", password = "";
            var count = 10;

            var passwordHistory = GetOldPasswords(userName, count);

            //if existing password re used
            if (passwordHistory.where(p => p.password== password))
            {
                //error
            }

            var hash = CreateHash(password);
        }

MSBuild

 

Scenario:

MSbuild solution

Solution:

 1 MSBuild.exe C:\MyProj/My.sln /property:Configuration=DEV

        

Encryption using Asymmetric keys

Scenario:

Encryption using Asymmetric keys

Solution:

Asymmetric can only encrypt/decrypt small amount of data (based on key size) and it can be used without shared a key.

         //add nuget pakage - CertificateManager  

 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
public sealed class RSAEncryption
        {
            public string Encrypt(string text, RSA rsa)
            {
                var data = Encoding.UTF8.GetBytes(text);
                var cypher = rsa.Encrypt(data, RSAEncryptionPadding.Pkcs1);
                return Convert.ToBase64String(cypher);
            }

            public string Decrypt(string encryptedText, RSA rsa)
            {
                var data = Convert.FromBase64String(encryptedText);
                var cypher = rsa.Decrypt(data, RSAEncryptionPadding.Pkcs1);
                return Encoding.UTF8.GetString(cypher);
            }

            public RSA CreateRSAPublicKey(X509Certificate2 cert)
            {
                return cert.GetRSAPublicKey();
            }

            public RSA CreateRSAPrivateKey(X509Certificate2 cert)
            {
                return cert.GetRSAPrivateKey();
            }

            public X509Certificate2 CreateRSACertificate(CreateCertificates createCertificates, int keySize)
            {
                var constraints = new BasicConstraints
                {
                    CertificateAuthority = true,
                    HasPathLengthConstraint = true,
                    Critical = false,
                    PathLengthConstraint = 2
                };

                var name = new SubjectAlternativeName
                {
                    DnsName = new List<string>
                    {
                        "CoreSigningCertificate"
                    }
                };

                var distinguishedName = new DistinguishedName { CommonName = "CoreSigningCertificate" };

                var flags = X509KeyUsageFlags.KeyCertSign
                    | X509KeyUsageFlags.DigitalSignature
                    | X509KeyUsageFlags.CrlSign
                    | X509KeyUsageFlags.DataEncipherment
                    | X509KeyUsageFlags.KeyAgreement
                    | X509KeyUsageFlags.NonRepudiation;

                var keyUsage = new OidCollection
                {
                    OidLookup.CodeSigning,
                    OidLookup.SecureEmail,
                    OidLookup.TimeStamping
                };

                var validdity = new ValidityPeriod
                {
                    ValidFrom = DateTimeOffset.UtcNow,
                    ValidTo = DateTimeOffset.UtcNow.AddYears(1)
                };

                var config = new RsaConfiguration
                {
                    KeySize = keySize,
                    HashAlgorithmName = HashAlgorithmName.SHA256,
                    RSASignaturePadding = RSASignaturePadding.Pkcs1
                };

                var certificate = createCertificates.NewRsaSelfSignedCertificate
                    (distinguishedName, constraints, validdity, name, keyUsage, flags, config);
                   
                return certificate;
            }
        }

         

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
static void Main(string[] args)
        {
            var serviceCollection = new ServiceCollection().AddCertificateManager().BuildServiceProvider();

            var instance = serviceCollection.GetService<CreateCertificates>();

            var e = new RSAEncryption();

            //create certificate
            var cert = e.CreateRSACertificate(instance, 3072);

            var data = "User Data";

            //encrypt using public key
            var encryptedData = e.Encrypt(data, e.CreateRSAPublicKey(cert));
            Console.WriteLine(encryptedData);

            //decrypt using private key
            var decryptedData = e.Decrypt(encryptedData, e.CreateRSAPrivateKey(cert));

            Console.WriteLine(decryptedData);

            Console.ReadLine();
        }

Encryption using Symmetric keys

Scenario:

Encryption using Symmetric keys

Solution:

          Symmetric encryption is fast and for large amounts of data and requires a shared key.

 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
public sealed class AESEncryption
        {
           public (string key, string IVBase64) SymmetricEncryptionKeyIV()
            {
                //256 byte
                var key = GetRandomText(32);

                var cipher = CreateCipher(key);

                var IVBase64 = Convert.ToBase64String(cipher.IV);

                return (key, IVBase64);
            }

            private Aes CreateCipher(string keyBase64)
            {
                var cipher = Aes.Create();
                cipher.Mode = CipherMode.CBC;

                cipher.Padding = PaddingMode.ISO10126;

                cipher.Key = Convert.FromBase64String(keyBase64);

                return cipher;
            }

            private string GetRandomText(int length)
            {
                var base64String = Convert.ToBase64String(GenerateRandomByte(length));

                return base64String;
            }

            private byte[] GenerateRandomByte(int length)
            {
                var byteArray = new byte[length];
                RandomNumberGenerator.Fill(byteArray);

                return byteArray;
            }

            public string Encrypt (string text, string IV, string key)
            {
                var cipher = CreateCipher(key);
                cipher.IV = Convert.FromBase64String(IV);

                var crytoTransform = cipher.CreateEncryptor();

                var plaintext = Encoding.UTF8.GetBytes(text);

                var cyphText = crytoTransform.TransformFinalBlock(plaintext, 0, plaintext.Length);

                return Convert.ToBase64String(cyphText);
            }

            public string Decrypt(string encryptedText, string IV, string key)
            {
                var cipher = CreateCipher(key);
                cipher.IV = Convert.FromBase64String(IV);

                var crytoTransform = cipher.CreateDecryptor();

                var enc = Convert.FromBase64String(encryptedText);

                var plainBytes = crytoTransform.TransformFinalBlock(enc, 0, enc.Length);

                return Encoding.UTF8.GetString(plainBytes);
            }
        }

          Using 128 byte salt and 256 byte key and iterations of 10000, create a hashed password. If the                  iterations has been updated then use the new one to rehash the password.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
        static void Main(string[] args)
        {
            var data = "User Data";
            var s = new AESEncryption();

            var (Key, IVBase64) = s.SymmetricEncryptionKeyIV();

            var encryptedData = s.Encrypt(data, IVBase64, Key);
            Console.WriteLine(encryptedData);

            var decryptedData = s.Decrypt(encryptedData, IVBase64, Key);
            Console.WriteLine(decryptedData);

            Console.ReadLine();
        }

Encryption using DataProtectionProvider

   

Scenario:

Encryption using DataProtectionProvider

Solution:

          The data-protection uses symmetric key encryption to protect data. Key contains random data                  which is used to encrypt the data and also decrypt.

The same key can be used for different purposes, but to seperate the concerns .NET Core provides "purposes". The data protection system has a parent key (can't be used directly). You derivechild keys out of it and use it to encrypt and decrypt.

         //add nuget pakage - Microsoft.AspNetCore.DataProtection.  

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
public sealed class PasswordEncrypt
        {
            IDataProtectionProvider _rootProvider;

            public PasswordEncrypt(IDataProtectionProvider provider)
            {
                _rootProvider = provider;
            }

            public IDataProtector GetDataProtector(string purpose)
            {
                IDataProtector protector = _rootProvider.CreateProtector(purpose);

                return protector;
            }

            public string EncryptData(string data, IDataProtector protector)
            {
                var encryptedData = protector.Protect(data);

                return encryptedData;
            }

            public string DecryptData(string encryptedData, IDataProtector protector)
            {
                var decryptedData = protector.Unprotect(encryptedData);

                return decryptedData;
            }
        }

The encrypted data is isolated as you can only decrypt using the same purpose child key though if you lost the child key you can still use parent key to decrypt if you know then purpose. The keys are also auto rotated (90 days). The collection of all keys is key ring.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
static void Main(string[] args)
        {
            var serviceCollection = new ServiceCollection();
            serviceCollection.AddDataProtection();

            var services = serviceCollection.BuildServiceProvider();

            var instance = ActivatorUtilities.CreateInstance<PasswordEncrypt>(services);

            var password = "mypassword";

            var protector = instance.GetDataProtector("Login");

            var encryptedData = instance.EncryptData(password, protector);

            Console.WriteLine(encryptedData);

            var decryptedData = instance.DecryptData(encryptedData, protector);

            Console.WriteLine(decryptedData);

            Console.ReadLine();
        }

 Avoid using this for long term encryption of data as keys expire and also rotated and if keys are deleted then encrypted data cant be recovered.

Hashing and Salting in .NET core

  

Scenario:

Salt and Hash password

Solution:

          Encrypt interface

 
1
2
3
4
5
 public interface IPasswordEncrypt
    {
        string Hash(string password);
        (bool IsValid, bool DoResetPassword) Validate(string hash, string password);
    }

          Using 128 byte salt and 256 byte key and iterations of 10000, create a hashed password. If the                  iterations has been updated then use the new one to rehash the password.

1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
public sealed class PasswordEncrypt : IPasswordEncrypt
    {
        private int saltSize = 16;
        private int keySize = 32;
        private int iterations = 10000;

        public string Hash(string password)
        {
            using (var algo = new Rfc2898DeriveBytes(password, saltSize, iterations, HashAlgorithmName.SHA512))
            {
                var key = Convert.ToBase64String(algo.GetBytes(keySize));
                var salt = Convert.ToBase64String(algo.Salt);

                return $"{iterations}.{salt}.{key}";
            }
        }

        public (bool IsValid, bool DoResetPassword) Validate(string hash, string password)
        {

            var handle = hash.Split('.');

            var iterationCount = Convert.ToInt32(handle[0]);
            var salt = Convert.FromBase64String(handle[1]);
            var key = Convert.FromBase64String(handle[2]);

            var DoResetPassword = iterationCount != iterations;

            using (var algo = new Rfc2898DeriveBytes(password, salt, iterations, HashAlgorithmName.SHA512))
            {
                var keyToVerify = algo.GetBytes(keySize);

                var valid = keyToVerify.SequenceEqual(key);

                return (valid, DoResetPassword);
            }
        }
    }

         Encrypt & Decrypt

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
static void Main(string[] args)
        {
            var storedPassword = "mypassword";

            var pe = new PasswordEncrypt();

            var hashstoredPassword = pe.Hash(storedPassword);

            var correctInput = "mypassword";

            var incorrectInput = "notmypassword";

            var result = pe.Validate(hashstoredPassword, correctInput);

            if (result.IsValid)
            {
                Console.WriteLine("match");
            }

            result = pe.Validate(hashstoredPassword, incorrectInput);

            if (!result.IsValid)
            {
                Console.WriteLine("no match");
            }

            Console.ReadLine();
        }

Autherization using OAuth in MVC

  

Scenario:

User Authorization using OAuth for MVC

Solution:

         MVC Authorization Filter

1
2
3
4
5
6
7
    public class AuthorizeFilter : ActionFilterAttribute
    { 
        public override void OnActionExecuting(ActionExecutingContext filterContext)
        {
            Authorized(UserId, token, Controller, Action, "Get");
        }
    }

          Authorization Service, internally calling OAuth service

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
    public class AuthServ
    {
        public bool Authorized(request)
        {
            var claims = GetClaims(user, token);

            if (claims.ContainsKey(claimRequested))
            {
                return true;
            }
            return false;
        }

        public Claims GetClaims(string user, string token)
        {
            var webClient = new WebClient();
            webClient.Headers.Add("Content-Type", "application/x-www-form-urlencoded");

            return webClient.UploadString("/TokenProvider/GetCustomerRoles?grant_type=role&Token={token}", "POST", "");
        }
    }

Authentication using OAuth in MVC

 

Scenario:

User Authentication using OAuth for MVC

Solution:

         MVC Auth Filter

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
public class AuthFilter : ActionFilterAttribute
    {
        public override void OnActionExecuting(ActionExecutingContext filterContext)
        {
            //Check if Access Token is present in request
            if (_authServ.TryParseAccessToken(filterContext.HttpContext))
            {
                //Validate the Access Token
                var result = _authServ.ValidateAccessToken(filterContext.HttpContext);
            }
            else if (tokenexired)
            {
                //refresh the token
                RefreshAccessToken();
            }
            else
            {
                //else authenticate
                Authenticate(filterContext.HttpContext);
            }
        }
    }

          Auth Service, internally calling OAuth service

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
public class AuthServ
    {
        public bool ParseToken(HttpContextBase context)
        {
            return !string.IsNullOrEmpty(context.Request.QueryString["token"]);
        }
        public bool ValidateToken(HttpContextBase context)
        {
            var webClient = new WebClient();

            webClient.Headers.Add("Content-Type", "application/x-www-form-urlencoded");

            var data = webClient.UploadString("/TokenProvider/Validate?grant_type=code&client_id=&client_secret=&accessToken=",
                authUrl,
                HttpUtility.UrlEncode(clientId),
                HttpUtility.UrlEncode(clientSecret),
                accessToken, "POST", "");
        }

        public void RefreshToken()
        {
            context.Response.Redirect(string.Format("TokenProvider/Index?grant_type=refresh_token&client_id=&client_secret=&refreshToken=&accessToken=&return_url=&callback_url=");
        }

        public string Authenticate(HttpContextBase context)
        {
            context.Response.Redirect("/TokenProvider/Index?grant_type=code&client_id=&client_secret=&return_url=&callback_url=&userId=");
        }
    }

GIT hub fork and pull request

 

Scenario:

GIT hub pull request

Solution:

1. Go to your GIT repository and click Fork.
2. Fork to your account and clone it to your windows folder.
3. Fetch -> Rebase and make changed.
4. Commit.
5. Push.
6. Go to GIT repo (your forked branch).
7. New Pull Request.
8. Redirected to the base (origin) repo.
9. Create Pull request
10. See difference between base and head (your).
11. If all good -> add comments -> Create Pull Request
12. Send the PR to the repo owner to have your changes merged to base repo.