parsetds.bro: Extract SQL commands and login credentials from TDS (MS-SQL) TCP data

######################################################
# 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];
        }
}

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s