Secure PB mysql/mariadb connectivity [SOLVED]

Just starting out? Need help? Post your questions and find answers here.
User avatar
captain_skank
Enthusiast
Enthusiast
Posts: 636
Joined: Fri Oct 06, 2006 3:57 pm
Location: England

Secure PB mysql/mariadb connectivity [SOLVED]

Post 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
Last edited by captain_skank on Thu Jun 06, 2019 12:17 pm, edited 1 time in total.
infratec
Always Here
Always Here
Posts: 6810
Joined: Sun Sep 07, 2008 12:45 pm
Location: Germany

Re: Secure communication PB's mysql/mariadb connectivity

Post 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.
User avatar
Paul
PureBasic Expert
PureBasic Expert
Posts: 1243
Joined: Fri Apr 25, 2003 4:34 pm
Location: Canada
Contact:

Re: Secure communication PB's mysql/mariadb connectivity

Post 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.
Image Image
User avatar
captain_skank
Enthusiast
Enthusiast
Posts: 636
Joined: Fri Oct 06, 2006 3:57 pm
Location: England

Re: Secure communication PB's mysql/mariadb connectivity

Post 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.
User avatar
captain_skank
Enthusiast
Enthusiast
Posts: 636
Joined: Fri Oct 06, 2006 3:57 pm
Location: England

Re: Secure communication PB's mysql/mariadb connectivity

Post 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
infratec
Always Here
Always Here
Posts: 6810
Joined: Sun Sep 07, 2008 12:45 pm
Location: Germany

Re: Secure communication PB's mysql/mariadb connectivity

Post by infratec »

RunProgram() has some flags.
Try #PB_Program_Unicode
User avatar
captain_skank
Enthusiast
Enthusiast
Posts: 636
Joined: Fri Oct 06, 2006 3:57 pm
Location: England

Re: Secure communication PB's mysql/mariadb connectivity

Post 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 ?
infratec
Always Here
Always Here
Posts: 6810
Joined: Sun Sep 07, 2008 12:45 pm
Location: Germany

Re: Secure communication PB's mysql/mariadb connectivity

Post by infratec »

Code: Select all

Output$ + PeekS(*Buffer, Size, #PB_UTF8)
User avatar
captain_skank
Enthusiast
Enthusiast
Posts: 636
Joined: Fri Oct 06, 2006 3:57 pm
Location: England

Re: Secure communication PB's mysql/mariadb connectivity

Post 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.
User avatar
captain_skank
Enthusiast
Enthusiast
Posts: 636
Joined: Fri Oct 06, 2006 3:57 pm
Location: England

Re: Secure communication PB's mysql/mariadb connectivity

Post 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.
User avatar
captain_skank
Enthusiast
Enthusiast
Posts: 636
Joined: Fri Oct 06, 2006 3:57 pm
Location: England

Re: Secure communication PB's mysql/mariadb connectivity

Post 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.
Post Reply