Oracle TNS 314 协议分析——2、基础包结构，
TNS包头格式
每个数据都包含一个通用包头，包含数据的长度、校验和解析信息
|
Packet Header |
8 |
通用包头 |
|
Data |
可变 |
数据 |
Packet Header
|
Length |
2 |
包的长度，包括通用包头 |
|
Packet check sum |
2 |
包的校验和 |
|
PacketType |
1 |
包类型 |
|
Reserved |
1 |
保留 |
|
Header check sum |
2 |
通用头的校验和 |
Packet Types
| CONNECT | 1 | 连接 |
| ACCEPT | 2 | 连接接受 |
| ACK | 3 | |
| REFUSE | 4 | 拒绝连接 |
| REDIRECT | 5 | 重定向 |
| DATA | 6 | 数据包，大多数包属于此包，包含SQL命令及返回等 |
| NULL DATA | 7 | 空 |
| ABORT | 9 | |
| RESEND | 11 | 重发 |
| MARKER | 12 | 提示错误等 |
| ATTENTION | 13 | |
| CONTROL | 14 | |
| MAX | 19 |
通用包头解析示例
通用包头解析代码示例
local pktLen=string.unpack(">I2",lengthdata)
local data,err=reqsock:receive(pktLen-2)
if(err) then
ngx.log(ngx.ERR,"err when reading packet")
break
end
ngx.log(ngx.DEBUG,"data"..data:hex())
local pktType=string.unpack(">B",data,3)PacketType 0x06 Data包格式
客户端与服务端除建立和断开连接外，基本上全部使用Data包进行交互，包括设置协议参数、认证、SQL请求与返回等，此包内部又有很多类型和子类型，不同类型和子类型差异较大，基础格式如下
|
Data flag |
2 |
状态 |
|
Command packet |
可变 |
命令包 |
|
… |
… |
… |
|
Command packet |
可变 |
命令包 |
DataFlag
DataFlag一般为0x0000,DATA结束时为0x0040,其他情况少见，详细见下表
|
Send token |
0x0001 |
|
|
Request Confirmation |
0x0002 |
|
|
Confirmation |
0x0004 |
|
|
Reserved |
0x0008 |
|
|
UNKNOWN |
0x0010 |
|
|
More Data to Come |
0x0020 |
|
|
End of File |
0x0040 |
data传输结束时可见 |
|
Do Immediate Confirmation |
0x0080 |
|
|
Request to send |
0x0100 |
|
|
Request Nt Trailer |
0x0200 |
|
Command Packet
Command Packet可以有多个，每个CommandPacket也有自己的类型DataID，有的DataID还有自己的子功能号叫做CallID，DataID类型除特殊的SetProtocol和Network等包外，一般请求包包含一个序号字节，而返回值没有这个序号
|
DataID |
1 |
Command 功能 |
|
CallID |
1 |
子功能，此字段可选，有些DataID无CallID |
|
Seq |
1 |
序号（返回包无此序列号） |
DataID
| SET_PROTOCOL | 1 | 连接后设置协议参数 |
| SET_DATATYPES | 2 | 连接后设置数据格式等 |
| USER_OCI_FUNC | 3 | 调用OCI函数，有子类型，传输sql命令等用此 |
| RETURN_STATUS | 4 | 返回状态，也有子命令 |
| ACCESS_USR_ADDR | 5 | |
| ROW_TRANSF_HEADER | 6 | SQL命令返回具体数据 |
| ROW_TRANSF_DATA | 7 | |
| RETURN_OPI_PARAM | 8 | 常见不需要返回数据的命令请求返回如Insert，后续OCI_RESPOND |
| FUNCCOMPLETE | 9 | |
| NERROR_RET_DEF | 10 | |
| IOVEC_4FAST_UPI | 11 | 常见不需要返回数据的命令请求返回，后续OCI_RESPOND |
| LONG_4FAST_UPI | 12 | |
| INVOKE_USER_CB | 13 | |
| LOB_FILE_DF | 14 | |
| WARNING | 15 | |
| DESCRIBE_INFO | 16 | 常见在sql请求后返回一些描述信息，比如列名 |
| PIGGYBACK_FUNC | 17 | 有子命令，常后续OCI_FUN_CALL，后续OCI_FUN_CALL携带真实命令 |
| SIG_4UCS | 18 | |
| FLUSH_BIND_DATA | 19 | |
| OCI_RESPOND | 23 | 一般用于返回信息，基本所有USER_OCI_FUNC的返回都会包含此包,此包最后一般有服务端返回客户端的文字消息，（312协议未见） |
| SNS | 0xdeadbeef | 额外安全网络协议交换 |
| XTRN_PROCSERV_R1 | 32 | |
| XTRN_PROCSERV_R2 | 68 |
USER_OCI_FUNC
DataID 为3时为USER_OCI_FUNC，SQL的执行基本是通过此命令完成，此命令有子命令，具体取值如下（参见wireshark tns desector）
|
1 |
Logon to Oracle |
41 |
Parse for syntax and SQL Dictionary lookup |
81 |
2nd Half of Logon |
|
2 |
Open Cursor |
42 |
Continue serving after EOF |
82 |
1st Half of Logon |
|
3 |
Parse a Row |
43 |
Array describe |
83 |
Do Streaming Operation |
|
4 |
Execute a Row |
44 |
Init sys pars command table |
84 |
Open Session (71 interface) |
|
5 |
Fetch a Row |
45 |
Finalize sys pars command table |
85 |
X/Open XA operations (71 interface) |
|
8 |
Close Cursor |
46 |
Put sys par in command table |
86 |
Debugging operations |
|
9 |
Logoff of Oracle |
47 |
Get sys pars from command table |
87 |
Special debugging operations |
|
10 |
Describe a select list column |
48 |
Start Oracle (V6) |
88 |
XA Start |
|
11 |
Define where the column goes |
49 |
Shutdown Oracle (V6) |
89 |
XA Switch and Commit |
|
12 |
Auto commit on |
50 |
Run Independent Process (V6) |
90 |
Direct copy from db buffers to client address |
|
13 |
Auto commit off |
51 |
Test RAM (V6) |
91 |
OKOD Call (In Oracle <= 7 this used to be Connect |
|
14 |
Commit |
52 |
Archive operation (V6) |
93 |
RPI Callback with ctxdef |
|
15 |
Rollback |
53 |
Media Recovery - start (V6) |
94 |
Bundled execution call (V7) |
|
16 |
Set fatal error options |
54 |
Media Recovery - record tablespace to recover (V6) |
95 |
Do Streaming Operation without begintxn |
|
17 |
Resume current operation |
55 |
Media Recovery - get starting log seq # (V6) |
96 |
LOB and FILE related calls |
|
18 |
Get Oracle version-date string |
56 |
Media Recovery - recover using offline log (V6) |
97 |
File Create call |
|
19 |
Until we get rid of OASQL |
57 |
Media Recovery - cancel media recovery (V6) |
98 |
Describe query (V8) call |
|
20 |
Cancel the current operation |
58 |
Logon to Oracle (V6) |
99 |
Connect (non-blocking attach host) |
|
21 |
Get error message |
59 |
Get Oracle version-date string in new format |
100 |
Open a recursive cursor |
|
22 |
Exit Oracle command |
60 |
Initialize Oracle |
101 |
Bundled KPR Execution |
|
23 |
Special function |
61 |
Reserved for MAC; close all cursors |
102 |
Bundled PL/SQL execution |
|
24 |
Abort |
62 |
Bundled execution call |
103 |
Transaction start attach detach |
|
25 |
Dequeue by RowID |
65 |
For direct loader: functions |
104 |
Transaction commit rollback recover |
|
26 |
Fetch a long column value |
66 |
For direct loader: buffer transfer |
105 |
Cursor close all |
|
27 |
Create Access Module |
67 |
Distrib. trans. mgr. RPC |
106 |
Failover into piggyback |
|
28 |
Save Access Module Statement |
68 |
Describe indexes for distributed query |
107 |
Session switching piggyback (V8) |
|
29 |
Save Access Module |
69 |
Session operations |
108 |
Do Dummy Defines |
|
30 |
Parse Access Module Statement |
70 |
Execute using synchronized system commit numbers |
109 |
Init sys pars (V8) |
|
31 |
How many items? |
71 |
Fast UPI calls to OPIAL7 |
110 |
Finalize sys pars (V8) |
|
32 |
Initialize Oracle |
72 |
Long Fetch (V7) |
111 |
Put sys par in par space (V8) |
|
33 |
Change User ID |
73 |
Call OPIEXE from OPIALL: no two-task access |
112 |
Terminate sys pars (V8) |
|
34 |
Bind by reference positional |
74 |
Parse Call (V7) to deal with various flavours |
114 |
Init Untrusted Callbacks |
|
35 |
Get n'th Bind Variable |
76 |
RPC call from PL/SQL |
115 |
Generic authentication call |
|
36 |
Get n'th Into Variable |
77 |
Do a KGL operation |
116 |
FailOver Get Instance call |
|
37 |
Bind by reference |
78 |
Execute and Fetch |
117 |
Oracle Transaction service Commit remote sites |
|
38 |
Bind by reference numeric |
79 |
X/Open XA operation |
118 |
Get the session key |
|
39 |
Parse and Execute |
80 |
New KGL operation call |
119 |
Describe any (V8) |
|
40 |
Parse for syntax (only) |
|
|
120 |
Cancel All |
其他一些DataID 与CallID组合（摘抄自《ORACLE协议分析》https://download.csdn.net/download/hg_zhoujj/10789649）
|
DataID |
CalID |
类型 |
说明 |
使用 |
|
0x01 |
0x05 |
请求 |
CLIENT TYPE |
|
|
0x01 |
0x06 |
|
Set Protocol |
|
|
0x01 |
0x2c |
|
IDENT |
|
|
0x02 |
0x00 |
|
RESET |
|
|
0x02 |
0x01 |
|
CHAR_MAP |
|
|
0x02 |
0x54 |
|
|
|
|
0x03 |
0x01 |
|
|
|
|
0x03 |
0x02 |
|
SQL_OPEN |
|
|
0x03 |
0x03 |
|
QUERY |
|
|
0x03 |
0x04 |
|
QUERY SECOND |
|
|
0x03 |
0x05 |
|
FETCH MORE |
|
|
0x03 |
0x08 |
|
|
|
|
0x03 |
0x09 |
|
DISCONNECT |
|
|
0x03 |
0x0E |
|
|
|
|
0x03 |
0x27 |
|
SET_LANG |
|
|
0x03 |
0x2B |
|
DESC_COLS |
|
|
0x03 |
0x3B |
|
DB VERSION |
|
|
0x03 |
0x47 |
|
FETCH |
|
|
0x03 |
0x51 |
|
Set password |
|
|
0x03 |
0x52 |
|
Set user |
|
|
0x03 |
0x54 |
|
HANDSHAKE4 |
|
|
0x03 |
0x5E |
|
SQL |
|
|
0x03 |
0x73 |
|
AUTH2 |
|
|
0x03 |
0x76 |
|
AUTH1 |
|
|
0x03 |
0x77 |
|
查询表结构信息 |
DESC |
|
0x04 |
0x01 |
|
ACK |
|
|
0x04 |
0x02 |
|
ACK |
|
|
0x04 |
0x05 |
|
ACK |
|
|
0x06 |
0x00 |
|
More Row Result Info |
|
|
0x06 |
0x01 |
|
First Row Result Info |
|
|
0x08 |
0x01 |
|
|
|
|
0x08 |
0x05 |
|
|
|
|
0x08 |
0x08 |
|
|
|
|
0x08 |
0x9c |
|
Db version |
|
|
0x10 |
0x19 |
|
Field Info |
SELEC返回 |
|
0x11 |
0x69 |
|
|
|
|
0x11 |
0x6b |
|
|
|
|
0x11 |
0x78 |
|
|
|
|
|
|
|
|
|
Data包中常见的数据结构及解析
变长字符串
变长字符串用一个前序一个字节长度标志的序列来表示
|
Data Length |
1 |
返回列数 |
|
Data |
Data Length |
|
比如：
05 73 63 6f 74 74
05标识字符串长度
后面的字符串内容为ASCII码，表示scott
变长字符串数组
变长字符串数字开头为fe，具体格式如下
|
Big Data Identify |
1 |
0xfe |
|
Data Length |
1 |
|
|
Data |
Data Length |
|
|
… |
… |
… |
|
Data Length |
1 |
|
|
Data |
Data Length |
|
|
End Mark |
1 |
|
示例
数字格式（参《ORACLE协议分析》未完全验证）
正数
|
Id |
1 |
C0+整数部分长度 |
|
整数部分 |
Intlength=Id-c0 |
|
|
小数部分 |
DecLength=length-intlength-1 |
|
注：
负数
|
Id |
1 |
3f-整数部分长度 |
|
整数部分 |
Intlength=3f-id |
|
|
小数部分 |
DecLength=length-intlength-1 |
|
|
Magic |
1 |
0x66 |
日期格式（参《ORACLE协议分析》未完全验证）
|
Year1 |
1 |
Year1-100 |
|
Year2 |
1 |
Year2-100 |
|
Month |
1 |
Month |
|
Day |
1 |
Day |
|
Hour |
1 |
Hour-1 |
|
Minute |
1 |
Minute-1 |
|
Second |
1 |
Second-1 |
注：