Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
How to encrypt Spacific Data field in Select Query sql2000?

已答覆 How to encrypt Spacific Data field in Select Query sql2000?

  • Wednesday, January 30, 2013 7:10 AM
     
     
    Sign In to Vote
    0
    i have Table colled User it cantains Password Field,
    Normaly in Salect Query for this Table, Will retrive All
    data Fields in this Table and Password Field all so Displed,

    My qeustion is How to encrypt Password Field in Fake Data
    ex(********) durring Select Query!

    like this..

    Id:    UserName:    Passwod
    1      CBS              *******
    2     VCB              *******

    Thanks
     

All Replies

  • Wednesday, January 30, 2013 7:32 AM
     
     
    Sign In to Vote
    0

    Check

    http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/440be198-48d7-4946-adeb-814145bda3ec

    http://msdn.microsoft.com/en-us/library/ms179331%28v=SQL.100%29.aspx

    Many Thanks & Best Regards, Hua Min

     
  • Wednesday, January 30, 2013 7:42 AM
    Answerer
     
     Answered
    Sign In to Vote
    0

    Do not store password in the table, but just a hash value and compare those values when the client typed the password

    use tempdb
    go

    create table sec (secUserId varbinary(150))
    go

    insert sec
    select HashBytes('MD5', 'MyUserID')


    declare @binVal as varbinary(150)
    set @binVal = HashBytes('MD5', 'MyUserID')
    select @binVal

    select *
    from sec
    where secUserId = @binVal



    drop table sec
    go
    Below is a C# version that you can use before 
    sending the value to SQL Server, which is a better approach, IMHO.

       var md5 = new System.Security.Cryptography.MD5CryptoServiceProvider();
       var encoding = new System.Text.ASCIIEncoding();
       byte[] passwordHash = md5.ComputeHash(encoding.GetBytes(password));

    ---Below is a snippet that compare the hash value from the database
    --- to the user-supplied one for password validation:

      //compare password hash
      if(compareHashes(databasePasswordHash, userSuppliedPasswordHash))
      {
       //password is valid
      }
      else
      {
       //password is invalid
      }


      //Helper function
      private bool compareHashes(byte[] value1, byte[] value2)
      {
       if (value1.Length != value2.Length)
       {
        return false;
       }
       for (int i = 0; i < value1.Length; ++i)
       {
        if (value1[i] != value2[i])
        {
         return false;
        }
       }
       return true;
      }

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance