PureBasic Forum
http://forums.purebasic.com/english/

Secure PB mysql/mariadb connectivity [SOLVED]
http://forums.purebasic.com/english/viewtopic.php?f=13&t=72861
Page 1 of 1

Author:  captain_skank [ Tue May 21, 2019 10:39 am ]
Post subject:  Secure PB mysql/mariadb connectivity [SOLVED]

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

Author:  infratec [ Tue May 21, 2019 3:50 pm ]
Post subject:  Re: Secure communication PB's mysql/mariadb connectivity

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.

Author:  Paul [ Tue May 21, 2019 11:32 pm ]
Post subject:  Re: Secure communication PB's mysql/mariadb connectivity

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.

Author:  captain_skank [ Wed May 22, 2019 8:07 am ]
Post subject:  Re: Secure communication PB's mysql/mariadb connectivity

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.

Author:  captain_skank [ Wed Jun 05, 2019 4:11 pm ]
Post subject:  Re: Secure communication PB's mysql/mariadb connectivity

I've had a bit more time to play with this and altered some code from a thread about ssh & postgress :

Code:
#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

Author:  infratec [ Wed Jun 05, 2019 8:22 pm ]
Post subject:  Re: Secure communication PB's mysql/mariadb connectivity

RunProgram() has some flags.
Try #PB_Program_Unicode

Author:  captain_skank [ Wed Jun 05, 2019 9:31 pm ]
Post subject:  Re: Secure communication PB's mysql/mariadb connectivity

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 ?

Author:  infratec [ Thu Jun 06, 2019 6:59 am ]
Post subject:  Re: Secure communication PB's mysql/mariadb connectivity

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

Author:  captain_skank [ Thu Jun 06, 2019 8:40 am ]
Post subject:  Re: Secure communication PB's mysql/mariadb connectivity

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.

Author:  captain_skank [ Thu Jun 06, 2019 9:24 am ]
Post subject:  Re: Secure communication PB's mysql/mariadb connectivity

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:
 -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.

Author:  captain_skank [ Thu Jun 06, 2019 12:15 pm ]
Post subject:  Re: Secure communication PB's mysql/mariadb connectivity

Figured it out after much trial and error.

You have to specify the port number in the opendatabase statement

Code:
DB = OpenDatabase(#PB_Any,"host=127.0.0.1 port=3306 dbname="+ #Database$, #DatabaseUser$, #DatabasePassword$)


Thanks for all the help and pointers.

Page 1 of 1 All times are UTC + 1 hour
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
http://www.phpbb.com/