CODEWITHSUNDEEP
sqlsql-serverdata-maskingpii
Raman
Raman

Reputation: 226

Mask values in a SQL Query string for SQL Server

I am a SQL Server DBA. I would like to write a procedure which I can provide to rest of my team where they can view the text for currently running queries on the server (Similar to how we view in sp_who2) but with all the values masked. Examples:

Query text Query text after Masking
Select * from sometable where rating = '4' Select * from sometable where rating = '****'
Select name, id from sometable where id = '3233' Select name, id from sometable where id = '****'
UPDATE Customers SET ContactName = 'Alfred Schmidt' WHERE CustomerID = 1; UPDATE Customers SET ContactName = '****' WHERE CustomerID = ****;
INSERT INTO Customers (CustomerName, ContactName) VALUES ('Cardinal', 'Tom B. Erichsen'); INSERT INTO Customers (CustomerName, ContactName) VALUES ('*****', '****');

Upvotes: 1

Views: 2370

Answers (3)

lptr
lptr

Reputation: 6798

sys.sp_get_query_template

fiddle

declare @t nvarchar(max), @p nvarchar(max);
declare @q nvarchar(max) = 'UPDATE Customers SET ContactName = N''Alfred Schmidt'' WHERE CustomerID = 1 AND Rate = 0.75 AND Rver = 0x0102 AND DateCreated = dateadd(day, -10, ''202z0818'')';

exec sys.sp_get_query_template @querytext = @q, @templatetext = @t OUTPUT, @parameters = @p OUTPUT;

select p,
  case when tp like '%int' then cast('****' as nvarchar(40))
  when tp like 'decimal(%' or tp like 'numeric(%' then '**.**'
  when tp like '%binary(%' then '0x****'
  when tp like 'n%char%' then 'N''****'''
  else '''****'''
  end as rv
into #t
from
(
select *, '@'+left(s.value, charindex(' ', s.value+' ')-1) as p, stuff(s.value, 1, charindex(' ', s.value), '') as tp
from string_split(replace(@p, ',@', '@'), '@') as s
where s.value <> ''
) as ss;

update #t
set @t = replace(@t, p, rv);

select @q union all select @t;

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 138980

You could try some XML-trickery to handle the strings. First replace all single quotes with an empty tag <X/> to get a XML that looks like this.

INSERT INTO Customers (CustomerName, ContactName) 
  VALUES (<X />Cardinal<X />, <X />Tom B. Erichsen<X />);

Then you shred the xml to get the text nodes and the node numbers where mod 2 is 0 is the ones you want to mask.

After that you can rebuild your query string using the mask values.

I have not found a way to deal with numbers other then removing all numbers from the query using Translate or nested replace and that will of course also remove numbers from table names and column names as well.

You could try something like this.

declare @S nvarchar(max);
declare @X xml;

set @S = N'UPDATE Customers SET ContactName = ''Alfred Schmidt'' WHERE CustomerID = 1;';

set @X = replace(@S, '''', '<X/>');

with C as
(
  select T.X.value('.', 'nvarchar(max)') as V, 
         row_number() over(order by T.X) as RN
  from @X.nodes('text()') as T(X)
)
select @S = (
            select case when C.RN % 2 = 0 then '''*****''' else C.V end
            from C
            order by C.RN
            for xml path(''), type
            ).value('text()[1]', 'nvarchar(max)');

set @S = translate(@S, '0123456789', '**********')

print @S;

Result:

UPDATE Customers SET ContactName = '*****' WHERE CustomerID = *;

Note: Just realized that this solution does not handle the cases where the string values contains single quotes but I think this is something that possibly can inspire more robust solution so I will leave it here.

Upvotes: 1

igdmitrov
igdmitrov

Reputation: 546

If I understand correctly your issue. You can use this query:

select 
    r.session_id,
    r.status,
    r.command,
    r.cpu_time,
    r.total_elapsed_time,
    t.text
from sys.dm_exec_requests as r
cross apply sys.dm_exec_sql_text(r.sql_handle) as t

e.g. I run it on my SQL server right now:

(@P1 nvarchar(5),@P2 bigint,@P3 int,@P4 numeric(28, 12),@P5 nvarchar(5),@P6 datetime,@P7 datetime)
SELECT SUM(A.SETTLEAMOUNTCUR) FROM CUSTSETTLEMENT A,CUSTTRANS B WHERE ((A.DATAAREAID=@P1) AND (((A.TRANSRECID=@P2) AND (A.CANBEREVERSED=@P3)) AND (A.SETTLEAMOUNTCUR<>@P4))) AND ((B.DATAAREAID=@P5) AND (((B.RECID=A.OFFSETRECID) AND (B.TRANSDATE>=@P6)) AND (B.TRANSDATE<=@P7)))

All variables are hidden.

Upvotes: 2

Related Questions