PostgreSql and ssh port tunneling

[karu]

Sorry ma bad Englis.
Because no postgressql ssl possibility in Purebasic, I'm looking for a solution how to use PostgreSQL through SSH tunnel. I found a small program Plink and I would like to use it this way to just extract it from exe and run hidden. But hidden it does not work. Does anyone have any ideas, or does anyone know any other program to establish the SSH channel and it must run from command line. Also it must be small and one exe because that i can include this to my exe.
Thanks

Code: Select all

Global tunnelId.l
Global tunneli_thread.l

Procedure LooTunnel(a.l)
  
  tunnelId = RunProgram("c:\plink.exe", "karu@192.168.1.247 -L 192.168.1.1:63333:192.168.1.227:5432 -P 22 -pw uhuu", "",  #PB_Program_Write | #PB_Program_Read)
  ;tunnelId = RunProgram("c:\plink.exe", "karu@192.168.1.247 -L 192.168.1.1:63333:192.168.1.227:5432 -P 22 -pw uhuu", "",  #PB_Program_Open | #PB_Program_Write | #PB_Program_Read | #PB_Program_Hide) ; -good flags, but they do not work
  Repeat : Delay(1) : ForEver

EndProcedure


Procedure HavitaTunnel()
  
  If IsThread(tunneli_thread) : KillThread(tunneli_thread) : EndIf
  If IsProgram(tunnelId)
    KillProgram(tunnelId)
  EndIf

EndProcedure


tunneli_thread = CreateThread(@LooTunnel(), 1)

Delay(10000)
HavitaTunnel()

[RichAlgeni]

You should be able to adapt this code from getting a secure web page, to making a secure connection to PostgreSQL.

http://www.purebasic.fr/english/viewtop ... =5&t=52616

See this page to download cryptlib 32 and 64 bit.

http://www.purebasic.fr/english/viewtop ... =5&t=52463

[infratec]

Hi Karu,

this works for me:

Code: Select all

#PLinkPath$ = #DQUOTE$ + "c:\Program Files (x86)\PuTTY\plink.exe" + #DQUOTE$

#Server$ = "192.168.18.201"
#User$ = "xxx"
#Password$ ="yyyl"

#DatabaseUser$ = "aaa"
#DatabasePassword$ = "bbb"
#Database$ = "ccc"


UsePostgreSQLDatabase()


PLinkID = RunProgram(#PLinkPath$, "-ssh -l " + #User$ + " -pw " + #Password$ + " -L 5432:127.0.0.1:5432 " + #Server$, "", #PB_Program_Open|#PB_Program_Read|#PB_Program_Write|#PB_Program_Hide)


If PLinkID
  *Buffer = AllocateMemory(1024)
  If *Buffer
    Repeat
      Size = AvailableProgramOutput(PLinkID)
      If Size
        Size = ReadProgramData(PLinkID, *Buffer, Size)      
        Output$ + PeekS(*Buffer, Size)
        Debug Output$
        If FindString(Output$, "$")
          Ok = #True
        EndIf
      Else
        Delay(10)
      EndIf
    Until Ok
    FreeMemory(*Buffer)
  EndIf
EndIf

DB = OpenDatabase(#PB_Any,"host=127.0.0.1 dbname="+ #Database$, #DatabaseUser$, #DatabasePassword$, #PB_Database_PostgreSQL)
If DB
  Debug "Database Ok"
  ;Delay(3000)
  CloseDatabase(DB)
EndIf

If ProgramRunning(PLinkID)
  Debug "still running"
  WriteProgramString(PLinkID, "exit")
  CloseProgram(PLinkID);
;  KillProgram(PLinkID)
EndIf

PostgreSQL running on a linux server.
Shell prompt is "$"

Bernd

[karu]

You are right, i don't know why i did not get it to work before, thanks.

[infratec]

Hi Karu,

it was not easy

If you want to use #PB_Program_Hide it is difficult.

I have to use #PB_Program_Read and #PB_Program_Write

Else I get not the result that I wanted.
It was also tricky to find out that I have to send 'exit' to the PLink linux shell to close PLink complete.
CloseProgram() did not closed the program, it was still in the taskmanager.

Bernd

[karu]

Hi Karu,
CloseProgram() did not closed the program, it was still in the taskmanager.
Bernd

Code: Select all

Procedure GetPidFromName(Name.s)
  
  Protected result
  Protected Process.PROCESSENTRY32
  Protected ProcSnap = CreateToolhelp32Snapshot_(#TH32CS_SNAPPROCESS, 0)
  If ProcSnap <> #ERROR_INVALID_HANDLE
    Process\dwsize = SizeOf(PROCESSENTRY32)
    If Process32First_(ProcSnap, Process) = #True
      While Process32Next_(ProcSnap, Process) <> #False
        If Trim(PeekS(@Process\szExeFile,#MAX_PATH)) = Name.s
          result = Process\th32ProcessID
          Break
        EndIf
      Wend
    EndIf
    CloseHandle_(ProcSnap)
  EndIf
  ProcedureReturn result
  
EndProcedure



Procedure TerminateProcess(Name.s, ExitCode = 0)
  
  Protected result
  Protected processID = GetPidFromName(Name.s)
  ;Debug processID
  If processID
    Protected hProcess  = OpenProcess_(#PROCESS_TERMINATE, #False, processID)
    If hProcess
      If TerminateProcess_(hProcess, ExitCode)
        result = #True
      EndIf
      CloseHandle_(hProcess)
    EndIf
  EndIf
  ProcedureReturn result
  
EndProcedure

  Repeat
    TerminateProcess("plink.exe")
  Until GetPidFromName("plink.exe") = 0

This is my code for terminating connection

[infratec]

Hi Karu,

I think my method is more friendly