VFP9 and connect to MySQL by SSH-protocol

[Eliott]

Hi to all,
My hosting company allowed me using SSH-protocol in order to update MySQL database on their host from VFP app on client computers. I picked Putty free application for setup SSH, and for this task I need only IP of host, port and username with password, no need for public or private key. I made a command line and put all necessary parameters for connection into *.bat file that after run made connection. Voila. Afer that I started Navicat and after setting correct parameters for connection I got hosting database right into Navicat window, what is proof that's working, right? But until my program code with local MySQL running under Win on same PC where is VFP is working great, I was unable to make connection to hosting MySQL and get data from any table! I'm now confuse shall I use name server as localhost, or IP-address of host, what is with port (3306 or a one I got from administrator for SSH..? Is here anybody that is willing to help me? Thanks.

lcServer="localhost"
lcPort= 3306
lcDatabase="mydatabase"
lcUser="myname"
lcPassword="mypassw"
lcStringConn="Driver={MySQL ODBC 3.51 Driver};Server=&lcServer;Port=lcPort;Option=16384;Stmt=;Database=&lcDatabase;Uid=&lcUser;Pwd=&lcPassword"
SQLSETPROP(0,"DispLogin",1)
lnHandle=SQLSTRINGCONNECT(lcStringConn)
upit="select * from table1"
IF lnHandle > 0
  rez=SQLEXEC(lnHandle, upit, "ctable1")
	browse
ELSE
*error description
endif
=SQLDISCONNECT(lnHandle)

There is no good nor evil, just decisions and consequences.

 

[Olaf Doschke]

SSH is SSH, Secure Shell, A remote MySQL connection only may use the same SSL certificate, but otherwise isn't related to SSH.

Create an ODBC DSN via ODBC Administrator, choose MySQL ODBC driver and in the Data Source Configuration click [Details>>], you'll find a tab "SSL settings" and need to set these parameters, especially private/public key and such, to make an SSL connection.

If the port of the MySQL Server is 3306 or not depends on how your provider set this up, but it's most certainly not the same port as SSH, as it's a different service and will have another Port, you don't put two services on the same Port.

Bye, Olaf.

 

[Eliott]

Hi Olaf,
thank you for your fast response. I found last version of MySQL ODBC and installed it; there I saw SSL option you mentioned in your post, but I don't know how it would help me in this case. Hosting company explicitly said "possible with SSH", there wasn't any SSL mention... so I don't know what now? Could you be a little more specific in your explanation, please, as I'm newbie in these extremely security waters?
Thank you.

There is no good nor evil, just decisions and consequences.

 

[Olaf Doschke]

http://netforbeginners.about.com/od/technoglossary/f/whatis_SSL.htm

Both are safe connections, but SSH rather is for remote control, a remote command prompt, not a connection to another service like FTP, HTTP or a database.

So with SSH you can remotely interact with your MySQL database this way:

http://service.futurequest.net/index.php?_m=knowledgebase&_a=viewarticle&kbarticleid=499

This just means you work on the remote server's command prompt and act with MySQL locally, that does not enable remote connections to the database for a client desktop app.

Your provider obviously didn't understand your needs.

You need the SSL connection to really have remote data access.

Bye, Olaf.

 

[Eliott]

This just means you work on the remote server's command prompt and act with MySQL locally, that does not enable remote connections to the database for a client desktop app.
Your provider obviously didn't understand your needs.

Hi Olaf, thank you a lot for your precise answer. Sure, hosting administrator probably have no idea for my needs about connection clients app and remote MySQL. Cpanel offer SSL icon, so I'll try this way, but also don't know how to resolve new solution (situation). I thought to make on clients PC some kind of CSV-file and upload it from VFP by proper FTP-routine and then execute PHP-script that will process mentioned file (parse) and update tables, but too many hard coding, I would like some more direct way, like: update DBF-table on client PC, then use ODBC-connection with remote MySQL and execute the right query. Now, this idea seems to me like neverending story and bring sorrow in my soul... how is possible that so mighty tool like VFP9 can't deal with such problem (challenge) that become daily needs of plenty programmers around World?
Thank you a lot Olaf. Best regards pal.

There is no good nor evil, just decisions and consequences.

 

[Olaf Doschke]

In fact remote access can be easier than that. But only with SSL it will be secure, otherwise you send connection user password and data in plaintext over the internet, and as that is no point to point connection every node on the way would have access.

That's the reason hosting providers typically don't open mySQL for remote connections unless it's done via SSL.

Nothing of the compliction about this lies within VFP.

You can have it easier, but you shouldn't be surprised to see your server capacities used by hackers or spam bots for example.

Bye, Olaf.