VB6 to PHP/MySQL Interface

[cliv]

I need to create a program in VB6 that interfaces with PHP scripts to access a MySQL database.
Users wishing to send queries via Visual Basic to a remote MySQL server that doesn't otherwise allow remote access.
I don't work until now with POST/GET ... so please post me a sample if you have.

<?php

$databasehost = XXXXXXXXXX;
$databasename = XXXXXXXXXX;
$databaseusername =XXXXXXXXXX;
$databasepassword = XXXXXXXXXX;

$con = mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error());
mysql_select_db($databasename) or die(mysql_error());
mysql_query("SET CHARACTER SET utf8");
$query = file_get_contents("php://input");
$sth = mysql_query($query);

if (mysql_errno()) {
    header("HTTP/1.1 500 Internal Server Error");
    echo $query.'\n';
    echo mysql_error();
}
else
{
    $rows = array();
    while($r = mysql_fetch_assoc($sth)) {
        $rows[] = $r;
    }
    print json_encode($rows);
}
?>

[Nightwalker83]

Why do you need the program to interact with php? Visual Basic 6.0 can communicate with the database on its own.

[dilettante]

Why do you need the program to interact with php? Visual Basic 6.0 can communicate with the database on its own.

Nobody with any sense exposes a database to direct connections across the Internet, and for that reason many hosting providers do not allow it.


From the looks of things your PHP script returns JSON results. So in addition to using an HTTP client library you will need at least a JSON parser, and if you post updates a JSON serializer too.

VB6 has a plethora of choices when it comes to making HTTP requests:

• AsyncRead method. Built into VB6, but can only do GET requests.
• Internet Transfer Control (INet). Aging and somewhat clumsy VB6 OCX.
• XmlHttpRequest object. Part of MSXML, supported as far as back Win95 with IE 4.x installed and built into Windows from at least Win2K onward.
• WinHTTPRequest object. Part of Windows since Win2K SP3.

Or you can cobble something up from WinInet API calls or buy a 3rd party library. But with so many "free" and reliable alternatives why bother?


As for examples, there are tons of them on the Web. But most of them are either trivial cases, outright junk, or otherwise ignore numerous factors. In particular you need to worry about cache control on GET requests, async operation to avoid program freezing, request timeout handling, error handling, and request authentication issues.

[dilettante]

The other concern I see is that the PHP blindly accepts SQL text and applies it to your database. This is almost as bad as just allowing direct database connections.

What if somebody sends queries to delete everything?

You should really create a query protocol to send requests that your script can validate for legal actions and then create its own SQL to carry out acceptable actions.

[Nightwalker83]

What if somebody sends queries to delete everything?

If I remember correctly the roles would be defined beforehand! Anybody who is a normal user should not be able to delete anything from the database.

[dilettante]

If I remember correctly the roles would be defined beforehand! Anybody who is a normal user should not be able to delete anything from the database.

If you mean the database would be read-only for all users that might be true, but if users will be doing updates they will probably also be doing deletes. Even if they don't do deletes (and the User ID isn't allowed to) they might well be able to update records or add records with garbage data.

[John Brown]

Nobody with any sense exposes a database to direct connections across the Internet, and for that reason many hosting providers do not allow it.

dilettante,

Would "direct connections" apply to a desktop VB6 application utilizing Microsoft ADO which constructs a connection string with username and password. Am I correct that even though this connection utilizes username / password security, it is still not secure?

(I do intend to read the cloud data storage ideas listed at the top of this forum's page. I am very interested in cloud data storage. I have been working with VS2012 but Microsoft makes the concept of cloud data access so convoluted that I have a hard time understanding the technology. I can make it work using their examples but I don't understand it. I have had better luck with open source stuff like MySQL than I have working exclusively with Microsoft products.)

Thanks, John Brown

[dilettante]

If a MySQL server has a connection port open to the Internet any specific MySQL exploits could make the service or even the entire machine and local network vulnerable. The same applies using any DBMS.

Ignoring that you'd need some way to protect the user/pw from being uncovered by malicious people. They might sniff it over the wire (even web apps don't send user/pw in plain text) or they might extract it from a copy of the client application or they might get lucky through brute force attacks.

Exposing any database server to the Internet just isn't done. A few web searches should turn up a lot on the subject.

What you want to do is allow access through a "window" so narrow it is only useful for a specific application. This is normally done through some software (a PHP "page" being just one example) that accepts requests in a format it can edit for safety and validity. Accepting a SQL query is a problem because it means a ton of extra work parsing and validating... and almost nobody does it so you have a security hole big enough to drive a truck through.

But I've learned not to expect much from most programmers so I'm sure the advice will be ignored.

[techgnome]

If a MySQL server has a connection port open to the Internet any specific MySQL exploits could make the service or even the entire machine and local network vulnerable. The same applies using any DBMS.

Ignoring that you'd need some way to protect the user/pw from being uncovered by malicious people. They might sniff it over the wire (even web apps don't send user/pw in plain text) or they might extract it from a copy of the client application or they might get lucky through brute force attacks.

Exposing any database server to the Internet just isn't done. A few web searches should turn up a lot on the subject.

What you want to do is allow access through a "window" so narrow it is only useful for a specific application. This is normally done through some software (a PHP "page" being just one example) that accepts requests in a format it can edit for safety and validity. Accepting a SQL query is a problem because it means a ton of extra work parsing and validating... and almost nobody does it so you have a security hole big enough to drive a truck through.

But I've learned not to expect much from most programmers so I'm sure the advice will be ignored.

Fear not mi'lord! For I shall echo your sage advice among the walls of this hallowed hall by momentarily repressing then liberating that text of magic and affirm to reply with quote. I shall then use an older and summarily somewhat ancient and anachronistic fashion as to draw attention to thine post and illuminate those that walk among the uninitiated that they too shall then strive to be a programmer of a Higher Order such as thine self.


-tg

[cliv]

Thank you all for advice...but my request remain...
Can someone post a sample?

[dilettante]

I waited but I haven't seen anything posted by the peanut gallery so I posted an example of my own. See VB6 - Web Service Calls via WinHTTP POST in the CodeBank.

This does what you asked for without being copy/paste code. Feel free to modify it to use JSON as the serialization format.

[cliv]

This does what you asked for without being copy/paste code. Feel free to modify it to use JSON as the serialization format.

Thank you ... but i'm a beginner and i really ask for a sample URL how to send query to a php server using code from first post.

I use WinHTTP like in your post but i don't now how to send variable $query from php code.

I use:

Option Explicit
Private WithEvents httpURL As WinHttp.WinHttpRequest
Const HTTPREQUEST_PROXYSETTING_DIRECT = 1

Private Sub cmdGo_Click()

    httpURL.SetProxy HTTPREQUEST_PROXYSETTING_DIRECT
    httpURL.Open "GET", "http://www.URLsite.com/code.php?query=%22SELECT%2B*%2BFROM%2BTable%22"
    httpURL.Send
    
    txtGET.Text = httpURL.ResponseText
End Sub

but return was "\nQuery was empty"
What is correct URL-encoded data?

[dilettante]

One issue with using GET requests for web service APIs is that the GET parameter string is limited in length. Today most servers will accept well over the old recommended maximum of 255 characters and most clients can send longer strings, but there is a fairly finite limit.

Another big issue is cached results, which can mean getting back out of date data from old requests. WinHTTP offers fairly easy local cache control so you can suppress that however.

POST requests can send long payloads as body text. HTTP caching is also designed not to cache responses to POSTs.


Your question above appears to be about URI/URL encoding.

The code above seems to try to pre-encode this information. But WinHTTP and most HTTP client components will do that for you. So if you try to do it manually, what reaches the server is "double-encoded."

Have you tried:

    httpURL.Open "GET", "http://www.URLsite.com/code.php?query=""SELECT * FROM Table"""

[cliv]

the code above seems to try to pre-encode this information. But winhttp and most http client components will do that for you. So if you try to do it manually, what reaches the server is "double-encoded."
have you tried:

...not working