antonscheffer / as_sftp Goto Github PK

View Code? Open in Web Editor NEW

53.0 53.0 19.0 205 KB

A plsql SFTP client package

License: MIT License

PLSQL 100.00%

plsql sftp

as_sftp's People

Contributors

Stargazers

Watchers

Forkers

shrikeshpandey leventfatihkaraer toby-marks abhisheknimje26101999 arintaphon swagner edgbaez maheshdmtech mumer29 seek78 victoire-sebu lee-lindley adamg4c maheshbabupara yojo76 jaghte nomklong ppshrms arasheghtedar

as_sftp's Issues

kex ecdh-sha2-nistp521 not always working

Hi @antonscheffer ,
The kex ecdh-sha2-nistp521 is not validating signature 4 times on 10 connections.
ecdh-sha2-nistp384 is working fine.

I tried to output l_H, l_rsa_e, l_rsa_n, l_rsa_s, l_tmp to see if there is something very wrong (I don't lonw much about crypto algorithms) but I couldn't find anything
dbms_crypto.hash( l_H, l_hash_type ) is sometimes different of UTL_RAW.SUBSTR( l_tmp, - l_hash_len ).

Morgan

Issue with DSA public key

Hi all,

While trying to connect with a server using key authentication, I always get this error:

try ssh-dss public key
ssh-dss public key not OK
ORA-20030: Could not login.

I tested the key with putty and FileZilla from different client PCs, without problems, but with the package it does not seem to work.
I cannot test another type (e.g. RSA) as DSA is the only type supported at the moment.

Problem with download

Hi, after a successful connection all downloaded files result zero-length.
Any advice?
Thanks

Does the package support RSA key login?

Dear Mr. Scheffer,

in your blog article https://technology.amis.nl/languages/oracle-plsql/finally-sftp-from-an-oracle-database-using-plain-plsql/ from Christmas 2019 you wrote that for the moment only login with username and password is possible with your package as_sftp.

Now, more than 3 years later (not 5 yet 😉) I got the package from github and found the procedure login_pk which seems to implement exactly the feature that you described as missing in your blog article 😊

Can you give me a short hint - does RSA key login work with that procedure?

In any case, you have done a great job with the package!

Thanks in advance for a short answer and best regards from Germany

Login using a RSA key

Hi, how can I login using a RSA key please? I did some tests but it didn't work.

Thanks.

TLS_AES_256_GCM_SHA384

Hello Anton,
How problematical would be to add TLS_AES_256_GCM_SHA384 support?

First attempt to open connection failed

I installed this package in an existing oracle (19.13) account that already has all the needed ACLs/ACEs for sftp. I tried to check if I could just open a connection. My server name is masked here, but the script has the fully-qualified host name.

begin as_sftp.open_connection( i_host => '***********' , i_trust_server => true ); as_sftp.close_connection; end; /

Do you know what might be causing this error?

everything closed
try to connect to ***********, port 22
ORA-06512: at "SYS.UTL_TCP", line 19
ORA-06512: at "SYS.UTL_TCP", line 295
ORA-06512: at "AS_SFTP", line 1717

everything closed
everything closed

PL/SQL procedure successfully completed.

Key management

Is just saw this nice addition: Protecting/Hiding Data in Oracle
I might even add it myself

Permission to add to OOS Utils

@antonscheffer can I add this code (may alter etc) to @OraOpenSource OOS Utils? If yes, I'll take this as full permission to copy the code, alter it, etc.

Thanks in advance.

ORA-29276: transfer timeout faced while using as_sftp.put_file

Hi @antonscheffer,

We have been using as_sftp in our project and it is working great :)

However, we are facing below issue intermittently:
ORA-29276: transfer timeout

while calling as_sftp.put_file(i_path, i_directory, i_filename) procedure.

I have checked that there is no timeout value specified in put_file procedure.

The procedure is returning 1 after printing: put_file: done, which means that the issue is appearing after moving file to the SFTP.
Please see below the logs snippet for the same:

connect with password
logged in
sftp openend, server version: 3
sftp open
2022-05-07 00:01:20
put_file: opened
2022-05-07 00:01:23
put_file: done
1

I am unable to replicate the issue as it is intermittent.

Could you please help me with this?

Problem with open_connection

Hello ,
I have an error with as_sftp.open_connection.
-- my code is :

begin
as_sftp.open_connection( i_host => ‘myhost’, i_port => 2222 , i_trust_server => true);
dbms_output.put_line('login');
as_sftp.login( i_user => 'mysuser', i_password =>'mypass' ) ;
as_sftp.close_connection;
end;

Rapport d'erreur -
ORA-29259: fin d'entrée atteinte
ORA-06512: à "SYS.UTL_TCP", ligne 94
ORA-06512: à "SYS.UTL_TCP", ligne 322
ORA-06512: à "SYS.UTL_TCP", ligne 385
ORA-06512: à "HR.AS_SFTP", ligne 1462
ORA-06512: à "HR.AS_SFTP", ligne 1710
ORA-06512: à "HR.AS_SFTP", ligne 2413
ORA-06512: à "HR.AS_SFTP", ligne 4468
ORA-06512: à "HR.AS_SFTP", ligne 4511
ORA-06512: à ligne 3
29259. 00000 - "end-of-input reached"
*Cause: The end of the input was reached.
*Action: If the end of the input is reached prematurely, check if the input
source terminates prematurely. Otherwise, close the connection
to the input.
everything closed

try to connect to XXXXX (my_host) , port 2222
SSH-2.0-mod_sftp/0.9.9
using aes128-cbc, aes128-cbc
using hmac-sha1, hmac-sha1
using ssh-dss
using diffie-hellman-group14-sha1

when i add i_excluded_kex_algos => 'diffie-hellman-group14-sha1,diffie-hellman-group14-sha256'
i have :

try to connect to (my host ...... ) , port 2222
SSH-2.0-mod_sftp/0.9.9
using aes128-cbc, aes128-cbc
using hmac-sha1, hmac-sha1
using ssh-dss
using diffie-hellman-group1-sha1

I tested on another SFTP host in port 2 and it works.

I don't undestand why on port 2222 it doesn't work.

Can you help me please.

Thank you.

Uploading the file partially

We have been using your SFTP package since 2011, and it works well with almost all of our servers, including PPK keys.

When we use put_file for sending a 5mb file to one of the servers (sftp.server_1.com), the file gets received partially around 200kb.

Debugging throws the exception from read_fxp_message exactly from the below line
put_file >read_fxp_message>p_buf := utl_raw.substr(p_buf, 5)

select utl_raw.substr('00000020', 5) from dual
ORA-06502: PL/SQL: numeric or value error

Please note that the same file can to sent without any issue to one of the other server(mft.server_2.com) . Attached both logs for your verification.
log_server_1.txt
log_server_2.txt

I would greatly appreciate any assistance you can provide

my_encr_algo_client_to_server and server_to_client list

Hi Anton,
First post on GitHub, I hope i will not do something wrong.

f_decrypt (server to client) and read_packet (server to client)
can manage 'aes128-ctr', 'aes192-ctr', 'aes256-ctr' 'aes128-cbc', 'aes192-cbc', 'aes256-cbc', 'rijndael256-cbc', '[email protected]' and '3des-cbc'

write_packet (client to server) can manage the same

 IF g_encr_algo_c IN ( 'aes128-cbc', 'aes128-ctr', 'aes192-cbc', 'aes192-ctr', 'aes256-cbc', 'aes256-ctr', 'rijndael256-cbc', '[email protected]' )
  THEN l_block_size := 16;
  ELSE l_block_size := 8;

I verified init_hmac_ids and add2name_list for 11g database, and see that add2name_list was only adding in 12c (cause HMAC_SH256 is NULL in 11g)
But the matching algo is the first one of client list.. so sha1 will always be first preference although it is not a recommended encrypting algo.

So I changed this :

init_hmac_ids

-- Commentary for understanding (because first invalid command exiting on Exception)
BEGIN
	-- 11g+
  execute immediate 'begin :x := dbms_crypto.HASH_MD5; end;'   USING OUT HASH_MD5;
  execute immediate 'begin :x := dbms_crypto.HASH_SH1; end;'   USING OUT HASH_SH1;
  execute immediate 'begin :x := dbms_crypto.HMAC_MD5; end;'   USING OUT HMAC_MD5;
  execute immediate 'begin :x := dbms_crypto.HMAC_SH1; end;'   USING OUT HMAC_SH1;

	-- 12c+
  execute immediate 'begin :x := dbms_crypto.HASH_SH256; end;' USING OUT HASH_SH256;
  execute immediate 'begin :x := dbms_crypto.HASH_SH384; end;' USING OUT HASH_SH384;
  execute immediate 'begin :x := dbms_crypto.HASH_SH512; end;' USING OUT HASH_SH512;
  execute immediate 'begin :x := dbms_crypto.HMAC_SH256; end;' USING OUT HMAC_SH256;
  execute immediate 'begin :x := dbms_crypto.HMAC_SH512; end;' USING OUT HMAC_SH512;

EXCEPTION WHEN OTHERS THEN NULL;
END;

Change in Procedure show_name_list (added p_lib for more info) and check is p_nl has data

PROCEDURE show_name_list( p_nl tp_name_list, p_lib VARCHAR2 DEFAULT NULL )
IS
  l_str varchar2(32767);
BEGIN
	
  IF NVL(p_nl.COUNT, 0) = 0 THEN RETURN; END IF; 
  
  FOR i IN 1 .. p_nl.COUNT
  LOOP
    l_str := l_str ||','|| p_nl( i );
  END LOOP;
  info_msg( p_lib || LTRIM( l_str, ',' ) );
END;

Change in handle_kex for having all algo used in read_packet and write_packet, and change order of algo (most secure first : SHA512 first, and SHA1 last)
So because SHA512 is not available in 11g, I have to initialize tp_name_list to an empty list

PROCEDURE handle_kex
..
	my_kex_algorithms              tp_name_list := tp_name_list();
  my_public_key_algorithms       tp_name_list := tp_name_list();
  my_encr_algo_client_to_server  tp_name_list := tp_name_list();
  my_encr_algo_server_to_client  tp_name_list := tp_name_list();
  my_mac_algo_client_to_server   tp_name_list := tp_name_list();
  my_mac_algo_server_to_client   tp_name_list := tp_name_list();
  my_compr_algo_client_to_server tp_name_list := tp_name_list();
  my_compr_algo_server_to_client tp_name_list := tp_name_list();
  my_languages_client_to_server  tp_name_list := tp_name_list();
  my_languages_server_to_client  tp_name_list := tp_name_list();
...
  PROCEDURE add2name_list( p_name_list IN OUT tp_name_list, p_val varchar2, p_usable NUMBER DEFAULT 1 )
  IS
  BEGIN
    IF p_usable IS NOT NULL
    THEN
      p_name_list.EXTEND;
      p_name_list( p_name_list.COUNT ) := p_val;
    END IF;
  END;
...
  show_name_list( kex_algorithms, 'kex_algorithms : ');
  show_name_list( public_key_algorithms, 'public_key_algorithms : ');
  show_name_list( encr_algo_client_to_server, 'encr_algo_client_to_server : ');
  show_name_list( encr_algo_server_to_client, 'encr_algo_server_to_client : ');
  show_name_list( mac_algo_client_to_server, 'mac_algo_client_to_server : ');
  show_name_list( mac_algo_server_to_client, 'mac_algo_server_to_client : ');
  show_name_list( compr_algo_client_to_server, 'compr_algo_client_to_server : ');
  show_name_list( compr_algo_server_to_client, 'compr_algo_server_to_client : ');
  show_name_list( languages_client_to_server, 'languages_client_to_server : ');
  show_name_list( languages_server_to_client, 'languages_server_to_client : ');

 /* The client sends algorithm lists in order of preference, while the server sends a list of algorithms that it supports.
	The first supported algorithm in order of the client’s preference is the algorithm that is chosen.
	So most encrypted (HASH_SH512) First, HASH_SH1 last*/

    add2name_list( my_kex_algorithms, 'diffie-hellman-group15-sha512', HASH_SH512 );
    add2name_list( my_kex_algorithms, 'diffie-hellman-group16-sha512', HASH_SH512 );
    add2name_list( my_kex_algorithms, 'diffie-hellman-group17-sha512', HASH_SH512 );
    add2name_list( my_kex_algorithms, 'diffie-hellman-group18-sha512', HASH_SH512 );
    add2name_list( my_kex_algorithms, 'ecdh-sha2-nistp521', HASH_SH512 );
    add2name_list( my_kex_algorithms, 'ecdh-sha2-nistp384', HASH_SH384 );
    add2name_list( my_kex_algorithms, 'ecdh-sha2-nistp256', HASH_SH256 );
    add2name_list( my_kex_algorithms, 'diffie-hellman-group14-sha256', HASH_SH256 );
    add2name_list( my_kex_algorithms, 'diffie-hellman-group-exchange-sha256', HASH_SH256 );
    add2name_list( my_kex_algorithms, 'diffie-hellman-group14-sha1',  HASH_SH1);
    add2name_list( my_kex_algorithms, 'diffie-hellman-group1-sha1',  HASH_SH1);
    add2name_list( my_kex_algorithms, 'diffie-hellman-group-exchange-sha1',  HASH_SH1);
  show_name_list( my_kex_algorithms, 'my_kex_algorithms : ');

--  my_public_key_algorithms := tp_name_list( 'ssh-dss', 'ssh-rsa' );
    add2name_list( my_public_key_algorithms, 'rsa-sha2-512', HASH_SH512 );
    add2name_list( my_public_key_algorithms, 'rsa-sha2-256', HASH_SH256 );
    add2name_list( my_public_key_algorithms, 'ssh-dss', HASH_SH1 );
    add2name_list( my_public_key_algorithms, 'ssh-rsa', HASH_SH1 );
  show_name_list( my_public_key_algorithms, 'my_public_key_algorithms : ');

    add2name_list( my_encr_algo_client_to_server, 'aes256-cbc', HASH_SH256 );
    add2name_list( my_encr_algo_client_to_server, 'rijndael256-cbc', HASH_SH256 ); 
    add2name_list( my_encr_algo_client_to_server, '[email protected]', HASH_SH256 ); -- Same as aes256-cbc and rijndael256-cbc : deprecated since 2014.
    add2name_list( my_encr_algo_client_to_server, 'aes256-ctr', HASH_SH256);
    add2name_list( my_encr_algo_client_to_server, 'aes192-cbc', HASH_SH256 );
    add2name_list( my_encr_algo_client_to_server, 'aes192-ctr', HASH_SH256 );
    add2name_list( my_encr_algo_client_to_server, 'aes128-cbc', HASH_SH1 ); 
    add2name_list( my_encr_algo_client_to_server, 'aes128-ctr', HASH_SH1 );
    add2name_list( my_encr_algo_client_to_server, '3des-cbc', HASH_SH1 );
  show_name_list( my_encr_algo_client_to_server, 'my_encr_algo_client_to_server : ');

    -- Same in server_to_client
     my_encr_algo_server_to_client := my_encr_algo_client_to_server;
  show_name_list( my_encr_algo_server_to_client, 'my_encr_algo_server_to_client : ');

    add2name_list( my_mac_algo_client_to_server, 'hmac-sha2-512', HMAC_SH512 );
    add2name_list( my_mac_algo_client_to_server, 'hmac-sha2-256', HMAC_SH256 );
    add2name_list( my_mac_algo_client_to_server, 'hmac-sha1', HMAC_SH1 );
    add2name_list( my_mac_algo_client_to_server, 'hmac-md5', HMAC_MD5 );
  show_name_list( my_mac_algo_client_to_server, 'my_mac_algo_client_to_server : ');

    my_mac_algo_server_to_client := my_mac_algo_client_to_server;
  show_name_list( my_mac_algo_server_to_client, 'my_mac_algo_server_to_client : ');

    my_compr_algo_client_to_server := tp_name_list( 'none' );
    my_compr_algo_server_to_client := tp_name_list( 'none' );
  info_msg('my_compr_algo_server_to_client / my_compr_algo_client_to_server : none');

I also added some info message in handle_kex on which algo is matched (usefull for debugging)

...
    IF my_encr_algo_client_to_server(i) MEMBER OF encr_algo_client_to_server
    THEN
      l_encr_algo_c := my_encr_algo_client_to_server(i);
      info_msg('Matching encr_algo_client_to_server : '|| l_encr_algo_c);
      exit;
    END IF;
..
    IF my_encr_algo_server_to_client(i) MEMBER OF encr_algo_server_to_client
    THEN
      l_encr_algo_s := my_encr_algo_server_to_client(i);
      info_msg('Matching encr_algo_server_to_client : '|| l_encr_algo_s);
      exit;
    END IF;
..
    IF my_mac_algo_client_to_server(i) MEMBER OF mac_algo_client_to_server
    THEN
      l_mac_algo_c := my_mac_algo_client_to_server(i);
      info_msg('Matching mac_algo_client_to_server : '|| l_mac_algo_c);
      exit;
    END IF;
..
    IF my_mac_algo_server_to_client(i) MEMBER OF mac_algo_server_to_client
    THEN
      l_mac_algo_s := my_mac_algo_server_to_client(i);
      info_msg('Matching mac_algo_server_to_client : '|| l_mac_algo_s);
      exit;
    END IF;
..
    IF my_compr_algo_client_to_server(i) MEMBER OF compr_algo_client_to_server
    THEN
      l_compr_algo_c := my_compr_algo_client_to_server(i);
      info_msg('Matching compr_algo_client_to_server : '|| l_compr_algo_c);
      exit;
    END IF;
..
    IF my_compr_algo_server_to_client(i) MEMBER OF compr_algo_server_to_client
    THEN
      l_compr_algo_s := my_compr_algo_server_to_client(i);
      info_msg('Matching compr_algo_server_to_client : '|| l_compr_algo_s);
      exit;
    END IF;
..
    IF my_public_key_algorithms(i) MEMBER OF public_key_algorithms
    THEN
      l_public_key_algorithm := my_public_key_algorithms(i);
      info_msg('Matching public_key_algorithms : '|| l_public_key_algorithm);
      exit;
    END IF;
..
    IF my_kex_algorithms(i) MEMBER OF kex_algorithms
    THEN
      l_kex_algorithm := my_kex_algorithms(i);
      info_msg('Matching kex_algorithms : '|| l_kex_algorithm);
      exit;
    END IF;

Morgan

Oracle Autonomous Transaction Processing (ATP) and Compute Instance sftp

I am trying to sftp files between an Oracle ATP instance and an Oracle Compute Instance sftp server using your package. It will open and close the connection without generating an error, but will not login. The error is ORA-29260 network error: not connected when executing as_sftp.login. (Also Ora-6512 Lines 168 and 341 in SYS.UTL_TCP.

I am using the Oracle Console network security list for authorizing access to the sftp server, not a software firewall on the sftp server. The sftp server will accept all connections from the internal Oracle network. Oracle ATP does not give an IP address.

Any suggestions?

Thanks!

New Ciphers?

Hi Anton,
I'am not familiar with sftp (from the database). I ran into your package and saw the code. I'am curious if your package is usable for my situation. The client has the following requirements:

Enable FIPS 140-2 Mode

Allowed OpenSSL Cipher Suites – ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-CHACHA20-POLY1305:ECDHE-RSA-CHACHA20-POLY1305

Allowed SFTP Key Exchange Algorithms – curve25519-sha256,[email protected],ecdh-sha2-nistp256,ecdh-sha2-nistp384,ecdh-sha2-nistp521,diffie-hellman-group16-sha512,diffie-hellman-group18-sha512

Allowed SFTP Encryption Algorithms - aes256-ctr,aes192-ctr,aes128-ctr,[email protected]

Allowed SFTP MAC Algorithms - hmac-sha2-256,hmac-sha2-512,[email protected],[email protected]

My question is:

Is this possible with your as_ftp package or do we have to add additional algorithms to the package? Some of the algorithms are not in your package as far as I could see
Is the bottleneck maybe Oracle dbms_crypto?
Maybe not possible at all?

My client is willing to invest.

regards,
Henk

rsa-sha2-256 for login

Hi Anton

Thank you very much for your quick feedback. Your solution (listed below) worked.

Is the link still up to date? "https://www.paypal.com/paypalme/apexplugins/10"

This might work
Change write_rsa_pk to
function write_rsa_pk( p_pk_parameters tp_pk_parameters, p_hash_type pls_integer )
return boolean
is
l_rv boolean;
l_buf3 raw(3999);
l_buf5 raw(3999);
l_algo varchar2(100);
begin
if p_pk_parameters.count = 0
then
return false;
end if;
l_buf2 := null;
l_algo := case p_hash_type
when HASH_SH256 then 'rsa-sha2-256'
when HASH_SH512 then 'rsa-sha2-512'
else 'ssh-rsa'
end;
append_string( l_buf2, utl_i18n.string_to_raw( l_algo, 'US7ASCII' ) );
append_mpint( l_buf2, p_pk_parameters(2) );
append_mpint( l_buf2, p_pk_parameters(1) );
l_rv := write_pk( l_algo, l_buf2, null );
if not l_pk_OK
then
return l_rv;
end if;
append_string( l_buf3, g_session_id );
append_byte( l_buf3, l_buf );
l_buf3 := powmod( utl_raw.concat( '01'
, utl_raw.copies( 'FF'
, utl_raw.length( p_pk_parameters(1) )
- case p_hash_type
when HASH_SH256 then 54
when HASH_SH512 then 86
else 38
end
- case when utl_raw.substr( p_pk_parameters(1), 1, 1 ) = '00' then 1 else 0 end
)
, case p_hash_type
when HASH_SH256 then '003031300D060960864801650304020105000420' -- fixed ASN.1 value SHA256
when HASH_SH512 then '003051300d060960864801650304020305000440' -- fixed ASN.1 value SHA512
else '003021300906052B0E03021A05000414' -- fixed ASN.1 value SHA1
end
, dbms_crypto.hash( l_buf3, p_hash_type )
)
, p_pk_parameters(3)
, p_pk_parameters(1)
);
append_string( l_buf5, utl_i18n.string_to_raw( l_algo, 'US7ASCII' ) );
append_string( l_buf5, l_buf3 );
return write_pk( l_algo, l_buf2, l_buf5 );
end;

And the call to that:
l_rv := case utl_raw.cast_to_varchar2( l_pk_parameters(0) )
when 'RSA' then write_rsa_pk( l_pk_parameters, HASH_SH1 )
or write_rsa_pk( l_pk_parameters, HASH_SH256 )
or write_rsa_pk( l_pk_parameters, HASH_SH512 )
when 'EC' then write_ec_pk( l_pk_parameters )
when 'DSA' then write_dsa_pk( l_pk_parameters )

Regards
Vincenzo

support for ed25519

I would like to see support for ed25519, but it is beyond me to add it. Looks like you started to handle it but there is just a framework and it returns false from parse_OPENSSH_private_key.

Everything else I had a need for works like a charm. Great work and thanks for sharing it!

Login with RSA Key and Passphrase

I am able to connect with userid/password. I followed your example in issue #12, additionally adding a i_passphrase parameter. It fails with ORA-20030: Could not login. I removed the passphrase from the private key and then it authenticated successfully.

Is the use of passphrases supposed to work?

Type conversion in login_pk function

Hello!
Tried to implement login_pk function in my code, but ran into an issue - because l_private_key is raw when we put it into i_priv_key, which type is varchar2 - it causes issues (No logging in).
I fixed it for me by using utl_raw.cast_to_varchar2() on l_private_key before putting it into i_priv_key.
Maybe I'm missing something in a bigger picture, could you explain?

Read_dir in recursive mode

Hello,

How can we modify read_dir function to list all files in the given directory and all its subdirectories too? Is there some -r flag that can be provided?

Thank you

Deleting File, Renaming File

Hi Anton,

Here is 3 functions I added : DELETE_FILE, RENAME_FILE and IS_FILE_EXISTS.
RENAME_FILE : I had trouble with overwriting parameter, so I have to use IS_FILE_EXISTS to check the file, deleting it, then rename)

PROCEDURE DELETE_FILE(p_file IN VARCHAR2)
IS
 -- p_file : path + filename
  l_dummy number;
  l_buf raw(32767);
	l_buf2 raw(32767);
	l_fxp_id number;
  l_idx PLS_INTEGER;
  v_errCode NUMBER;
BEGIN

/* byte   SSH_FXP_REMOVE
   uint32 request-id
   string filename [UTF-8]*/
 	l_buf := NULL;
  l_fxp_id := 300;
  append_int32( l_buf, l_fxp_id );
  append_string( l_buf, utl_i18n.string_to_raw( p_file, 'AL32UTF8' ) );
  write_fxp_message( SSH_FXP_REMOVE, l_buf );

  LOOP
    read_fxp_message( l_buf );
    
    IF UTL_RAW.SUBSTR( l_buf, 1, 1 ) = SSH_FXP_STATUS
    THEN
    
    /* byte   SSH_FXP_STATUS
       uint32 request-id
       uint32 error/status code
       string error message (ISO-10646 UTF-8 [RFC-2279])
       string language tag (as defined in [RFC-1766])
         error-specific data*/
    
    	l_idx := 2;
      get_int32( l_idx, l_buf, l_dummy ); -- id
      get_int32( l_idx, l_buf, v_errCode ); -- reason code
      
      IF v_errCode = SSH_FX_OK
      THEN 
    
    		  info_msg('Fichier '|| p_file ||' : delete Ok');
      		EXIT; -- Fichier Supprimé
    
      ELSE
				-- Erreur       
        get_string(l_idx, l_buf, l_buf2);
        info_msg('Delete File '|| p_file ||' : Errcode '|| v_errCode ||' - '|| utl_i18n.raw_to_char(l_buf2, 'AL32UTF8'));
        RAISE_APPLICATION_ERROR(-20095, 'Delete File '|| p_file ||' : Errcode '|| v_errCode ||' - '|| utl_i18n.raw_to_char(l_buf2, 'AL32UTF8'));

    	END IF;
    ELSE
    
      info_msg(  SUBSTR('Delete File '|| p_file ||' : '|| utl_i18n.raw_to_char(l_buf, 'AL32UTF8'), 1, 1024));
    	RAISE_APPLICATION_ERROR(-20095, SUBSTR('elete File '|| p_file ||' : '|| utl_i18n.raw_to_char(l_buf, 'AL32UTF8'), 1, 1024));
      
		END IF;
    
  END LOOP;
      
END;

FUNCTION IS_FILE_EXISTS(p_file IN VARCHAR2)
RETURN BOOLEAN
IS
-- Check if p_file (path+filename) exists Return TRUE or FALSE
/*
byte   SSH_FXP_STAT (suit symbolic) or SSH_FXP_LSTAT
       uint32 request-id
       string path [UTF-8]
       uint32 flags

         SSH_FILEXFER_ATTR_SIZE              0x00000001
         SSH_FILEXFER_ATTR_PERMISSIONS       0x00000004
         SSH_FILEXFER_ATTR_ACCESSTIME        0x00000008
         SSH_FILEXFER_ATTR_CREATETIME        0x00000010
         SSH_FILEXFER_ATTR_MODIFYTIME        0x00000020
         SSH_FILEXFER_ATTR_ACL               0x00000040
         SSH_FILEXFER_ATTR_OWNERGROUP        0x00000080
         SSH_FILEXFER_ATTR_SUBSECOND_TIMES   0x00000100
         SSH_FILEXFER_ATTR_BITS              0x00000200
         SSH_FILEXFER_ATTR_ALLOCATION_SIZE   0x00000400
         SSH_FILEXFER_ATTR_TEXT_HINT         0x00000800
         SSH_FILEXFER_ATTR_MIME_TYPE         0x00001000
         SSH_FILEXFER_ATTR_LINK_COUNT        0x00002000
         SSH_FILEXFER_ATTR_UNTRANSLATED_NAME 0x00004000
         SSH_FILEXFER_ATTR_CTIME             0x00008000
         SSH_FILEXFER_ATTR_EXTENDED          0x80000000
       
responds to this request with either SSH_FXP_ATTRS or SSH_FXP_STATUS.

*/
	l_dummy number;
  l_buf raw(32767);
	l_buf2 raw(32767);
	l_fxp_id number;
  l_idx PLS_INTEGER;
  v_errCode NUMBER;
  SSH_FILEXFER_ATTR_PERMISSIONS   NUMBER := 4;
  
BEGIN

	l_buf := NULL;
  l_fxp_id := 303;
  append_int32( l_buf, l_fxp_id );
  append_string( l_buf, utl_i18n.string_to_raw( p_file, 'AL32UTF8' ) );
  append_int32( l_buf, SSH_FILEXFER_ATTR_PERMISSIONS );
  write_fxp_message( SSH_FXP_LSTAT, l_buf );

  LOOP
    read_fxp_message( l_buf );
    
    IF UTL_RAW.SUBSTR( l_buf, 1, 1 ) = SSH_FXP_ATTRS
    THEN
    	
    	info_msg('is_file_exists : '|| p_file ||' exists');
      RETURN TRUE;
    
    
    ELSIF UTL_RAW.SUBSTR( l_buf, 1, 1 ) = SSH_FXP_STATUS
    THEN
    	/* byte   SSH_FXP_STATUS
       uint32 request-id
       uint32 error/status code
       string error message (ISO-10646 UTF-8 [RFC-2279])
       string language tag (as defined in [RFC-1766])
         error-specific data*/
    	
    	l_idx := 2;
      get_int32( l_idx, l_buf, l_dummy ); -- id
      get_int32( l_idx, l_buf, v_errCode ); -- reason code
      
      IF v_errCode =  SSH_FX_NO_SUCH_FILE
      THEN 
    	  info_msg('is_file_exists : '|| p_file ||' : No Such File');
      	RETURN FALSE;
      END IF;
      
      -- Erreur       
      get_string(l_idx, l_buf, l_buf2);
      info_msg('is_file_exists '|| p_file ||' : Errcode '|| v_errCode ||' - '|| utl_i18n.raw_to_char(l_buf2, 'AL32UTF8'));
      RAISE_APPLICATION_ERROR(-20095, 'is_file_exists '|| p_file ||' : Errcode '|| v_errCode ||' - '|| utl_i18n.raw_to_char(l_buf2, 'AL32UTF8'));

    ELSE
    
      info_msg( SUBSTR('is_file_exists '|| p_file ||' : '|| utl_i18n.raw_to_char(l_buf, 'AL32UTF8'), 1, 1024));
    	RAISE_APPLICATION_ERROR(-20095, SUBSTR('is_file_exists  '|| p_file ||' : '|| utl_i18n.raw_to_char(l_buf, 'AL32UTF8'), 1, 1024));
      
		END IF;
    
  END LOOP;
      	
	RETURN FALSE;

END;

PROCEDURE RENAME_FILE(p_file_orig IN VARCHAR2, p_file_dest IN VARCHAR2)
IS
  -- p_file_orig (path + filename) : file to move
  -- p_file_orig (path + filename) : destination 
  l_dummy number;
  l_buf raw(32767);
	l_buf2 raw(32767);
	l_fxp_id number;
  l_idx PLS_INTEGER;
  v_errCode NUMBER;
  
  SSH_FXF_RENAME_OVERWRITE  NUMBER := 1;
  SSH_FXF_RENAME_ATOMIC     NUMBER := 2;
  SSH_FXF_RENAME_NATIVE     NUMBER := 4;
           
/*  Files (and directories) can be renamed using the SSH_FXP_RENAME message.
       byte   SSH_FXP_RENAME
       uint32 request-id
       string oldpath [UTF-8]
       string newpath [UTF-8]
       uint32 flags

   where 'request-id' is the request identifier, 'oldpath' is the name of an existing file or directory,
   and 'newpath' is the new name for the file or directory.

   'flags' is 0 or a combination of:
       SSH_FXF_RENAME_OVERWRITE  0x00000001
       SSH_FXF_RENAME_ATOMIC     0x00000002
       SSH_FXF_RENAME_NATIVE     0x00000004  */
         
BEGIN

	-- Because Overwriting does not work on my test server (Return 4-Failure !)
	IF IS_FILE_EXISTS(p_file_dest)
  THEN 
  	-- Delting 
		 DELETE_FILE(p_file_dest);
  END IF;

	-- renaming file
  
 	l_buf := NULL;
  l_fxp_id := 301;
  append_int32( l_buf, l_fxp_id );
  append_string( l_buf, utl_i18n.string_to_raw( p_file_orig, 'AL32UTF8' ) );
  append_string( l_buf, utl_i18n.string_to_raw( p_file_dest, 'AL32UTF8' ) );
  append_int32( l_buf, SSH_FXF_RENAME_OVERWRITE); -- I tried SSH_FXF_RENAME_OVERWRITE but my test server returns 4-Failure if file exists.. that's not RFC compliant
  write_fxp_message( SSH_FXP_RENAME, l_buf );

  LOOP
    read_fxp_message( l_buf );
    
    IF UTL_RAW.SUBSTR( l_buf, 1, 1 ) = SSH_FXP_STATUS
    THEN
    
    /* byte   SSH_FXP_STATUS
       uint32 request-id
       uint32 error/status code
       string error message (ISO-10646 UTF-8 [RFC-2279])
       string language tag (as defined in [RFC-1766])
       error-specific data*/
    
    	l_idx := 2;
      get_int32( l_idx, l_buf, l_dummy ); -- id
      get_int32( l_idx, l_buf, v_errCode ); -- reason code
      
      IF v_errCode = SSH_FX_OK
      THEN 
      	
        info_msg('File '|| p_file_orig ||' rename to '|| p_file_dest);
      	EXIT; -- File Renamed
      
      ELSE
				-- Error       
        get_string(l_idx, l_buf, l_buf2);
        info_msg('Renaming File '|| p_file_orig ||' to '|| p_file_dest ||' : Errcode '|| v_errCode ||' - '|| utl_i18n.raw_to_char(l_buf2, 'AL32UTF8'));
        RAISE_APPLICATION_ERROR(-20095, 'Renaming File '|| p_file_orig ||' to '|| p_file_dest||' : Errcode '|| v_errCode ||' - '|| utl_i18n.raw_to_char(l_buf2, 'AL32UTF8'));

    	END IF;
    ELSE
    
      info_msg( SUBSTR('Renaming File '|| p_file_orig ||' to '|| p_file_dest||' : '|| RAWTOHEX( l_buf), 1, 1024) );
    	RAISE_APPLICATION_ERROR(-20095, SUBSTR('Renaming File '|| p_file_orig ||' to '|| p_file_dest ||' : '|| RAWTOHEX( l_buf), 1, 1024));
      
		END IF;
    
  END LOOP;
END;

Morgan

Incorrect signature verification for ssh-dss

Hi Anton,

There is a problem in validate_signature for ssh-dss when the l_dss_r start with '00' (1% of my connexions to 1 sftp server) because demag is returning LTRIM( rv, '0')

PROCEDURE validate_signature
l_v := demag( xmod( xmod( rmul( mag( powmod( l_g, l_u1, l_p ) ), mag( powmod( l_y, l_u2, l_p ) ) ), mag( l_p ) ), mag( l_q ) ) );
      IF l_v != l_dss_r
      THEN
        RAISE_APPLICATION_ERROR( -20012, 'ssh-dss NOT OK' );
      END IF;

I made this correction in my code (can't use LTRIM on l_dss_r because l_v and l_dss_r are RAW, and demag returning a VARCHAR)
l_v := LPAD(demag( xmod( xmod( rmul( mag( powmod( l_g, l_u1, l_p ) ), mag( powmod( l_y, l_u2, l_p ) ) ), mag( l_p ) ), mag( l_q ) ) ), 40, '0');

Regards
Morgan

put_file missing l_idx initialisation when error

Hi Anton,

In function put_file, line 2863 is missing (l_idx := 2;)
So there is an error 6512 when the server send an error message like "access denied"

if utl_raw.substr( l_buf, 1, 1 ) = SSH_FXP_STATUS
      then
        debug_msg( 'put_file: not opened' );
l_idx := 2; -- line to add
        get_int32( l_idx, l_buf, l_dummy ); -- id
        get_int32( l_idx, l_buf, l_dummy ); -- reason code
        get_string( l_idx, l_buf, l_buf2 );
        debug_msg( utl_i18n.raw_to_char( l_buf2, 'AL32UTF8' ) );
        exit;
      elsif utl_raw.substr( l_buf, 1, 1 ) = SSH_FXP_HANDLE
      then
        debug_msg( 'put_file: opened' );
        l_idx := 2;

Support for read_dir function

Hello,

I would like support on how to use the read_dir function in the as_sftp package.
I am facing difficulties defining the correct return type in pl/sql anonymous block. Any advice?
Sample code:
declare l_hostname varchar2(100); l_username varchar2(100); l_password varchar2(100); l_rem_dir varchar2(100); type tp_dir_line_test is record ( file_name varchar2(32767) , long_name varchar2(32767) , is_directory boolean , file_size number , uid number , gid number , perm number , atime date , mtime date ); type tp_dir_listing_test is table of tp_dir_line_test; l_dir_listing_test tp_dir_listing_test; begin l_hostname := get_sftp_host; l_username := get_sftp_usr; l_password := get_sftp_pwd; l_rem_dir := get_sftp_dir; xx_as_sftp_pkg.open_connection(i_host => l_hostname); xx_as_sftp_pkg.login(i_user => l_username, i_password => l_password); xx_as_sftp_pkg.set_log_level(1); l_dir_listing_test := xx_as_sftp_pkg.read_dir(i_path => l_rem_dir); end;

Executing the above block results in ORA-06550: line 26, column 23: PLS-00382: expression is of wrong type

Get-Put file from Oracle Directory

@antonscheffer Could you suggest me about transferring data from Oracle directory and sFTP server? From your sample, it work fine to create spreadsheet but I don't know how to get-put file.

MKDIR (create a directory) implementation

Hello,

I needed to use the MKDIR functionality; this is the code that I've implemented for it, in case you need it also.

function create_dir( i_path varchar2 )
  return boolean
  is
    l_dummy number;
    l_fxp_id number;
    l_idx pls_integer;
    l_buf raw(32767);
    l_buf2 raw(32767);
    l_rv boolean;
    l_flags number;
    l_reason number;
  begin
    l_rv := false;
    l_buf := null;
    l_fxp_id := 401;
    append_int32( l_buf, l_fxp_id );
    append_string( l_buf, utl_i18n.string_to_raw( i_path, 'AL32UTF8' ) );
    append_int32( l_buf, l_flags);
    
    write_fxp_message( SSH_FXP_MKDIR, l_buf );
    read_fxp_message( l_buf );
    
    if utl_raw.substr( l_buf, 1, 1 ) = SSH_FXP_STATUS then
        debug_msg( 'create dir: not created' );
        l_idx := 2;
        get_int32( l_idx, l_buf, l_dummy ); -- id
        get_int32( l_idx, l_buf, l_reason ); -- reason code
        get_string( l_idx, l_buf, l_buf2 );
        debug_msg( utl_i18n.raw_to_char( l_buf2, 'AL32UTF8' ) );
        
        if l_reason = SSH_FX_OK then
            debug_msg( 'create dir: created OK' );
            l_rv := true;
        else
             debug_msg( 'create dir: not created' );
            l_rv := false;
        end if;
        
    else
        raise_application_error(-20100, 'Unexpected server response');
    end if;
    
    return l_rv;
  end create_dir;

procedure create_dir( i_path varchar2 ) is
    l_rv boolean;
  begin
    l_rv := create_dir( i_path );
  end create_dir;

antonscheffer / as_sftp Goto Github PK

as_sftp's People

Contributors

Stargazers

Watchers

Forkers

as_sftp's Issues

Recommend Projects

Recommend Topics

Recommend Org