My text

Data scrambling in SQL

When developing an application, it can often be quite useful to utilise the production data for testing. However, the danger of this is that production data is then being stored on the relatively less-secure development devices. The answer to this solution is to scramble the data in such a way that the original information is no longer recoverable, but it still takes a similar form.

For simplicity, random 0-9a-zA-Z strings are the core of this, using the function RAND().

Note: RAND() is not cryptographically-secure, but that's ok in this scenario as we're generating these values independently of the original data. If you need cryptographically-secure random values, use CRYPT_GEN_RANDOM()).

Script Architecture

Yes - unfortunately due to SQL constrains there needs to be mild consideration of architecture. Namely, we want to use scalar functions as the generators of random values as these will fit in easily to an UPDATE script. However, SQL does not permit the use of RAND() inside a scalar function, as running RAND() has side-effects on the DB (due to the deterministic nature I presume). We can bypass this by creating a minimal view which returns a RAND() value:

CREATE VIEW [dbo].[vw_RandomNumber]

AS

SELECT RAND() AS RandomNumber

GO

This forms the basis of our random data functions.

Functions

RandomString

CREATE function [dbo].[udf_RandomString](@length INTEGER)   

    RETURNS varchar(MAX) AS BEGIN

DECLARE @charPool VARCHAR(MAX) =

                 'abcdefghijkmnopqrstuvwxyzABCDEFGHIJKLMNPQRSTUVWXYZ23456789'

DECLARE @poolLength INTEGER = Len(@charPool)

DECLARE @loopCount INTEGER = 0

DECLARE @randomString VARCHAR(MAX) = ''

WHILE (@loopCount < @Length) BEGIN

SELECT @randomString = @randomString +
            SUBSTRING(@charPool, CONVERT(int, 
            (SELECT TOP 1 RandomNumber FROM vw_RandomNumber) * @poolLength), 1)

SELECT @loopCount = @loopCount + 1

END

    RETURN (@randomString)  

END 

This script does not generate the lowercase letter "l" and number "1", and capital letter "O" and number "0" due to their similar appearance. Maybe slightly overkill, but hey!

RandomEmail

CREATE function [dbo].[udf_RandomEmail](@length INTEGER)   

    RETURNS varchar(MAX) AS BEGIN

    RETURN LOWER((dbo.udf_RandomString(@length) + '@email.com')  )

END  

Full script example

UPDATE WEB_Booking

SET JobName = dbo.udf_RandomString(12),

DeliveryAddress = '123 ' + dbo.udf_RandomString(6) + ' Rd',

DeliverySuburb = dbo.udf_RandomString(8),

DeliveryState = 'NSW',

DeliveryPostcode = '2000',

PickupAddress = '123 ' + dbo.udf_RandomString(6) + ' Rd',

PickupSuburb = dbo.udf_RandomString(8),

PickupState = 'NSW',

PickupPostcode = '2000',

SiteContactName = dbo.udf_RandomString(4) + ' ' + dbo.udf_RandomString(6),

SiteContactNumber =  '04 1234 5678'

Numbers have not yet been scrambled, this is a potential future improvement though it was considered low value for this project, as there was no need for addresses or contact numbers to be unique and diverse.

Troubleshooting: .NET MVC RedirectToAction chopping out domain

Problem:

After using RedirectToAction({action}, {controller}, {parameters}), the new URL has the domain chopped out. E.g.:

expected: http://localhost:5050/gift-certificates/success

actual: http://gift-certificates/success

This subsequently causes a failed page load, as it's become a redirect to an external (and likely non-existent) page.

Solution:

Ensure that the Route is fully and correctly mapped in the global.asax setup

Integrate new Web API with old WebForms Cookies

To make Web API 2 with Identity 2.0 and OWIN accept default cookies from WebForms you need to configure CookieAuthentication in the following way:

public partial class Startup
{
 public void ConfigureAuth(IAppBuilder app)
 {
  app.UseCookieAuthentication(new CookieAuthenticationOptions()
  {
   CookieName = FormsAuthentication.FormsCookieName,
   CookieDomain = FormsAuthentication.CookieDomain,
   CookiePath = FormsAuthentication.FormsCookiePath,
   CookieSecure = CookieSecureOption.SameAsRequest,
   AuthenticationMode = AuthenticationMode.Active,
   ExpireTimeSpan = FormsAuthentication.Timeout,
   SlidingExpiration = true,
   AuthenticationType = CustomAuthenticationTypes.FormsAuthenticationType,
   TicketDataFormat = new SecureDataFormat(
    new FormsAuthTicketSerializer(),
    new FormsAuthTicketDataProtector(),
    new HexEncoder())
  });
 }
}

First you have to add a reference to "System.Web.Security" to be able to reference the FormsAuthentication object with has static fields that expose the default cookie name, domain, path and timeout. Note if you have set a non default value for these options in your WebForms Project you need to set them to your new settings.

The AuthenticationType is just a string to identify between types of authentication in your OWIN pipeline

The SecureDataFormat is an object allocates how the incoming data (a FormsAuthenticationTicket from WebForms  in this case) should be deserialized, unprotected and decoded or the opposite for an outgoing Identity

The FormsAuthTicketSerializer, FormsAuthTicketDataProtector, and HexEncoder are all custom classes which are described below

HexEncoder
public class HexEncoder : ITextEncoder
{
 public string Encode(byte[] data)
 {
  return ToHexadecimal(data);
 }

 public byte[] Decode(string text)
 {
  return ToBytesFromHexadecimal(text);
 }

 public static string ToHexadecimal(byte[] ba)
 {
  StringBuilder hex = new StringBuilder(ba.Length * 2);
  foreach (byte b in ba)
   hex.AppendFormat("{0:x2}", b);
  return hex.ToString();
 }

 public static byte[] ToBytesFromHexadecimal(string hex)
 {
  int NumberChars = hex.Length;
  byte[] bytes = new byte[NumberChars / 2];
  for (int i = 0; i < NumberChars; i += 2)
   bytes[i / 2] = Convert.ToByte(hex.Substring(i, 2), 16);
  return bytes;
 }
} 

The HexEncoder is a class that is used by CookieAuthentication and the FormsAuthTicketDataProtector to simply convert a hexadecimal string to bytes and vice versa


FormsAuthTicketDataProtector
public class FormsAuthTicketDataProtector : IDataProtector
{
 public byte[] Protect(byte[] userData)
 {
  FormsAuthenticationTicket ticket;
  using (var memoryStream = new MemoryStream(userData))
  {
   var binaryFormatter = new BinaryFormatter();
   ticket = binaryFormatter.Deserialize(memoryStream) as FormsAuthenticationTicket;
  }

  if (ticket == null)
  {
   return null;
  }

  try
  {
   return HexEncoder.ToBytesFromHexadecimal(FormsAuthentication.Encrypt(ticket));
  }
  catch
  {
   return null;
  }
 }

 public byte[] Unprotect(byte[] protectedData)
 {
  FormsAuthenticationTicket ticket;
  try
  {
   ticket = FormsAuthentication.Decrypt(HexEncoder.ToHexadecimal(protectedData));
  }
  catch
  {
   return null;
  }

  if (ticket == null)
  {
   return null;
  }

  using (var memoryStream = new MemoryStream())
  {
   var binaryFormatter = new BinaryFormatter();
   binaryFormatter.Serialize(memoryStream, ticket);

   return memoryStream.ToArray();
  }
 }
}

The FormsAuthTicketDataProtector is a class that encrypts a FormsAuthenticationTicket to a byte stream and decrypts a FormsAuthenticationTicket from a byte stream


FormsAuthTicketSerializer
public class FormsAuthTicketSerializer : IDataSerializer
{
 public AuthenticationTicket Deserialize(byte[] data)
 {
  using (var dataStream = new MemoryStream(data))
  {
   var binaryFormatter = new BinaryFormatter();
   var ticket = binaryFormatter.Deserialize(dataStream) as FormsAuthenticationTicket;
   if (ticket == null)
   {
    return null;
   }

   var identity = AccountService.CreateIdentity(ticket.Name, CustomAuthenticationTypes.FormsAuthenticationType);
   var authTicket = new AuthenticationTicket(identity, new AuthenticationProperties());

   authTicket.Properties.IssuedUtc = new DateTimeOffset(ticket.IssueDate);
   authTicket.Properties.ExpiresUtc = new DateTimeOffset(ticket.Expiration);
   authTicket.Properties.IsPersistent = ticket.IsPersistent;
   return authTicket;
  }
 }

 public byte[] Serialize(AuthenticationTicket model)
 {
  var userTicket = new FormsAuthenticationTicket(
    2,
    model.Identity.Claims.Single(c => c.Type == ClaimTypes.Name).Value,
    new DateTime(model.Properties.IssuedUtc.Value.UtcDateTime.Ticks, DateTimeKind.Utc),
    new DateTime(model.Properties.ExpiresUtc.Value.UtcDateTime.Ticks, DateTimeKind.Utc),
    model.Properties.IsPersistent,
    "",
    FormsAuthentication.FormsCookiePath);

  using (var dataStream = new MemoryStream())
  {
   var binaryFormatter = new BinaryFormatter();
   binaryFormatter.Serialize(dataStream, userTicket);

   return dataStream.ToArray();
  }
 }
}

Finally the FormsAuthTicketSerializer serialises the FormsAuthenticationTicket from bytes to an OWIN based AuthenticationTicket and vice versa. This new AuthenticationTicket is what OWIN will use to create the users identity further down the process.

This technique is useful if you are wanting to migrate a large WebForms site to the newer MVC and Web API standards incrementally because it allows you to move all the business logic to a WebAPI while still maintaining the WebForms front end interface

Having a standalone RadImageManager

The Telerik RadImageManager is a handy control for uploading / managing and selecting images, however it is embedded into the Rich Text editor control (RadEditor). To use it as a standalone tool you can use the following method:

In the front end view do the following:

1. Add into Web.Config the telerik dialog handler declaration
2. Declare a RadDialogOpener
3. Declare a hidden field to store the domain path to be used in the URL
4. Declare a textbox to store the URL
5. Create an image icon to launch the Image Manager.

Add the following into the Web.config file:

<system.web>
    <httpHandlers>
     ....
      <add path="Telerik.Web.UI.DialogHandler.aspx" verb="*" type="Telerik.Web.UI.DialogHandler, Telerik.Web.UI"/>
    </httpHandlers>
</system.web>


Additional code in the aspx file:

<telerik:RadDialogOpener runat="server" ID="dop1" />
<asp:HiddenField ID="hfImageServer" runat="server" ClientIDMode="Static" />

<asp:TextBox ID="tbxImageURL" runat="server" Width="500" Text='<%#Bind("ImageURL")%>' MaxLength="256" ClientIDMode="Static" />
<img src="/images/icon/photo.png" onclick="$find('<%= dop1.ClientID %>').open('ImageManager', {CssClasses: []});return false;" width="16" height="16" alt="Image Manager for Image URL" title="Image Manager" />


In the code behind set up a function to initialise the Image Manager with appropriate property values. This can be reused for multiple Dialog Openers

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
  SetupImageManager(dop1)
End Sub

Private Sub SetupImageManager(ByVal dop As RadDialogOpener)
  hfImageServer.Value = "http://www.mydomain.com"

  Dim imageManagerParameters As New FileManagerDialogParameters()
  imageManagerParameters.ViewPaths = New String() {"~/UploadFolderName"}
  imageManagerParameters.UploadPaths = New String() {"~/UploadFolderName"}
  imageManagerParameters.DeletePaths = New String() {"~/UploadFolderName"}
  'imageManagerParameters.MaxUploadFileSize = 102400
  'imageManagerParameters.SearchPatterns = new string[] { "*.jpg" };

  Dim imageManager As New DialogDefinition(GetType(ImageManagerDialog), imageManagerParameters)
  imageManager.ClientCallbackFunction = "ImageManagerFunction" & dop.ID
  imageManager.Width = Unit.Pixel(600)
  imageManager.Height = Unit.Pixel(500)
  imageManager.Parameters("ExternalDialogsPath") = "~/ExternalDialogs/"

  dop.DialogDefinitions.Add("ImageManager", imageManager)

  Dim imageEditorParameters As New FileManagerDialogParameters()
  imageEditorParameters.ViewPaths = New String() {"~/UploadFolderName"}
  imageEditorParameters.UploadPaths = New String() {"~/UploadFolderName"}
  imageEditorParameters.DeletePaths = New String() {"~/UploadFolderName"}
  'imageEditorParameters.MaxUploadFileSize = 102400

  Dim imageEditor As New DialogDefinition(GetType(ImageEditorDialog), imageEditorParameters)
  imageEditor.Width = Unit.Pixel(600)
  imageEditor.Height = Unit.Pixel(500)
  dop.DialogDefinitions.Add("ImageEditor", imageEditor)
End Sub

In the Javascript file have a callback function to populate the text field value

function ImageManagerFunctiondop1(sender, args) {
    if (!args) {
        alert('No file was selected!');
        return false;
    }
    var txt = $get('tbxImageURL');
    var path = args.value.getAttribute("src", 2);
    txt.value = $('#hfImageServer').val() + path;
}

How to manage Local Asp.Net Membership Users easily

During development you can manage local Asp.Net Membership Users easily through the .Net Users section in Internet Information Services Manager (IIS Manager).

Prerequisites

• IIS Manager is installed on your local machine. Search for IIS Manager in your programs or follow this to install (Install IIS Manager)
• Visual Studio Project that uses Membership and has a valid connection to your local database
• Valid database setup to store Membership Users

Steps

1. Create a new Website (Right Click Sites > Add a Website)
2. Set the application path to the folder containing your target project in the pop up
3. Navigate to the .Net Users section (see below)
4. From here you can add, edit and delete all the Membership Users in the database

 

iOS Development Signing identities in a tangle?

If you're using a developer account on only one Mac, you can get away with clicking "Fix issues" and letting XCode do hacky patch-ups to get things sorted.

Otherwise, you need to properly sort your certs out.

How to sort it out, the slash and burn way:

1. From now on, stop using the "Fix issue" buttons and inform everyone on the team likewise - a single "Fix issues" can break everything if it resets the certs.
2. Clear Keychain of old dev account keys on all Macs
1. Open Keychain Access
2. In top left sidebar, login
3. In bottom left sidebar, Keys
4. Find keys relating to dev account - these are based on the original certificate request naming, so it could be anything! There'll be a private key and an associated certificate, and possibly a public key. Likely a set of these for both Development and Distribution. Delete these.
3. Open https://developer.apple.com/account/ios/certificate/ and revoke all certificates
4. Generate certificate requests for both Development and Distribution on one Mac (this one will be the only one with public keys, from what I've found)
1. Open Keychain Access
2. Go to Keychain Access > Certificate Assistant > Request Certificate from a Certificate Authority
3. Fill in email address
4. Fill in Common Name with something like "{dev account email} - {distribution/development}"
5. Select Save to disk instead of "Emailed to the CA"
5. Create new certificates via https://developer.apple.com/account/ios/certificate/ for both Development & Distribution, following the fairly self-explanatory wizard. Download and double-click on the "master" Mac so that they're saved via Keychain Access.
6. Export the private key/certificate pairs
1. Open Keychain Access
2. In top left sidebar, select login
3. In bottom left sidebar, Keys
4. Find the keys, export with some one-time use password
7. Import the private key/certificate pairs onto the rest of the Macs
1. Get key files onto rest of Macs, double-click in Finder and save via Keychain Access
8. Create required provisioning profiles via https://developer.apple.com/account/ios
9. Set up dev accounts on all Macs
1. XCode > Preferences > Accounts
2. Add account, log in via credentials
3. Select the Agent user, view details
4. Download All - this not only does provisioning profiles, but seems to fix something up with the signing identities