Skip to Content.
Sympa Menu

freetds - RE: [freetds] RPC/Packet Type 3 Format

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "Thompson, Bill D (London)" <ThompBil AT exchange.uk.ml.com>
  • To: "'FreeTDS Development Group'" <freetds AT lists.ibiblio.org>
  • Subject: RE: [freetds] RPC/Packet Type 3 Format
  • Date: Tue, 22 Jul 2003 09:08:27 +0100

Hi,

There's more information here than I have time to do justice to - sorry!

One thing that's confusing you is the "name" of the stored procedure to be
executed.

As described in our file doc/tds.html, the name length is held as a *two
byte* integer, followed by the name.

In TDS 76.0 of course this integer is little endian, so in your first
example the name length is 0x21 0x00 (33 in decimal), and the name of the SP
appears to be [mk1]..sp_procedure_params_rowset - that's 33 characters (its
in UNICODE in the packet of course).

This stored procedure seems to be a SQL server internal procedure, but it's
known about - search in google!

You can execute it in SQL Query Analyzer, it seems to take a single
parameter - the procedure name - and returns details about the parameters.
VERY USEFUL as it turns out - I'd been wondering in the RPC logic how to
find out about a stored procedure's parameters as it would be very useful
information to have in the FreeTDS API's (when we try and validate a user's
calls to the RPC interface API's)...

So (back on to your first example) . We're calling
sp_procedure_params_rowset with a single parameter of a procedure name.

After the procedure name we have two bytes of "flags" - whose meaning we
don't yet fully understand.
However these are not set in this case, so we have two bytes 0x00 0x00

Then we have the parameter data.
First up is the parameter name, sent as a self describing token - 1 byte of
length followed by n bytes of actual name where n = the length.
In this case, the length is 0x00, so there is no name.

Next up is a byte which we understand to say whether the parameter is input
or output. In this case the byte is 0x00, which indicates an INPUT
parameter.

Next up is the description of the type of parameter. This can get a bit
complicated. I'm not going to describe the many variations that can be
coded, look in tds_put_data_info in src/tds/query.c.

In this case though we have the following:

A byte for the data type - in this case 0xE7 which indicates a UNICODE
varchar ( NVARCHAR in Database terms)
There's then a type dependent field describing the length of the parameter.
A NVARCHAR requires a two byte integer to describe its length, in this case
0x1e 0x00 - I make that 30.

Then we have the real Parameter data, first a type dependent length (0x1e
0x00 again) followed by the actual data (at last).
This is "mk_find_product" 15 characters in length which equals 30 in UNICODE


Q.E.D. (I hope)

Bill


0080 74 00 00 00 00 00 e7 1e 00 1e 00 6d 00 6b 00 5f
t..........m.k._
0090 00 66 00 69 00 6e 00 64 00 5f 00 70 00 72 00 6f
.f.i.n.d._.p.r.o
00a0 00 64 00 75 00 63 00 74 00 00 00 38 01 00 00 00
.d.u.c.t...8....
00b0 00 00 e7 00 00 ff ff 00 00 e7 00 00 ff ff
..............



> -----Original Message-----
> From: lists-tds AT mymailfilter.net [SMTP:lists-tds AT mymailfilter.net]
> Sent: 22 July 2003 04:41
> To: FreeTDS Development Group
> Subject: Re: [freetds] RPC/Packet Type 3 Format
>
> Thanks, that certainly explains the codes with which I was struggling.
>
> > It's an RPC with parameters: -1 (output), NULL ntext, sql text, 1...
> > It seem an sp_prepare call... I don't understand where this code came
> > cause is not very good. A better way is to use RPC, withot sp_prepare...
>
> The information I sent was from a packet taken from the wire going from an
>
> ADO-based client toward an MSSQL 2000 server. The underlying ADO library
> or db-lib would've generated the TDS packet.
>
> I'm trying to interpret the packet more than I'm trying to construct it
> right now, and based on the current information it doesn't seem to be very
>
> straight forward -- without knowing how many parameters, I'm not sure how
> to tell where the parameters end and the SQL text begins.
>
> For example,
>
> 0030 fb 04 84 b8 00 00 03 01 00 88 00 00 01 00 21 00 ..............!.
> 0040 5b 00 6d 00 6b 00 31 00 5d 00 2e 00 2e 00 73 00 [.m.k.1.].....s.
> 0050 70 00 5f 00 70 00 72 00 6f 00 63 00 65 00 64 00 p._.p.r.o.c.e.d.
> 0060 75 00 72 00 65 00 5f 00 70 00 61 00 72 00 61 00 u.r.e._.p.a.r.a.
> 0070 6d 00 73 00 5f 00 72 00 6f 00 77 00 73 00 65 00 m.s._.r.o.w.s.e.
> 0080 74 00 00 00 00 00 e7 1e 00 1e 00 6d 00 6b 00 5f t..........m.k._
> 0090 00 66 00 69 00 6e 00 64 00 5f 00 70 00 72 00 6f .f.i.n.d._.p.r.o
> 00a0 00 64 00 75 00 63 00 74 00 00 00 38 01 00 00 00 .d.u.c.t...8....
> 00b0 00 00 e7 00 00 ff ff 00 00 e7 00 00 ff ff ..............
>
> 0030 21 00 !.
>
> If 21 (1st byte after the tds header) is interpretted as the length of the
>
> procedure name (33), Length(sp_procedure_params_rowset) != 33.
>
> FYI:
> ---
> [mk1] is the database name.
> mk_find_product is the procedure name being called.
>
> This packet is some sort of "prepare" activity being generated by the ADO
> driver, as the procedure would not be called like this.
>
> Here's an sp_unprepare the supports your explanation of the first byte
> following the header being the stored procedure length:
>
> 0000 00 01 03 e7 d7 aa 00 10 a4 93 e9 87 08 00 45 00 ..............E.
> 0010 00 53 b8 98 40 00 80 06 ab 5b c0 a8 0a c8 c0 a8 .S..@....[......
> 0020 0a 98 10 53 05 99 c6 fe 83 29 42 2a 18 20 50 18 ...S.....)B*. P.
> 0030 fb 04 07 5b 00 00 03 01 00 2b 00 00 01 00 0c 00 ...[.....+......
> 0040 73 00 70 00 5f 00 75 00 6e 00 70 00 72 00 65 00 s.p._.u.n.p.r.e.
> 0050 70 00 61 00 72 00 65 00 00 00 00 00 38 01 00 00 p.a.r.e.....8...
> 0060 00 .
>
> But here's one that seems to pack a couple of procedures together:
>
> 0030 fa dd 3d a5 00 00 03 01 00 e4 00 00 01 00 0a 00 ..=.............
> 0040 73 00 70 00 5f 00 70 00 72 00 65 00 70 00 61 00 s.p._.p.r.e.p.a.
> 0050 72 00 65 00 00 00 00 01 26 04 00 00 00 63 5c 00 r.e.....&....c\.
> 0060 00 00 5c 00 00 00 40 00 50 00 31 00 20 00 69 00 ..\...@.P.1. .i.
> 0070 6e 00 74 00 20 00 4f 00 55 00 54 00 50 00 55 00 n.t. .O.U.T.P.U.
> 0080 54 00 2c 00 40 00 50 00 32 00 20 00 76 00 61 00 T.,.@.P.2. .v.a.
> 0090 72 00 63 00 68 00 61 00 72 00 28 00 36 00 34 00 r.c.h.a.r.(.6.4.
> 00a0 29 00 2c 00 40 00 50 00 33 00 20 00 76 00 61 00 ).,.@.P.3. .v.a.
> 00b0 72 00 63 00 68 00 61 00 72 00 28 00 31 00 32 00 r.c.h.a.r.(.1.2.
> 00c0 29 00 00 00 63 46 00 00 00 46 00 00 00 20 00 45 )...cF...F... .E
> 00d0 00 58 00 45 00 43 00 20 00 40 00 50 00 31 00 3d .X.E.C. .@.P.1.=
> 00e0 00 6d 00 6b 00 5f 00 66 00 69 00 6e 00 64 00 5f .m.k._.f.i.n.d._
> 00f0 00 70 00 72 00 6f 00 64 00 75 00 63 00 74 00 20 .p.r.o.d.u.c.t.
> 0100 00 40 00 50 00 32 00 2c 00 40 00 50 00 33 00 20 .@.P.2.,.@.P.3.
> 0110 00 20 00 00 00 38 01 00 00 00 . ...8....
>
> I think one can follow your explanation through the bytes and see the
> meaning of some of the parameters and such, but how does one break it
> down and dynamically determine what's there? In the second data chunk
> above, the sp_prepare starts 1 byte after the length:
>
> 0030 fa dd 3d a5 00 00 03 01 00 e4 00 00 01 00 0a 00 ..=.............
> ^^
> 0040 73 00 70 00 5f 00 70 00 72 00 65 00 70 00 61 00 s.p._.p.r.e.p.a.
> ^^
>
> Length = 10, a 00 byte and the start of a procedure that has a length of
> 10. However, my original data held the parameter info between the length
> byte and the actual SQL text:
>
> > 0030 f7 48 ca 56 00 00 03 01 00 fa 00 00 01 00 ff ff .H.V............
> ^^
> > 0040 0b 00 00 00 00 01 26 04 04 ff ff ff ff 00 00 63 ......&........c
> > 0050 00 00 00 00 09 04 00 01 32 ff ff ff ff 00 00 63 ........2......c
> > 0060 ba 00 00 00 09 04 00 01 32 ba 00 00 00 64 00 65 ........2....d.e
> ^^
> > 0070 00 63 00 6c 00 61 00 72 00 65 00 20 00 40 00 70 .c.l.a.r.e. .@.p
>
> What about the data tells me where the parameters start/end, and where the
>
> SQL text starts/ends, etc? I think I need to keep looking at this, I must
>
> be missing something...
>
> Thanks again for the info.
>
>
> On 21 Jul 2003, Frediano Ziglio wrote:
>
> > I didn't read all messages related so perhaps some information will be
> > duplicated.
> > >
> > > 0030 f7 48 ca 56 00 00
> >
> > This should be a TCP/IP info or other related.
> > > 03 01 00 fa 00 00 01 00
> >
> > Header: type 3 (RPC) final, length 0xfa (correct)
> >
> > > ff ff .H.V............
> >
> > Length of store procedure name. TDS8 (mssql2k+, not mssql7) handle null
> > name (-1 length) using numbers instead of names (I know only some
> > numbers like 15 -> sp_unprepare).
> >
> > > 0040 0b 00
> >
> > This is RPC numbers... perhaps sp_executesql
> >
> > > 00 00
> >
> > flags
> >
> > > 00 01 26 04 04 ff ff ff ff
> >
> > output parameter, type INTN (0x26) length 4 (data and type length),
> > value -1
> >
> > > 00 00 63 ......&........c
> >
> > input parameter, NTEXT
> >
> > > 0050 00 00 00 00
> >
> > ??? strange, must be length of type... perhaps I have to check tds
> > code...
> >
> > > 09 04 00 01 32
> >
> > collation info (0x409 = english)
> >
> > > ff ff ff ff
> >
> > length of text (NULL)
> >
> > > 00 00 63 ........2......c
> >
> > input NTEXT...
> >
> > > 0060 ba 00 00 00
> >
> > yes, this is length
> >
> > > 09 04 00 01 32
> >
> > collate
> >
> > > ba 00 00 00
> >
> > length
> >
> > > 64 00 65 ........2....d.e
> > > 0070 00 63 00 6c 00 61 00 72 00 65 00 20 00 40 00 70 .c.l.a.r.e.
> .@.p
> > > 0080 00 34 00 20 00 69 00 6e 00 74 00 65 00 67 00 65 .4.
> .i.n.t.e.g.e
> > > 0090 00 72 00 0d 00 0a 00 65 00 78 00 65 00 63 00 20
> .r.....e.x.e.c.
> > > 00a0 00 6a 00 64 00 5f 00 74 00 65 00 73 00 74 00 5f
> .j.d._.t.e.s.t._
> > > 00b0 00 30 00 30 00 31 00 20 00 40 00 70 00 31 00 3d .0.0.1.
> .@.p.1.=
> > > 00c0 00 33 00 2c 00 20 00 40 00 70 00 32 00 3d 00 27 .3.,.
> .@.p.2.=.'
> > > 00d0 00 74 00 65 00 73 00 74 00 27 00 2c 00 20 00 40 .t.e.s.t.'.,.
> .@
> > > 00e0 00 70 00 33 00 3d 00 20 00 27 00 30 00 37 00 2f .p.3.=.
> .'.0.7./
> > > 00f0 00 31 00 37 00 2f 00 32 00 30 00 30 00 33 00 27
> .1.7./.2.0.0.3.'
> > > 0100 00 2c 00 20 00 40 00 40 00 70 00 34 00 3d 00 40 .,.
> .@.@.p.4.=.@
> > > 0110 00 70 00 34 00 20 00 6f 00 75 00 74 00 70 00 75 .p.4.
> .o.u.t.p.u
> > > 0120 00 74 00 0d 00 0a 00
> >
> > just sql code you send
> >
> > > 00 00 26 04 04 01 00 00 00 .t.......&......
> >
> > input type INTN, length 4, value 1
> >
> > >
> > > The actual SQL code is:
> > >
> > > declare @p4 integer
> > > exec jd_test_001 @p1=3, @p2='test', @p3= '07/17/2003', @@p4=@p4 output
> > >
> > > I'm confused about what appears to be some flags and info preceding
> the
> > > actual SQL code.
> >
> > ...
> >
> > Well, any byte is decoded...
> >
> > It's an RPC with parameters: -1 (output), NULL ntext, sql text, 1...
> > It seem an sp_prepare call... I don't understand where this code came
> > cause is not very good. A better way is to use RPC, withot sp_prepare...
> >
> > I've learn two things:
> > - length of NTEXT is before collation information
> > - if parameter list is empty I can use a NULL value per parameters
> >
> > freddy77
> >
> >
> > _______________________________________________
> > FreeTDS mailing list
> > FreeTDS AT lists.ibiblio.org
> > http://lists.ibiblio.org/mailman/listinfo/freetds
> >
>
>
>
> _______________________________________________
> FreeTDS mailing list
> FreeTDS AT lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds





Archive powered by MHonArc 2.6.24.

Top of Page