Server-server Communication

[hedgefighter]

Here's what I want to do:

I have 2 servers. On one I have a mySQL database. On the other a website. Is there any way to have my website server send a request to the database server for some data and have the database server return the data?

Thanks a lot!

[ober]

Yes.  Can you show us the code you've come up with so far?

[hedgefighter]

I don't really have anything written yet. I was just looking for advice on how to go about doing it.

[hedgefighter]

Essentially what I want to do is have my web server send username, password, and what data it needs to my database server. The database server performs checks on the username and password and then sends the data back to the web server. I don't necessarily want the web server to be able to diectly access the database on the other server. I want the database server to authenticate the user and then access it's database and then return the data to the web server. Is this at all possible?

[mainewoods]

Call your second website using the curl library on the first website.  Create a php page on the second website to recieve that call.  As well as the username and password, the page from the first server could also pass a sql statement to the page on the second server(use curl POST fields).  The php page on the second server would then execute the sql and retrieve the appropriate record set.  When that page echoes out the data in the recordset it will be sent down the pipe to the curl statement in the php page on the first website - it will not be sent to the browser.  A good strategy would be to encode the recordset into an xml file and then return that to the first website which can then just decode the xml file and do what it wants with it like printing them out or saving them.

[mainewoods]

-To make the system more secure, create an extra field to transmit to the second website from curl on the first website.  Make that field equal to the md5 hash of the sql statement you are going to transmit plus a 'secret word', like this:
[code]<?php
    $security = md5($sqlstatement . 'php rules');
?>[/code]
-on the other side you would make sure it passes security:
[code]<?php
   if ($_POST['security'] != md5($_POST['sqlstatement'] . 'php rules')) {
        //doesn't pass security!
        exit; //or return a 'forbidden' header
    }
?>[/code]
--using a security strategy like that you could even avoid sending the db username and password every call and just hard code them on the page on the second server.  It's probably a little more secure that way.

[hedgefighter]

Thanks a lot. That looks like just the thing I need.

[Jenk]

[quote author=mainewoods link=topic=107671.msg432412#msg432412 date=1158018398]
-To make the system more secure, create an extra field to transmit to the second website from curl on the first website.  Make that field equal to the md5 hash of the sql statement you are going to transmit plus a 'secret word', like this:
[code]<?php
    $security = md5($sqlstatement . 'php rules');
?>[/code]
-on the other side you would make sure it passes security:
[code]<?php
   if ($_POST['security'] != md5($_POST['sqlstatement'] . 'php rules')) {
        //doesn't pass security!
        exit; //or return a 'forbidden' header
    }
?>[/code]
--using a security strategy like that you could even avoid sending the db username and password every call and just hard code them on the page on the second server.  It's probably a little more secure that way.
[/quote]That is so vulnerable you may as well not even bother :)

[mainewoods]

    In what way is that vulnerable Jenk?  Of course the system could be made more secure by not allowing a generic sql statement as I did, I did that to shorten the illustration and simplify the key points.  You'd probably want the database side php page to only allow a small selection of database retrieval actions, maybe using a switch() statement.  The database passwords would be stored on the database server side and protected as usual like storing them in a file located off the web tree.  Conclusion: php 'DBserver' page on the database side no more vulnerable than other php pages on that site.

    Using a md5 security token included with the call to the remote db from the local web site could prevent others from even reading your db records if you want, or you could use this method to 'expose your own API' and allow the world at large to retrieve your db records!  Conclusion: It's the easy way to 'expose your own API' and create a 'Web Service' with a 'Service Orientated Architecture'!  Your 'Web Service' could be called from another website as simply as using the file_get_contents function:
[code]<?php
    $dbRecordset =  file_get_contents("http://dbwebsite.com/dbserver.php?option=1&md5=deddddd3456deffabcdef9807fff456890888");
?>[/code]

[Jenk]

Sending an SQL statement over POST... that's the security vulnerability.

You are limiting the accessibility of such functionality to a select few, but for a start you might as well just write down your DB schema on a piece of paper, make a paper plane out of it and launch it out the window :)


I am utterly surpised no one has pointed out mysql_connect() does not have to have the host specified as 127.0.0.1/localhost everytime.. you can use other hosts (machines) ..

[mainewoods]

If you are on a shared host, they usually don't allow you to set a username/password so the db can be called from a remote host/ only localhost connections are allowed.

If I made a paper airplane with my db schema on it and flew it out the window, the chances that it would land on someone who knew how to malicously use it would be about .00001%!

Reducing functionality is what a lot of security is all about.  Desktops and Browsers have so many security vulnabilities because of all the 'user friendly' features.  Desktops and Browsers with way less functionalities would be way easier to make 'bullet proof'.  A simple switch statement db side should work fine for hedgefighter, or he could use a more flexible system and transmit the 'WHERE' part of the sql statement.  The sql phrase could be encryted before transmittal and an md5 security token sent as well.  If security doen't pass db side, an exit(); is executed an no db structure info is exposed.

So how would you crack that system Jenk?  I submit that it would take a master hacker to have any chance whatsoever cracking that!

[ober]

I would submit that it only takes one person to find the hole in your system.  Why give someone that chance?

[mainewoods]

It only takes only one person to find a hole in a message board so should there be no message boards?  In fact a message board is more vulnerable than what I outlined above because a message board is vulnerable to javascript injection, sql injection, and url parameter corrupting.  Lots of code has to be included under the hood in a message board to protect from those things and it still may not be enough as seen by this web sites  recent Private Message System penetration.

Many big websites like google now expose their api, are they in error?  Are web services in error because they might return db records?

If we don't do anything that someone might find a hole in, then we would have to give up the web and computers completely. 

[Jenk]

Obscurity != Security.

First rule of programming.

You can argue all day long with 'whatifs' but POST'ing SQL statements is a definite 'No.'

[mainewoods]

Ya I wouldn't actually recommend sending a full sql statement, I only used that in the first post to this topic to simplify the explanation(I explained that, nobody seemed to notice).  Probably you'd just want to sent the post fields that the query would be based on like a usual query would be done and the actual sql is created when the fields are recieved and are cleaned using mysql_real_escape_string before inserted them into the sql.  If I added a security token in the transmission to stop unautherized access, the database would be at no more risk than usual when both the form that is going to specify the query terms and the php page that retrieves the records are on the same website.  In fact it could easily have at much security as a logged in user on a web site! In fact maybe more because there would be no session id to hacked.

So tell me how that could be hacked Jenk.  Give me an actual technique someone would use to hack it.  I've studied a lot of hacking techniques including sql injection, url variable corruption, session id theft, using unprotected includes, register globals problems, and others.  I don't see anything I've outlined that makes the system 'more' vulnerable to those things than they would usually be.

[Jenk]

Simple.

MD5 rainbow table, a few minutes patience and boom - they have discovered your salt.

[mainewoods]

I believe those md5 translation tables only work if someone uses an poor password like say 'startrek' or words in the dictionary.  If I use good rules for creating the salt, I can pedict it would be found in no md5 translation tables.  Rules involving not using dictionary words, using upper and lower case letters, and using numerals.  As well if I use characters in my salt that are not even usually allowed as part of passwords like the space character or quotes then the chance of finding the translation in a md5 translation table is further reduced.  As well, your own password is probably stored as a md5 hash on this very website, so your own password is just as insecure!

-by the way there are this # unique md5 hashes:
16^32 = 3.4028236692093846346337460743177e+38
-that's 38 decimal places!  That number might be greater than the entirety of all the current hard drive storage in the world!