Oracle Tns 协议解析

Security Classification: 【C-1】 | Publish Time:2024-03-12 | Category:Test Notes | Edit
Old Version | Diff |
Expiry Notice: The article was published three months ago. Please independently assess the validity of the technical methods and code mentioned within. :)

AI摘要:本文记录了对Oracle数据库进行阻断与审计的解析过程，使用Go语言编写了一个Mitm代理脚本，通过监听1521端口拦截并转发数据包。在协议分析中，通过测试SQL语句，获取了多条数据包的十六进制输出，并分析了协议结构，发现数据包的第五个字节均为06，表示这是数据类型包。作者指出可以筛选出DATA包，从而降低无用数据的影响。通过对数据包内容的进一步分析，确认所有SQL语句存储在特定的字节范围内，进而优化了脚本的适配效果。 --- (来自模型:gpt-4o-mini-2024-07-18)

## Begin

某项目需要对大量数据库做阻断与审计，其中包含Oracle，本文记录解析过程。（不考虑证书加密）

部分资料来源：http://www.c-s-a.org.cn/csa/article/pdf/6605

## Mitm

Client ---> Server 的架构下，中间加入Mitm的Agent去拦截packets。用Go语言编写脚本，net.Listen函数去监听1521端口,之后再与Server建立链接，把src传输过来的数据原样write到dst，代码大致如下：

``` go
listener, err := net.Listen("tcp", ":1521")

// ....
		
	for {
		clientConn, err := listener.Accept() 
		if err != nil {
			log.Printf("Error accepting connection: %v", err)
			continue
		}
		go handleClient(clientConn) 
	}
	
	
	//....
	
	go copyAndInspectOracle(clientConn, serverConn, client_addr)
```
在copyAndInspectOracle函数中读取缓冲区数据，转换成hexstring打印出来，然后原样转发到目标Server。

```
		hexString := fmt.Sprintf("%x", buffer[:n])
		log.Printf("packets:" + hexString)
		
		//....
		
		if _, err := dst.Write(buffer[:n]); err != nil {
			log.Printf("Write error: %v", err)
			break
		}
```

之后部署到服务器上观察输出即可，测试链接正常。

![](https://img.meituan.net/imgupload/89571a850d42b8cfc3434385f9c1213c81635.png)

## 协议分析

测试两条语句，拿到输入输出

``` bash
2024/03/12 15:08:15 packets:0015000006000000000011690a010101010303930b
2024/03/12 15:08:15 packets:000f0000060000000000090101010b
2024/03/12 15:08:15 packets:000d000006000000000003930c
2024/03/12 15:08:15 packets:000f0000060000000000090101010c
2024/03/12 15:08:15 packets:000d000006000000000003930d
2024/03/12 15:08:15 packets:000f0000060000000000090101010d
2024/03/12 15:08:15 packets:00590000060000000000035e0e0280210001011701010d00000000047fffffff000000000000000000000001000000000053415645504f494e54204f5241434c455f535650545f320101010100000000000000028000000000
2024/03/12 15:08:15 packets:003c00000600000000000801060310abcd00010201050000000000040103010e000000000102002e0000000000000000000000000e00010100000000
2024/03/12 15:08:15 packets:008a000006000000000011690f0101010102035e100280210001013b01010d000004ffffffff010a047fffffff000000000000000000000001000000000073656c656374207379735f636f6e74657874282775736572656e76272c202763757272656e745f736368656d61272920782066726f6d206475616c0101000000000000010100028000000000
2024/03/12 15:08:15 packets:00c100000600000000001017605a984874aec77bf036b549543268a5787c030c0f391002010001015101800000020100000000000203690102010001010101015800000000010707787c030c10091000021fe801020102000622010100010a000000070653595354454d0801060310abcd000103010500000000000401030110010102057b0000010300030000000000000000000000001000010100000000214f52412d30313430333a20e69caae689bee588b0e4bbbbe4bd95e695b0e68dae0a
2024/03/12 15:08:16 packets:006600000600000000001169110101010103035e120280210001011701010d000004ffffffff0164047fffffff000000000000000000000001000000000073656c656374202a2066726f6d20616c6c5f75736572730101000000000000010100028000000000
2024/03/12 15:08:16 packets:040400000600000000001017035c37adca4d16adb065758698f34e8f787c030c0f3a2a013b01035101800000011e0000000002036901011e0008010808555345524e414d4500000000020000817f0116000000000000000007010707555345525f494400000101000c0000000101000000000000000007010707435245415445440000010200010707787c030c10091100021fe8010a010a00062201030001640000000702424902c15b0778720817071b180702504d02c15a0778720817071b18150102030702534802c159150102030702495802c1581501020307024f4502c157150102030702485202c15615010307070553434f545402c15507786d080f01330f15010307070c4f57425359535f415544495402c15407786d080f0132261501030707064f574253595302c15007786d080f01322515010307070b415045585f30333032303002c14f07786d080f012c0f150102030710415045585f5055424c49435f5553455202c14d15010203070b464c4f57535f46494c455302c14c1501030707094d474d545f5649455702c14b07786d080f012b141501030707065359534d414e02c14907786d080f01290e1501030707155350415449414c5f4353575f41444d494e5f55535202c14707786d080f0129041501030707155350415449414c5f5746535f41444d494e5f55535202c14407786d080f01283c1501030707064d444441544102c14207786d080f01252f1501030707054d4453595302c13a07786d080f01202015010203071253495f494e464f524d544e5f534348454d4102c13915010203070a4f5244504c5547494e5302c1381501020307074f52444441544102c1371501020307064f524453595302c1361501030707074f4c415053595302c13e07786d080f012505150103070709414e4f4e594d4f555302c12f07786d080f011e3115010203070358444202c12e15010307070643545853595302c12c07786d080f011e1b15010307070645584653595302c12b07786d080f011e0f1501030707075853244e554c4c06c5163031252707786d080f012016150103070705574d53595302c12107786d080f011a04150103070709415050514f5353595302c12007786d080f0119151501030707064442534e4d5002c11f07786d080f01191415010307070a4f5241434c455f4f434d02c11607786d080f01143615010307070344495002c10f07786d080f01140c1501030707054f55544c4e02c10a07786d080f01120f15010307070653595354454d02c10607786d080f01120c15010203070353595301800801060310abcd000102010500000000000401030112012402057b0000010200030000000000000000000000001200010100000000214f52412d30313430333a20e69caae689bee588b0e4bbbbe4bd95e695b0e68dae0a

select * from all_users;

---------------------------------------------------------------------

2024/03/12 15:12:39 packets:001500000600000000001169130101010102039314
2024/03/12 15:12:40 packets:000f00000600000000000901030114
2024/03/12 15:12:40 packets:000d0000060000000000039315
2024/03/12 15:12:40 packets:000f00000600000000000901030115
2024/03/12 15:12:40 packets:000d0000060000000000039316
2024/03/12 15:12:40 packets:000f00000600000000000901030116
2024/03/12 15:12:40 packets:00590000060000000000035e170280210001011701010d00000000047fffffff000000000000000000000001000000000053415645504f494e54204f5241434c455f535650545f330101010100000000000000028000000000
2024/03/12 15:12:40 packets:003c00000600000000000801060310b444000103010500000000000401030117000000000103002e0000000000000000000000001700010100000000
2024/03/12 15:12:40 packets:008a00000600000000001169180101010103035e190280210001013b01010d000004ffffffff010a047fffffff000000000000000000000001000000000073656c656374207379735f636f6e74657874282775736572656e76272c202763757272656e745f736368656d61272920782066726f6d206475616c0101000000000000010100028000000000
2024/03/12 15:12:40 packets:00c100000600000000001017605a984874aec77bf036b549543268a5787c030c0f391002010001015101800000020100000000000203690102010001010101015800000000010707787c030c100d2900021fe801020102000622010100010a000000070653595354454d0801060310b444000102010500000000000401030119010102057b0000010200030000000000000000000000001900010100000000214f52412d30313430333a20e69caae689bee588b0e4bbbbe4bd95e695b0e68dae0a
2024/03/12 15:12:40 packets:0060000006000000000011691a0101010102035e1b0280210001011101010d000004ffffffff0164047fffffff000000000000000000000001000000000073656c656374202a2066726f6d207461620101000000000000010100028000000000
2024/03/12 15:12:40 packets:095d00000600000000001017ace82b2a0f1f872278ba1e73acdaba9a787c030c100d29013b01035101800000011e0000000002036901011e0005010505544e414d4500000000018000000107000000000203690101070107010707544142545950450000010100020000817f0116000000000000000109010909434c555354455249440000010200010707787c030c100d2900021fe8010a010a0006220103000164000000070e415124444546245f415143414c4c045649455700070f415124444546245f41514552524f520456494557001501010107114151245f444546245f415143414c4c5f461501010107124151245f444546245f41514552524f525f461501020307134151245f494e5445524e45545f4147454e5453055441424c451501010107184151245f494e5445524e45545f4147454e545f505249565315010101070a4151245f5155455545531501010107104151245f51554555455f5441424c455315010101070d4151245f5343484544554c4553150102030707434154414c4f470753594e4f4e594d150101010703434f4c15010203070b444546245f415143414c4c055441424c4515010101070c444546245f41514552524f5215010101070d444546245f43414c4c44455354150101010710444546245f44454641554c5444455354150101010710444546245f44455354494e4154494f4e15010101070a444546245f4552524f52150101010708444546245f4c4f4215010101070b444546245f4f524947494e15010101070f444546245f50524f50414741544f52150101010718444546245f5055534845445f5452414e53414354494f4e5315010101070448454c501501010107164c4f474d4e52435f44424e414d455f5549445f4d415015010101070c4c4f474d4e52435f4753424115010101070c4c4f474d4e52435f4753494915010101070c4c4f474d4e52435f4754435315010101070c4c4f474d4e52435f47544c4f1501010107154c4f474d4e52505f435441535f504152545f4d415015010101070d4c4f474d4e52545f4d44444c241501010107114c4f474d4e525f4147455f5350494c4c2415010101070f4c4f474d4e525f41545452434f4c241501010107114c4f474d4e525f4154545249425554452415010101070c4c4f474d4e525f43434f4c2415010101070c4c4f474d4e525f434445462415010101070b4c4f474d4e525f434f4c2415010101070f4c4f474d4e525f434f4c54595045241501010107124c4f474d4e525f44494354494f4e415259241501010107114c4f474d4e525f4449435453544154452415010101070b4c4f474d4e525f454e432415010101070d4c4f474d4e525f4552524f522415010101070e4c4f474d4e525f46494c5445522415010101070e4c4f474d4e525f474c4f42414c241501010107164c4f474d4e525f47545f5441425f494e434c554445241501010107174c4f474d4e525f47545f555345525f494e434c554445241501010107164c4f474d4e525f47545f5849445f494e434c5544452415010101070c4c4f474d4e525f49434f4c2415010101070b4c4f474d4e525f494e44241501010107124c4f474d4e525f494e44434f4d504152542415010101070f4c4f474d4e525f494e4450415254241501010107124c4f474d4e525f494e44535542504152542415010101070c4c4f474d4e525f4b4f504d2415010101070b4c4f474d4e525f4c4f422415010101070f4c4f474d4e525f4c4f42465241472415010101070b4c4f474d4e525f4c4f47241501010107164c4f474d4e525f4c4f474d4e525f4255494c444c4f4715010101070c4c4f474d4e525f4e5441422415010101070b4c4f474d4e525f4f424a2415010101070f4c4f474d4e525f4f505154595045241501010107114c4f474d4e525f504152414d455445522415010101070f4c4f474d4e525f504152544f424a241501010107154c4f474d4e525f50524f4345535345445f4c4f472415010101070d4c4f474d4e525f50524f50532415010101070e4c4f474d4e525f524546434f4e241501010107144c4f474d4e525f524553544152545f434b50542415010101071b4c4f474d4e525f524553544152545f434b50545f5458494e464f2415010101070c4c4f474d4e525f534545442415010101070f4c4f474d4e525f53455353494f4e241501010107174c4f474d4e525f53455353494f4e5f414354494f4e53241501010107164c4f474d4e525f53455353494f4e5f45564f4c56452415010101070d4c4f474d4e525f5350494c4c241501010107124c4f474d4e525f535542434f4c545950452415010101070b4c4f474d4e525f544142241501010107124c4f474d4e525f544142434f4d504152542415010101070f4c4f474d4e525f54414250415254241501010107124c4f474d4e525f544142535542504152542415010101070a4c4f474d4e525f54532415010101070c4c4f474d4e525f545950452415010101070b4c4f474d4e525f5549442415010101070c4c4f474d4e525f55534552241501010107184c4f475354444259244150504c595f4d494c4553544f4e451501010107174c4f475354444259244150504c595f50524f47524553531501010107134c4f475354444259244544535f5441424c455315010101070f4c4f475354444259244556454e54531501010107164c4f47535444425924464c4153484241434b5f53434e1501010107104c4f47535444425924484953544f52591501010107134c4f47535444425924504152414d455445525315010101070e4c4f47535444425924504c53514c15010101070c4c4f4753544442592453434e15010101070d4c4f47535444425924534b49501501010107154c4f47535444425924534b49505f535550504f52541501010107194c4f47535444425924534b49505f5452414e53414354494f4e15010101070e4d56494557245f4144565f414a471501010107144d56494557245f4144565f424153455441424c451501010107114d56494557245f4144565f434c495155451501010107134d56494557245f4144565f454c494749424c451501010107154d56494557245f4144565f455843455054494f4e531501010107114d56494557245f4144565f46494c5445521501010107194d56494557245f4144565f46494c544552494e5354414e434515010101070e4d56494557245f4144565f464a4715010101070d4d56494557245f4144565f47430801060310b44400010301050000000000040103011b01640000000103010e030000000000000000000000001b00010100000000
2024/03/12 15:12:40 packets:0011000006000000000003051c01030164
2024/03/12 15:12:40 packets:079300000600000000000602010100016400010101010007104d56494557245f4144565f494e44455815010101070f4d56494557245f4144565f494e464f1501010107124d56494557245f4144565f4a4f55524e414c1501010107104d56494557245f4144565f4c4556454c15010101070e4d56494557245f4144565f4c4f471501010107114d56494557245f4144565f4f555450555415010101070e4d56494557245f4144565f4f57421501010107154d56494557245f4144565f504152414d45544552531501010107144d56494557245f4144565f504152544954494f4e15010101070f4d56494557245f4144565f504c414e1501010107114d56494557245f4144565f5052455454591501010107114d56494557245f4144565f524f4c4c55501501010107144d56494557245f4144565f53514c444550454e4415010101070f4d56494557245f4144565f54454d501501010107134d56494557245f4144565f574f524b4c4f41441501020307114d564945575f4556414c554154494f4e5304564945571501010107104d564945575f455843455054494f4e5315010101070c4d564945575f46494c5445521501010107144d564945575f46494c544552494e5354414e43451501010107094d564945575f4c4f471501010107154d564945575f5245434f4d4d454e444154494f4e5315010101070e4d564945575f574f524b4c4f41441501020307034f4c24055441424c451501010107084f4c2448494e54531501010107084f4c244e4f44455315010203070d50524f445543545f5052495653045649455715010203071450524f445543545f555345525f50524f46494c450753594e4f4e594d1501010107095055424c494353594e150102030717524550434154245f41554449545f415454524942555445055441424c45150101010714524550434154245f41554449545f434f4c554d4e150101010714524550434154245f434f4c554d4e5f47524f5550150101010710524550434154245f434f4e464c49435415010101070b524550434154245f44444c150101010712524550434154245f455843455054494f4e53150101010711524550434154245f455854454e53494f4e15010101070f524550434154245f464c41564f5253150101010716524550434154245f464c41564f525f4f424a45435453150101010711524550434154245f47454e455241544544150101010716524550434154245f47524f555045445f434f4c554d4e150101010719524550434154245f494e5354414e54494154494f4e5f44444c150101010713524550434154245f4b45595f434f4c554d4e53150101010714524550434154245f4f424a4543545f5041524d53150101010714524550434154245f4f424a4543545f5459504553150101010718524550434154245f504152414d455445525f434f4c554d4e150101010710524550434154245f5052494f52495459150101010716524550434154245f5052494f524954595f47524f5550150101010719524550434154245f524546524553485f54454d504c4154455315010101070e524550434154245f524550434154150101010711524550434154245f5245504341544c4f47150101010711524550434154245f524550434f4c554d4e150101010716524550434154245f52455047524f55505f5052495653150101010711524550434154245f5245504f424a45435415010101070f524550434154245f52455050524f50150101010711524550434154245f524550534348454d41150101010712524550434154245f5245534f4c5554494f4e150101010719524550434154245f5245534f4c5554494f4e5f4d4554484f4415010101071d524550434154245f5245534f4c5554494f4e5f5354415449535449435315010101071b524550434154245f5245534f4c5f53544154535f434f4e54524f4c150101010715524550434154245f52554e54494d455f5041524d53150101010711524550434154245f53495445535f4e4557150101010714524550434154245f534954455f4f424a45435453150101010711524550434154245f534e415047524f5550150101010718524550434154245f54454d504c4154455f4f424a45435453150101010716524550434154245f54454d504c4154455f5041524d5315010101071a524550434154245f54454d504c4154455f52454647524f555053150101010716524550434154245f54454d504c4154455f5349544553150101010717524550434154245f54454d504c4154455f535441545553150101010718524550434154245f54454d504c4154455f54415247455453150101010716524550434154245f54454d504c4154455f545950455315010101071b524550434154245f555345525f415554484f52495a4154494f4e53150101010718524550434154245f555345525f5041524d5f56414c55455315010101071753514c504c55535f50524f445543545f50524f46494c4515010203070a535953434154414c4f470753594e4f4e594d15010101070853595346494c455315010101070354414215010101070954414251554f544153040103011c01b002057b0000010300030000000000000000000000001c00010100000000214f52412d30313430333a20e69caae689bee588b0e4bbbbe4bd95e695b0e68dae0a

select * from tab;

---------------------------------------------------------------------
```

查阅文档，结构如下：

```
0      8       16            31

+--------------+--------------+

| Packet Length| Packet Chksm |

+------+-------+--------------+   8 byte header

| Type | Rsrvd | Header Chksm |

+------+-------+--------------+

|        P A Y L O A D        |

+-----------------------------+
```

由于我们不对认证阶段以及类似心跳检测的包做采集，首要目的是确认发送查询语句的时候，是什么type的包。可以看到type字段存在第五个字节，可以发现所有packet的第五个字节均为06，对应data类型。

这是一个agent筛选的点，这样可以过滤掉很多无用的数据包，降低影响。DATA 包是类型6，包括2个字节的 flag 标志位，1字节的 packet id，可选的 TTI id，还有数据本身。

![](https://img.meituan.net/imgupload/3a927f03251a7b0f3ae456b3e72e7cde44321.png)

把中科大的论文看完发现，作者一直在做训练集，最终目的找到目标偏移。对于不一样版本的，实际上会有很大差距，后面得出的结果集也没有给出一个宽泛的协议协定。最终还是把每个hex都做了一遍转换，发现存在这样的规律：

所有的sql都存储于packet中 47fffffff0000000000000000000000010000000000 与 0101之间。

改写一下脚本发现适配效果良好，上面的分析仿佛是多余的。

## End
![](https://img.meituan.net/imgupload/ef0cb7862e0c46c1d8bbd9c5ed54a7ec142208.png)

Comment List

Comment

© Copyright: This article is an original work and the copyright belongs to the Depy's docs unless marked as Reproduced

Please contact the blogger for authorization to reprint

Oracle Tns 协议解析

© Copyright: This article is an original work and the copyright belongs to the Depy's docs unless marked as ReproducedPlease contact the blogger for authorization to reprint

© Copyright: This article is an original work and the copyright belongs to the Depy's docs unless marked as Reproduced

Please contact the blogger for authorization to reprint