Mask values in a SQL Query string for SQL Server

Question

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 ('***', '**');

Hard to do this using regular SQL Server code. If your stored proc has access to a regex replacement function, then replace '.*?' with '****'. — Tim Biegeleisen
– Tim Biegeleisen, Commented Aug 8, 2022 at 6:03
@TimBiegeleisen Yeah. that is a good recommendation. Any suggestion to deal with integer as well like in the third example? — Raman
– Raman, Commented Aug 8, 2022 at 6:11
Actually doesn't seem like I have it, but I am thinking it might be not too hard to write a regex replace method in sql... — Raman
– Raman, Commented Aug 8, 2022 at 6:22
Doing anything useful like performance tuning is going to be hit and miss without parameter values. What's the actual end goal? Do you have an XY problem? — Mitch Wheat
– Mitch Wheat, Commented Aug 8, 2022 at 7:02

VaZeR · Accepted Answer · 2022-08-08 06:48:05Z

2

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.

answered Aug 8, 2022 at 6:48

VaZeR

6761 gold badge5 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Dávid Laczkó Over a year ago

Are you sure it is not your client application that transforms the queries to that form? When I append where r.session_id = 99 to your query and run it, I get back the entire query as is, i.e. it has 99 coded in it without any @P1 introduced.

Charlieface Over a year ago

@DávidLaczkó This is because the query is parameterized, as all queries should be. The above output is what XEvents and SQL Profiler return in such cases.

Dávid Laczkó Over a year ago

@Charlieface But then does this mean that this can not be a solution for the OP's queries, which are not parameterized, apparently? EDIT: I've just seen you asked this in comment.

Mikael Eriksson · Accepted Answer · 2022-08-08 07:10:52Z

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.

I really like this solution Mikael, the only case which I am not able to figure out the case where there is a number in the table name. something like where table name is mytable878 it gets changed to mytable*** right now..

lptr · Accepted Answer · 2022-08-18 21:08:16Z

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;

Collectives™ on Stack Overflow

Mask values in a SQL Query string for SQL Server

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related