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