Page 1 of 1

Secure PB mysql/mariadb connectivity [SOLVED]

Posted: Tue May 21, 2019 10:39 am
by captain_skank
Is the new mysql/mariadb connectivity secure ?

E.g : is all information sent between the server and client 'in the clear'

If it is, is there a way to do this nativley using ssl or ssh ?

I know it can be done using ODBC ( manualy ) but i've never managed to setup an SSL connection through PB.

Any help/info appreaciated.

cheers

Re: Secure communication PB's mysql/mariadb connectivity

Posted: Tue May 21, 2019 3:50 pm
by infratec
The easiest way is to use plink.exe from the PuTTY collection.
You can start it with RunProgram() to establish a ssh tunnel.
Then you can use the normal unsecure PB database stuff through the secured tunnel.

Re: Secure communication PB's mysql/mariadb connectivity

Posted: Tue May 21, 2019 11:32 pm
by Paul
If you have a bit of a budget, ChilKat is pretty amazing with lots of support for PureBasic.
I currently use it in a number of projects for tunneling in to servers, specifically for secure access to Postges and MariaDB databases.

Re: Secure communication PB's mysql/mariadb connectivity

Posted: Wed May 22, 2019 8:07 am
by captain_skank
OK Thanks for the info - will give both options a look.

I have looked a the Chilkat livrary before -perhaps it's time for a revisit.

Re: Secure communication PB's mysql/mariadb connectivity

Posted: Wed Jun 05, 2019 4:11 pm
by captain_skank
I've had a bit more time to play with this and altered some code from a thread about ssh & postgress :

Code: Select all

#PLinkPath$ = #DQUOTE$ + "plink.exe" + #DQUOTE$

#Server$ = "xxxxxxxx"
#User$ = "xxxxxx"
#Password$ ="xxxxxxxx"

#DatabaseUser$ = "xxxxxxxxx"
#DatabasePassword$ = "xxxxxxx"
#Database$ = "xxxxxxx"


UseMySQLDatabase()


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

Debug "looking for connecton.."
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
      Debug "."
    Until Ok
    FreeMemory(*Buffer)
  EndIf
EndIf
Debug "SSH Connected"

Debug "Connecting to database"

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

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

Seems to be getting stuck in a loop looking for '$' when connecting with plink ? And all debug output appears to be in Japanese ?

is this a Unicode issue ?

[EDIT] Oh, i'm using PB 5.70 on windows 10 x64 ( up to date ) and connecting to a Ubuntu server if that makes any difference

Re: Secure communication PB's mysql/mariadb connectivity

Posted: Wed Jun 05, 2019 8:22 pm
by infratec
RunProgram() has some flags.
Try #PB_Program_Unicode

Re: Secure communication PB's mysql/mariadb connectivity

Posted: Wed Jun 05, 2019 9:31 pm
by captain_skank
Thanks for the info.

Tried that, still the same response, just continues looping and debug output is in either chines or Japanese characters ( which I can't paste here cos it makes the editor have a brain fart :D )

Any other ideas ?

Re: Secure communication PB's mysql/mariadb connectivity

Posted: Thu Jun 06, 2019 6:59 am
by infratec

Code: Select all

Output$ + PeekS(*Buffer, Size, #PB_UTF8)

Re: Secure communication PB's mysql/mariadb connectivity

Posted: Thu Jun 06, 2019 8:40 am
by captain_skank
Many thanks infratec.

I had not thought of that at all ( even though i'd suspected UTF to be the issue )

SSH connection working fine now.

Just need to figure out the database connection which for some reason isn't working.

Re: Secure communication PB's mysql/mariadb connectivity

Posted: Thu Jun 06, 2019 9:24 am
by captain_skank
I think this is more of a PLINK issue but here goes.

The code can now establish an ssh tunnel to the server, but the database can't connect.

if I do it manualy using putty and the command line it woks fine.

Just so i'm understanding this correctly the connection string part :

Code: Select all

 -L 3306:127.0.0.1:3306 
this redirects the remote port 3306 to port 3306 on the local machine and therefore the database is accessed on 127.0.0.1.

It's a little confusing as the PLINK manual also lists the -R option but i think thats the opposit way around e.g the remote machine.

Re: Secure communication PB's mysql/mariadb connectivity

Posted: Thu Jun 06, 2019 12:15 pm
by captain_skank
Figured it out after much trial and error.

You have to specify the port number in the opendatabase statement

Code: Select all

DB = OpenDatabase(#PB_Any,"host=127.0.0.1 port=3306 dbname="+ #Database$, #DatabaseUser$, #DatabasePassword$)
Thanks for all the help and pointers.