Using SFTP to upload files

[Rick Weiser]

Hi guys,

I am in the middle of a big project to send ACH data to a bank for processing.  This requires using an SFTP script to connect to the server with a private key and then send the file to a specific directory.

Does anyone have samples of this type that they would be willing to share?

Thanks,

Rick

[Richard Lewis]

We're doing this with multiple banks in different countries.  I don't know that I can share our proprietary code, but I would be happy to talk to you about it in a private thread.

Kind Regards,

Richard Lewis

Programmer/Analyst V

Nu Skin Enterprises

--
You received this message because you are subscribed to
the "Pick and MultiValue Databases" group.
To post, email to: mvd...@googlegroups.com
To unsubscribe, email to: mvdbms+un...@googlegroups.com
For more options, visit http://groups.google.com/group/mvdbms
---
You received this message because you are subscribed to the Google Groups "Pick and MultiValue Databases" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mvdbms+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mvdbms/ac412202-5f4e-4b2a-9a14-840e6b3a6683n%40googlegroups.com.

[joseba]

What platform? I am sending data using SFTP scripts in D3 Linux without any problem. I can send you the code. I have done it also with QM/Windows but I should find out how I did it (I think I used powershell) 

You can contact directly with me or if anybody are interested I can publish it here

[Richard Lewis]

Some principles to consider:

(We're on a version of linux, so I don't know how this would be accomplished on Windows, but I expect there are similar techniques available.)

Banks are VERY security-concious organizations.  You're not likely going to want to have any and every login able to make sftp connections with the bank, so you probably want to setup a new user-id that will be used for interactions with the bank.  That is the user that should generate the keys that will be exchanged with the bank.  Your program can then execute a 'sudo' to that user, and execute the script that will encrypt (like pgp) the data file and stfp the encrypted file to the bank.  Since the sudo is only executed from within the program, as long as your everyday users are restricted to menu access, your bank accesses and connections will remain secure.

We also have a program to connect to the bank and retrieve via sftp, files containing the results of the transactions, whether successful or rejected, so that the program can automatically reconcile and handle rejections appropriately.

[Tom Marracci]

I just did one today for D3/Linux. 

 

The linux shell script uses sshpass to fill in the password on the command line and turn off strict host checking since it's not interactive:

 

/usr/local/bin/sendsftpfile:

#/bin/sh

 

if [ "$1" != "" ]
then
if [ -f /pub/$1 ]
then
sshpass -p PASSWORD sftp -o StrictHostKeyChecking=no USER@HOST << SFTP
put /pub/$1
quit
SFTP
rm /pub/$1
fi
fi
exit

 

Then from within D3, I create the file (using %fopen, %fputs or some other C function method) and shell out to this script with

 

EXECUTE '!sendsftpfile SOMEFILENAME'

 

I've tried using pipes (%popen, %fputs, and %pclose) but can't seem to get the end of lines to work correctly with sftp.

 

Your mileage may vary.

 

Tom

 

To view this discussion on the web visit https://groups.google.com/d/msgid/mvdbms/CAC9MznKJm2TNhgb9YNLSadWk71pqjVQzMrFcqhGuEECWXiBFRw%40mail.gmail.com.

[Patrick Payne]

Take a look at Curl.  It can do sftp and is all command-line switch based.  You will have to do some searched on how to use a private key

How to use Curl Command line tool with FTP and SFTP | Mukesh Kumar 

[Tom Marracci]

Even better! I've used curl for ftps, but was not aware it supported sftp

 

 

To view this discussion on the web visit https://groups.google.com/d/msgid/mvdbms/24e23435-5e94-41d4-a011-35b13b0fa5c4n%40googlegroups.com.

[Tony Gravagno]

Tom M wrote:

> sshpass -p PASSWORD sftp -o StrictHostKeyChecking=no USER@HOST << SFTP

That's highly insecure and not appropriate for a banking application.Not to be hypocritical, I probably would have suggested something similar, years ago, not anymore.

Rick said he's being asked for a private key. So, we don't know without more info but this might mean a passwordless authentication. On the local system you generate a public and private key. Then you give the PUBLIC key to your trading partner. NEVER GIVE ANYONE YOUR PRIVATE KEY. When you connect to their system, there will be a known-hosts file for your login user with your public key. In the SSH handshake, your public key is used to encrypt a challenge back to your system. Only your private key can decrypt that challenge and return a result which proves to the bank server that your client system is indeed the host it claims to be. This exchange of a "dynamic password" precludes the need for a static password.

T

[Tom Marracci]

Agreed regarding the security. But when you're accessing an outside service that only provides user id and password, you use what you can. Certificates would be better, but not always an option.

 

Tom

 

----- Original Message -----

From: Tony Gravagno

To: Pick and MultiValue Databases

Sent: Wednesday, February 03, 2021 1:14 PM

Subject: Re: [mvdbms] Using SFTP to upload files

--
You received this message because you are subscribed to
the "Pick and MultiValue Databases" group.
To post, email to: mvd...@googlegroups.com
To unsubscribe, email to: mvdbms+un...@googlegroups.com
For more options, visit http://groups.google.com/group/mvdbms
---
You received this message because you are subscribed to the Google Groups "Pick and MultiValue Databases" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mvdbms+un...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/mvdbms/3fd3eb64-bc3e-493e-b82d-c488abde17e2n%40googlegroups.com.

[joseba]

Hi Rick, Take a look to lftp . I think lftp is included in the majority of Linux versions and you can download it for windows also.

After that, take a look at this or just google lftp private key:

I use it a lot, but with user-password to save remote D3 backups and works like a champ.

Hope this help:

https://enotacoes.wordpress.com/2017/02/26/using-sftp-ssh-rsa-key-no-pass/

https://stackoverflow.com/questions/11577090/lftp-with-key-passphrase

joseba

[Rick Weiser]

HI Guys,

Thanks for your help.

Although I hate to say it :), Tony is correct.  You create a pair of keys using a generator (I used putty).  You give the public key to the Bank and use the private key in the transmission.  You still need a UserId but no password.

I am playing with the CURL request and it appears to be what is needed.  I will let you know.

Thanks again,

Rick

[Will Johnson]

Make sure you're creating a log, that gets emailed back to you and maybe a few other people.

There's nothing worse than your script breaking for one reason or six and no one knowing about it for a month.

[Tony Gravagno]

Tom M wrote:

Agreed regarding the security. But when you're accessing an outside service that only provides user id and password, you use what you can. Certificates would be better, but not always an option.

 

We're on the same page. I was focused on Rick's requirements, where he specified banking and a key. Those factors point away from sshpass. But password on the command-line is indeed a valid option ... when it's a valid option. ;)

Best,

T

[Scott Ballinger]

Hello all,

I agree on using lftp. It handles vanilla ftp, sftp, ftps and even https with a consistent scripted interface. I use it for about 100 different sites across many D3 and UV platforms, typically via site-specific programs like FTP.GET.XXX. FTP.PUT.XXX and FTP.LIST.XXX for each site. I don't think I've seen a site that I couldn't connect to using lftp. In typical use, I execute the FTP.LIST.XXX and capture the list of filenames, then loop through and use FTP.GET.XXX to download each  one individually. If file encryption is required, I usually include the appropriate gpg command in the FTP.PUT/GET.XXX process so the file is automatically encrypted/decrypted as part of the process.

Attached is a generic FTP.LIST.XXX template ("nlist" is like "ls" but not necessarily sorted, most sites treat "ls" like "ls -l"; change "nlist" to "get" or "put" as needed; remove the ",xxx-password" if using a key), and FTP.LOGGER.SUB which tests the debug/log file to determine success and updates the FTP.LOG file

/Scott Ballinger

[Rick Weiser]

Hi Scott,

This appears to be a Linux based command, right?

If so, it will not work for me as the client is on D3 Windows.

Rick

[Steve Trimble]

Rick:

check out: https://www.kodfor.com/LFTP-on-Windows-10

Computerized Data Mgmt Inc
Steve Trimble
(501) 772-3450 cell / text

cdm...@gmail.com

--

You received this message because you are subscribed to
the "Pick and MultiValue Databases" group.
To post, email to: mvd...@googlegroups.com
To unsubscribe, email to: mvdbms+un...@googlegroups.com
For more options, visit http://groups.google.com/group/mvdbms
---
You received this message because you are subscribed to the Google Groups "Pick and MultiValue Databases" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mvdbms+un...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/mvdbms/a1014b36-2e03-4f56-9db2-fc7138523184n%40googlegroups.com.

[Steve Trimble]

Rick - on a user with D3/Windows, I installed WinSCP

here is sample code to create a .bat file, then EXECUTE it.

SCRIPT.ID:= ".bat"
BAT.REC = ''
BAT.REC<-1> = '"c:\Program Files (x86)\WinSCP\Winscp.com" /script=':LOCALDIR:SCRIPT.ID
WRITE BAT.REC ON LOCDIR.FL,BAT.ID

SCRIPT.REC = ''
SCRIPT.REC<-1> = \option echo off\
SCRIPT.REC<-1> = \option batch on\
SCRIPT.REC<-1> = \option confirm off\

SCRIPT.REC<-1> = \open sftp://USER$ID:PSWD$@SERVER$/ -hostkey="HOST$KEY"\
SCRIPT.REC<-1> = 'lcd "':LOCALDIR:'CUR$YEAR\"'
*SCRIPT.REC<-1> = \# copy an entire folder\
*SCRIPT.REC<-1> = \# put -nopermissions -nopreservetime "asubfolder"\
SCRIPT.REC<-1> = \# copy an individual file\
SCRIPT.REC<-1> = 'put -nopermissions -nopreservetime "':LOCALDIR:@DS:'CUR$YEAR':@DS:'CUR$FL" CUR$FL'
SCRIPT.REC<-1> = \exit\

SCRIPT.REC = SWAP(SCRIPT.REC,\CUR$YEAR\,CURYR)
SCRIPT.REC = SWAP(SCRIPT.REC,\CUR$FL\,CURFL)
SCRIPT.REC = SWAP(SCRIPT.REC,\USER$ID\,USER.ID)
SCRIPT.REC = SWAP(SCRIPT.REC,\PSWD$\,PSWD)
SCRIPT.REC = SWAP(SCRIPT.REC,\SERVER$\,SERVER)
SCRIPT.REC = SWAP(SCRIPT.REC,\HOST$KEY\,HOSTKEY)
WRITE SCRIPT.REC ON LOCDIR.FL,SCRIPT.ID

....
VERB = "!":LOCALDIR:BAT.ID
EXECUTE VERB CAPTURING RESULT

have fun

Computerized Data Mgmt Inc
Steve Trimble
(501) 772-3450 cell / text

cdm...@gmail.com

On Tue, Feb 9, 2021 at 10:36 AM Rick Weiser <yld...@gmail.com> wrote:

--

You received this message because you are subscribed to
the "Pick and MultiValue Databases" group.
To post, email to: mvd...@googlegroups.com
To unsubscribe, email to: mvdbms+un...@googlegroups.com
For more options, visit http://groups.google.com/group/mvdbms
---
You received this message because you are subscribed to the Google Groups "Pick and MultiValue Databases" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mvdbms+un...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/mvdbms/a1014b36-2e03-4f56-9db2-fc7138523184n%40googlegroups.com.

[sreekanth dheerendranath]

Hope this may be helpful

https://www.toolbox.com/tech/operating-systems/question/putting-a-file-on-a-remote-server-via-sftp-using-expect-120211/

To view this discussion on the web visit https://groups.google.com/d/msgid/mvdbms/CAJDLGm7WmYurSsLDQG5vT687NAatia4KU9ubZjroTTwH0FBH1A%40mail.gmail.com.

--

Sr33

[Rick Weiser]

Hi Steve,

This is exactly what I am doing now.  I found WinSCP yesterday.  I was able to get it all working in a couple of hours.  They have an excellent interface and tools.

Thanks,

RIck

[Bill H]

The easy solution is to install an SFTP client (e.g. Bitvise SSH Client).  Then make the connection to the bank's SFTP server.  Save the profile.  Then use the command-line SFTP command (supplied by Bitvise) referencing the saved profile.  That will hold the keys and all will work.  Call if you want to see how this is done.

[Will Johnson]

Our solution was to have a scheduled job run in Pick to export the file at a certain time (we have an internal Pick scheduler written in BASIC)

And then we use WinSCP to do all the rest using Windows scheduler.

We have various reports, logs and emails going to various people for each step so we can debug it when it fails (as it will more often than you think)

[Rick Weiser]

Hi,

I am using WinSCP and its working perfectly for what I need to do.

Rick