Oracle TNS 314 协议分析：四、SQL执行流程与包分析

柔情只为你懂 2023-03-13 03:20 41阅读 0赞

# **SQL 命令典型执行过程（以Select为例）** #

<table> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;width:42.3pt;"> Client </td> 
 <td style="vertical-align:top;width:42.45pt;"> &nbsp; </td> 
 <td style="vertical-align:top;width:163.1pt;"> &nbsp; </td> 
 <td style="vertical-align:top;width:42.5pt;"> &nbsp; </td> 
 <td style="vertical-align:top;width:79.15pt;"> Server </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:42.3pt;"> 1 </td> 
 <td style="vertical-align:top;width:42.45pt;"> ------- </td> 
 <td style="vertical-align:top;width:163.1pt;"> Data&nbsp;Piggyback(11) Cursor&nbsp;Close All(69) 注意此处也有可能是 116b,035e,0303 </td> 
 <td style="vertical-align:top;width:42.5pt;"> -----&gt; </td> 
 <td style="vertical-align:top;width:79.15pt;"> 具体语句 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:42.3pt;"> 2 </td> 
 <td style="vertical-align:top;width:42.45pt;"> &lt;----- </td> 
 <td style="vertical-align:top;width:163.1pt;"> Data&nbsp;DescribeInfo(10) &nbsp;17 </td> 
 <td style="vertical-align:top;width:42.5pt;"> ------- </td> 
 <td style="vertical-align:top;width:79.15pt;"> 返回列 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:42.3pt;"> 3 </td> 
 <td style="vertical-align:top;width:42.45pt;"> ------- </td> 
 <td style="vertical-align:top;width:163.1pt;"> Data&nbsp;UOCIFun(03) FetchARow(05) </td> 
 <td style="vertical-align:top;width:42.5pt;"> -----&gt; </td> 
 <td style="vertical-align:top;width:79.15pt;"> 获取其他值 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:42.3pt;"> 4 </td> 
 <td style="vertical-align:top;width:42.45pt;"> &lt;----- </td> 
 <td style="vertical-align:top;width:163.1pt;"> Data RowTransferHeader(06) &nbsp;01 </td> 
 <td style="vertical-align:top;width:42.5pt;"> ------- </td> 
 <td style="vertical-align:top;width:79.15pt;"> 返回值 </td> 
 </tr> 
 </tbody> 
</table>

11g，12c有三种sql命令执行的模式：

1.  第一种是前序一个piggyback（1169）包，然后紧接execute buddle command（035e）
2.  第二种是前序一个session switch piggy back（116b）包，然后紧接execute buddle command（035e）
3.  第三种是直接发送execute buddle command（035e），前者可以看做只是在后者前面加了一个头

*参考网上其他文章Oracle 9.2还有可能使用0303来发命令，而对其的返回（上述流程中2处）可能使用data 1019的包*

# **使用0x1169执行SQL命令的情况** #

## **包格式** ##

<table> 
 <tbody> 
 <tr> 
 <td style="width:156.45pt;"> &nbsp; </td> 
 <td style="width:72.5pt;"> 32bit </td> 
 <td style="width:72.5pt;"> 64bit </td> 
 <td style="width:53.25pt;"> &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="width:156.45pt;"> 序列号 </td> 
 <td style="width:72.5pt;"> 1 </td> 
 <td style="width:72.5pt;"> 1 </td> 
 <td style="width:53.25pt;"> &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="width:156.45pt;"> Piggyback cusor close all （1169） command </td> 
 <td style="width:72.5pt;"> 9 or 13 </td> 
 <td style="width:72.5pt;"> 16 or 20 </td> 
 <td style="width:53.25pt;"> &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="width:156.45pt;"> Buddle execute （035e）command </td> 
 <td style="width:72.5pt;"> 变长 </td> 
 <td style="width:72.5pt;"> 变长 </td> 
 <td style="width:53.25pt;"> &nbsp; </td> 
 </tr> 
 </tbody> 
</table>

*参考网上一些文章，Oracle9，10 Piggy Command长度为12*

## 1169 **command** ##

这个部分是变长的，下面是几种变化，都以feMagic（定义参见本系列第二篇 《[Oracle TNS 314 协议分析---基础包结构][Oracle TNS 314 _---]》）数组开头，有的长16个字节，有的20个字节

<table> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;width:52.05pt;"> 语句 </td> 
 <td style="vertical-align:top;width:103.65pt;"> Plsql </td> 
 <td style="vertical-align:top;width:106.3pt;"> Sqlplus </td> 
 <td style="vertical-align:top;width:107.5pt;"> Navicat </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:52.05pt;"> Select </td> 
 <td style="vertical-align:top;width:103.65pt;"> fe ff ff ff ff ff ff ff 01 00 00 00 04&nbsp;00 00 00 </td> 
 <td style="vertical-align:top;width:106.3pt;"> fe ff ff ff ff ff ff ff 01 00 00 00 00 00 00&nbsp;00 05&nbsp;00 00 00 当没有输入；05会变03 </td> 
 <td style="vertical-align:top;width:107.5pt;"> fe ff ff ff ff ff ff ff 01 00 00 00 05&nbsp;00 00 00 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:52.05pt;"> Update </td> 
 <td style="vertical-align:top;width:103.65pt;"> fe ff ff ff ff ff ff ff 01 00 00 00 02&nbsp;00 00 00 </td> 
 <td style="vertical-align:top;width:106.3pt;"> fe ff ff ff ff ff ff ff 01 00 00 00 00 00 00 00 06 00 00 00 </td> 
 <td style="vertical-align:top;width:107.5pt;"> &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:52.05pt;"> Delete </td> 
 <td style="vertical-align:top;width:103.65pt;"> fe ff ff ff ff ff ff ff 01 00 00 00 02&nbsp;00 00 00 </td> 
 <td style="vertical-align:top;width:106.3pt;"> fe ff ff ff ff ff ff ff 01 00 00 00 00 00 00 00 03&nbsp;00 00 00 </td> 
 <td style="vertical-align:top;width:107.5pt;"> &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:52.05pt;"> Insert </td> 
 <td style="vertical-align:top;width:103.65pt;"> &nbsp; </td> 
 <td style="vertical-align:top;width:106.3pt;"> fe ff ff ff ff ff ff ff 01 00 00 00 00 00 00 00 05&nbsp;00 00 00 有时是0b&nbsp;有时是09 </td> 
 <td style="vertical-align:top;width:107.5pt;"> &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:52.05pt;"> CreateTable </td> 
 <td style="vertical-align:top;width:103.65pt;"> &nbsp; </td> 
 <td style="vertical-align:top;width:106.3pt;"> fe ff ff ff ff ff ff ff 01 00 00 00 00 00 00 00 07&nbsp;00 00 00 </td> 
 <td style="vertical-align:top;width:107.5pt;"> &nbsp; </td> 
 </tr> 
 </tbody> 
</table>

# **使用0x116b执行SQL命令的情况** #

## **包格式** ##

<table> 
 <tbody> 
 <tr> 
 <td style="width:156.45pt;"> &nbsp; </td> 
 <td style="width:72.5pt;"> 32bit </td> 
 <td style="width:72.5pt;"> 64bit </td> 
 <td style="width:53.25pt;"> &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="width:156.45pt;"> 序列号 </td> 
 <td style="width:72.5pt;"> 1 </td> 
 <td style="width:72.5pt;"> 1 </td> 
 <td style="width:53.25pt;"> &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="width:156.45pt;"> Piggy command </td> 
 <td style="width:72.5pt;"> 12 </td> 
 <td style="width:72.5pt;"> 12 </td> 
 <td style="width:53.25pt;"> &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="width:156.45pt;"> Buddle execute command 035e </td> 
 <td style="width:72.5pt;"> 变长 </td> 
 <td style="width:72.5pt;"> 变长 </td> 
 <td style="width:53.25pt;"> &nbsp; </td> 
 </tr> 
 </tbody> 
</table>

*Oracle9 10 Piggy Command长度也为12*

# **BuddleexecuteCommand 035e** #

<table> 
 <tbody> 
 <tr> 
 <td style="width:394px;"> &nbsp; </td> 
 <td style="width:189px;"> 32bit </td> 
 <td style="width:181px;"> 64bit </td> 
 <td style="width:133px;">ThinClient</td> 
 <td style="width:133px;"> &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="width:394px;"> 序列号 </td> 
 <td style="width:189px;"> 1 </td> 
 <td style="width:181px;"> 1 </td> 
 <td style="width:133px;">1</td> 
 <td style="width:133px;"> &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="width:394px;"> 命令相关 </td> 
 <td style="width:189px;"> 8 </td> 
 <td style="width:181px;"> 8 </td> 
 <td style="width:133px;">5</td> 
 <td style="width:133px;"> &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="width:394px;"> feMagic </td> 
 <td style="width:189px;"> 1 </td> 
 <td style="width:181px;"> 8 </td> 
 <td style="width:133px;">1</td> 
 <td style="width:133px;"> &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="width:394px;"> 语句长度相关字段 </td> 
 <td style="width:189px;"> 2 </td> 
 <td style="width:181px;"> 2 </td> 
 <td style="width:133px;">1</td> 
 <td style="width:133px;"> &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="width:394px;">未知</td> 
 <td style="width:189px;">2</td> 
 <td style="width:181px;">2</td> 
 <td style="width:133px;">1</td> 
 <td style="width:133px;">&nbsp;</td> 
 </tr> 
 <tr> 
 <td style="width:394px;"> 固定字节 </td> 
 <td style="width:189px;"> 54 </td> 
 <td style="width:181px;"> 152 </td> 
 <td style="width:133px;">41</td> 
 <td style="width:133px;"> 所有命令都相同 </td> 
 </tr> 
 <tr> 
 <td style="width:394px;"> SQL命令 </td> 
 <td style="width:189px;"> 变长 </td> 
 <td style="width:181px;"> 变长 </td> 
 <td style="width:133px;">变长</td> 
 <td style="width:133px;"> &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="width:394px;"> 尾部字节 </td> 
 <td style="width:189px;"> 52 </td> 
 <td style="width:181px;"> 52 </td> 
 <td style="width:133px;">17</td> 
 <td style="width:133px;"> 01开头 </td> 
 </tr> 
 </tbody> 
</table>

*根据其他文章来看Oracle9、10（TNS312,313）下这个包有一定区别（主要是从序列号到SQL的字节数分别为80,92,而11,12为172）下面是根据其他文章分析的包结构*

<table> 
 <tbody> 
 <tr> 
 <td style="width:340px;"> 序列号 </td> 
 <td style="width:272px;"> 1 </td> 
 <td style="width:81.4pt;"> &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="width:340px;"> 固定长度头部 </td> 
 <td style="width:272px;"> 80&nbsp;or 92 </td> 
 <td style="width:81.4pt;"> &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="width:340px;"> SQL命令 </td> 
 <td style="width:272px;"> 变长 </td> 
 <td style="width:81.4pt;"> SQL查询语句 </td> 
 </tr> 
 <tr> 
 <td style="width:340px;"> 尾部字节 </td> 
 <td style="width:272px;"> 48 </td> 
 <td style="width:81.4pt;"> &nbsp; </td> 
 </tr> 
 </tbody> 
</table>

ThinClient发出的包与此相差较大，将另文描述

## **命令相关** ##

各个平台各种语句调用如下

<table> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;width:52.05pt;"> 语句 </td> 
 <td style="vertical-align:top;width:197px;"> PlSQL </td> 
 <td style="vertical-align:top;width:195px;"> SQLplus </td> 
 <td style="vertical-align:top;width:204px;"> Navicat </td> 
 <td style="vertical-align:top;width:195px;">Thin Client</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:52.05pt;"> Select </td> 
 <td style="vertical-align:top;width:197px;"> 61 80&nbsp;00 00 00 00 00 00 </td> 
 <td style="vertical-align:top;width:195px;"> 61 80&nbsp;00 00 00 00 00 00 </td> 
 <td style="vertical-align:top;width:204px;"> 61 81&nbsp;00 00 00 00 00 00 </td> 
 <td style="vertical-align:top;width:195px;">02 80 21 00 01</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:52.05pt;"> Update </td> 
 <td style="vertical-align:top;width:197px;"> 21 80&nbsp;00 00 00 00 00 00 </td> 
 <td style="vertical-align:top;width:195px;"> 21 80&nbsp;00 00 00 00 00 00 </td> 
 <td style="vertical-align:top;width:204px;"> 21 81&nbsp;00 00 00 00 00 00 </td> 
 <td style="vertical-align:top;width:195px;">&nbsp;</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:52.05pt;"> Delete </td> 
 <td style="vertical-align:top;width:197px;"> 21 80&nbsp;00 00 00 00 00 00 </td> 
 <td style="vertical-align:top;width:195px;"> 21 80&nbsp;00 00 00 00 00 00 </td> 
 <td style="vertical-align:top;width:204px;"> 21 81&nbsp;00 00 00 00 00 00 </td> 
 <td style="vertical-align:top;width:195px;">&nbsp;</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:52.05pt;"> Insert </td> 
 <td style="vertical-align:top;width:197px;"> 21 80&nbsp;00 00 00 00 00 00 </td> 
 <td style="vertical-align:top;width:195px;"> 21 80&nbsp;00 00 00 00 00 00 </td> 
 <td style="vertical-align:top;width:204px;"> 21 81&nbsp;00 00 00 00 00 00 </td> 
 <td style="vertical-align:top;width:195px;">&nbsp;</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:52.05pt;"> CreateTable </td> 
 <td style="vertical-align:top;width:197px;"> 21 80&nbsp;00 00 00 00 00 00 </td> 
 <td style="vertical-align:top;width:195px;"> 21 80&nbsp;00 00 00 00 00 00 </td> 
 <td style="vertical-align:top;width:204px;"> 21 81&nbsp;00 00 00 00 00 00 </td> 
 <td style="vertical-align:top;width:195px;">&nbsp;</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:52.05pt;"> AlterTable </td> 
 <td style="vertical-align:top;width:197px;"> 21 80&nbsp;00 00 00 00 00 00 </td> 
 <td style="vertical-align:top;width:195px;"> 21 80&nbsp;00 00 00 00 00 00 </td> 
 <td style="vertical-align:top;width:204px;"> 21 81&nbsp;00 00 00 00 00 00 </td> 
 <td style="vertical-align:top;width:195px;">&nbsp;</td> 
 </tr> 
 </tbody> 
</table>

红色部分见过这些取值

61 80 plsql select

61 81 navicat select

01 80 02 plsql select another

29 04 04 plsql declare function

29 05 04 navicat declare function

21 80 plsql 除select 外

21 81 navicat 除select 外

21 01 04 navicat call function

而在ThinClient中，返现Endian会发生变化，且前序一个长度字节的情况，且此字段长度也不相同

于是21 80会变成 02 80 21

## **语句长度字段** ##

此字节与sql长度有关，OCI Client 下为sql长度字段大小\*3，如果sql较长，则会产生进位，注意进位后是little endian的形式，02 01 表示102，ThinClient下直接就是语句长度，ThinClient下语句前不会再附加长度，此处是获取长度的最佳地点。

39 00 00 00 表示 0x39

21 01 00 00 表示 0x121

在命令超长的情况下，这个长度字段非常有用，因为一个command会在多个TNSData包中出现，必须用这个长度字段和TNS Command Header的长度字段，才能正确组合超长命令。

![20200510225056405.png][]

上图中可以看出，一个超长命令可能跨越多个TNS包，如果一个TNS包中的Command长度不足，说明还没有收取完毕，需要继续从buffer中读取数据，直到命令长度满足要求。另外处理程序如果工作在应用层，那么处理程序从buffer中取出的数据可能横跨多个TNS包，由于每个TNS包都有自己的头部，就会在解析的命令中引入这些额外的头部。为了跳过这些头部，必须利用第一个TNS 的package Length作为指针，逐个找到每个头部，从而处理这些额外的10字节头。

## **固定字节** ##

### 64bit OCI Client（SQLPLUS，PLSQL，Navicat） ###

后续19\*8=152个固定字节，使用navicat和plsql无论什么命令都相同：

0000 fe ff ff ff ff ff ff ff 0d 00 00 00 fe ff ff ff ........ ........

0010 ff ff ff ff fe ff ff ff ff ff ff ff 00 00 00 00 ........ ........

0020 01 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ........ ........

0030 00 00 00 00 00 00 00 00 00 00 00 00 fe ff ff ff ........ ........

0040 ff ff ff ff 00 00 00 00 00 00 00 00 fe ff ff ff ........ ........

0050 ff ff ff ff fe ff ff ff ff ff ff ff fe ff ff ff ........ ........

0060 ff ff ff ff 00 00 00 00 00 00 00 00 fe ff ff ff ........ ........

0070 ff ff ff ff fe ff ff ff ff ff ff ff 00 00 00 00 ........ ........

0080 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ........ ........

0090 00 00 00 00 00 00 00 00 ........

### 32bit OCI Client（SQLPLUS，PLSQL，Navicat） ###

后续54个固定字节

0000 01 0d 00 00 00 01 01 00 00 00 00 01 00 00 00 00 ........ ........

0010 00 00 00 00 00 00 00 00 00 01 00 01 01 01 00 00 ........ ........

0020 00 00 00 00 00 00 01 01 00 00 00 00 00 00 00 00 ........ ........

0030 00 00 00 00 00 00

### Thin Client（JAVA） ###

后续41个固定字节

0000 01 0d 00 00 04 ff ff ff ff 01 0a 04 7f ff ff ff ........ ........

0010 00 00 00 00 00 00 00 00 00 00 00 01 00 00 00 00 ........ ........

0020 00 00 00 00 00 00 00 00 00

## **Sql命令** ##

后续立刻接sql，sql遵循变长字符串或数组形式，即len+值定义，第一个字节为长度，或fe表示数组，数组中元素也是变长字符串，当为数组时数组由00结尾，所有sql都后续01。如果输入时忘记输入；语句后会多个0a，所以结尾看起来是0a01或0a0001（变长）。

### **定长命令** ###

00A0 13 53 45 ........ ......SE

00B0 4c 45 43 54 20 2a 20 46 52 4f 4d 20 44 45 50 54 LECT \* F ROM DEPT

00C0 0a 01 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ........ ........

00D0 00 00 00 00 00 00 00 00 00 00 00 00 00 01 00 00 ........ ........

00E0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ........ ........

00F0 00 00 00 00 00

### **变长命令** ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpemh1MjAwMA_size_16_color_FFFFFF_t_70][]

### ThinClient 无长度前序命令 ###

ThinClient下发现此包无前序长度，长度由前面长度相关字段确定

### **包示例（Navicat 15 premium 64bit to Oracle 12）** ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpemh1MjAwMA_size_16_color_FFFFFF_t_70 1][]

#  **0303** QUERY**包** #

根据其他文章在Oracle9.2下有可能使用这种包发送命令（本文11,12 64bit下未测得），其格式如下

<table> 
 <tbody> 
 <tr> 
 <td style="width:114.9pt;"> Request id </td> 
 <td style="width:32.85pt;"> 1 </td> 
 <td style="width:109.5pt;"> &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="width:114.9pt;"> Magic </td> 
 <td style="width:32.85pt;"> 12 </td> 
 <td style="width:109.5pt;"> &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="width:114.9pt;"> Data Format </td> 
 <td style="width:32.85pt;"> 可变 </td> 
 <td style="width:109.5pt;"> 查询语句 </td> 
 </tr> 
 </tbody> 
</table>

# 通过T**ransfer header 获取返回值** #

## 包示例 ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpemh1MjAwMA_size_16_color_FFFFFF_t_70 2][]

[Oracle TNS 314 _---]: https://blog.csdn.net/yizhu2000/article/details/106010654
[20200510225056405.png]: /images/20230312/2e5bbe6fc67b4148837944c81415d698.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpemh1MjAwMA_size_16_color_FFFFFF_t_70]: /images/20230312/6dca6684b4024e7e837c6b644a596e50.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpemh1MjAwMA_size_16_color_FFFFFF_t_70 1]: /images/20230312/65d496043a0d4a68b1e368a3c9189c80.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpemh1MjAwMA_size_16_color_FFFFFF_t_70 2]: /images/20230312/2042e611cf4646e38a549e9a01dda9fd.png