Welcome

Hello, Welcome to my blog. If you like feel free to refer others

Tuesday 11 October 2011

Encrypt Your Data With Hash Algorithm Using C#


Below mentioned function generates a hash for the given plain text value and returns a base64-encoded result. Before the hash is computed, a random salt is generated and appended to the plain text. This salt is stored at the end of the hash value, so it can be used later for has verification.


public static string ComputeHash(string Input,string HashAlgorithmName,byte[] saltBytes)
    {
        // If salt is not specified, generate it on the fly.
        if (saltBytes == null)
        {
            // Define min and max salt sizes.
            int minSaltSize = 4;
            int maxSaltSize = 8;

            // Generate a random number for the size of the salt.
            Random random = new Random();
            int saltSize = random.Next(minSaltSize, maxSaltSize);

            // Allocate a byte array, which will hold the salt.
            saltBytes = new byte[saltSize];

            // Initialize a random number generator.
            RNGCryptoServiceProvider rng = new RNGCryptoServiceProvider();

            // Fill the salt with cryptographically strong byte values.
            rng.GetNonZeroBytes(saltBytes);
        }

        // Convert plain text into a byte array.
        byte[] plainTextBytes = Encoding.UTF8.GetBytes(Input);

        // Allocate array, which will hold plain text and salt.
        byte[] plainTextWithSaltBytes =
                new byte[plainTextBytes.Length + saltBytes.Length];

        // Copy plain text bytes into resulting array.
        for (int i = 0; i < plainTextBytes.Length; i++)
            plainTextWithSaltBytes[i] = plainTextBytes[i];

        // Append salt bytes to the resulting array.
        for (int i = 0; i < saltBytes.Length; i++)
            plainTextWithSaltBytes[plainTextBytes.Length + i] = saltBytes[i];

        // Because we support multiple hashing algorithms, we must define
        // hash object as a common (abstract) base class. We will specify the
        // actual hashing algorithm class later during object creation.
        HashAlgorithm hash;

        // Make sure hashing algorithm name is specified.
        if (HashAlgorithmName == null)
            HashAlgorithmName = "";

        // Initialize appropriate hashing algorithm class.
        switch (HashAlgorithmName.ToUpper())
        {
            case "SHA1":
                hash = new SHA1Managed();
                break;

            case "SHA256":
                hash = new SHA256Managed();
                break;

            case "SHA384":
                hash = new SHA384Managed();
                break;

            case "SHA512":
                hash = new SHA512Managed();
                break;

            default:
                hash = new MD5CryptoServiceProvider();
                break;
        }

        // Compute hash value of our plain text with appended salt.
        byte[] hashBytes = hash.ComputeHash(plainTextWithSaltBytes);

        // Create array which will hold hash and original salt bytes.
        byte[] hashWithSaltBytes = new byte[hashBytes.Length +
                                            saltBytes.Length];

        // Copy hash bytes into resulting array.
        for (int i = 0; i < hashBytes.Length; i++)
            hashWithSaltBytes[i] = hashBytes[i];

        // Append salt bytes to the result.
        for (int i = 0; i < saltBytes.Length; i++)
            hashWithSaltBytes[hashBytes.Length + i] = saltBytes[i];

        // Convert result into a base64-encoded string.
        string hashValue = Convert.ToBase64String(hashWithSaltBytes);

        // Return the result.
        return hashValue;
    }

To use this function you need to import System.Security.Cryptography namespace.

Description of parameter:
1) Parameter name="Input".Input value to be hashed. The function does not check whether this parameter is null.
2) Parameter name="HashAlgorithmName".Name of the hash algorithm. Allowed values are: "MD5", "SHA1", "SHA256", "SHA384", and "SHA512" (if any other value is specified MD5 hashing algorithm will be used). This value is case-insensitive.
3) Parameter name="saltBytes". This parameter can be null, in which case a random salt value will be generated.

Note: Hash value formatted as a base64-encoded string.




Now I will describe how to verify this Hash value.Below mentioned function Compares a hash of the specified plain text value to a given hash value. Plain text is hashed with the same salt value as the original hash.



public static bool VerifyHash(string Input,string HashAlgorithmName,string hashValue)
    {
        // Convert base64-encoded hash value into a byte array.
        byte[] hashWithSaltBytes = Convert.FromBase64String(hashValue);

        // We must know size of hash (without salt).
        int hashSizeInBits, hashSizeInBytes;

        // Make sure that hashing algorithm name is specified.
        if (HashAlgorithmName == null)
            HashAlgorithmName = "";

        // Size of hash is based on the specified algorithm.
        switch (HashAlgorithmName.ToUpper())
        {
            case "SHA1":
                hashSizeInBits = 160;
                break;

            case "SHA256":
                hashSizeInBits = 256;
                break;

            case "SHA384":
                hashSizeInBits = 384;
                break;

            case "SHA512":
                hashSizeInBits = 512;
                break;

            default: // Must be MD5
                hashSizeInBits = 128;
                break;
        }

        // Convert size of hash from bits to bytes.
        hashSizeInBytes = hashSizeInBits / 8;

        // Make sure that the specified hash value is long enough.
        if (hashWithSaltBytes.Length < hashSizeInBytes)
            return false;

        // Allocate array to hold original salt bytes retrieved from hash.
        byte[] saltBytes = new byte[hashWithSaltBytes.Length -
                                    hashSizeInBytes];

        // Copy salt from the end of the hash to the new array.
        for (int i = 0; i < saltBytes.Length; i++)
            saltBytes[i] = hashWithSaltBytes[hashSizeInBytes + i];

        // Compute a new hash string.
        string expectedHashString =
                    ComputeHash(Input, HashAlgorithmName, saltBytes);

        // If the computed hash matches the specified hash,
        // the plain text value must be correct.
        return (hashValue == expectedHashString);
    }



Result :

If computed hash mathes the specified hash the function the return value is true; otherwise, the function returns false.



Description of parameter:
1) Parameter name="Input".Input to be verified against the specified hash. The function does not check whether this parameter is null.
2) Parameter name="HashAlgorithmName".Name of the hash algorithm. Allowed values are: "MD5", "SHA1", "SHA256", "SHA384", and "SHA512" (if any other value is specified MD5 hashing algorithm will be used). This value is case-insensitive.
3) Parameter name="hashValue". Base64-encoded hash value produced by ComputeHash function. This value includes the original salt appended to it




Happy learning......




Friday 7 October 2011

Automate Backup Database on SQL Server

Let’s me explain what I’m going to do to automate the task that I’ve mentioned above. First, I’ll create a VB Script file that perform backup database on SQL Server. Then, I create a Scheduled Task to execute the script daily. That’s it, the script will be executed according to the scheduled time without any user interaction.
  1. Part I: Create VB Script
    This is the part which you’re reading show how to create a VB Script for backup database.
  2. Part II: Create Scheduled Task
    In this part, I’ll create a task schedule to execute the VB Script on scheduled time 
  

PART 1: Create VB Script 

  1. In the example below, I’m going to create a VB Script that backup a database Northwind on SQL Server 2005 (INSTANCE01). Then, I’ll create a Scheduled Task to execute the script at 1:00 AM daily. Sounds easy, isn’t it? Let’s see it in action.
  2. On SQL Server 2005 server, open Notepad and type the following code:
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
On Error Resume Next

strComputer = "."

'Set objWMIService = GetObject("winmgmts:" _
'    & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")

Dim sDBUser
Dim sDBPwd
Dim sDBServer
Dim sDBName

sDBUser = "sa"
sDBPwd = "password"
sDBServer = ".\INSTANCE01"
sDBName = "Northwind"
backupPath = "C:\Test\"

Set oSQLServer = CreateObject("SQLDMO.SQLServer")
Set oBackup = CreateObject("SQLDMO.Backup")

oSQLServer.LoginTimeout = 30
oSQLServer.LoginSecure = True
'oSQLServer.Connect sDBServer
oSQLServer.Connect sDBServer, sDBUser, sDBPwd

oBackUp.Initialize = "TRUE" ' Means overwrite existing .bak file.
oBackup.Database = sDBName
oBackup.Action = SQLDMOBackup_Database
oBackup.Files = backupPath & sDBName & ".bak"
oBackup.SQLBackup oSQLServer

oSQLServer.Close()
  1. Code Explanation:
    • Line 3: Specify server name. “.” means the local computer.
    • Line 5-6 and 24: Connect to SQL Server with Windows Authentication mode (Using current user credential). If you don’t want to specify username and password in the script, uncomment these line and comment the line 25 instead.
    • Line 8-11: Variables Declaration
    • Line 13-17: Assign values to variables.
      • Line 13: Username for connect to SQL Server
      • Line 14: Password of the username
      • Line 15: The SQL Server. For SQL Server Express Edition, the value should be “.\SQLEXPRESS”.
      • Line 16: The Database name. In this example, it is Northwind.
      • Line 17: Define location where you want to keep the backup file.
    • Line 19-20: Create Objects to perform backup.
    • Line 22-23: SQL Connection attributes.
    • Line 24: Connect to SQL Server with Windows Authentication Mode (Doesn’t need username and password). See Line 5-6 and 24 for more detail.
    • Line 25: Connect to SQL Server with SQL Authentication Mode (Specify username and password). The code above is set to connect by this method.
    • Line 27: Set to True to overwrite the existing backup file.
    • Line 28-29: Backup attributes,
    • Line 30: Set location of the backup file.
    • Line 31: Perform backup operation.
    • Line 33: Close the connection to SQL Server.
  2. Customize the code as you desired. You should change the configurations on line 13-17 to match your environment. Then, save the file to .vbs format. In this example, I save to Northwind.vbs.
  3. Next, test the script by double-click the script to execute it. You should see the Northwind.bak file in the location where you have specified in the script.
    Backup Northwind  
  4. If you didn’t see the Northwind.bak, check the Application event log to see if there is any error. The figure below is the success backup message.
    Backup Message Log  


Part 2 : Create Scheduled Task
  1. Open Scheduled Task. Start -> Programs -> Accessories -> System Tools -> Scheduled Task.
  2. Double-click on Add Scheduled Task to create a new task schedule.
    Add Scheduled Task
  3. On Scheduled Task Wizard, click Next.
    Scheduled Task Wizard
  4. On Select Program to Run, click Browse and select the VB script file.
    Browse to VB Script
  5. Define name of the task and select when to perform the task. In this example, I want to backup daily. Click Next.
    Select Daily
  6. Select time to run this task. I set to 1:00 AM every day. Click Next.
    Set Time
  7. Enter the username and password. The task will be run by this user account. If you create a VB Script that using Windows Authentication mode, you have to enter the account that has backup privilege on the SQL Server.
    User Account
  8. Click Finish to complete creating a task schedule.
    Finish Create Task Schedule
  9. The task schedule has been created. Now when the time is 1:00 AM, the task will be run as the user account that you’ve spcified in the task schedule.

    Task Schedule
 Happy learning........

    Constraint in SQL Server

    A constraint is a property assigned to a column or the set of columns in a table that prevents certain types of inconsistent data values from being placed in the column(s). Constraints are used to enforce the data integrity. This ensures the accuracy and reliability of the data in the database. The following categories of the data integrity exist:

    # Entity Integrity
    # Domain Integrity
    # Referential integrity
    # User-Defined Integrity


    Entity Integrity ensures that there are no duplicate rows in a table.
    Domain Integrity enforces valid entries for a given column by restricting the type, the format, or the range of possible values.
    Referential integrity ensures that rows cannot be deleted, which are used by other records (for example, corresponding data values between tables will be vital).
    User-Defined Integrity enforces some specific business rules that do not fall into entity, domain, or referential integrity categories.

    Each of these categories of the data integrity can be enforced by the appropriate constraints. Microsoft SQL Server supports the following constraints:

    # PRIMARY KEY
    # UNIQUE
    # FOREIGN KEY
    # CHECK
    # NOT NULL


    A PRIMARY KEY constraint is a unique identifier for a row within a database table. Every table should have a primary key constraint to uniquely identify each row and only one primary key constraint can be created for each table. The primary key constraints are used to enforce entity integrity.

    A UNIQUE constraint enforces the uniqueness of the values in a set of columns, so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints.

    A FOREIGN KEY constraint prevents any actions that would destroy link between tables with the corresponding data values. A foreign key in one table points to a primary key in another table. Foreign keys prevent actions that would leave rows with foreign key values when there are no primary keys with that value. The foreign key constraints are used to enforce referential integrity.

    A CHECK constraint is used to limit the values that can be placed in a column. The check constraints are used to enforce domain integrity.

    A NOT NULL constraint enforces that the column will not accept null values. The not null constraints are used to enforce domain integrity, as the check constraints.



    Happy learning.......

    Nth Highest Salary Using SQL Query

    Here is the SQL Query:
    SELECT * FROM emptable e1 WHERE (N = (SELECT COUNT(DISTINCT (e2.empsalary))
    FROM emptable e2 WHERE e2.empsalary >= e1.empsalary))

    Note: you need to replace the value of N while running the query.
    Example:
    If you want highest paid employee details then value of N=1 then query will look like :

    SELECT * FROM emptable e1 WHERE (1 = (SELECT COUNT(DISTINCT (e2.empsalary))
    FROM emptable e2 WHERE e2.empsalary >= e1.empsalary))

    Generate Comma Separated List with SELECT statement

    My data in the table looks like :


    The result I want to show is like :




    Select query will be:

    Assuming my table name is  #test, It has 2 column field1,field2

    SELECT field1,
     SUBSTRING(
     (
      SELECT ( ',' + field2)
      FROM #test t2
      WHERE t1.Field1 = t2.Field1
      ORDER BY t1.Field1, t2.Field1
      FOR XML PATH('')
     ), 3, 1000)
    FROM #test t1
    GROUP BY field1

    If you wish select all the rows of field2 as comma separated list then query will be:


    SELECT STUFF( -- Remove first comma
        (
            SELECT  ', ' + field2 FROM -- create comma separated values
            (
              SELECT field2 FROM #test --Your query here
            ) AS T FOR XML PATH('')
        )
        ,1,1,'') AS field2

    OR

    DECLARE @test NVARCHAR(max) 
    SELECT @test = COALESCE(@test + ',', '') + field2 FROM #test
    SELECT field2= @test


    Output:



    Happy learning......















    Function in Sql Server to break Comma-Separated Strings into Table

    The below function is Table-valued function which would help us splitting comma-separated (or any other delimiter value) string to individual string.

    CREATE FUNCTION dbo.SplitRowsIntoColumns(@InputString varchar(8000), @Delimiter char(1))      
        returns @temptable TABLE (items varchar(8000))      
        as      
        begin      
            declare @index int      
            declare @slice varchar(8000)      
             
            select @index = 1      
                if len(@InputString)<1 or @InputString is null  return      
             
            while @index!= 0      
            begin      
                set @index = charindex(@Delimiter,@InputString)      
                if @index!=0      
                    set @slice = left(@InputString,@index - 1)      
                else      
                    set @slice = @InputString      
                 
                if(len(@slice)>0) 
                    insert into @temptable(Items) values(@slice)      
         
                set @InputString = right(@InputString,len(@InputString) - @index)      
                if len(@InputString) = 0 break      
            end  
        return      
        end 

    The above function can be called as :

    select top 10 * from dbo.SplitRowsIntoColumns('Ashis,Rashmi,Vishal,Amit',',') 

    The Output will be :


    Happy learning....