13 Replies Latest reply: Aug 9, 2012 7:04 AM by cagatay RSS

    XML encryption

    cagatay
      Hi,

      is there any method to xml encrytion do with pl-sql ?

      for example ;
      <ROW>
      <NAME>JOHN</NAME>
      <SURNAME>WALLER</SURNAME>
      </ROW>

      i want to see ;

      <ROW>
      <NAME>encrypted value</NAME>
      <SURNAME>encrypted value</SURNAME>
      </ROW>

      by the way i'm creating xml via Dbms_Xmlgen.Getxml function.

      Thanks.

      Edited by: cagatayyil on 08.Ağu.2012 05:00

      Edited by: cagatayyil on 08.Ağu.2012 05:23
        • 1. Re: XML encryption
          odie_63
          i want to see ;

          <ROW>
          <NAME>encrypted value</NAME>
          <SURNAME>encrypted value</SURNAME>
          </ROW>
          Care to be more specific?
          Which encryption method do you want to use? There are plenty.
          • 2. Re: XML encryption
            cagatay
            thank for your response,
            i want to use base64 or another oracle built-in encryption method.
            • 3. Re: XML encryption
              AlexAnd
              look at http://psoug.org/reference/utl_encode.html

              UTL_ENCODE.BASE64_ENCODE
              UTL_ENCODE.BASE64_DECODE
              • 4. Re: XML encryption
                odie_63
                Well, base64 is not encryption, it's encoding.

                See UTL_ENCODE.BASE64_ENCODE

                For example :
                SQL> select xmlserialize(document
                  2           xmlelement("rowset",
                  3             xmlagg(
                  4               xmlelement("row",
                  5                 xmlelement("name", ename)
                  6               , xmlelement("encoded_name",
                  7                   utl_raw.cast_to_varchar2(
                  8                     utl_encode.base64_encode(
                  9                       utl_raw.cast_to_raw(ename)
                 10                     )
                 11                   )
                 12                 )
                 13               )
                 14             )
                 15           )
                 16           as clob indent
                 17         )
                 18  from scott.emp
                 19  where deptno = 10
                 20  ;
                 
                XMLSERIALIZE(DOCUMENTXMLELEMEN
                --------------------------------------------------------------------------------
                <rowset>
                  <row>
                    <name>CLARK</name>
                    <encoded_name>Q0xBUks=</encoded_name>
                  </row>
                  <row>
                    <name>KING</name>
                    <encoded_name>S0lORw==</encoded_name>
                  </row>
                  <row>
                    <name>MILLER</name>
                    <encoded_name>TUlMTEVS</encoded_name>
                  </row>
                </rowset>
                 
                • 5. Re: XML encryption
                  cagatay
                  but i'm creating dynamic xml using this;

                  select Dbms_Xmlgen.getXML('select ename from scott.emp where deptno = 10') from dual;

                  while using this how can i encode my xml, actually that's gonna be fix my problem.
                  • 6. Re: XML encryption
                    odie_63
                    but i'm creating dynamic xml using this;

                    select Dbms_Xmlgen.getXML('select ename from scott.emp where deptno = 10') from dual;
                    Is it used in a generic procedure that accepts any query, or is the same query used every time?

                    Depending on the answer, I suppose it's not an option to do the following :
                    select Dbms_Xmlgen.getXML('select utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(ename))) from scott.emp where deptno = 10') from dual;
                    Do you want to encode every column in the result set?
                    • 7. Re: XML encryption
                      AlexAnd
                      select Dbms_Xmlgen.getXML('select ename, utl_encode.base64_encode(utl_raw.cast_to_raw(ename)) as ename64 from scott.emp where deptno = 10') from dual;
                      <?xml version="1.0"?>
                      <ROWSET>
                       <ROW>
                        <ENAME>CLARK</ENAME>
                        <ENAME64>51307842556B733D</ENAME64>
                       </ROW>
                       <ROW>
                        <ENAME>KING</ENAME>
                        <ENAME64>53306C4F52773D3D</ENAME64>
                       </ROW>
                       <ROW>
                        <ENAME>MILLER</ENAME>
                        <ENAME64>54556C4D54455653</ENAME64>
                       </ROW>
                      </ROWSET>
                      • 8. Re: XML encryption
                        cagatay
                        actually it is same query used every time.

                        yeah i want to encode every column on the result set.
                        • 9. Re: XML encryption
                          odie_63
                          cagatayyil wrote:
                          actually it is same query used every time.

                          yeah i want to encode every column on the result set.
                          Then why go dynamic in this case?

                          Just use SQL/XML functions as shown earlier and apply the encoding function to each column.
                          • 10. Re: XML encryption
                            cagatay
                            i have a lots of column, that's why i used dynamic structure.

                            actually i have no idea sql/xml functions and i don't know how?
                            • 11. Re: XML encryption
                              odie_63
                              cagatayyil wrote:
                              i have a lots of column, that's why i used dynamic structure.
                              So that you can use "SELECT *" ?
                              Sorry, not a good reason, and a bad practice with that.
                              actually i have no idea sql/xml functions and i don't know how?
                              See my reply above where I used XMLElement, XMLAgg etc.

                              Edited by: odie_63 on 8 août 2012 18:27
                              • 12. Re: XML encryption
                                AlexAnd
                                >
                                i have a lots of column, that's why i used dynamic structure.

                                actually i have no idea sql/xml functions and i don't know how?
                                >

                                you can construct your query statement based on user_tab_columns then copy-past to Dbms_Xmlgen.getXML if you use sql engine or construct directly in plsql block

                                for example for
                                select ename, utl_encode.base64_encode(utl_raw.cast_to_raw(ename)) as ename64 from scott.emp
                                you can use smth
                                declare
                                stmt varchar2(4000) := null;
                                q_table_name varchar2(100) := 'EMP';
                                begin
                                
                                for x in (select column_name from user_tab_columns where table_name = q_table_name)
                                loop
                                  stmt := stmt || 
                                          ', ' || x.column_name || ' as ' || x.column_name || 
                                          ', ' || 'utl_encode.base64_encode(utl_raw.cast_to_raw(' || x.column_name || ')) as ' || x.column_name || '_base64';
                                end loop;
                                  stmt := 'select ' || ltrim(stmt, ', ') || ' from ' || q_table_name || ' where deptno=''10''';
                                  dbms_output.put_line(stmt);  
                                end;
                                as result
                                select EMPNO as EMPNO, utl_encode.base64_encode(utl_raw.cast_to_raw(EMPNO)) as EMPNO_base64, ENAME as ENAME, utl_encode.base64_encode(utl_raw.cast_to_raw(ENAME)) as ENAME_base64, JOB as JOB, utl_encode.base64_encode(utl_raw.cast_to_raw(JOB)) as JOB_base64, MGR as MGR, utl_encode.base64_encode(utl_raw.cast_to_raw(MGR)) as MGR_base64, HIREDATE as HIREDATE, utl_encode.base64_encode(utl_raw.cast_to_raw(HIREDATE)) as HIREDATE_base64, SAL as SAL, utl_encode.base64_encode(utl_raw.cast_to_raw(SAL)) as SAL_base64, COMM as COMM, utl_encode.base64_encode(utl_raw.cast_to_raw(COMM)) as COMM_base64, DEPTNO as DEPTNO, utl_encode.base64_encode(utl_raw.cast_to_raw(DEPTNO)) as DEPTNO_base64 from EMP where deptno='10'
                                • 13. Re: XML encryption
                                  cagatay
                                  okey guys thank you, i fixed my problem using your instructions.
                                  thanks again.

                                  Edited by: cagatayyil on 09.Ağu.2012 05:04