I need your advice or ideas on connecting to a remote MySQL database. I'm doing this in D5.
I need your advice or ideas on connecting to a remote MySQL database. I'm doing this in D5.
I've been working on an application that I would like to fill some tables with data collected by my desktop app. At first I explored using the TWebBrowser component and hacking some PHPand Javascript with JSON. I'm sure something like that is doable but in my mind not the cleanest approach.
Next, I came across and downloaded a unit called mysql.pas by Matthias Fichtner that serves as a Pascal interface unit for libmySQL.dll. I've been able to connect to the database and do some testing that confirms its suitability for the task at hand.
But here's the problem, in order for it to function (to my knowledge) it seems I need to open up my whole site domain to any IP address and also embed my admin user name and password in my program. Obviously a huge security breach waiting to happen.
So I'm looking for some thoughs on this. Am I missing something (other than current Delphi technology)?
I've been working on an application that I would like to fill some tables with data collected by my desktop app. At first I explored using the TWebBrowser component and hacking some PHPand Javascript with JSON. I'm sure something like that is doable but in my mind not the cleanest approach.
Next, I came across and downloaded a unit called mysql.pas by Matthias Fichtner that serves as a Pascal interface unit for libmySQL.dll. I've been able to connect to the database and do some testing that confirms its suitability for the task at hand.
But here's the problem, in order for it to function (to my knowledge) it seems I need to open up my whole site domain to any IP address and also embed my admin user name and password in my program. Obviously a huge security breach waiting to happen.
So I'm looking for some thoughs on this. Am I missing something (other than current Delphi technology)?
I think your best option is JSON with PHP(on SSL, if you need it) or a PHP webservice might do as well.
ReplyDeleteRegards,
A
If you were using D7 or better, I would recommend something with out-of-the box functionality such as RealThinClient, RemObjects, ICS, etc. Regardless, I think you could still make it work using some type of simple RESTful (web) service design.
ReplyDeleteYour desktop app acts as a HTTP client to send the data in a pre-determined format (XML, JSON, binary, etc) to a remote HTTP server application which updates your database. The server side doesn't even have to be a Delphi application. You could easily use some other web development technology/framework such as PHP, Python, Ruby, etc. There really are a lot of choices on the server side.
Thank you gentlemen, I've been putting a site together with Joomla which is PHP based. I'm a little timid due to the learning curve involved but I better soldier on.
ReplyDeleteYou have several Delphi options:
ReplyDeleteYou can write the back-end in straight Delphi using any of the MySQL libraries out there. I have used Zeos (open source/free) and DevArt MyDAC ($). Both are very fast compared to some other components I have used. I am currently using (and loving) MyDAC. DevArt has great support and rock solid components. MyDAC does not require any DLLs, since it talks directly to the DB engine. Zeos is found on SourceForge.
Your web site can also be written in straight Delphi using IntraWeb. I am not sure if D5 comes bundled with IntraWeb, but you can buy the IntraWeb XII version from AtoZed. Avoid IntraWeb XIV, since most 3rd party libs don't work with it yet. You design your web site look and feel in your Delphi IDE with special IntraWeb components. Your IDE outputs either a stand-alone web site server EXE or an ISAPI dll that you can plug in to Apache or IIS web servers. Note that you have to pay AtoZed extra for the ISAPI feature.
There are some new CGjQuery components for IntraWeb that are really nice. You can buy them from CGDevTools. jQuery includes all sorts of editors, DB grids and label components for web sites. They support themes (skins), rounded edges on editors, and other modern features. CGDevTools also has a suite of mobile components for smartphone-friendly web sites that is currently bundled free with their jQuery product.
D5 is pretty old, so you might want to check compatibility with any component libs you choose before plunking down cash. I haven't used D5 since... well D6, so memories fade. :-)
As the previous poster mentioned, you also can write your web site in PHP. I use phpEd from Nusphere for PHP development. It has an IDE similar to Delphi and a really nice debugger. The built in help/documentation is superb too.
Thanks Kevin, Looks like I've got a number of options, just got to get off the fence at some point.
ReplyDeleteAs for security, you can create a low-privilege read-only account or one with minimal write privs on MySQL. Your back end EXE and any web server software should use these account(s) and never your admin account! You can also restrict this account to be usable from only the IP of the web server. You do that on MySQL in the user settings.
ReplyDeleteBeware of putting personal information in your MySQL tables - consider encrypting data in your tables if it is sensitive information. Also use parameterized queries instead of building the SQL with simple string concatenation. You do not want to make your site vulnerable to SQL injection attacks.
Thanks more Kevin McCoy - it appears MyDAC does support D5 and looks to be within my limited budget. On the other hand your comment about creating a low-privilege account appeals to me, I'm just having a little trouble figuring out how. Probably I need to get my host's support team involved in some way. Most of this stuff is pretty new to me.
ReplyDeleteMost all of the data is not of a sensitive nature so encryption is not a major concern but I'll have to keep the SQL injection in mind.
What Andrea Raimondi said. Read up on REST interfaces.
ReplyDeletePatrick Hughes You mention your host's support team. If your talking about a hosted instance of MySQL and your website at a commercial provider, it's highly doubtful that you'll be allowed to deploy custom executables (i.e. programs written in Delphi) or use ports other than the few they probably allow; They're probably not hosting using Windows anyway. Also, I'd really be surprised if they allow direct access to MySQL over non SSL connections.
ReplyDeleteIf this is the case, you're going to have to work within the limits of your provider's technology stack. It's still doable, but your choices may be more limited. By what you've said so far, I'm guessing you'll be limited to a PHP solution on the server side.
Food for thought Kevin Powick , thanks. Yes it is a hosted by a commercial provider (Arvixe, which has been pretty good). It is a Linux server which I had just moved from a Windows server (also Arvixe). Like I mentioned though what I'm looking to do is add the functionality to a desktop app and basically allow the app to upload data into one or two different tables.
ReplyDeleteIman Crawford I looke briefly now into REST is this more for consuming data rather than creating/loading data?
You can create data as well. If you don't want to use REST per se, just use an INI file and use a simple POST request via INDY.
ReplyDeleteDoesn't have to be painful :)
Even better, you could use a simple HTTP POST with a CSV content.
PHP has got a standard library routine for that, creating an array as output.
Doesn't get simpler than this :)
A
Andrea Raimondi - at my advanced age anything new is painful ;-) I don't even have INDY components as I've had trouble loading them in the past. In fact my D5 install on my Win7 x64 is just hobbling along for now.
ReplyDeleteYou can do it with any component that does http. I think D5 has something based on wininet.dll.
ReplyDeleteIman Crawford I think the Synapse library could be a good fit - or ICS. It's a simple HTTP post...
ReplyDelete