It is currently Fri Nov 22, 2019 4:03 pm

All times are UTC + 1 hour




Post new topic Reply to topic  [ 11 posts ] 
Author Message
 Post subject: Secure PB mysql/mariadb connectivity [SOLVED]
PostPosted: Tue May 21, 2019 10:39 am 
Offline
Enthusiast
Enthusiast
User avatar

Joined: Fri Oct 06, 2006 3:57 pm
Posts: 534
Location: England
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.

Top
 Profile  
Reply with quote  
 Post subject: Re: Secure communication PB's mysql/mariadb connectivity
PostPosted: Tue May 21, 2019 3:50 pm 
Offline
Addict
Addict

Joined: Sun Sep 07, 2008 12:45 pm
Posts: 4425
Location: Germany
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.


Top
 Profile  
Reply with quote  
 Post subject: Re: Secure communication PB's mysql/mariadb connectivity
PostPosted: Tue May 21, 2019 11:32 pm 
Offline
PureBasic Expert
PureBasic Expert
User avatar

Joined: Fri Apr 25, 2003 4:34 pm
Posts: 918
Location: Canada
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


Top
 Profile  
Reply with quote  
 Post subject: Re: Secure communication PB's mysql/mariadb connectivity
PostPosted: Wed May 22, 2019 8:07 am 
Offline
Enthusiast
Enthusiast
User avatar

Joined: Fri Oct 06, 2006 3:57 pm
Posts: 534
Location: England
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.


Top
 Profile  
Reply with quote  
 Post subject: Re: Secure communication PB's mysql/mariadb connectivity
PostPosted: Wed Jun 05, 2019 4:11 pm 
Offline
Enthusiast
Enthusiast
User avatar

Joined: Fri Oct 06, 2006 3:57 pm
Posts: 534
Location: England
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


Top
 Profile  
Reply with quote  
 Post subject: Re: Secure communication PB's mysql/mariadb connectivity
PostPosted: Wed Jun 05, 2019 8:22 pm 
Offline
Addict
Addict

Joined: Sun Sep 07, 2008 12:45 pm
Posts: 4425
Location: Germany
RunProgram() has some flags.
Try #PB_Program_Unicode


Top
 Profile  
Reply with quote  
 Post subject: Re: Secure communication PB's mysql/mariadb connectivity
PostPosted: Wed Jun 05, 2019 9:31 pm 
Offline
Enthusiast
Enthusiast
User avatar

Joined: Fri Oct 06, 2006 3:57 pm
Posts: 534
Location: England
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 ?


Top
 Profile  
Reply with quote  
 Post subject: Re: Secure communication PB's mysql/mariadb connectivity
PostPosted: Thu Jun 06, 2019 6:59 am 
Offline
Addict
Addict

Joined: Sun Sep 07, 2008 12:45 pm
Posts: 4425
Location: Germany
Code:
Output$ + PeekS(*Buffer, Size, #PB_UTF8)


Top
 Profile  
Reply with quote  
 Post subject: Re: Secure communication PB's mysql/mariadb connectivity
PostPosted: Thu Jun 06, 2019 8:40 am 
Offline
Enthusiast
Enthusiast
User avatar

Joined: Fri Oct 06, 2006 3:57 pm
Posts: 534
Location: England
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.


Top
 Profile  
Reply with quote  
 Post subject: Re: Secure communication PB's mysql/mariadb connectivity
PostPosted: Thu Jun 06, 2019 9:24 am 
Offline
Enthusiast
Enthusiast
User avatar

Joined: Fri Oct 06, 2006 3:57 pm
Posts: 534
Location: England
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.


Top
 Profile  
Reply with quote  
 Post subject: Re: Secure communication PB's mysql/mariadb connectivity
PostPosted: Thu Jun 06, 2019 12:15 pm 
Offline
Enthusiast
Enthusiast
User avatar

Joined: Fri Oct 06, 2006 3:57 pm
Posts: 534
Location: England
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.


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 11 posts ] 

All times are UTC + 1 hour


Who is online

Users browsing this forum: No registered users and 28 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  

 


Powered by phpBB © 2008 phpBB Group
subSilver+ theme by Canver Software, sponsor Sanal Modifiye