locked
Sending encrypted querystring to stored procedure and return results RRS feed

  • Question

  • User1957145615 posted

    Hello all sorry not sure what part of the forum this post should go please move if this is not right.

    .Net C#

    using MSSQL server for backend

    For my issue I am trying to send a encrypted querystring to my stored procedure which takes it in as a Type varbinary and decrypts it then sends back the 3 values that were encrypted. When I debug my code seems as if the encrypted value is sent however no values are being returned please help. The storedProcedure when excuted within SQL returns the correct values so it is something with my c# code.

    I tried to add a listbox as you see in the code to display the Select Statement at the bottom of my storedprocedure which should have the decrypted values stored in there after the encrypted value is passed but does not seem to work. Any Help would be appreciated as I am a little lost on how to accomplish this if I am even approaching it the correct way.

    StoredProcedure

    ALTER PROCEDURE [dbo].[usp_PW_Update]
    			(@encrypt_value varbinary(max))
    AS
    BEGIN
    	SET NOCOUNT ON;
    	
    Declare @id VARCHAR(25)
    Declare @name VARCHAR(100)
    Declare @password VARCHAR(100)
    Declare @combination VARCHAR(300)
    Declare @passphrase VARCHAR(MAX)
    Declare @dec varchar(1500)
    
    -- encryption key or phrase
    SET @phrase = N'practice';
    Set @rosterid = '';
    Set @name = '';
    Set @password = '';
    
    select @dec= cast(decryptbypassphrase(@phrase,@encrypt_value) as varchar)
    select @dec = replace(@dec,char(9),'.')
    select @id = parsename(@dec,3), @name = parsename(@dec,2), @password = parsename(@dec,1) 
    
    
    
    select 'Yeah, it worked' as Result, @id, @name, @password
    
    END

    C# Code

    public partial class AcceptQueryString : System.Web.UI.Page
    {

        string connectionString =
          WebConfigurationManager.ConnectionStrings["Membership"].ConnectionString;


        protected void Page_Load(object sender, EventArgs e)
        {
            SendEncryptedValue();
        }


    public void SendEncryptedValue() { byte[] buf = System.Text.Encoding.UTF8.GetBytes("0x0100000044B2E5E57F34E5635C34FFF297E18D299FB36D47F54E3E6EB517C549806B3DE3F3126C385CBE38FFBE60C248E63BA0FF"); using (SqlConnection con = new SqlConnection(connectionString)) {

    try
    {
    con.Open();
                DataSet ds = new DataSet();
                SqlCommand cmd = new SqlCommand("usp_PW_Update", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@encrypt_value", SqlDbType.VarBinary).Value = buf;
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(ds);
               Gridview1.DataSource = ds;
    Gridview1.Databind();
     
    catch (SqlException err)
                {
                    throw new ApplicationException("Erorr", err);
                }
               
    finally
                {
                  con.Close();
                } } }

    Tuesday, September 11, 2012 9:41 AM

Answers

  • User863160722 posted

    Encoding.GetBytes will not convert a hex string to the equivalent byte array; you'll need some custom code for that. For example:

    static byte[] ParseHexString(string value)
    {
       if (string.IsNullOrEmpty(value)) return null;
       if (1 == (1 & value.Length)) throw new ArgumentException("Invalid length for a hex string.", "value");
       
       int startIndex = 0;
       int length = value.Length;
       char[] input = value.ToCharArray();
       if ('0' == input[0] && 'x' == input[1])
       {
          if (2 == length) return null;
          startIndex = 2;
          length -= 2;
       }
       
       Func<char, byte> charToWord = c =>
       {
          if ('0' <= c && c <= '9') return (byte)(c - '0');
          if ('A' <= c && c <= 'F') return (byte)(10 + c - 'A');
          if ('a' <= c && c <= 'f') return (byte)(10 + c - 'a');
          throw new ArgumentException("Invalid character for a hex string.", "value");
       };
       
       byte[] result = new byte[length >> 1];
       for (int index = 0, i = startIndex; index < result.Length; index++, i += 2)
       {
          byte w1 = charToWord(input[i]);
          byte w2 = charToWord(input[i + 1]);
          result[index] = (byte)((w1 << 4) + w2);
       }
       
       return result;
    }
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 11, 2012 3:02 PM

All replies

  • User1191505944 posted
    using (SqlConnection con = new SqlConnection(connectionString)) 
            { 
    con.Open();
    .
    .
    .
    }


    looks you haven't open your sql connection

    Tuesday, September 11, 2012 11:05 AM
  • User1957145615 posted

    Sorry yes I was not opening it correctly thanks for that piece of info also now I am opening and closing correctly I still am not getting any values from when the stored procedure is decrypting the varbinary data. Could it be the way I am sending the data to my stored procedure?

    Tuesday, September 11, 2012 11:42 AM
  • User1191505944 posted

    Can you move up your try catch block and see if you are getting any errors? Is the connection string correct?

    Tuesday, September 11, 2012 12:35 PM
  • User1957145615 posted

    Moved the try catch up and not receving any errors. Also i changed the code to just bind to a gridview to see if it would display anything and the only thing that displays is "Yeah, it worked". The id, name, password is blank.

    Tuesday, September 11, 2012 12:52 PM
  • User863160722 posted

    The string you've posted can't be decrypted using that pass-phrase, so the "decryptbypassphrase" function returns null, and therefore the "parsename" function also returns null.

    Also, I'm not sure that Encoding.UTF8.GetBytes is the correct approach. Your string starts with "0x", which would suggest it's supposed to be a hex string. However, it contains several non-hex characters (G, H, J and K), so I'm not sure how you generated it.

    Tuesday, September 11, 2012 2:07 PM
  • User1957145615 posted

    Sorry about the (G,H,J,and K) that was me fooling around with something else. the actual generated code is (0x0100000044B2E5E57F34E5635C34FFF297E18D299FB36D47F54E3E6EB517C549806B3DE3F3126C385CBE38FFBE60C248E63BA0FF)

    Also when I exec the stored procedure alone and use that value it returns the correct information so I thought the stored procedure was working correctly.

    How would I send it? The reason why I went bytes is because we are using varbinary in our StoredProcedure so I was assuming that bytes would send it as I was not able to send it as a string because our stored procedure did not like it because the data type was varbinary.

    Thanks for your response

    Tuesday, September 11, 2012 2:27 PM
  • User863160722 posted

    Encoding.GetBytes will not convert a hex string to the equivalent byte array; you'll need some custom code for that. For example:

    static byte[] ParseHexString(string value)
    {
       if (string.IsNullOrEmpty(value)) return null;
       if (1 == (1 & value.Length)) throw new ArgumentException("Invalid length for a hex string.", "value");
       
       int startIndex = 0;
       int length = value.Length;
       char[] input = value.ToCharArray();
       if ('0' == input[0] && 'x' == input[1])
       {
          if (2 == length) return null;
          startIndex = 2;
          length -= 2;
       }
       
       Func<char, byte> charToWord = c =>
       {
          if ('0' <= c && c <= '9') return (byte)(c - '0');
          if ('A' <= c && c <= 'F') return (byte)(10 + c - 'A');
          if ('a' <= c && c <= 'f') return (byte)(10 + c - 'a');
          throw new ArgumentException("Invalid character for a hex string.", "value");
       };
       
       byte[] result = new byte[length >> 1];
       for (int index = 0, i = startIndex; index < result.Length; index++, i += 2)
       {
          byte w1 = charToWord(input[i]);
          byte w2 = charToWord(input[i + 1]);
          result[index] = (byte)((w1 << 4) + w2);
       }
       
       return result;
    }
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 11, 2012 3:02 PM
  • User1957145615 posted

    Nice Example, Can you recommend any articles or book materials that will go over what I am trying to accomplish so i can better understand what I need to do and how. Sorry new to this and trying to learn just when I search I get scattered 90 million different ways as everyone has a way of doing something different.

    Thanks

    Tuesday, September 11, 2012 3:12 PM