Jump to content
Sign in to follow this  
icu

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

Recommended Posts

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 by icu

Share this post


Link to post
Share on other sites
guinness

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


UDF List:

 
_AdapterConnections()_AlwaysRun()_AppMon()_AppMonEx()_ArrayFilter/_ArrayReduce_BinaryBin()_CheckMsgBox()_CmdLineRaw()_ContextMenu()_ConvertLHWebColor()/_ConvertSHWebColor()_DesktopDimensions()_DisplayPassword()_DotNet_Load()/_DotNet_Unload()_Fibonacci()_FileCompare()_FileCompareContents()_FileNameByHandle()_FilePrefix/SRE()_FindInFile()_GetBackgroundColor()/_SetBackgroundColor()_GetConrolID()_GetCtrlClass()_GetDirectoryFormat()_GetDriveMediaType()_GetFilename()/_GetFilenameExt()_GetHardwareID()_GetIP()_GetIP_Country()_GetOSLanguage()_GetSavedSource()_GetStringSize()_GetSystemPaths()_GetURLImage()_GIFImage()_GoogleWeather()_GUICtrlCreateGroup()_GUICtrlListBox_CreateArray()_GUICtrlListView_CreateArray()_GUICtrlListView_SaveCSV()_GUICtrlListView_SaveHTML()_GUICtrlListView_SaveTxt()_GUICtrlListView_SaveXML()_GUICtrlMenu_Recent()_GUICtrlMenu_SetItemImage()_GUICtrlTreeView_CreateArray()_GUIDisable()_GUIImageList_SetIconFromHandle()_GUIRegisterMsg()_GUISetIcon()_Icon_Clear()/_Icon_Set()_IdleTime()_InetGet()_InetGetGUI()_InetGetProgress()_IPDetails()_IsFileOlder()_IsGUID()_IsHex()_IsPalindrome()_IsRegKey()_IsStringRegExp()_IsSystemDrive()_IsUPX()_IsValidType()_IsWebColor()_Language()_Log()_MicrosoftInternetConnectivity()_MSDNDataType()_PathFull/GetRelative/Split()_PathSplitEx()_PrintFromArray()_ProgressSetMarquee()_ReDim()_RockPaperScissors()/_RockPaperScissorsLizardSpock()_ScrollingCredits_SelfDelete()_SelfRename()_SelfUpdate()_SendTo()_ShellAll()_ShellFile()_ShellFolder()_SingletonHWID()_SingletonPID()_Startup()_StringCompact()_StringIsValid()_StringRegExpMetaCharacters()_StringReplaceWholeWord()_StringStripChars()_Temperature()_TrialPeriod()_UKToUSDate()/_USToUKDate()_WinAPI_Create_CTL_CODE()_WinAPI_CreateGUID()_WMIDateStringToDate()/_DateToWMIDateString()Au3 script parsingAutoIt SearchAutoIt3 PortableAutoIt3WrapperToPragmaAutoItWinGetTitle()/AutoItWinSetTitle()CodingDirToHTML5FileInstallrFileReadLastChars()GeoIP databaseGUI - Only Close ButtonGUI ExamplesGUICtrlDeleteImage()GUICtrlGetBkColor()GUICtrlGetStyle()GUIEventsGUIGetBkColor()Int_Parse() & Int_TryParse()IsISBN()LockFile()Mapping CtrlIDsOOP in AutoItParseHeadersToSciTE()PasswordValidPasteBinPosts Per DayPreExpandProtect GlobalsQueue()Resource UpdateResourcesExSciTE JumpSettings INISHELLHOOKShunting-YardSignature CreatorStack()Stopwatch()StringAddLF()/StringStripLF()StringEOLToCRLF()VSCROLLWM_COPYDATAMore Examples...

Updated: 22/04/2018

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
guinness

It's an idea, but...

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


UDF List:

 
_AdapterConnections()_AlwaysRun()_AppMon()_AppMonEx()_ArrayFilter/_ArrayReduce_BinaryBin()_CheckMsgBox()_CmdLineRaw()_ContextMenu()_ConvertLHWebColor()/_ConvertSHWebColor()_DesktopDimensions()_DisplayPassword()_DotNet_Load()/_DotNet_Unload()_Fibonacci()_FileCompare()_FileCompareContents()_FileNameByHandle()_FilePrefix/SRE()_FindInFile()_GetBackgroundColor()/_SetBackgroundColor()_GetConrolID()_GetCtrlClass()_GetDirectoryFormat()_GetDriveMediaType()_GetFilename()/_GetFilenameExt()_GetHardwareID()_GetIP()_GetIP_Country()_GetOSLanguage()_GetSavedSource()_GetStringSize()_GetSystemPaths()_GetURLImage()_GIFImage()_GoogleWeather()_GUICtrlCreateGroup()_GUICtrlListBox_CreateArray()_GUICtrlListView_CreateArray()_GUICtrlListView_SaveCSV()_GUICtrlListView_SaveHTML()_GUICtrlListView_SaveTxt()_GUICtrlListView_SaveXML()_GUICtrlMenu_Recent()_GUICtrlMenu_SetItemImage()_GUICtrlTreeView_CreateArray()_GUIDisable()_GUIImageList_SetIconFromHandle()_GUIRegisterMsg()_GUISetIcon()_Icon_Clear()/_Icon_Set()_IdleTime()_InetGet()_InetGetGUI()_InetGetProgress()_IPDetails()_IsFileOlder()_IsGUID()_IsHex()_IsPalindrome()_IsRegKey()_IsStringRegExp()_IsSystemDrive()_IsUPX()_IsValidType()_IsWebColor()_Language()_Log()_MicrosoftInternetConnectivity()_MSDNDataType()_PathFull/GetRelative/Split()_PathSplitEx()_PrintFromArray()_ProgressSetMarquee()_ReDim()_RockPaperScissors()/_RockPaperScissorsLizardSpock()_ScrollingCredits_SelfDelete()_SelfRename()_SelfUpdate()_SendTo()_ShellAll()_ShellFile()_ShellFolder()_SingletonHWID()_SingletonPID()_Startup()_StringCompact()_StringIsValid()_StringRegExpMetaCharacters()_StringReplaceWholeWord()_StringStripChars()_Temperature()_TrialPeriod()_UKToUSDate()/_USToUKDate()_WinAPI_Create_CTL_CODE()_WinAPI_CreateGUID()_WMIDateStringToDate()/_DateToWMIDateString()Au3 script parsingAutoIt SearchAutoIt3 PortableAutoIt3WrapperToPragmaAutoItWinGetTitle()/AutoItWinSetTitle()CodingDirToHTML5FileInstallrFileReadLastChars()GeoIP databaseGUI - Only Close ButtonGUI ExamplesGUICtrlDeleteImage()GUICtrlGetBkColor()GUICtrlGetStyle()GUIEventsGUIGetBkColor()Int_Parse() & Int_TryParse()IsISBN()LockFile()Mapping CtrlIDsOOP in AutoItParseHeadersToSciTE()PasswordValidPasteBinPosts Per DayPreExpandProtect GlobalsQueue()Resource UpdateResourcesExSciTE JumpSettings INISHELLHOOKShunting-YardSignature CreatorStack()Stopwatch()StringAddLF()/StringStripLF()StringEOLToCRLF()VSCROLLWM_COPYDATAMore Examples...

Updated: 22/04/2018

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
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 by icu

Share this post


Link to post
Share on other sites
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


Please don't send me any personal message and ask for support! I will not reply!

Selection of finest graphical examples at Codepen.io

The own fart smells best!
Her 'sikim hıyar' diyene bir avuç tuz alıp koşma!
¯\_(ツ)_/¯  ٩(●̮̮̃•̃)۶ ٩(-̮̮̃-̃)۶ૐ

Share this post


Link to post
Share on other sites
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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×