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.