Oracle / Python转换为字符串 - > HEX（对于RAW列） - > varchar2

I have a table with a RAW column for holding an encrypted string.

我有一个带有RAW列的表,用于保存加密的字符串。

I have the PL/SQL code for encrypting from plain text into this field.

我有PL / SQL代码用于从纯文本加密到此字段。

I wish to create a trigger containg the encryption code.

我希望创建一个包含加密代码的触发器。

I wish to 'misuse' the RAW field to pass the plain text into the trigger. (I can't modify the schema, for example to add another column for the plain text field)

我希望“误用”RAW字段将纯文本传递给触发器。 (我无法修改架构,例如为纯文本字段添加另一列)

The client inserting the data is Python (cx_Oracle).

插入数据的客户端是Python(cx_Oracle)。

My question is how to best convert from a python string into HEX, then back to VARCHAR2 in the trigger so that the encryption code can be used without modification (encryption code expects VARCHAR2).

我的问题是如何最好地从python字符串转换为HEX,然后返回到触发器中的VARCHAR2,以便加密代码可以不加修改地使用(加密代码需要VARCHAR2)。

Here's an example:

这是一个例子:

create table BOB (name_enc raw(16));

In python. This is my initial attempt at encoding, I suspect I'll need something more portable for international character sets.

在python中。这是我最初的编码尝试,我怀疑我需要更便携的国际字符集。

name_enc = 'some text'.encode('hex')

The trigger

create or replace trigger enc_bob before insert on BOB
for each row
DECLARE
    v_name varchar2(50);

BEGIN

    v_name := :new.name_enc;   <----  WHAT DO I NEED HERE TO CONVERT FROM HEX to VARCHAR?

    --
    -- encryption code that expects v_name to contain string 

END;

UPDATE

The suggestion for using Base64 worked for me

使用Base64的建议对我有用

Python:

name_enc = base64.b64encode('some text')

PL/SQL:

v_name := utl_raw.cast_to_varchar2(UTL_ENCODE.BASE64_DECODE(:new.name_enc));

1 个解决方案

#1

Do you have to encode to hex?

你必须编码为十六进制?

I think there is a package (utl_encode) available for PL/SQL to decode Base64 for instance, you could use that?

我认为有一个包(utl_encode)可供PL / SQL解码Base64,你可以使用它吗?

#1