Friday, August 27, 2010

User Saved Safe URL without security risks

Often there is a need to have a url like:

https://myhost/AccountStatement.aspx?accountId=0392342&Date=20111109

Unfortunately this opens a security risk. The https protects the page contents but not the URL. The URL is sent as open text. The above URL tempts hackers that may also be users of the system to try enumerating various accountId in the hope that someone has forgotten to check the access permission against the user for each account (A naive assumption is that you don’t know the account number unless you are the owner…).

 

One solution is to put put the data in a post back and thus take it off the URL line – unfortunately it is not friendly because it prevents a user from saving the URL for quick reference.  You can have both friendly and secure by encrypting the arguments.

 

If you use a Membership Provider you have a key that you can use for encrypting, using the user name or host name results in a second key so you can now well encrypt the URL Parameters. I usually just concatenated the URL parameters with tabs between each ordered part to make decomposition using a split easy, i.e. 0392342\t20111109 is what I would encrypt and assign to ?key=

 

The first part is a SALT for which I use the MembershipUser ProviderID, typically a GUID.

Code Snippet
  1. private static byte[] UserSpecificKey
  2. {
  3.     get
  4.     {
  5.         var user = Membership.GetUser();
  6.         if (user == null)
  7.         {
  8.             return Encoding.ASCII.GetBytes("GUEST");
  9.         }
  10.         return Encoding.ASCII.GetBytes(user.ProviderUserKey.ToString());
  11.     }
  12. }

Then I use a standard built in two way encrypt/decrypt and use some other bit of information, for example, the host name (which means that the URL is host specific).

Code Snippet
  1. public static string EncryptStringAES(string plainText)
  2.         {
  3.             if (string.IsNullOrEmpty(plainText))
  4.                 throw new ArgumentNullException("plainText");
  5.             var hostName = HttpContext.Current.Request.Url.DnsSafeHost;
  6.  
  7.             string outStr = null;
  8.             RijndaelManaged aesAlg = null;
  9.  
  10.             try
  11.             {
  12.                 var key = new Rfc2898DeriveBytes(hostName, UserSpecificKey);
  13.                 aesAlg = new RijndaelManaged();
  14.                 aesAlg.Key = key.GetBytes(aesAlg.KeySize / 8);
  15.                 aesAlg.IV = key.GetBytes(aesAlg.BlockSize / 8);
  16.                 var encryptor = aesAlg.CreateEncryptor(aesAlg.Key, aesAlg.IV);
  17.  
  18.                 using (var msEncrypt = new MemoryStream())
  19.                 {
  20.                     using (var csEncrypt = new CryptoStream(msEncrypt, encryptor, CryptoStreamMode.Write))
  21.                     {
  22.                         using (var swEncrypt = new StreamWriter(csEncrypt))
  23.                         {
  24.                             swEncrypt.Write(plainText);
  25.                         }
  26.                     }
  27.                     outStr = Convert.ToBase64String(msEncrypt.ToArray());
  28.                 }
  29.             }
  30.             finally
  31.             {
  32.                 if (aesAlg != null)
  33.                     aesAlg.Clear();
  34.             }
  35.             return outStr;
  36.         }

and

Code Snippet
  1. public static string DecryptStringAES(string cipherText)
  2. {
  3.     if (string.IsNullOrEmpty(cipherText))
  4.         throw new ArgumentNullException("cipherText");
  5.     var hostName = HttpContext.Current.Request.Url.DnsSafeHost;
  6.     RijndaelManaged aesAlg = null;
  7.     string plaintext = null;
  8.  
  9.     try
  10.     {
  11.         var key = new Rfc2898DeriveBytes(hostName, UserSpecificKey);
  12.         aesAlg = new RijndaelManaged();
  13.         aesAlg.Key = key.GetBytes(aesAlg.KeySize / 8);
  14.         aesAlg.IV = key.GetBytes(aesAlg.BlockSize / 8);
  15.  
  16.         var decryptor = aesAlg.CreateDecryptor(aesAlg.Key, aesAlg.IV);
  17.         byte[] bytes = Convert.FromBase64String(cipherText);
  18.         using (var msDecrypt = new MemoryStream(bytes))
  19.         {
  20.             using (var csDecrypt = new CryptoStream(msDecrypt, decryptor, CryptoStreamMode.Read))
  21.             {
  22.                 using (var srDecrypt = new StreamReader(csDecrypt))
  23.                     plaintext = srDecrypt.ReadToEnd();
  24.             }
  25.         }
  26.     }
  27.     finally
  28.     {
  29.         if (aesAlg != null)
  30.             aesAlg.Clear();
  31.     }
  32.     return plaintext;
  33. }

 

The user can now save the URLs,  they will be directed to the login page (if not logged in) and then redirected to the page they saved. There is no ability for anyone to go fishing. If someone on the same computer finds the saved link, they will be asked for a login (they may be a valid user of the system) but the page will NOT grant them access to the data since it will fail to decode the parameters.

 

Someone searching the browser history will not find any information that is exploitable. A room mate with your wallet can’t call up and say “Hi, my name is Jack Smith, Account No 0392342 and I want to do a wire transfer, my SSN is…. “ The account number is not available in the browser history.

Wednesday, August 25, 2010

A Generic GetProperty

 

Bill from VisibleReality.com shipped me a generic version of the code that I forgot initially on my prior post. Here it is.

 

You need to include:

Code Snippet
  1. using System;
  2. using System.ComponentModel;

The code

Code Snippet
  1. /// <summary>
  2.     /// Gets the property value.
  3.     /// </summary>
  4.     /// <typeparam name="T">The type of the property value to get.</typeparam>
  5.     /// <param name="dataItem">The data item.</param>
  6.     /// <param name="field">The field.</param>
  7.     /// <param name="defaultValue">The default value.</param>
  8.     /// <returns>The property value.</returns>
  9.     public static T GetProperty<T>(this object dataItem, string field, T defaultValue = default(T))
  10.     {
  11.       if (dataItem == null)
  12.       {
  13.         throw new ArgumentNullException("dataItem");
  14.       }
  15.  
  16.       if (string.IsNullOrWhiteSpace(field))
  17.       {
  18.         return defaultValue;
  19.       }
  20.  
  21.       var props = TypeDescriptor.GetProperties(dataItem);
  22.       if (props.Count > 0)
  23.       {
  24.         if (props[field] != null && null != props[field].GetValue(dataItem))
  25.         {
  26.           return (T)props[field].GetValue(dataItem);
  27.         }
  28.  
  29.         for (var i = 0; i < props.Count; i++)
  30.         {
  31.           if (string.Compare(props[i].Name, field, StringComparison.OrdinalIgnoreCase) == 0)
  32.           {
  33.             return (T)props[i].GetValue(dataItem);
  34.           }
  35.         }
  36.       }
  37.  
  38.       return default(T);
  39.     }

Monday, August 23, 2010

Adding a DataDefaultField to a DropDownList

At times you want to return the default to show in an asp:DropDownList from the DataSource. Normally you have only two items:

  • DataTextField
  • DataValueField.

I will illustrate how you can add and use a DataDefaultField (boolean) to select a specific item without needing to do any C# in the page.

 

The steps are:

  1. Create a class that inherits from DropDownList
  2. Add in two private variable, defaultValue, isPostBack
  3. Code Snippet
    1. public class DropDownList : System.Web.UI.WebControls.DropDownList
    2. {
    3.   private string defaultValue;
    4.   private bool isPostback ;
  4. Do an override as shown below to detect PostBacks
    Code Snippet
    1. protected override void RaisePostDataChangedEvent()
    2.  {
    3.    isPostback = true;
    4.    base.RaisePostDataChangedEvent();
    5.  }
  5. Add a new Property
    Code Snippet
    1. public virtual string DataDefaultField { get; set; }
  6. Add another override (this picks up the (last) default value from the data.
    Code Snippet
    1. protected override void PerformDataBinding(IEnumerable data)
    2. {           
    3.   if (string.IsNullOrWhiteSpace(DataDefaultField))
    4.   {
    5.     DataDefaultField = "default";
    6.   }
    7.  
    8.   foreach (var dataItem in data)
    9.   {
    10.     bool isDefault;
    11.     if (bool.TryParse(dataItem.GetProperty(DataDefaultField), out isDefault) && isDefault)
    12.     {
    13.       defaultValue = dataItem.GetProperty(DataValueField);
    14.     }
    15.   }
    16.   base.PerformDataBinding(data);
    17. }
  7. Add the last override which sets the default value IF it is not a postback
  8. Code Snippet
    1. public override void RenderControl(HtmlTextWriter writer)
    2. {
    3.   if (! isPostback && !string.IsNullOrWhiteSpace(defaultValue))
    4.   {
    5.     var defaultItem = Items.FindByValue(defaultValue);
    6.     if (defaultItem != null)
    7.     {
    8.       SelectedIndex = -1;
    9.       defaultItem.Selected = true;
    10.     }
    11.   }
    12.   base.RenderControl(writer);
    13. }

That’s it. You can now set the default value in the datasource.

 

Oops.. Bill just pointed out that I used extensions that I did not include.. Here there are:

 

Code Snippet
  1. public static string GetProperty(this object dataItem, string field, string defaultValue)
  2.   {
  3.   if (string.IsNullOrWhiteSpace(field))
  4.     {
  5.     return defaultValue;
  6.     }
  7.  
  8.   var props = TypeDescriptor.GetProperties(dataItem);
  9.   if (props.Count > 0)
  10.     {
  11.     if (props[field] != null && null != props[field].GetValue(dataItem))
  12.       {
  13.       return props[field].GetValue(dataItem).ToString();
  14.       }
  15.  
  16.     for (var i = 0; i < props.Count; i++)
  17.       {
  18.       if (string.Compare(props[i].Name, field, StringComparison.OrdinalIgnoreCase) == 0)
  19.         {
  20.         return props[i].GetValue(dataItem).ToString();
  21.         }
  22.       }
  23.     }
  24.  
  25.   return null;
  26.   }

and

Code Snippet
  1. public static string GetProperty(this object dataItem, string field)
  2.   {
  3.   return GetProperty(dataItem, field, null);
  4.   }

Wednesday, August 11, 2010

Time to figure out your Christmas List Wish…

There are trial (fully functional) versions of software that I would recommend at: Typically 30 days. In your spare time you may wish to try

You may wish to download, install and "use the beegeeves out of each for 30 days" and then send summary to your significant others….

It is good to get your Christmas Wish List in early,,,,

Thursday, August 5, 2010

Linq DateTime.UtcNow and SQL DateTime Datatype

Recently I have being using SQLMetal that generates dbml and the backing code. I encountered a problem that was obtuse, apparently good code was failing to execute. The message from Linq was "Row not found or changed".

 

Launching SQL Profiler showed me the SQL

 

exec sp_executesql N'UPDATE [WebApp].[OperationInfo] SET [WorkflowResultsID] = @p8 WHERE ([MachineVersionID] = @p0) AND ([OperationID] = @p1) AND ([ExecutionDate] = @p2) AND ([OperationType] = @p3) AND ([WorkflowResultsID] IS NULL) AND ([ConfigurationResultsID] IS NULL) AND ([SequenceResultsID] IS NULL) AND ([ExecutionGuids] IS NULL) AND ([WorkRequestID] IS NULL) AND ([LastStep] = @p4) AND ([TrafficRuleCommitStatus] = @p5) AND ([CreatedUTC] = @p6) AND ([LastModifiedUTC] = @p7)', N'@p0 bigint,@p1 bigint,@p2 datetime,@p3 smallint,@p4 varchar(12),@p5 smallint,@p6 datetime,@p7 datetime,@p8 bigint',@p0=1,@p1=6,@p2='2010-08-05 09:56:47.2470000',@p3=1,@p4='Requirements',@p5=0,@p6='2010-08-05 09:56:47.2470000',@p7='2010-08-05 09:56:47.2470000',@p8=3

Manually executing, I got:

 

Msg 241, Level 16, State 1, Line 4

Conversion failed when converting date and/or time from character string.

 

Looking at the TSQL, I said huh???!!!???

 

I then tried an experiment.

 

Declare @p2 datetime

set @p2='2010-08-05 09:56:47'

set @p2='2010-08-05 09:56:47.247'

set @p2='2010-08-05 09:56:47.2470'

set @p2='2010-08-05 09:56:47.24700'

set @p2='2010-08-05 09:56:47.247000'

set @p2='2010-08-05 09:56:47.2470000'

The red ones gave the above error messages.

 

Tried it with:

 

Declare @p2 datetimeoffset

set @p2='2010-08-05 09:56:47'

set @p2='2010-08-05 09:56:47.247'

set @p2='2010-08-05 09:56:47.2470'

set @p2='2010-08-05 09:56:47.24700'

set @p2='2010-08-05 09:56:47.247000'

set @p2='2010-08-05 09:56:47.2470000'

Set @p2='2010-08-05 09:56:47.2470000'

Set @p2='2010-08-05 09:56:47.2470000'

Everything worked....

then with


Declare @p2 datetime2

set @p2='2010-08-05 09:56:47'

set @p2='2010-08-05 09:56:47.247'

set @p2='2010-08-05 09:56:47.2470'

set @p2='2010-08-05 09:56:47.24700'

set @p2='2010-08-05 09:56:47.247000'

set @p2='2010-08-05 09:56:47.2470000'

Set @p2='2010-08-05 09:56:47.2470000'

Set @p2='2010-08-05 09:56:47.2470000'

Everything worked....

 

In the C# code, I had changed the code from

 

OperationInfo.CreatedUTC=DateTime.Now;

 

to

 

OperationInfo.CreatedUTC=DateTime.UtcNow;

 

This change was causing the problem!

 

The solutions are

  • OperationInfo.CreatedUTC=DateTime.Now.ToUniversalTime();
  • Changing the database column types.

Friday, July 30, 2010

Time to get SCIENCE into Computer Science

Yesterday while hiking I listened to BBC Discovery and they were talking about the first controlled experiments on humans done by a British Navy Surgeon, James Lind, with people suffering from scurvy.  The closing comments about the difficulty of people to keep scientific (basing decisions on hard good data and not popular belief, religious belief, anecdotes etc) struck home with me.

 

Often I have had a discussions about which technology paths to take and usually end up using the following criteria for my recommendations:

  • Number of lines of code to produce (lower is better) intended results
  • Application performance – how fast is it going to run
  • Application scalability – can we handle bigger load
  • Expected time to completion: Expected in a mathematical sense:
    • Estimated Time x Probability of being correct.
  • Maintainability of code base, which breaks down into:
    • Availability on the market of people skilled enough to do maintenance and development
    • Trends on the market (growing or decreasing PERCENTAGE of people with those skill sets on the market).
    • Ramp-up time for learning needed skills

Often I find that the other side will not address these issues in our discussion. The discussion can go religious (or power play)  “This is the way we should do it…”, or everyone that I talked to says it’s a problem (all of your friends are JAVA/ORACLE developers)  i.e. anecdotes.

 

A lot of bad software decision comes from not being scientific. QUANTIFY the criteria and then make the decision. Get the SCIENCE back into Computer Science.

Sunday, July 25, 2010

SQL Server and Rich-Sparse Data (Real Estate) – Part I

Over the last two years I have been involved with two clients that deal with Real Estate (different market segments so no conflict of interest for me). I would describe real estate data as being sparse, rich data. The typical scenario is that the data on one house consists of MLS data and County Assessor data. In the case of SQL Server 2008R2,the number of fields involve quickly exceed the number of columns available in a SQL Server table (non-wide 1024 columns), but less than the number of columns of a wide table (30,000).  Wide tables were introduced in SQL Server 2008, so if you are forced to run on older versions of SQL Server the options changes.

 

In the case of wide tables, you are restricted to 4096 columns for Inserts, Select or Updates (despite having 30000 columns available). The wide table has a column set which is essentially an untyped XML that combines all the sparse columns [more info]. This means that you could use XML if you are running SQL Server 2005. There’s a bit of a dilemma here, using XML gives you the ability to update all 30000 rows by updating one XML column instead of having to sparse and create up to 8 inserts; the other side of the coin is that you would need to generate the computed columns etc from the XML.

 

For displaying data, I may need to do 8 selects with a wide column but can get all 30K attributes in a single column with a XML column type.

 

Before diving into these issues, I should cite the operation needs that I often encountered, namely:

  • A need to retrieve the original/earliest record in full
  • A need to list what changed between updates --- only what changed.
  • A need to query the current record.

For discussion purposes, we will assume that the data coming in is either XML or some format that may be converted to Xml with all of the properties being expressible as attributes in XML (i.e. a flaten structure – in some cases, it means doing cross-products of child data). The logic become:

  • Get the key from the Xml
  • If the key exists in the table, go to Update.
  • Insert:
    • Insert into [OriginalXml]
    • Insert into [CurrentXml]
    • Insert record with no attributes into [DeltaXml] with:
      • Received/Processed date
      • VersionNo  =0
      • End of Processing
  • Update:
    • Retrieve record from [CurrentXml]
    • Update [CurrentXml]
    • Walk all attributes in currentRecord and newRecord and retain only those attributes that have changed.  Use a XmlTextReader for performance.
      • Insert into [DeltaXml] the resulting Xml with:
        • VersionNo=Max(VersionNo)+1 for key
    • End of Processing.

Now if a user wants to see the history of a property, it is easy to show, just do a select on DeltaXml and spit out the items, for example:

  1. Dec 11, 2009 <key=”3” AP=”209000” BTH=”4” />
    1. Asking Price Changed: $209,000
    2. Bathrooms Changed: 4
  2. Dec 31, 2009 <key=”3”  BTH=”3” />
    1. Bathrooms Changed: 3
  3. Jan 10, 2010 <key=”3” AP=”199000”  />
    1. Asking Price Changed: $199,000

In the above example, the original Bathroom count may have been 3, it was changed to 4 (clerical error?), and then changed back to 3.  You get performance in retrieving the data because you do not have to walk 30K fields. Additionally, you have the complete history available (update by update) by just applying each deltaRecord to the originalRecord without sucking up massive storage requirements.

In theory, you could discard the original (saving space) and work backwards from the currentRecord but user usage is that they want to see the originalRecord – so I rather omit the overhead and performance hit of doing a reconstruction. Second, having both the [OriginalXml] and [CurrentXml] allows audits to be done: 

  • [OriginalXml] +[DeltaXml] = [CurrentXml]

In Part II I will look at the issues in searching records and performance differences between raw XML and wide tables.