Skip to content

Instantly share code, notes, and snippets.

@Patrick-Kelley

Patrick-Kelley/(MS-SQL) TDS data streams

Created January 24, 2023 17:23
Show Gist options
  • Save Patrick-Kelley/5260d0b77ed6cdb3cb022a14fe912cb5 to your computer and use it in GitHub Desktop.
Save Patrick-Kelley/5260d0b77ed6cdb3cb022a14fe912cb5 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
(MS-SQL) TDS data streams
######################################################
# Bro script to parse (MS-SQL) TDS data streams and #
# extract SQL statements and TDS7 login information. #
# #
# parsetds.bro v2014.01.27 #
# https://malwaremusings.com/scripts/parsetds.bro #
# #
# usage: #
# Place the script in $BRO_HOME/share/bro/site #
# and either load it from local.bro with #
# @load parsetds.bro #
# or call it directly from bro's command line #
# bro ... parsetds.bro #
######################################################
#
# Give ourself a name
#
module TDS;
###
# Exports
###
export {
#
# Our log IDs -- these determine log file names
# We'll use two log files
# one for TDS packets
# one for SQLBatch requests
# one for TDS7 Login packets
#
redef enum Log::ID += { LOG };
redef enum Log::ID += { SQLBATCH };
redef enum Log::ID += { TDS7LOGIN };
#
# The information that is logged for each TDS packet
#
type TDSInfo: record {
ts: time &log;
uid: string &log;
id: conn_id &log;
tdstype:count &log;
};
#
# The information logged for each SQLBatch request
#
type TDSSQLBatchInfo: record {
ts: time &log;
uid: string &log;
id: conn_id &log;
sqlbatch:string &log;
};
#
# The information logged for each TDS7 Login packet
#
type TDSTDS7LoginInfo: record {
ts: time &log;
uid: string &log;
id: conn_id &log;
tdsver: count &log;
clientver: count &log;
timezone: count &log;
clientname: string &log;
username: string &log;
password: string &log;
appname: string &log;
servername: string &log;
libraryname: string &log;
dbname: string &log;
};
}
###
# Types
###
#
# A buffer data structure for reassembling TDS packets from TCP segment data in IP packets
#
type TDSPktBuffer: record {
pktlen: count; # Holds the length of the TDS packet, from the TDS header
pktoff: count; # Points to the offset, in pkt, of the start of the next TDS packet
pkt: string; # Used to combine TCP segment data in to TDS packets
};
###
# Variables
###
#
# Request the contents of port 1433/tcp connections from originator (client) to receiver (server)
#
redef tcp_content_delivery_ports_orig += { [1433/tcp] = T };
#
# Our TDS packet buffer
#
global tdspktbuffer: table[string] of TDSPktBuffer;
#
# A buffer that can be used to reassemble SQLBatch commands from data in TDS packets
#
global sqlbatch: table[string] of string;
###
# Functions
###
#
# Function to extract variable length fields from a block of data
#
# varlendata: A block of data containing the offsets and lengths
# of the fields to extract
# fielddata: A block of data containing the actual fields
# fieldidxs: A vector containing the indices of the fields to
# extract (first field is index 0).
#
# Returns a vector of srings, being the values of the requested fields
#
function extract_varlen_fields(varlendata: string,fielddata: string,fieldidxs: vector of count):vector of string {
local i: count;
local fields: vector of string;
local value: string;
#
# E.2 extract each of the variable length values
#
for (i in fieldidxs) {
#
# Calculate the location of the offset
#
local o = fieldidxs[i] * 4;
#
# Calculate the location of the length
#
local l = o + 2;
#
# Extract the offset
#
local begin = bytestring_to_count(varlendata[o:o + 1],T);
#
# Extract the length
#
local length = bytestring_to_count(varlendata[l:l + 1],T);
#
# Calculate the offset of the last byte of the value
#
local end = begin + ((length - 1) * 2);
if (length > 0) {
#
# If the field is present, then badly convert it from
# UTF-16 to ASCII by removing null bytes
#
fields[fieldidxs[i]] = subst_string(fielddata[begin:end],"\0","");
} else {
#
# If the field isn't present, return an empty string
# so the caller doesn't generate an error by requesting it
# (causes the logging code to log it as '(empty)')
#
fields[fieldidxs[i]] = "";
}
}
return fields;
}
###
# Event handlers
###
#
# Called when Bro initialises
# Used to create our logging streams
#
event bro_init() &priority=5 {
Log::create_stream(TDS::LOG, [$columns=TDSInfo]);
Log::create_stream(TDS::SQLBATCH, [$columns=TDSSQLBatchInfo]);
Log::create_stream(TDS::TDS7LOGIN, [$columns=TDSTDS7LoginInfo]);
}
#
# A locally generated event called when a complete SQLBatch request is received
# This event handler just logs the SQLBatch request
#
event tds_sqlbatch(c:connection,data:string) &priority=-5 {
local tdssqlbatchinfo: TDSSQLBatchInfo = TDSSQLBatchInfo($ts = network_time(),$uid = c$uid,$id = c$id,$sqlbatch = data);
Log::write(TDS::SQLBATCH, tdssqlbatchinfo);
}
#
# A locally generated event called when a complete TDS7 Login request is received
# This event handler just extracts the information and logs it
#
event tds_tds7login(c:connection,data:string) &priority=-5 {
#
# E.1 get variables from TDS7 Login packet header
#
local login_totlen = bytestring_to_count(data[0:3],T);
local login_tdsver = bytestring_to_count(data[4:7],T);
local login_clntver = bytestring_to_count(data[12:15],T);
local login_tz = bytestring_to_count(data[28:31],T);
#
# Call a function to extract the variable length values
#
local login_lenoff = data[36:85];
local fieldnums: vector of count = {0,1,2,3,4,6,8};
local fields = extract_varlen_fields(login_lenoff,data,fieldnums);
#
# Log it... Log it good...
#
local tdstds7logininfo: TDSTDS7LoginInfo = TDSTDS7LoginInfo($ts = network_time(),$uid = c$uid,$id = c$id,$tdsver = login_tdsver,$clientver = login_clntver,$timezone = login_tz,$clientname = fields[0],$username = fields[1],$password = fields[2],$appname = fields[3],$servername = fields[4],$libraryname = fields[6],$dbname = fields[8]);
Log::write(TDS::TDS7LOGIN,tdstds7logininfo);
}
#
# A locally generated event called when a complete TDS packet has been received
# This event handler just logs the TDS packet type
#
event tds_packet(c:connection, data:string) &priority=-5 {
local pkttype = bytestring_to_count(data[0]);
local tdsinfo: TDSInfo = TDSInfo($ts = network_time(),$uid = c$uid,$id = c$id,$tdstype = pkttype);
Log::write(TDS::LOG, tdsinfo);
}
#
# A locally generated event called when a complete TDS packet has been received
# This event handler combines multiple SQLBatch TDS packets in to an SQLBatch request
#
event tds_packet(c:connection, data:string) {
#
# Get the packet type from the header
#
local pkttype = bytestring_to_count(data[0]);
#
# Only process non-SSL packet types
#
if (pkttype != 0x17) {
#
# B. Get the various packet header variables
# (packet status, length, spid (TDS documentation)/channel number (Wireshark), packet ID, and window)
#
local pktstat = bytestring_to_count(data[1]);
local pktspid = bytestring_to_count(data[4:5]);
local pktpktn = bytestring_to_count(data[6]);
local pktwin = bytestring_to_count(data[7]);
#
# C. Get the TDS packet's data payload and check the packet type
#
local tdspkt = data[8:|data| - 1];
#
# D. Handle SQL Batch packet
#
if (pkttype == 0x01) {
#
# An SQL Batch request consists of an ALL_HEADERS section
# and then the data
#
local d:string;
if (pktpktn == 1) {
#
# First packet starts with an ALL_HEADERS section
# that we need to skip
#
sqlbatch[c$uid] = "";
local ah_totlen = bytestring_to_count(tdspkt[0:3],T);
d = tdspkt[ah_totlen:|tdspkt| - 1];
} else {
d = tdspkt;
}
#
# Badly convert the UTF-16 string to ASCII
# Obviously this'll only work for the
# C0 Controls and Basic Latin (0x0000 - 0x007f), and the
# C1 Controls and Latin-1 Supplement (0x0080 - 0x00ff)
# blocks of the Basic Multilingual Plane
#
d = subst_string(d,"\0","");
#
# Add it to our SQL Batch request buffer
#
sqlbatch[c$uid] += d;
#
# Check to see if bit 0 (End of Message) of the status byte is set
# If so, we have a complete SQLBatch request so generate a
# 'tds_sqlbatch' event, and then empty our SQLBatch buffer
#
if (pktstat % 2 == 1) {
event tds_sqlbatch(c,sqlbatch[c$uid]);
delete sqlbatch[c$uid];
}
}
#
# E. Handle TDS7 Login packet
#
if (pkttype == 0x10) {
#
# Remove the TDS packet header and generate a
# 'tds_tds7login' event
#
local tds_payload = data[8:|data| - 1];
event tds_tds7login(c,tds_payload);
}
}
}
#
# A Bro generated event called to deliver reassembled TCP stream data
# This event handler basically just packetises the TDS packets from
# the TCP stream data, and generates a 'tds_packet' event for each one
#
event tcp_contents(c: connection, is_orig: bool, seq: count, contents: string) {
#
# Check that we have an MSSQL connection from a client to a server
# This is necessary despite us setting tcp_content_delivery_ports_orig
# as other scripts may have added ports to tcp_content_delivery_ports_orig
# in which case we'll see their TCP contents also
#
if (is_orig && c$id$resp_p == 1433/tcp) {
#
# Add the TCP stream contents to our TDS packet buffer
#
if (c$uid !in tdspktbuffer) {
tdspktbuffer[c$uid] = TDSPktBuffer($pktlen = 0,$pktoff = 0,$pkt = "");
}
tdspktbuffer[c$uid]$pkt += contents;
#
# If we don't yet know the length of the next TDS packet, and we have a complete
# header sitting in the packet buffer to be able to read the length, then read it
#
if (tdspktbuffer[c$uid]$pktlen == 0 && (|tdspktbuffer[c$uid]$pkt| - tdspktbuffer[c$uid]$pktoff >= 8)) {
local pktoff = tdspktbuffer[c$uid]$pktoff;
local pkttype = bytestring_to_count(tdspktbuffer[c$uid]$pkt[pktoff]);
local pktlen:count;
#
# A. Check for TLS packet
# If we find one, skip it, as we won't be able to decode the data
#
if (pkttype == 0x17) {
#
# SSL packet, which has a different header
#
local tlsver = bytestring_to_count(tdspktbuffer[c$uid]$pkt[pktoff + 1:pktoff + 2]);
pktlen = bytestring_to_count(tdspktbuffer[c$uid]$pkt[pktoff + 3:pktoff + 4]) + 5;
} else {
pktlen = bytestring_to_count(tdspktbuffer[c$uid]$pkt[pktoff + 2:pktoff + 3]);
}
tdspktbuffer[c$uid]$pktlen = pktlen;
}
#
# Are we there yet? (do we have a complete TDS packet in the packet buffer?)
#
if (tdspktbuffer[c$uid]$pktoff + tdspktbuffer[c$uid]$pktlen <= |tdspktbuffer[c$uid]$pkt|) {
#
# We have a complete TDS packet, but we may have received more bytes in the TCP segment
# than what we need to complete the TDS packet
#
local extrabytes = (|tdspktbuffer[c$uid]$pkt| - tdspktbuffer[c$uid]$pktoff) - tdspktbuffer[c$uid]$pktlen;
#
# Generate a tds_packet event
#
event tds_packet(c,tdspktbuffer[c$uid]$pkt[tdspktbuffer[c$uid]$pktoff:(tdspktbuffer[c$uid]$pktoff + tdspktbuffer[c$uid]$pktlen) - 1]);
#
# Remove the processed TDS packet from the buffer
#
if (extrabytes > 0) {
#
# We have extra bytes which we need to hang on to
#
local start = tdspktbuffer[c$uid]$pktoff + tdspktbuffer[c$uid]$pktlen;
local end = start + extrabytes - 1;
tdspktbuffer[c$uid]$pkt = tdspktbuffer[c$uid]$pkt[start:end];
} else {
#
# No extra bytes so we can just empty the buffer
#
# (the outer 'if' clause guarantees that
# extrabytes >= 0, so no need to check for < 0)
#
tdspktbuffer[c$uid]$pkt = "";
}
#
# The next TDS packet now starts at the start of the buffer
# and we do not yet know its length
#
tdspktbuffer[c$uid]$pktoff = 0;
tdspktbuffer[c$uid]$pktlen = 0;
}
}
}
#
# A Bro generated event called as Bro is about to clean up the connection state information
# This event handler just removes any buffer information that we created
# for the connection
#
event connection_state_remove(c: connection) {
#
# Check for a TDS packet buffer belonging to the connection
#
if (c$uid in tdspktbuffer) {
delete tdspktbuffer[c$uid];
}
#
# Check for an SQL Batch buffer belonging to the connection
# This shouldn't happen as this buffer should be deleted by
# the tcp_packet event handler after processing the last TDS
# packet of the transaction
# If that last packet is missed though, then this will make
# sure the buffer is cleaned up
#
if (c$uid in sqlbatch) {
delete sqlbatch[c$uid];
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment