FTPS with PL/SQL

Anton Scheffer October 1, 2014 Database, Oracle, PL/SQL, Tools 15 Comments

Doing a FTP-job with PL/SQL is not difficult.
A basic implementation of RFC 959 can be written in a few hundred lines. See for instance ORACLE-BASE, How to FTP with Oracle PL/SQL or Oracle FAQ’s
But what if you want to secure your FTP transmission. Google doesn’t find any pure PL/SQL solutions, only java solutions with a PL/SQL wrapper.
Securing FTP transmission can be done in two ways: SFTP and FTPS.
SFTP stands for SSH File Transfer Protocol. This protocol is not based on the plain FTP protocol, implementing this in PL/SQL is not more difficult, it’s just a lot more work. You will not find anything about SFTP in this posting, maybe I will talk about that later (but see here an example)
FTPS stands for FTP Secure, RFC 4217 – Securing FTP with TLS. And it’s secured with the TLS protocol. Oracle has added support for this protocol to utl_tcp in version 11.2.0.2, UTP_TCP.
As with https you need a certificate (chain) of the server in a Oracle Wallet.
You have pass the path of this wallet to the utl_tcp.open_connection procedure

t_conn := utl_tcp.open_connection( p_host
, p_port
, wallet_path =&amp;amp;amp;amp;gt; p_wallet_path
, wallet_password =&amp;amp;amp;amp;gt; p_wallet_password
); -- open connection

And you tell the server that you want to secure the connection
write_cmd( t_conn, 'AUTH', 'TLS' )

If the server is OK with that, do it.
utl_tcp.secure_connection( t_conn );

That’s enough to secure the control channel of the FTP connection!
Doing the same for the data channel requires just two more commands
write_cmd( t_conn, 'PBSZ', '0' )
write_cmd( t_conn, 'PROT', 'P' )

A complete example:
declare
  t_conn utl_tcp.connection;
  t_blob blob;
  t_clob clob;
  t_reply number;
  type tp_dir_listing is table of varchar2(32767) index by pls_integer;
  t_dir_listing tp_dir_listing;
--
  g_response varchar2(1000 char);
  g_wallet_path     varchar2(1000 char);
  g_wallet_password varchar2(1000 char);
--
  function read_reply( p_conn in out nocopy utl_tcp.connection, p_verbose boolean := true )
  return number
  is
    t_line varchar(32767);
    t_code varchar2(3 char);
  begin
    t_line := utl_tcp.get_line( p_conn, true );
    if nvl( length( t_line ), 0 )  0
       and instr( t_tmp, '(', 1, 2 ) = 0
       and instr( t_tmp, ')' ) &amp;amp;amp;amp;gt; 0
       and instr( t_tmp, ')', 1, 2 ) = 0
       )
    then
      t_tmp := substr( t_tmp, instr( t_tmp, '(' ) + 1 );
      t_tmp := substr( t_tmp, 1, instr( t_tmp, ')' ) - 1 );
    else
      t_tmp := translate( t_tmp, '1234567890,' || t_tmp, '1234567890,' );
    end if;
    t_host := substr( t_tmp, 1, instr( t_tmp, ',', 1, 4 ) - 1 );
    t_host := replace( t_host, ',', '.' );
    t_tmp := substr( t_tmp, instr( t_tmp, ',', 1, 4 ) + 1 );
    t_port := to_number( substr( t_tmp, 1, instr( t_tmp, ',' ) - 1 ) );
    t_port := t_port * 256;
    t_port := t_port + to_number( substr( t_tmp, instr( t_tmp, ',' ) + 1 ) );
    t_conn := utl_tcp.open_connection( t_host, t_port, wallet_path =&amp;amp;amp;amp;gt; g_wallet_path, wallet_password =&amp;amp;amp;amp;gt; g_wallet_password );
    return t_conn;
  end;
--
  function write_cmd( p_conn in out nocopy utl_tcp.connection, p_cmd varchar2, p_param varchar2 := '' )
  return number
  is
    t_dummy pls_integer;
  begin
    t_dummy := utl_tcp.write_line( p_conn, p_cmd || rtrim( ' ' || p_param ) );
    return read_reply( p_conn );
  end;
--
  function open_data_channel( p_conn in out nocopy utl_tcp.connection )
  return utl_tcp.connection
  is
    t_tmp varchar2(32767);
    t_host varchar2(1000 char);
    t_port pls_integer;
    t_conn utl_tcp.connection;
  begin
    if write_cmd( p_conn, 'PASV' ) != 227
    then
      raise_application_error( -20001, 'Could not set PASSIVE: ' || g_response, true );
    end if;
    t_tmp := g_response;
    if (   instr( t_tmp, '(' ) &amp;amp;amp;amp;gt; 0
       and instr( t_tmp, '(', 1, 2 ) = 0
       and instr( t_tmp, ')' ) &amp;amp;amp;amp;gt; 0
       and instr( t_tmp, ')', 1, 2 ) = 0
       )
    then
      t_tmp := substr( t_tmp, instr( t_tmp, '(' ) + 1 );
      t_tmp := substr( t_tmp, 1, instr( t_tmp, ')' ) - 1 );
    else
      t_tmp := translate( t_tmp, '1234567890,' || t_tmp, '1234567890,' );
    end if;
    t_host := substr( t_tmp, 1, instr( t_tmp, ',', 1, 4 ) - 1 );
    t_host := replace( t_host, ',', '.' );
    t_tmp := substr( t_tmp, instr( t_tmp, ',', 1, 4 ) + 1 );
    t_port := to_number( substr( t_tmp, 1, instr( t_tmp, ',' ) - 1 ) );
    t_port := t_port * 256;
    t_port := t_port + to_number( substr( t_tmp, instr( t_tmp, ',' ) + 1 ) );
    t_conn := utl_tcp.open_connection( t_host, t_port, wallet_path =&amp;amp;amp;amp;gt; g_wallet_path, wallet_password =&amp;amp;amp;amp;gt; g_wallet_password );
    return t_conn;
  end;
--
  function login
    ( p_host varchar2
    , p_usr  varchar2 := null
    , p_pw   varchar2 := null
    , p_port number := 21
    , p_account varchar2 := null
    , p_wallet_path varchar2 := null
    , p_wallet_password varchar2 := null
    )
  return utl_tcp.connection
  is
    t_reply number;
    t_conn utl_tcp.connection;
    t_usr varchar2(1000 char);
    t_pw  varchar2(1000 char);
    t_acc varchar2(1000 char);
  begin
    t_conn := utl_tcp.open_connection( p_host, p_port, wallet_path =&amp;amp;amp;amp;gt; p_wallet_path, wallet_password =&amp;amp;amp;amp;gt; p_wallet_password ); -- open connection
    case read_reply( t_conn )
      when 120
      then
        raise_application_error( -20001, 'FTP server not ready: ' || g_response, true );
      when 421
      then
        raise_application_error( -20001, 'FTP server not available: ' || g_response, true );
      else
        null;
    end case;
    if write_cmd( t_conn, 'AUTH', 'TLS' ) = 234
    then
      utl_tcp.secure_connection( t_conn );
      if     write_cmd( t_conn, 'PBSZ', '0' ) = 200
         and write_cmd( t_conn, 'PROT', 'P' ) = 200
      then
        g_wallet_path     := p_wallet_path;
        g_wallet_password := p_wallet_password;
      end if;
    end if;
    if p_usr is null
    then
      t_usr := 'anonymous';
      t_pw  := 'anonymous@mysite.com';
      t_acc := '*********';
    else
      t_usr := p_usr;
      t_pw  := p_pw;
      t_acc := p_account;
    end if;
    t_reply := write_cmd( t_conn, 'USER', t_usr );
    if t_reply = 331
    then
      t_reply := write_cmd( t_conn, 'PASS', t_pw );
    end if;
    if t_reply = 332
    then
      t_reply := write_cmd( t_conn, 'ACCT', t_acc );
    end if;
    if t_reply not in ( 230, 202 )
    then
      raise_application_error( -20001, 'Could not log in: ' || g_response, true );
    end if;
    return t_conn;
  end;
--
  procedure get_file
    ( p_conn in out nocopy utl_tcp.connection
    , p_path in varchar2
    , p_file in out blob
    )
  is
    t_reply number;
    t_buf raw(32767);
    t_cnt pls_integer;
    t_conn utl_tcp.connection;
  begin
    t_reply := write_cmd( p_conn, 'TYPE', 'I' );
    t_conn := open_data_channel( p_conn );
    t_reply := write_cmd( p_conn, 'RETR', p_path );
    if t_reply in ( 125, 150 )
    then
      if g_wallet_path is not null
      then
        utl_tcp.secure_connection( t_conn );
      end if;
      dbms_lob.createtemporary( p_file, true );
      begin
        loop
          t_cnt := utl_tcp.read_raw( t_conn, t_buf, 32767 );
          dbms_lob.writeappend( p_file, t_cnt, t_buf );
        end loop;
      exception
        when utl_tcp.end_of_input
        then
          null;
      end;
      utl_tcp.close_connection( t_conn );
      t_reply := read_reply( p_conn );
    end if;
    if t_reply not in ( 226, 250 )
    then
      raise_application_error( -20001, 'Could not retrieve file: ' || g_response, true );
    end if;
  end;
--
  procedure get_file
    ( p_conn in out nocopy utl_tcp.connection
    , p_path in varchar2
    , p_file in out clob
    )
  is
    t_reply number;
    t_buf varchar2(32767);
    t_cnt pls_integer;
    t_conn utl_tcp.connection;
  begin
    t_reply := write_cmd( p_conn, 'TYPE', 'A' );
    t_conn := open_data_channel( p_conn );
    t_reply := write_cmd( p_conn, 'RETR', p_path );
    if t_reply in ( 125, 150 )
    then
      if g_wallet_path is not null
      then
        utl_tcp.secure_connection( t_conn );
      end if;
      dbms_lob.createtemporary( p_file, true );
      begin
        loop
          t_buf := utl_i18n.raw_to_char( utl_tcp.get_raw( t_conn, 32767 ), 'US7ASCII' );
          dbms_lob.writeappend( p_file, length( t_buf ), t_buf );
        end loop;
      exception
        when utl_tcp.end_of_input
        then
          null;
      end;
      utl_tcp.close_connection( t_conn );
      t_reply := read_reply( p_conn );
    end if;
    if t_reply not in ( 226, 250 )
    then
      raise_application_error( -20001, 'Could not retrieve file: ' || g_response, true );
    end if;
  end;
--
  procedure nlst
    ( p_conn in out nocopy utl_tcp.connection
    , p_dir_listing in out tp_dir_listing
    , p_path in varchar2 := null
    )
  is
    t_reply number;
    t_conn utl_tcp.connection;
  begin
    p_dir_listing.delete;
    t_reply := write_cmd( p_conn, 'TYPE', 'A' );
    t_conn := open_data_channel( p_conn );
    t_reply := write_cmd( p_conn, 'NLST', p_path );
    if t_reply in ( 125, 150 )
    then
      if g_wallet_path is not null
      then
        utl_tcp.secure_connection( t_conn );
      end if;
      begin
        loop
          p_dir_listing( p_dir_listing.count + 1 ) := utl_tcp.get_line( t_conn, true );
        end loop;
      exception
        when utl_tcp.end_of_input
        then
          null;
      end;
      utl_tcp.close_connection( t_conn );
      t_reply := read_reply( p_conn );
    end if;
    if t_reply != 226
    then
      raise_application_error( -20001, 'Could not get NLST: ' || g_response, true );
    end if;
  end;
--
begin
  t_conn := login( 'localhost', p_wallet_path =&amp;amp;amp;amp;gt; 'file:C:\oracle\wallet', p_wallet_password =&amp;amp;amp;amp;gt; 'amis.rules.again' );
--  get_file( t_conn, '/my_dir/my_ascii_file.txt', t_clob );
  nlst( t_conn, t_dir_listing, '*.txt' );
  t_reply := write_cmd( t_conn, 'QUIT' );
  utl_tcp.close_connection( t_conn );
end;

You will find the same example here

			Share this:
Click to print (Opens in new window)
Click to share on LinkedIn (Opens in new window)
Click to share on Twitter (Opens in new window)
Click to email a link to a friend (Opens in new window)
Click to share on WhatsApp (Opens in new window)
Like this:
Like Loading...

Tags:FTP, plsql

About The Author

Anton Scheffer

Oracle Consultant at AMIS

15 Comments

Trung Vu Duc March 12, 2018

Dear Sir

My system use Protocol: FTP, Encryption: TSL/SSL Explicit Encryption, no use cef file.
I run that code

t_reply := write_cmd(t_conn, ‘AUTH’, ‘TLS’);
if p_usr is null then
t_usr := ‘anonymous’;
t_pw := ‘anonymous@mysite.com’;
t_acc := ‘*********’;
else
t_usr := p_usr;
t_pw := p_pw;
t_acc := p_account;
end if;
t_reply := write_cmd(t_conn, ‘USER ‘ || t_usr);
if t_reply = 331 then
t_reply := write_cmd(t_conn,’PASS ‘ || t_pw);
end if;

display error:
220 (vsFTPd 3.0.2)
234 Proceed with negotiation.
500 OOPS: error:140760FC:SSL routines:SSL23_GET_CLIENT_HELLO:unknown protocol
Pls check help us.
Jennifer Shi October 6, 2016

Hi, thanks for the excellent example! I was online search for FTPS with utl_tcp and found you excellent example.

I’m running the code from an 11.2.0.4.0 DB but I am not able to get it work yet. my session keep get timeout on write_cmd( t_conn, ‘AUTH’, ‘TLS’ ). Do you have any idea why?
- Anton Scheffer October 6, 2016
  
  @Jennifer. Sorry, I have no idea.
Aswin Kumar April 21, 2016

Thanks for the details. Can you also provide details for SFTP using UTL_TCP?
- Anton Scheffer April 21, 2016
  
  Yes, I can. And will do so, someday, when I have some (a lot) spare time to make the code presentable.
Marianne Mulinski April 2, 2016

Excellent, thank you!
Johny HP February 26, 2016

For anyone interested in putting data on an FTPs server, I have managed to put remote data into a file with the following.

PROCEDURE put_remote_binary_data (p_conn IN OUT NOCOPY UTL_TCP.connection,
p_file IN VARCHAR2,
p_data IN BLOB) IS
l_result PLS_INTEGER;
l_buffer RAW(32767);
l_amount BINARY_INTEGER := 32767;
l_pos INTEGER := 1;
l_blob_len INTEGER;
BEGIN
t_reply := write_cmd( p_conn, ‘TYPE’, ‘I’ );
t_conn := open_data_channel( p_conn );
t_reply := write_cmd( p_conn, ‘STOR ‘ || p_file);
l_blob_len := DBMS_LOB.getlength(p_data);
—
WHILE l_pos <= l_blob_len LOOP
DBMS_LOB.READ (p_data, l_amount, l_pos, l_buffer);
l_result := UTL_TCP.write_raw(t_conn, l_buffer, l_amount);
UTL_TCP.flush(t_conn);
l_pos := l_pos + l_amount;
END LOOP;

UTL_TCP.close_connection(t_conn);

EXCEPTION
WHEN OTHERS THEN
UTL_TCP.close_connection(t_conn);
RAISE;
END;
Johny HP February 26, 2016

Thank you for this. I have managed to make a secure connection with your example. Isn’t there a method for putting files on the server?
- Anton Scheffer February 26, 2016
  
  @Johny HP.
  No, there’s isn’t a method for putting files in this example. But if you want:
  procedure put_file
  ( p_conn in out nocopy utl_tcp.connection
  , p_path in varchar2
  , p_file in blob
  )
  is
  t_reply number;
  t_cnt pls_integer;
  t_conn utl_tcp.connection;
  t_chunksize pls_integer := 32767;
  begin
  t_reply := write_cmd( p_conn, ‘TYPE’, ‘I’ );
  t_conn := open_data_channel( p_conn );
  t_reply := write_cmd( p_conn, ‘STOR’, p_path );
  if t_reply in ( 125, 150 )
  then
  if g_wallet_path is not null
  then
  utl_tcp.secure_connection( t_conn );
  end if;
  for i in 0 .. trunc( ( dbms_lob.getlength( p_file ) – 1 ) / t_chunksize )
  loop
  t_cnt := utl_tcp.write_raw( t_conn, dbms_lob.substr( p_file, t_chunksize, i * t_chunksize + 1 ) );
  end loop;
  utl_tcp.close_connection( t_conn );
  t_reply := read_reply( p_conn );
  end if;
  if t_reply not in ( 226, 250 )
  then
  raise_application_error( -20001, ‘Could not store file: ‘ || g_response, true );
  end if;
  end;
  —
  procedure put_file
  ( p_conn in out nocopy utl_tcp.connection
  , p_path in varchar2
  , p_file in clob
  , p_change_crlf boolean := false
  )
  is
  t_reply number;
  t_buf raw(32767);
  t_cnt pls_integer;
  t_conn utl_tcp.connection;
  t_chunksize pls_integer := 32767;
  begin
  t_reply := write_cmd( p_conn, ‘TYPE’, ‘A’ );
  t_conn := open_data_channel( p_conn );
  t_reply := write_cmd( p_conn, ‘STOR’, p_path );
  if t_reply in ( 125, 150 )
  then
  if g_wallet_path is not null
  then
  utl_tcp.secure_connection( t_conn );
  end if;
  if p_change_crlf
  then
  t_chunksize := 16383;
  end if;
  for i in 0 .. trunc( ( dbms_lob.getlength( p_file ) – 1 ) / t_chunksize )
  loop
  if p_change_crlf
  then
  t_buf := utl_i18n.string_to_raw( replace( dbms_lob.substr( p_file, t_chunksize, i * t_chunksize + 1 )
  , c_lf
  , c_crlf
  )
  , ‘US7ASCII’
  );
  else
  t_buf := utl_i18n.string_to_raw( dbms_lob.substr( p_file, t_chunksize, i * t_chunksize + 1 )
  , ‘US7ASCII’
  );
  end if;
  t_cnt := utl_tcp.write_raw( t_conn, t_buf );
  end loop;
  utl_tcp.close_connection( t_conn );
  t_reply := read_reply( p_conn );
  end if;
  if t_reply not in ( 226, 250 )
  then
  raise_application_error( -20001, ‘Could not store file: ‘ || g_response, true );
  end if;
  end;
  —
  - Johny HP March 31, 2016
    
    You are the boos
Mike Huitson February 5, 2016

Thanks Anton, that is a shame.
Mike Huitson February 5, 2016

Hi, thanks for the excellent example!

I’m running the code from an 11.2.0.4.0 DB and it works perfectly as long as the server I’m connecting to does not “Require TLS session resumption on data connection when using PROT P”.

When session resumption is required I get the following error when the nlst or get_file procedures call utl_tcp.secure_connection for the data connection:

ORA-29259: end-of-input reached
ORA-06512: at “SYS.UTL_TCP”, line 59
ORA-06512: at “SYS.UTL_TCP”, line 284

and on the FTP server the following message is logged:

“450 TLS session of data connection has not resumed or the session does not match the control connection”

Do I need to do something different when session resumption is required or is this just not supported by utl_tcp?
- Anton Scheffer February 5, 2016
  
  @Mike Huitson
  As far as I know utl_tcp doesn’t support TLS session resumption.
Francisco Javier Melado March 10, 2015

Muchas gracias por el código.

Please, could you send me the procedure code for WRITE_CMD?
- Anton Scheffer March 10, 2015
  
  It is in this link: https://technology.amis.nl/wp-content/uploads/2014/10/as_ftp01.txt

Share this:

Like this:

Related Posts

About The Author

Anton Scheffer

15 Comments