antonscheffer / as_sftp Goto Github PK
View Code? Open in Web Editor NEWA plsql SFTP client package
License: MIT License
A plsql SFTP client package
License: MIT License
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.
@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.
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!
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
Hi, how can I login using a RSA key please? I did some tests but it didn't work.
Thanks.
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
Hello Anton,
How problematical would be to add TLS_AES_256_GCM_SHA384 support?
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
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
Hi
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
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?
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?
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
Hi, after a successful connection all downloaded files result zero-length.
Any advice?
Thanks
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
@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.
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;
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:
My client is willing to invest.
regards,
Henk
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!
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
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.
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.
Is just saw this nice addition: Protecting/Hiding Data in Oracle
I might even add it myself
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?
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;
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
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.