Please post here if you have problems in using Wing FTP Server.

Using external automated means for creating a weblink

Fri Oct 01, 2021 2:02 pm

Hello,

I am in need of a way to automate the creation of a weblink...now we give an URL with userid and password by email to the recipient, but want to make this more secure.

We have Wing-ftp corp ed.(latest) and Our IBM i-series drops a file in the ftp customer directory, sends out the email with a link to the file that includes a user and password in plain text.

--> How do I create a weblink externally; I saw the bookmark_db file that contains the 32-chr files that make up the weblinks, but can't seem to recreate them from an external script. We tried the powershell that was available on the forum, but that didn't work. Do we need to use the MySQL option?

Preale some advice!

Re: Using external automated means for creating a weblink

Tue Oct 12, 2021 4:44 am

OK, you can generate a weblink for the uploaded file automatically by the event "OnFileUploaded -> Lua Script":

Code:
local domain = "%Domain"
local user = "%Name"
local filepath = string.gsub("%PathName", "\\","/")
local filename = "%FileName"
local filesize = "%FileSize"

local weblink = md5(domain..user..filepath..c_GetGlobalOptionStr(GOPTION_START_TIME_STR))
local fp = assert(io.open(c_GetAppPath().."/Data/_WEBLINK/"..weblink, "wb"))
fp:write(filepath.."\r\n-1\r\n-1")
fp:close()

local sql = "insert into wftp_weblink values (NULL, '"..weblink.."', '"..domain.."', '"..user.."', 0, datetime(CURRENT_TIMESTAMP,'localtime'), datetime(CURRENT_TIMESTAMP,'localtime'), 0, '"..filename.."', '"..filepath.."', "..filesize..", '', '',-1)"
c_ExecuteSQL(sql)

Re: Using external automated means for creating a weblink

Fri Oct 15, 2021 8:44 am

ThanX !

It works almost....

1 issue left only:

if I generate an MD5 of the file name only, it differs from the MD5 I get when using the windows certutil -hashfile [file] MD5 option!

That would make it easier to generate a link SEPARATELY from the wingftp database, but still use the buildin weblink generator.

Re: Using external automated means for creating a weblink

Fri Oct 15, 2021 9:30 am

Is it possible to get the MD5 hash from the file? that would solve my problem.

Re: Using external automated means for creating a weblink

Fri Oct 15, 2021 12:51 pm

Sorry, the parameter for the Lua API "md5" is a string, not a filename or a file handle.

Re: Using external automated means for creating a weblink

Tue Oct 26, 2021 12:30 pm

Thnx,

I wanted to know as I need a means to generate the filename from some string. And an external app has to do this.

So... Can an external app write in the sql file wftp_weblink ???

Re: Using external automated means for creating a weblink

Tue Oct 26, 2021 1:26 pm

OK, wftp_weblink is the table name, the database file is "Data/bookmark_db", its a SQLite database, of course you can access that database via external app.

Re: Using external automated means for creating a weblink

Wed Oct 27, 2021 6:15 am

thank you! I will try this immediately.


i'll let you know if it functions.

Re: Using external automated means for creating a weblink

Wed Oct 27, 2021 12:14 pm

It Worked. (using powershell)

Small gripe.... the bookmark_db is locked while using Powershell, so changes won't reflect immediately in the admin console.


Thanks for your assistance ( we're planning an extra 5 ftp corp licenses for next month)

Re: Using external automated means for creating a weblink

Tue Nov 02, 2021 1:41 pm

sample code attached for anyone trying to replicate:
Code:
################################################################
#
#    Prerequisites:
#   Powershell:   
#   Register-PSRepository -default    (for psgallery repo)
#    Install-Module pssqlite       (check with get-module )
#   Import-Module PSSqlite
#   --> takes filename as input <-- outputs a working URL for the weblink
#
################################################################
#
#   1. create sqlite connection to database file bookmark_db
#   2. Create string (32char) as weblink
#   3. Make file with name $weblinkstr containing real filename & storage location and optional parameters
#   4. Create SQL statement containing the weblink parameters and insert these into the database filename
#   5. Create Weblink in variable $http
#   6. Close DB-file
################################################################


# Housekeeping & Cleaning

   Param($Filename)   # Parameter for script:  is passed on commandline
   $Database = "\\xxxxxxx\c$\Program Files (x86)\Wing FTP Server\Data\bookmark_db"
   $Connection = ""
   $WeblinkStr = ""
   $Filepath = "\\xxxxxxxxxx\e$\ftp_in\weblink"
   $LocalFilePath = ""
   $Query = ""
   $Http = ""
   
# Create 32 char string for weblink. Also used for writing file into weblink folder

   $WeblinkStr= [System.Guid]::NewGuid().ToString() -replace '-',''

# Opening database file for weblink

   $Connection = New-SQLiteConnection -DataSource $Database
   
# Create weblink file   
   
   $Webfile = New-Item -Path $Filepath -Name $WeblinkStr -ItemType "File"
   Set-Content $Webfile $Filepath\$Filename
   Add-content $Webfile "-1`n-1"   
# -1 unlimited downloads, -1 unlimited time to download;
# When counter >0 it will count down inside the file until 0 then link stops working
   
# SQL statement to create a weblink inside the table wftp_weblink of bookmark_db sqlite flat-file database
# $query is used to precreate the sql-statement
# The @q_ vars are being filled in during the actual insert statement

   $LocalFilePath = "e:/ftp_in/"+$filepath+"/"+$filename
   $Query = "INSERT INTO wftp_weblink (f_weblink, f_domain, f_username, f_create_time, f_update_time, f_filename, f_filepath)
   VALUES (@q_weblink, @q_domain, @q_user, @q_created, @q_updated, @q_filename, @q_filepath)"
   
   Invoke-SqliteQuery -DataSource $Connection -Query $Query -SQLParameters @{
      q_weblink = $WeblinkStr
      q_domain = "it"
      q_user = "xxxxx"
      q_created = (Get-Date).ToString('dd-MM-yyyy HH:mm:ss') # realtime insert
      q_updated = (Get-Date).ToString('dd-MM-yyyy HH:mm:ss') # Let's keep this in for now (might not work without)
      q_filename = $FileName
      q_filepath = ($LocalFilePath -replace "\","/")    # Need to flip the Path-slashes (unix thingie)
   }
   
   

# Close SQL file

$connection.Close()

# Create weblink string:

$http = 'https://ftp.xxxxxxx.nl:443/main.html?download&weblink='+$WeblinkStr+'&realfilename='+$Filename

Write-Host $http