Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Development Tools
 ASP.NET
 Sniffing at two sockets

Author  Topic 

Stoad
Freaky Yak Linguist

1983 Posts
Posted - 2005-04-19 : 14:40:44
Recently I wrote (in Python 2.3.4) a kind of sniffer, just a dozen of
lines of code. It works fine except it works not exactly as it should work.
Namely, in my python code I open two sockets and use next scheme in order
to force a very simple VB_SCRIPT to communicate with SQL_SERVER which listens
to its default port 1433:

VB_SCRIPT -->-- Socket#1(1434) |||data||| Socket#2 -->-- SQL_SERVER(1433)

VB_SCRIPT:
Set cn = CreateObject("ADODB.Connection")
cn.Open _
"Provider=sqloledb;Data Source=127.0.0.1,1434;" & _
"Network Library=DBMSSOCN;Initial Catalog=pubs;" & _
"User ID=qwe;Password=asdasd;"
cn.Execute _
"select 'XXXXXXXXXXXXX';" & _
"waitfor delay '000:00:05'; raiserror ('AAA',10,1) with nowait;" & _
"waitfor delay '000:00:05'; raiserror ('BBB',10,1) with nowait;" & _
"waitfor delay '000:00:05'; raiserror ('CCC',10,1) with nowait;" & _
"select 'YYYYYYYYYYYYY';"
cn.Close
Set cn = Nothing

PYTHON_CODE:
import socket
host = '127.0.0.1'
port = 1434
s1 = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
s2 = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
s2.connect((host, 1433))
s1.bind((host, port))
s1.listen(1)
cn, addr = s1.accept()
while 1:
.. data = cn.recv(4096)
.. if not data: break
.. s2.send(data)
.. print 'VB_SCRIPT:', data ##intercepted VB_SCRIPT's requests
.. data = s2.recv(4096)
.. if not data: break
.. cn.send(data)
.. print 'SQL_SERVER:', data ##intercepted SQL_SERVER's replies
s1.close()
cn.close()
s2.close()


Intercepted talk between VB_SCRIPT and SQL_SERVER:
VB_SCRIPT:
 )          T 

SQL_SERVER:
 %           T 

VB_SCRIPT:
 ò  - q  - ð È    V  X  ^  j ! ì  L 
T T  L TïS - W q w e ¦åÒåóå¦åÒåóåM i c r o s o f t ( r )
W i n d o w s S c r i p t H o s t 1 2 7 . 0 . 0 . 1 , 1 4 3 4 O L E D B p u b s

SQL_SERVER:
U 3 ó p u b s m a s t e r ëT E  # C h a n g e d d a t a
b a s e c o n t e x t t o ' p u b s ' . W ó ¦ ó 
u s _ e n g l i s h ë\ G  ' C h a n g e d l a n g u a g e s e t
t i n g t o u s _ e n g l i s h . W í6  M i c r o s o f t
S Q L S e r v e r  Tó 4 0 9 6 4 0 9 6 ¤

VB_SCRIPT:
+  s e l e c t ' X X X X X X X X X X X X X ' ; w a i t f o r
d e l a y ' 0 0 0 : 0 0 : 0 5 ' ; r a i s e r r o r ( ' A A A ' ,
1 0 , 1 ) w i t h n o w a i t ; w a i t f o r d e l a y ' 0 0 0
: 0 0 : 0 5 ' ; r a i s e r r o r ( ' B B B ' , 1 0 , 1 ) w i t h
n o w a i t ; w a i t f o r d e l a y ' 0 0 0 : 0 0 : 0 5 ' ;
r a i s e r r o r ( ' C C C ' , 1 0 , 1 ) w i t h n o w a i t ;
s e l e c t ' Y Y Y Y Y Y Y Y Y Y Y Y Y ' ;

SQL_SERVER:
 Z 3 Á ç ¦ T XXXXXXXXXXXXX¤ +  ¤ º ë P+  
A A A W  ¤ ¡

VB_SCRIPT:
 vLu

SQL_SERVER:
 1 3 ¤ º ë P+   B B B W  ¤ ¡  1 3 ¤ º ë
P+   C C C W  ¤ ¡  1 3 Á ç ¦ T
YYYYYYYYYYYYY¤ +    3 ¤ ¤

And my asking is:
can anybody re-write my python code in C# just for to check how it
works in the "regular" language (pity I still don't know even its ABC)?
In general, any hints on this stuff are welcome.

Stoad
Freaky Yak Linguist

1983 Posts
Posted - 2005-04-20 : 05:05:46
For those who have .NET Framework and know C#.
Can you please test the below C# code against my VB script
from my previous post and post here what you'll get in stdout?
SQL_SERVER has to listen to its default port 1433.


using System.Net;
using System.Net.Sockets;
using System.Text;

int port = 1434;
Socket s1 = new Socket
(AddressFamily.InterNetwork, SocketType.Stream, ProtocolType.Tcp);
Socket s2 = new Socket
(AddressFamily.InterNetwork, SocketType.Stream, ProtocolType.Tcp);
s2.Connect(new IPEndPoint(new IPAddress(new byte[]{127,0,0,1}),1433));
s1.Bind(new IPEndPoint(new IPAddress(new byte[]{127,0,0,1}),port));
s1.Listen(1);
Socket s3 = s1.Accept();
int bytes;
Byte[] RecvBytes = new Byte[4096];

do{
bytes = s3.Receive(RecvBytes, RecvBytes.Length, 0);
if(bytes == 0) break;
Console.WriteLine("client:{0}", Encoding.ASCII.GetString(RecvBytes));
s2.Send(RecvBytes);
bytes = s2.Receive(RecvBytes, RecvBytes.Length, 0);
if(bytes == 0) break;
Console.WriteLine("sqlserver:{0}", Encoding.ASCII.GetString(RecvBytes));
s3.Send(RecvBytes);
}while(true);

s1.Shutdown(SocketShutdown.Both);
s1.Close();
s2.Shutdown(SocketShutdown.Both);
s2.Close();
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts
Posted - 2005-04-20 : 14:59:59
client:#8597;#9786; ) § #9824;#9786; #8592; #9786;#9787; #8735; #9786;#9829; #8596; #9830; #9786;U D#8616;
sqlserver:#9830;#9786; % #9786; § #9824;#9786; #8592; #9786;#9787; #8735; #9786;#9829; #8596; #9787;~ #9787; D#8616;

I'll see later if I can make more sense of this...

rockmoose
Go to Top of Page

Stoad
Freaky Yak Linguist

1983 Posts
Posted - 2005-04-20 : 15:19:54
First of all, many thanks, rocko!
1.
I see only 1 client request and only 1 server reply. ???
Can you correct "my" (it's not mine) C# code if it's the case?
2.
> I'll see later if I can make more sense of this...
I'll be waiting... and please use my VB script in your tests.
Also, compare how it (the VB script) works if directed to "real"
port 1433 (instead of fake port 1434).
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts
Posted - 2005-04-20 : 15:56:53
1. I get an exception after 1 request/reply
these are the (int)byte arrays returned, and the exception:
-----------------------------------------------------------------------------------------------------
client: 18 1 0 41 0 0 0 0 0 0 21 0 6 1 0 27 0 1 2 0 28 0 1 3 0 29 0 4 255 8 0 1 85 0 0 0 0 204 17 0 0
sqlserver: 4 1 0 37 0 0 1 0 0 0 21 0 6 1 0 27 0 1 2 0 28 0 1 3 0 29 0 0 255 8 0 2 254 0 0 2 0
An existing connection was forcibly closed by the remote host
-----------------------------------------------------------------------------------------------------

Maybe I can fix this...

rockmoose
Go to Top of Page

Stoad
Freaky Yak Linguist

1983 Posts
Posted - 2005-04-21 : 03:58:39
To be fair, I expected to see the following reply to my asking:
"Yes! In C# it works fine! And Python sucks."
I hope I'll see such a reply a bit later. The man who re-wrote
it for me in C# (without much understanding what's going on there)
has not SQL Server at the time and could not test it on his own.

What exactly goes wrong (i.m.o.):

SQL_SERVER:
 Z 3 Á ç ¦ T XXXXXXXXXXXXX¤ +  ¤ º ë P+  
A A A W  ¤ ¡

VB_SCRIPT:
 vLu
this client's req is issued too late - right before CommandTime times out -
which default value is 30 secs

SQL_SERVER:
 1 3 ¤ º ë P+   B B B W  ¤ ¡  1 3 ¤ º ë
P+   C C C W  ¤ ¡  1 3 Á ç ¦ T
YYYYYYYYYYYYY¤ +    3 ¤ ¤
now this SS reply looks as if SQL Server's just forced to flush some
internal buffer and sends ALL remaining data it has in store for the client
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts
Posted - 2005-04-21 : 05:17:21
It doesn't work at all for me, I just get one request/reply to/from sql server,
then I get the SocketException: "An existing connection was forcibly closed by the remote host" (#10054).

In the code I changed: new IPAddress(new byte[]{127,0,0,1})
to: IPAddress.Parse("127.0.0.1")

Oh well..., not much help.

rockmoose
Go to Top of Page

Stoad
Freaky Yak Linguist

1983 Posts
Posted - 2005-04-21 : 06:37:13
> Oh well..., not much help.

Nevermind, rocko! It's quite enough ... so far.
Go to Top of Page

kselvia
Aged Yak Warrior

526 Posts
Posted - 2005-04-28 : 14:04:49
Hey Stoad, try https://msdn.demoservers.com where you can connect to a virtual session with Visual Studio and SQL Server.

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.
Go to Top of Page

Stoad
Freaky Yak Linguist

1983 Posts
Posted - 2005-04-28 : 14:37:33
Thanks, Ken,
but it is not for my f***ingly slow dial-up...
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site