Secure PB mysql/mariadb connectivity [SOLVED]
- captain_skank
- Enthusiast
- Posts: 636
- Joined: Fri Oct 06, 2006 3:57 pm
- Location: England
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
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.
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.
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
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.
I currently use it in a number of projects for tunneling in to servers, specifically for secure access to Postges and MariaDB databases.
- captain_skank
- Enthusiast
- Posts: 636
- Joined: Fri Oct 06, 2006 3:57 pm
- Location: England
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.
I have looked a the Chilkat livrary before -perhaps it's time for a revisit.
- captain_skank
- Enthusiast
- Posts: 636
- Joined: Fri Oct 06, 2006 3:57 pm
- Location: England
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 :
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
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
RunProgram() has some flags.
Try #PB_Program_Unicode
Try #PB_Program_Unicode
- captain_skank
- Enthusiast
- Posts: 636
- Joined: Fri Oct 06, 2006 3:57 pm
- Location: England
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 )
Any other ideas ?
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 )
Any other ideas ?
Re: Secure communication PB's mysql/mariadb connectivity
Code: Select all
Output$ + PeekS(*Buffer, Size, #PB_UTF8)
- captain_skank
- Enthusiast
- Posts: 636
- Joined: Fri Oct 06, 2006 3:57 pm
- Location: England
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.
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.
- captain_skank
- Enthusiast
- Posts: 636
- Joined: Fri Oct 06, 2006 3:57 pm
- Location: England
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 :
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.
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
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.
- captain_skank
- Enthusiast
- Posts: 636
- Joined: Fri Oct 06, 2006 3:57 pm
- Location: England
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
Thanks for all the help and pointers.
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$)