Information from remotely hosted SQL database

Hello.

I’m trying to retrieve data using WinHttpRequest from a database. I’m displaying the results from the GET in a text box and I can see the HTML code displayed, but I can’t figure out how to get specific information without displaying the entire html.

Example:
I send my id request like this…
strUrl = "http://mysubdomain.mydomain.com/ admin/admin_login.php?"
xobj.Open "POST", strUrl, False
xobj.SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
xobj.SetRequestHeader "Content-Type", "application/x-www-form-urlencoded"

xobj.Send "username=manager&password=mypassword"

I get the results like this…
strUrl = "http://mysubdomain.mydomain.com/ admin/customer_details.php?"
xobj.Open "GET", strUrl, False

The returned body.innerHtml display is…

<B>/homepages/26/dataserver/htdocs/admin/customer_details.php</B> on line <B>39</B><BR><LINK rel=stylesheet type=text/css href="styles/customer_details.css">
<DIV class=page>
<DIV class=top></DIV>
<DIV class=text>Customer Details - ID: &nbsp;2
<P><A title="Back to the Index" href="about:index.php">Back to the Index</A> - <A href="about:product_list.php">List Customers</A></P>
<DIV class=details>
<TABLE cellPadding=5 width=960 bgColor=#ffffff border=1>
<TBODY>
<TR>
<TD vAlign=middle width=130 align=right>Username:</TD>
<TD width=581>manager2</TD></TR>
<TR>
<TD vAlign=middle align=right>Password:</TD>
<TD>myPa$$word</TD></TR>
<TR>
<TD vAlign=middle align=right>:</TD>
<TD>07 Jul, 2016</TD></TR></TBODY></TABLE></DIV><BR><BR></DIV></DIV>

Question: How can I get the username and password into a string variable?

Thank you.

All of this sounds decidedly dubious to me.

> "I’m trying to retrieve data using WinHttpRequest from a database"

The only DBMS I know can do that is SqlServer, which can host HTTP endpoints (although why anyone in their right mind would want to do so is beyond me). Web servers do web-y stuff and have a whole range of tools on hand to deal with all the nastiness that's rampant "Out There". Databases do database-y stuff, and don't.

> "The returned body.innerHtml display is … How can I get the username and password into a string variable?"

Two things worry me about this.

* Why are you trying to "screen scrape" somebody's password from a remote system, and
* Why the H*** is that remote system even capable of rendering that somebody's password in the first place??

You should never, never store passwords in plain text and, except in the most exceptional of circumstances, never store them in any kind of recoverable format.

All that said, your task requires on you only basic String manipulation functions - Instr, Mid and the like - and remember, of course, that Html is case insensitive and likely to change with any update to the site. Whoever it belongs to.

Regards, Phill W.

Thank you for the response Phill.

Your comments intrigue me, perhaps some background would help and my overall intentions then if there is a better solution I will gladly take this on board.

The application is currently being used internally to allow users to logon and request print jobs. The local DB holds user logon details that are set by the manager and there is a log table that is populated when a request is made which the manger can use to check the print and volume requests. There is almost a guarantee that the current users will be working from home within the next six months so I have been tasked with moving the DB to a hosted solution and linking the original application.

At first I was hoping to do a simple ADO connect to the remote DB, however our host provider like all other does not allow direct access to port 3306. I have some knowledge with php so I made a simple login screen that listed user details to see if this could be read by the application using Winsock or WinHttpRequest. Once past the initial test I will test with SSL so that the requests are not past in cleartext.

My brief is...
1. Keep the application the same
2. Move the DB to a managed hosted solution (our webhost)

If there’s a better option which will work with the brief then I will gladly take it on board.

Thank you,
Jason

OK, so you're not trying to "hack" into Faceplant or Twaddle or whatever the Latest and Grate-est [Anti]Social Medium is these days. Good.

However ...

> " ... our host provider like all other does not allow direct access to port 3306"

Curiosity: How are you supposed to make changes to your database [schema]?
Are you stuck with PhpMyAdmin on the hosting machine?

> "I will test with SSL so that the requests are not passed in cleartext"

That's not the problem.
It's a problem, to be sure; you don't want anyone else "sniffing" this traffic and helping themselves to people's passwords.

No; your problem is that a mechanism exists whereby your application can retrieve a user's password. To accomplish this, your database must hold all of your users' passwords in a recoverable form. Hopefully this is encrypted; a step in the right direction but, quite possibly, they are held in plain text which is, IMHO, inexcusable.

Your database (and all those passwords) are sitting on a hosted machine somewhere "Out There". If that machine is compromised, someone can walk off with your entire database and all your passwords and cause all sorts of trouble.
If the machine on which your database backups are stored is compromised, then someone can walk off with your entire database and all your passwords and cause all sorts of trouble.

> "My brief is ... Keep the application the same"
There's still nothing to stop you highlighting potential Security problems before they cost your business [a lot of] money. Your application contains Data of value to your business; it may well be seen as valuable by someone else.

Regards, Phill W.

Thank you Phill W.

Yes I’m stuck with PhpMyAdmin.

I will take a look at encrypting the passwords on the DB and decrypting at the application, however there is very little risk exposing the username and passwords hence the lack of willingness to invest in changes.

The username and passwords are simply used to allow the user access to the local application to avoid unauthorised users sending print requests. Once the user has logged in the print request is generated and then sent to the print room via email, then there is a confirmation process via email before any print work is completed, as a result any unauthorised request would be picked up during the confirmation process.

Regards,
Jason

Ok... so the DB is essentially sitting behind a firewall so there is no direct access... OK, that's good.

You have the right idea, wrong approach. It sounds like you're trying to automate phpMyAdmin ... wrong.. wrong wrong...
What you should be doing is creating a set of web services through which you communicate.

For example, let's say I want the customer details for some customer... so I create a web page that takes an ID from the url, opens the database, gets the data and displays it as xml...
the url would look like this:

http://www.mydomain.com/services/api/customer.php?ID=1234568&authToken=sdfi34934dsc

The result I get back is some xml that contains that customer's info... which I grab using essentially the same technique you're using... I can then parse it and display it in my web form.

To update the customer I package up the details and POST it to a web service again:

http://www.mydomain.com/services/api/customerUpdate.php


Since I'm using POST, I don't display the customer's data through the query string, and it would include the id as well...

the php page then issues the UPDATE command against the database.

you should NOT be going through phpMyAdmin for any of this.

-tg

Hi Techgnome,

Phill. W asked –“Curiosity: How are you supposed to make changes to your database [schema]? Are you stuck with PhpMyAdmin on the hosting machine?”

Me – “Yes I’m stuck with PhpMyAdmin.”

My understanding of this question is… that I use PhpMyAdmin to administer and make table layout changes - I’m not trying to automate PhpMyAdmin as you assume, on the contrary I’m trying to do as you are, passing information from and to php pages.

Tg, what set of web services do you use to communicate? I assumed I was using WinHttpRequest as my service, is there something else?

What prompted do you get if you were to go to http://www.mydomain.com/services/api/customer.php? without passing the ID and authToken in the string?

I’ve designed my php pages to redirect to a login page if there is no active session ID. Is this a good approach?

Regards,
Jason

I'm not sure how phpMyAdmin even figures into this....where did "layout changes" come from? (never mind, I see where it comes from and at the moment it's irrelevant) Are you trying to admin your database? or access it from code to get data from it?

WinHTTPRequest should be fine...

Quote:

---

What prompted do you get if you were to go to http://www.mydomain.com/services/api/customer.php? without passing the ID and authToken in the string?

---

I'd expect to see an authorization error message. If the AuthToken isn't valid (the user isn't logged in, or the token has expired), then it should throw back an error message of somekind.

We maybe talking cross purpose here... it might be that I'm not 100% clear on the goal here...

What I assume:
you have a VB6 app that's on a desktop somewhere.
You have a database hosted by 1&1 out on the interwebtubesnet
You don't have direct access to the db because it is behind a firewall (as it should be)
So how do you get the data from the db into VB6?

What I'd do:
set up a set of pages that accepts arguments via POST or GET to perform the operations for you...
At the top of each one, it checks for the AuthToken - if it is invalid or wasn't passed in, the page generates an exception and sends it back.
From the VB6 code, I check the return value, was it the data I expected, or was it an error? If it's an error message, depending on what it is, I notify the user or do what's needed to fix it.
So if it gets back an authentication method, I know my token wasn' acctepted and I either need to prompt the user to login (not from a page though, but in your app) and resend it to get a new token by invoking the login web api... which takes the user id & pwd and sees if it is valid. The user never actually sees any pages or anything... their interaction is with the app.

there is no session, nothing in the browser. it's all communication between the app and the server. You send it data via GET/POST and it replies back with some thing - could be XML or JSON - or some other data structure - it's all up to you since you're the one building it.... I gravitate to XML because it's what I'm used to, but JSON is just as good.


Here's a real example ... I used similar setup to control a streaming radio station... so if I wanted info about an album, the call looked like this:
\radiostation\album?id=123445
and it would return this:

Code:

---

<album id=123445>
  <track id=453545 number=1>Track 1 Title</track>
  <track id=453546 number=2>Track 2 Title</track>
  <track id=453547 number=3>Track 3 Title</track>
  <track id=453548 number=4>Track 4 Title</track>
  <track id=453549 number=5>Track 5 Title</track>
</album>

---

And so I would then parse that XML into what I needed and display it. if you were to plug it into the url bar of a browser, that's exactly what you would get back - the raw XML rendered.... not html or anything else.

But that's just me...


-tg

"Web service" could be a confusing term I suppose. It is shorthand for saying "an API using HTTP over TCP/IP to talk to remote logic running in a web server."

There is no HTML involved, no CSS, no JavaScript, no user interface... none of that. It is an API.

What rides on top of the HTTP requests and their responses can be almost anything but is usually some combination of HTTP headers, XML, JSON, binary BLOBs, or even CSV text.

Your client could use WinHttpRequest objects to provide the HTTP transport infrastructure. Your server could use Apache, IIS, etc. as infrastructure.

You design the web service by deciding on several things:

• How clients should pass "call" arguments, typically either in GET method query string name/value pairs or in POST or PUT method bodies of some format.
• How the service will return results in responses, either in headers if the amount of information returned is small, or in bodies of some format, or a combination of both.
• What set of calls are required.
• How clients will authenticate with the server.

From there you write your service in PHP or whatever, and your clients in VB6 or whatever.

Oops, too slow. ;)