How to move data from an AutoIt program a server securely?

[icu]

Dear AutoIt community,

I'm looking for advice on the easiest way of moving data from an AutoIt program that has information in SQLite on a client machine and sending it securely over the Internet to a server.

I've thought about encrypting the data using the _StringEncrypt and then using Universalist's SOAP Example.

As far as I know AutoIT doesn't natively support SFTP or SSH... and no, I'm not looking for a wrapper.

Is there another way to skin this cat?

Thanks for any and all help on this.

Edited March 8, 2013 by icu

[guinness]

Don't use _StringEncrypt, opt for the _Crypt functions.

[Tim H.]

I use PuTTY command-line tools (opensource freeware) on a daily basis to tranfer files securely with SSH2. I haven't tried running them from within an AutoIt script, but I see no reason why they wouldn't function properly. Depending on the complexity of what you're trying to do, you may be able to get by with simple batch files and SFTP scripts.

[guinness]

It's an idea, but...

and no, I'm not looking for a wrapper.

[Tim H.]

The PuTTY tools are comprehensive enough that AutoIt may not be needed. That was the other cat-skinning option I was suggesting. Of course, depending on what icu means by "data from an AutoIt program" my suggestion could be completely invalid.

[icu]

Hi All,

Thanks for your replies.

The data to move is simple text strings which are stored in a SQLite database.

I was thinking of zipping up the database file and then encrypting it but again maybe this isn't the best way of shifting the data. I'm open to ideas here.

However I want this to be a self contained solution, which is why installing PuTTY isn't my preference. I suppose if I have to do this through a dll that should be okay... but I haven't worked with dlls so they're all a bit of a mystery to me... thus I was hoping for an all AutoIt solution.

[Tim H.]

The command-line PuTTY tools don't need to be "installed" per se. Its files need only be available. psftp.exe is probably the only executable you need. You can extract the files on one machine and then just copy them to another. The chm help file is useful for understanding the syntax.

The command line might look something like this:

C:Puttypsftp HostAddress -b ftpbatchout.psftp -bc -be -batch -l AcctID -pw SFTPPW

The ftpbatchout.psftp reference is to a text file that contains your sftp commands, something like this:

# PuTTY script file

lcd D:Putty***FTPFilesOut

cd "/RemoteDirectory/"

put MyFileName

quit

[Tim H.]

By the way, 7zip has free-standing command-line tools that will let you automate encryption of files (AES256). Then you can just use regular FTP commands to transfer them, as long as your destination host accepts regular FTP connections on port 21. This could be another way to go without having to write an AutoIt script.

[jchd]

Another simple alternative is to use SQLite to encrypt the DB by itself. Since SQLite databases are cross-platform and besides you're only doing Windows to Windows transfer this is a workable easy way. All you have to do is download the convenient SQLite .NET DLL (all of these include encryption) for the OSes you have. The stock SQLite3.dll version doesn't include encryption.

Setting up encryption is a simple pragma statement with a strong enough passphrase. Use the same at the other end and you're done. Massaging the data you want from the server end is as easy as it is on the client side.

Benefit of transfering the whole baby depends on your actual use case, as usual, but it may reveal simpler than transfering sparse data.

[icu]

@jchd: Thank you, I didn't know SQLite could be encrypted. Looks like I need to learn about DLLs after all if this is the case.

@Tim H: Thanks for the idea, encrypting via 7zip is an interesting idea. I've done some searching around and it looks like there was a DLL for Putty called W-PuTTY-CD but the home page is dead so the project may be dead as well. The other option is Extraputty which looks like it offers a DLL.

Conclusion: I need to investigate more and learn how to interface AutoIt with DLLs. If anyone has any suggested learning resources I would be grateful.

Thanks again to all who have helped.

Edited March 15, 2013 by icu

[UEZ]

If you have a possibility to run an AutoIt script on the server where the SQL is running then you can crypt the data, copy it to a share/ftp on the SQL server.

Over there the AutoIt script can decrypt it and move it to the SQL db.

Br,

UEZ

[jchd]

The DLL version allowing encryption can be found on the download page of SQLite. Select the one you see fit for the OSes your run. AFAICT all of them except the purely managed code versions include the very same C-style API that AutoIt knows via the standard SQLite UDF. In short you don't need to dig into DLL interfacing for that: the usual SQLite function will work for you as they proved to work for other people.

All you have to do to encrypt/decrypt/re-encrypt any SQLite DB is to follow among others. Use the forum search feature to locate posts with "pragma key" for instance.

Going a bit deeper with why encrypting the DB is better than encrypting the fields individually, realize that the schema is likely to fail if you encrypt the fields individually.