using System;
using System.Windows.Forms;
namespace MyClasses
{
public class PacketSQL
{
public struct PACKET_SQL
{
public byte type;
public byte responseType;
public string sql;
}
public PacketSQL()
{
}
static string DecodeType(int i)
{
string dt = "Unknown";
switch (i)
{
case 239:
dt = "nchar";
break;
case 38:
dt = "int (check length for type of int)";
break;
case 48:
dt = "tinyint";
break;
case 56:
dt = "int";
break;
case 34:
dt = "image";
break;
case 35:
dt = "text";
break;
case 108:
dt = "numeric";
break;
case 52:
dt = "smallint";
break;
case 127:
dt = "bigint";
break;
case 111:
dt = "datetime (check length for type of datetime)";
break;
case 58:
dt = "smalldatetime";
break;
case 61:
dt = "datetime";
break;
case 167:
dt = "varchar";
break;
case 175:
dt = "char";
break;
case 104:
case 50:
dt = "bit";
break;
case 59:
dt = "real";
break;
case 62:
dt = "float";
break;
case 109:
dt = "float (check length for type of float)";
break;
case 165:
dt = "varbinary";
break;
case 122:
dt = "smallmoney";
break;
case 98:
dt = "sqlvariant";
break;
case 106:
dt = "decimal";
break;
case 36:
dt = "uniqueidentifier";
break;
case 60:
dt = "money";
break;
case 99:
dt = "ntext";
break;
case 173:
dt = "binary/timestamp";
break;
case 110:
dt = "money (check length for type of money)";
break;
case 231:
dt = "nvarchar";
break;
default:
dt = "Unknown : " + i.ToString();
break;
}
return dt;
}
static void ProcessCol(ref TreeNode mNodey, byte[] PacketData, ref int Index)
{
Dump2ByteInt("Usertype id : ", mNodey, PacketData, ref Index);
int flags = Function.Get2Bytes(PacketData, ref Index, Const.VALUE);
string fl = "Flags : ";
if ((flags & 0x01) != 0)
{
fl += " nullable,";
}
if ((flags & 0x02) != 0)
{
fl += " case sensitive,";
}
if ((flags & 0x04) != 0 || (flags & 0x08) != 0)
{
fl += " is writable,";
}
if ((flags & 0x10) != 0)
{
fl += " auto increment,";
}
mNodey.Nodes.Add(fl);
Function.SetPosition( ref mNodey , Index -2, 2, false );
int datatype = PacketData[Index++];
string dt = DecodeType(datatype);
mNodey.Nodes.Add("Datatype : " + dt);
Function.SetPosition( ref mNodey , Index -1, 1, false );
if (datatype == 35 || datatype == 99) // TEXT, NTEXT, IMAGE
{
Dump4ByteInt("Unknown : ", mNodey, PacketData, ref Index);
Dump1ByteInt("Unknown : ", mNodey, PacketData, ref Index);
Dump4ByteInt("Column size : ", mNodey, PacketData, ref Index);
int coltablen = Dump2ByteInt("Column table length : ", mNodey, PacketData, ref Index);
DumpString("Column table name : ", mNodey, PacketData, ref Index, coltablen);
}
else if (datatype == 98)
{
Dump4ByteInt("Unknown : ", mNodey, PacketData, ref Index);
}
else if (datatype == 34)
{
Dump4ByteInt("Column size : ", mNodey, PacketData, ref Index);
int coltablen = Dump2ByteInt("Column table length : ", mNodey, PacketData, ref Index);
DumpString("Column table name : ", mNodey, PacketData, ref Index, coltablen);
}
else if (datatype == 106 || datatype == 108) // NUMERIC, DECIMAL
{
Dump1ByteInt("Column size : ", mNodey, PacketData, ref Index);
Dump1ByteInt("Precision : ", mNodey, PacketData, ref Index);
Dump1ByteInt("Scale : ", mNodey, PacketData, ref Index);
}
else if (datatype == 111 || datatype == 38 || datatype == 109 ||
datatype == 110 || datatype == 104 || datatype == 36)
{
Dump1ByteInt("Column size : ", mNodey, PacketData, ref Index);
}
else if (datatype == 173 || datatype == 165)
{
Dump2ByteInt("Column size : ", mNodey, PacketData, ref Index);
}
else if ((datatype & 0x80) != 0)
{
Dump2ByteInt("Column size : ", mNodey, PacketData, ref Index);
Dump4ByteInt("Unknown : ", mNodey, PacketData, ref Index);
Dump1ByteInt("Unknown : ", mNodey, PacketData, ref Index);
}
else
{
}
int colnamelen = Dump1ByteInt("Column name length : ", mNodey, PacketData, ref Index);
DumpString("Column name : ", mNodey, PacketData, ref Index, colnamelen);
}
static int ProcessTable(ref TreeNode mNodex, byte[] PacketData, ref int Index)
{
int tabnamelen = Dump2ByteInt("Table name length : ", mNodex, PacketData, ref Index);
DumpString("Table name : ", mNodex, PacketData, ref Index, tabnamelen);
return 2 + tabnamelen*2+1;
}
static int Dump1ByteInt(string desc, TreeNode mNodex, byte[] PacketData, ref int Index)
{
int i = PacketData[Index++];
mNodex.Nodes.Add(desc + i.ToString());
Function.SetPosition( ref mNodex , Index -1, 1, false );
return i;
}
static int Dump2ByteInt(string desc, TreeNode mNodex, byte[] PacketData, ref int Index)
{
int i = Function.Get2Bytes(PacketData, ref Index, Const.VALUE);
mNodex.Nodes.Add(desc + i.ToString());
Function.SetPosition( ref mNodex , Index -2, 2, false );
return i;
}
static ulong Dump4ByteInt(string desc, TreeNode mNodex, byte[] PacketData, ref int Index)
{
ulong i = Function.Get4Bytes(PacketData, ref Index, Const.VALUE);
mNodex.Nodes.Add(desc + i.ToString());
Function.SetPosition( ref mNodex , Index -4, 4, false );
return i;
}
static string DumpString(string desc, TreeNode mNodex, byte[] PacketData, ref int Index, int msglen)
{
string s = Function.GetString(PacketData, ref Index, true, msglen*2);
mNodex.Nodes.Add(desc + s);
Function.SetPosition( ref mNodex, Index -msglen*2, msglen*2, false );
return s;
}
static bool Process4(ref TreeNode mNodex, byte[] PacketData, ref int Index)
{
int responseType = PacketData[Index ++];
string responsetype = string.Empty;
switch(responseType)
{
case 0x21:
responsetype = "language packet";
break;
case 0x71:
responsetype = "Logout";
break;
case 0x79:
responsetype = "TDS_RETURNSTATUS";
break;
case 0x7C:
responsetype = "process id";
break;
case 0x81:
responsetype = "TDS_COLMETADATA";
break;
case 0x88:
responsetype = "TDS_ALTCOLMETADATA";
break;
case 0xA0:
responsetype = "Column name";
break;
case 0xA1:
responsetype = "Column info";
break;
case 0xA4:
responsetype = "TDS_TABNAME";
break;
case 0xA5:
responsetype = "Column info 2";
break;
case 0xA7:
responsetype = "Compute related";
break;
case 0xA8:
responsetype = "Colimn Info - Compute result";
break;
case 0xA9:
responsetype = "TDS_ORDER";
break;
case 0xAA:
responsetype = "TDS_ERROR";
break;
case 0xAB:
responsetype = "TDS_INFO";
break;
case 0xAC:
responsetype = "TDS_PARAM";
break;
case 0xAD:
responsetype = "TDS_LOGINACK";
break;
case 0xAE:
responsetype = "Control";
break;
case 0xD1:
responsetype = "TDS_ROW";
break;
case 0xD3:
responsetype = "TDS_ALTROW";
break;
case 0xD7:
responsetype = "Param packet";
break;
case 0xE2:
responsetype = "Capability packet";
break;
case 0xE3:
responsetype = "TDS_ENVCHANGE";
break;
case 0xE5:
responsetype = "Extended error message";
break;
case 0xE6:
responsetype = "DBRPC";
break;
case 0xEC:
responsetype = "Param format packet";
break;
case 0xEE:
responsetype = "Result set";
break;
case 0xFD:
responsetype = "TDS_DONE";
break;
case 0xFE:
responsetype = "TDS_DONEPROC";
break;
case 0xFF:
responsetype = "TDS_DONEINPROC";
break;
default:
responsetype = "Unkown " + responseType.ToString();
break;
}
mNodex.Nodes.Add("Response Type : " + responsetype);
Function.SetPosition( ref mNodex , Index -1, 1, false );
if (responseType == 0xFD || responseType == 0xFE || responseType == 0xFF)
{
int flags = Function.Get2Bytes(PacketData, ref Index, Const.VALUE);
string fl = "Flags : ";
if ((flags & 0x01) != 0)
{
fl += " More Results,";
}
if ((flags & 0x02) != 0)
{
fl += " Error,";
}
if ((flags & 0x10) != 0)
{
fl += " SELECT/INSERT/UPDATE(?),";
}
if ((flags & 0x20) != 0)
{
fl += " Cancel ack,";
}
mNodex.Nodes.Add(fl);
Function.SetPosition( ref mNodex , Index -2, 2, false );
int operation = Function.Get2Bytes(PacketData, ref Index, Const.VALUE);
string op = string.Empty;
switch(operation)
{
case 0xB9:
op = "IMPLICIT TRANSACTION ON";
break;
case 0xBA:
op = "IMPLICIT TRANSACTION OFF";
break;
case 0xC0:
op = "IF (block start)(?)";
break;
case 0xC1:
op = "SELECT";
break;
case 0xC3:
op = "INSERT";
break;
case 0xC5:
op = "UPDATE";
break;
case 0xC6:
op = "CREATE TABLE";
break;
case 0xC7:
op = "DROP TABLE FAILED";
break;
case 0xCA:
op = "ENDIF (block end)(?)";
break;
case 0xD2:
op = "ROLLBACK";
break;
case 0xD5:
op = "COMMIT";
break;
case 0xDE:
op = "CREATE PROCEDURE";
break;
case 0xDF:
op = "DROP PROCEDURE";
break;
case 0xE0:
op = "END PROCEDURE";
break;
case 0xE2:
op = "USE (database change)";
break;
case 0xF9:
op = "SET";
break;
case 0xFD:
op = "Syntac error or batch execution interupted (?)";
break;
default:
op = "Unkown " + operation.ToString();
break;
}
mNodex.Nodes.Add("Operation : " + op);
Function.SetPosition( ref mNodex , Index -2, 2, false );
Dump4ByteInt("Affected rows : ", mNodex, PacketData, ref Index);
}
else if (responseType == 0xD1)
{
mNodex.Nodes.Add("Parsing abandoned as TDS_ROW data is too hard");
return false;
}
else if (responseType == 0x81)
{
int cols = Dump2ByteInt("Columns : ", mNodex, PacketData, ref Index);
for (int i = 0; i < cols; i++)
{
int oldIndex = Index;
TreeNode mNodey = new TreeNode();
mNodey.Text = "SQL ( COL"+i.ToString()+" )";
ProcessCol(ref mNodey, PacketData, ref Index);
mNodex.Nodes.Add( mNodey );
Function.SetPosition( ref mNodex , oldIndex, Index-oldIndex, false );
}
}
else if (responseType == 0x79)
{
Dump4ByteInt("Return status : ", mNodex, PacketData, ref Index);
}
else if (responseType == 0xA5)
{
int packetsize = Dump2ByteInt("PacketSize : ", mNodex, PacketData, ref Index);
int i = 0;
int b = 1;
while (i < packetsize)
{
int lookahead = PacketData[Index];
if (lookahead != b)
{
int colnamelen = Dump1ByteInt("Col name length(?) : ", mNodex, PacketData, ref Index);
DumpString("Col name : ", mNodex, PacketData, ref Index, colnamelen);
i += 2;
i += 2*colnamelen;
}
if (i < packetsize)
{
int x = Dump1ByteInt("Col(?) : ", mNodex, PacketData, ref Index);
Dump2ByteInt("(?) : ", mNodex, PacketData, ref Index);
i+=3;
b++;
}
}
}
else if (responseType == 0xA4)
{
int packetsize = Dump2ByteInt("PacketSize : ", mNodex, PacketData, ref Index);
Dump1ByteInt("Unknown : ", mNodex, PacketData, ref Index);
int i = 0;
while (i < packetsize)
{
i += ProcessTable(ref mNodex, PacketData, ref Index);
if (i < packetsize)
{
Dump1ByteInt("Unknown : ", mNodex, PacketData, ref Index);
}
}
}
else if (responseType == 0xA9)
{
int packetsize = Dump2ByteInt("PacketSize : ", mNodex, PacketData, ref Index);
for (int i = 0; i < packetsize; i+=2)
{
Dump2ByteInt("Order col : ", mNodex, PacketData, ref Index);
}
}
else if (responseType == 0xAD)
{
int packetsize = Dump2ByteInt("PacketSize : ", mNodex, PacketData, ref Index);
Dump1ByteInt("Unknown : ", mNodex, PacketData, ref Index);
Dump4ByteInt("TDS Version : ", mNodex, PacketData, ref Index);
int prodnamelen = Dump1ByteInt("Database product name length : ", mNodex, PacketData, ref Index);
DumpString("Database product name : ", mNodex, PacketData, ref Index, prodnamelen);
Dump4ByteInt("Database Version : ", mNodex, PacketData, ref Index);
}
else if (responseType == 0xAC)
{
int packetsize = Dump2ByteInt("PacketSize : ", mNodex, PacketData, ref Index);
int paramnamelen = Dump1ByteInt("Param name length : ", mNodex, PacketData, ref Index);
DumpString("Parameter name : ", mNodex, PacketData, ref Index, paramnamelen);
Dump1ByteInt("Unknown : ", mNodex, PacketData, ref Index);
int type = PacketData[Index];
Dump1ByteInt("Formal type(?) : " + DecodeType(type) + " : ", mNodex, PacketData, ref Index);
Dump2ByteInt("Unknown : ", mNodex, PacketData, ref Index);
Dump1ByteInt("Unknown : ", mNodex, PacketData, ref Index);
type = PacketData[Index];
Dump1ByteInt("Actual type : " + DecodeType(type) + " : ", mNodex, PacketData, ref Index);
mNodex.Nodes.Add("Parsing abandoned as TDS_PARAM is too hard");
for (int i = 1 + paramnamelen + 1 + 1+ 2 + 1 + 1; i < packetsize; i++)
{
Dump1ByteInt("Unknown : ", mNodex, PacketData, ref Index);
}
}
else if (responseType == 0xE3)
{
int packetsize = Dump2ByteInt("PacketSize : ", mNodex, PacketData, ref Index);
int changetype = Dump1ByteInt("Change type : ", mNodex, PacketData, ref Index);
switch(changetype)
{
case 1:
int dbnamelen = Dump1ByteInt("Database name len : ", mNodex, PacketData, ref Index);
DumpString("Database name : ", mNodex, PacketData, ref Index, dbnamelen);
dbnamelen = Dump1ByteInt("Old database name len : ", mNodex, PacketData, ref Index);
DumpString("Old database name : ", mNodex, PacketData, ref Index, dbnamelen);
break;
case 3:
int charsetlen = Dump1ByteInt("Charset len : ", mNodex, PacketData, ref Index);
DumpString("Charset : ", mNodex, PacketData, ref Index, charsetlen);
Dump2ByteInt("Charset : ", mNodex, PacketData, ref Index);
charsetlen = Dump1ByteInt("Old charset len : ", mNodex, PacketData, ref Index);
DumpString("Old charset : ", mNodex, PacketData, ref Index, charsetlen);
break;
case 4:
int blocksizelen = Dump1ByteInt("Block size len : ", mNodex, PacketData, ref Index);
DumpString("Blocksize : ", mNodex, PacketData, ref Index, blocksizelen);
blocksizelen = Dump1ByteInt("Old blocksize len : ", mNodex, PacketData, ref Index);
DumpString("Old block size : ", mNodex, PacketData, ref Index, blocksizelen);
break;
default:
mNodex.Nodes.Add("Unrecognised ENVCHANGE parsing abandoned ");
for (int i = 1; i < packetsize; i++)
{
Dump1ByteInt("Unknown : ", mNodex, PacketData, ref Index);
}
break;
}
}
else if (responseType == 0xAA || responseType == 0xAB)
{
int packetsize = Dump2ByteInt("PacketSize : ", mNodex, PacketData, ref Index);
Dump4ByteInt("Error message number : ", mNodex, PacketData, ref Index);
Dump1ByteInt("State : ", mNodex, PacketData, ref Index);
Dump1ByteInt("Severity : ", mNodex, PacketData, ref Index);
int msglen = Dump2ByteInt("Message length : ", mNodex, PacketData, ref Index);
DumpString("Message : ", mNodex, PacketData, ref Index, msglen);
int svrlen = Dump1ByteInt("Server name length : ", mNodex, PacketData, ref Index);
DumpString("Server name : ", mNodex, PacketData, ref Index, svrlen);
int proclen = Dump1ByteInt("Procedure name length : ", mNodex, PacketData, ref Index);
DumpString("Procedure name : ", mNodex, PacketData, ref Index, proclen);
Dump2ByteInt("Line num : ", mNodex, PacketData, ref Index);
}
else
{
mNodex.Nodes.Add("Unrecognised TDS_* parsing abandoned");
return false;
}
return true;
}
public static bool Parser( ref TreeNodeCollection mNode,
byte [] PacketData ,
ref int Index ,
ref ListViewItem LItem , bool DisplayData )
{
string Tmp = "";
TreeNode mNodex;
int Size = 0;
PACKET_SQL PSql;
Size = PacketData.GetLength(0) - Index;
mNodex = new TreeNode();
mNodex.Text = "SQL ( SQL )";
Function.SetPosition( ref mNodex , Index , PacketData.Length - Index , true );
if (Index >= PacketData.GetLength(0))
{
mNodex.Nodes.Add("No data.");
return false;
}
if (PacketData.GetLength(0) == 1514)
{
mNodex.Nodes.Add("Packet is very likely fragmented so parsing is unlikely to work.");
}
try
{
PSql.type = PacketData[Index ++];
string type = string.Empty;
switch(PSql.type)
{
case 1:
type = "Query from client";
break;
case 2:
type = "Login from client";
break;
case 3:
type = "Stored procedure call from client";
break;
case 4:
type = "Reply from server";
break;
case 6:
type = "Cancel query from client";
break;
case 7:
type = "Bulk";
break;
case 15:
type = "Query from client 2";
break;
case 16:
type = "Login from client 2";
break;
default:
type = "Unkown " + PSql.type.ToString();
break;
}
mNodex.Nodes.Add("Type : " + type);
Function.SetPosition( ref mNodex , Index -1, 1, false );
int lastpack = PacketData[Index ++];
if (lastpack == 1)
{
mNodex.Nodes.Add("Last packet");
Function.SetPosition( ref mNodex , Index -1, 1, false );
}
else
{
mNodex.Nodes.Add("More packets to come");
Function.SetPosition( ref mNodex , Index -1, 1, false );
}
Dump2ByteInt("Packet Size : ", mNodex, PacketData, ref Index);
Dump4ByteInt("Unknown : ", mNodex, PacketData, ref Index);
if (PSql.type == 1)
{
int oldindex = Index;
PSql.sql = Function.GetString(PacketData, ref Index, true, Size - 8);
mNodex.Nodes.Add("SQL : " + PSql.sql);
Function.SetPosition( ref mNodex , oldindex, Index-oldindex, false );
}
else if (PSql.type == 2)
{
}
else if (PSql.type == 3)
{
int procnamelen = Dump2ByteInt("Proc name length : ", mNodex, PacketData, ref Index);
DumpString("Proc name : ", mNodex, PacketData, ref Index, procnamelen);
}
else if (PSql.type == 4)
{
bool cont = true;
while (cont && Index < PacketData.GetLength(0))
{
int oldIndex = Index;
TreeNode mNodey = new TreeNode();
mNodey.Text = "SQL ( SERVER RESPONSE )";
cont = Process4(ref mNodey, PacketData, ref Index);
mNodex.Nodes.Add( mNodey );
Function.SetPosition( ref mNodex , oldIndex, Index-oldIndex, false );
}
}
// for( i = 0; i < PSql.Contents.GetLength(0); i ++ )
// {
// Tmp = (string) PSql.Contents.GetValue( i );
// Tmp = Tmp.Trim();
// if( Tmp != "" )
// mNodex.Nodes.Add( Tmp + "\\r\\n" );
// }
LItem.SubItems[ Const.LIST_VIEW_PROTOCOL_INDEX ].Text = "SQL";
LItem.SubItems[ Const.LIST_VIEW_INFO_INDEX ].Text = "TDS Protocol";
mNode.Add( mNodex );
}
catch( Exception Ex )
{
mNode.Add( mNodex );
Tmp = "[ Malformed SQL packet. Remaining bytes don't fit an SQL packet. Possibly due to bad decoding ]";
mNode.Add( Tmp );
Tmp = "[ Exception raised is <" + Ex.GetType().ToString() + "> at packet index <" + Index.ToString() + "> ]";
mNode.Add( Tmp );
LItem.SubItems[ Const.LIST_VIEW_INFO_INDEX ].Text = "[ Malformed SQL packet. Remaining bytes don't fit an SQL packet. Possibly due to bad decoding ]";
return false;
}
return true;
}
public static bool Parser( byte [] PacketData ,
ref int Index ,
ref ListViewItem LItem , bool DisplayData )
{
int Size = 0;
PACKET_SQL PSql;
try
{
Size = PacketData.GetLength(0) - Index;
PSql.type = PacketData[Index ++];
// PSql.length = Function.Get2Bytes(PacketData, ref Index, Const.VALUE);
// PSql.number = Function.Get2Bytes(PacketData, ref Index, Const.VALUE);
// PSql.state = PacketData[Index ++];
// PSql.level = PacketData[Index ++];
// PSql.textLength = Function.Get2Bytes(PacketData, ref Index, Const.VALUE);
// PSql.text = Function.GetString(PacketData, ref Index, PSql.textLength);
// PSql.nameLength = PacketData[Index ++];
// PSql.name = Function.GetString(PacketData, ref Index, PSql.nameLength);
// PSql.procNameLength = PacketData[Index ++];
// PSql.procName = Function.GetString(PacketData, ref Index, PSql.procNameLength);
// PSql.lineNumber = PacketData[Index ++];
LItem.SubItems[ Const.LIST_VIEW_PROTOCOL_INDEX ].Text = "SQL";
LItem.SubItems[ Const.LIST_VIEW_INFO_INDEX ].Text = "TDS Protocol";
}
catch
{
LItem.SubItems[ Const.LIST_VIEW_INFO_INDEX ].Text = "[ Malformed SQL packet. Remaining bytes don't fit an SQL packet. Possibly due to bad decoding ]";
return false;
}
return true;
}
}
}