云数据库 GaussDB-密态函数和操作符:示例

时间：2023-11-01 16:18:33

云数据库 GaussDB

示例

byteawithoutorderwithequalcolin、byteawithoutorderwithequalcolout等密态等值函数为数据库内核中数据类型byteawithoutorderwithequalcol指定的in、out、send、recv等读写格式转换函数，具体可参考bytea类型的byteain、byteaout等函数，但会对本地的cek进行验证，需要密文字段中有本地存在的cekoid才能执行成功。

  
       -- 例如存在加密表int_type，int_col2为其加密列-- 使用非密态客户端连接数据库，查询加密列密文gaussdb=# select int_col2 from int_type;                                                                       int_col2------------------------------------------------------------------------------------------------------------------------------------------------------ \x01c35301bf421c8edf38c34704bcc82838742917778ccb402a1b7452ad4a6ac7371acc0ac33100000035fe3424919854c86194f1aa5bb4e1ca656e8fc6d05324a1419b69f488bdc3c6(1 row)-- 将加密列密文当做byteawithoutorderwithequalcolin入参，格式从cstring输入转码转化成内部byteawithoutorderwithequalcol形式gaussdb=# select byteawithoutorderwithequalcolin('\x01c35301bf421c8edf38c34704bcc82838742917778ccb402a1b7452ad4a6ac7371acc0ac33100000035fe3424919854c86194f1aa5bb4e1ca656e8fc6d05324a1419b69f488bdc3c6');                                                           byteawithoutorderwithequalcolin------------------------------------------------------------------------------------------------------------------------------------------------------ \x01c35301bf421c8edf38c34704bcc82838742917778ccb402a1b7452ad4a6ac7371acc0ac33100000035fe3424919854c86194f1aa5bb4e1ca656e8fc6d05324a1419b69f488bdc3c6(1 row)

由于byteawithoutorderwithequalcolin等的实现会对cek进行查找，并且判断是否为正常加密后的数据类型。

因此如果用户输入数据的格式不是加密后的数据格式，并且在本地不存在对应cek的情况下，会返回错误。

  
       gaussdb=# SELECT * FROM byteawithoutorderwithequalcolsend('\x907219912381298461289346129'::byteawithoutorderwithequalcol);ERROR:  cek with OID 596711794 not foundLINE 1: SELECT * FROM byteawithoutorderwithequalcolsend('\x907219912...                                                        ^gaussdb=# SELECT * FROM byteawithoutordercolout('\x90721901999999999999912381298461289346129');ERROR:  cek with OID 2566986098 not foundLINE 1: SELECT * FROM byteawithoutordercolout('\x9072190199999999999...   SELECT * FROM byteawithoutorderwithequalcolrecv('\x90721901999999999999912381298461289346129'::byteawithoutorderwithequalcol);ERROR:  cek with OID 2566986098 not found                                           ^gaussdb=# SELECT * FROM byteawithoutorderwithequalcolsend('\x90721901999999999999912381298461289346129'::byteawithoutorderwithequalcol);ERROR:  cek with OID 2566986098 not foundLINE 1: SELECT * FROM byteawithoutorderwithequalcolsend('\x907219019...                                                        ^