Replacing literals

Foglight replaces string and numeric literals in uploaded SQL statements and queries by default as they may contain sensitive or Personally Identifiable Information (PII). The placeholder data has no reference to the original data, cannot be reversed into the original data, and has no association with the original data.

Configure the replace literals feature

To configure the replace literals feature perform the following steps:

1. Login to Foglight with your Quest account.

2. Select the Databases dashboard on the left navigation pane.

3. Click Settings and select Replace literals. You can view two options: Yes or No.

   Only users who have Admin or Owner roles, have permission to configure replace literals.

   Select Yes to enable replace literals. This is the default setting for Foglight Cloud. Foglight replaces literals found in SQL statements and queries with placeholder data. This placeholder data has no reference to the original data which may contain sensitive or Personally Identifiable Information (PII). This placeholder data cannot be reversed into the original data and has no association with that data.

   Select No to disable replace literals. This is the default setting for Foglight on-prem. Foglight will no longer replace literals in SQL statements and queries with placeholder data. String literals inside uploaded SQL statements and queries may contain sensitive or Personally Identifiable Information (PII).

   Queries and plans are collected based on their ID. If a certain query or plan is already collected and saved, it will not be collected again. Hence, if ‘Remove literals’ is enabled and it is changed to disable, queries already collected will still appear with literals replaced even if they are executed again. The vice versa is also correct.

4. Select the checkboxes for documentation and state change.

5. Click Save.

Expected output replacing string literals

Foglight replaces string literals with placeholder data. The placeholder data reads: ** Removed by Foglight **.

Following are some examples:

Before uploading to Foglight	On upload to Foglight string literals are replaced
select @dog	select @dog
select ‘dog’	select ‘** Removed by Foglight **’
select ‘dog’ and ‘TIGER	select ‘** Removed by Foglight **’ and ‘** Removed by Foglight **
select ‘dog’ SET QUOTED_IDENTIFIER OFF; select “tiger”	select ‘** Removed by Foglight **’ SET QUOTED_IDENTIFIER OFF; select “** Removed by Foglight **”
select ‘dog’ SET QUOTED_IDENTIFIER OFF; select “tiger” SET QUOTED_IDENTIFIER ON select “wolf”	select ‘dog’ SET QUOTED_IDENTIFIER OFF; select “tiger” SET QUOTED_IDENTIFIER ON select “wolf”.
select ‘dog’ /* this is a comment ‘Next’ line */ ‘frog’	select ‘** Removed by Foglight **’ /* this is a comment ‘Next’ line */ ‘** Removed by Foglight **’
select payment where ccn = ‘12345678901234’;	select payment where ccn = ‘** Removed by Foglight **';
‘select top(526) from aaa’	‘** Removed by Foglight **’
begin – get the data select target_data from sys.dm_xe_sessions s join sys.dm_xe_session_targets t on t.event_session_address = s.address where s.name = N’FoglightWorkloadAnalysis_MEL602102_3843_mel602102_sql2008r2_sqlserver’ and t.target_name = N’ring_buffer’ – stop session	begin – get the data select target_data from sys.dm_xe_sessions s join sys.dm_xe_session_targets t on t.event_session_address = s.address where s.name = N’** Removed by Foglight **' and t.target_name = N'** Removed by Foglight **' – stop session
select ‘dog’ ‘hare’	select ‘** Removed by Foglight **’ and ‘** Removed by Foglight **’
select ‘dog’ – ‘hare’	select ‘** Removed by Foglight **’ – ‘hare’
select ‘dog’ – ‘hare’ –	select ‘** Removed by Foglight **’ – ‘hare’ –
select ‘dog’ and “hare”	select ‘** Removed by Foglight **’ and “hare”
select ‘dog’ and “hare” and ‘dog’	select ‘** Removed by Foglight**’ and “hare” and ‘** Removed by Foglight **’
select ’d"og' and ‘hare’ and ’d"og'	select ‘** Removed by Foglight***’ and ‘** Removed by Foglight **’ and ‘** Removed by Foglight **’

Expected output replacing numeric literals

Foglight replaces numeric literals with placeholder data. The placeholder data is a series of digits where the first digit is 1, the remaining digits are 0, the number of digits in the placeholder data is the same as in the original data. Following are some examples.

Foglight comments at the end of each SQL statement where numeric literals have been replaced: /* Foglight replaces numeric literals with placeholder data prior to uploading to Foglight. Refer to the Foglight help for more information. */

Before uploading to Foglight	On upload to Foglight numeric literals are replaced
select payment where ccn = 12345678901234;	select payment where ccn = 10000000000000;
select payment where other = 0;	select payment where other = 1;
declare @Cmd nvarchar(255);	declare @Cmd nvarchar(100);
if @tmp=0.0;	if @tmp=1.0;
select top(526) from aaa	select top(100) from aaa
select payment where other = +12345;	select payment where other = +10000;
select payment where other = -12345;	select payment where other = -10000;

Not all numbers are replaced

Sample	Data not replaced
/* You are number 66666. */	Numbers inside comments are not replaced
SELECT col1, col2 FROM AdventureWorks2014.dbo.sometable	Numbers in variable names are not replaced.
declare @123 int	Numbers in variable names are not replaced.