Inserting row into MySQL table on remote server

[suklee]

Using PHP:

On website/server A, I need to take a PayPal payment - once it goes through, I want to insert a row of data into a database residing on server B. What's a good way to do this 'remote insertion'? (I'm not sure what this is called technically)

Ideally I wouldn't have to expose the database password & details to server A - is this possible?

 

[Ka0t1x]

If you can make a new user on server B you could have a MySQL user just setup to write to that table.

But I don't think there's a way to do it without the user/pass. What kind of security are we talking? You could possibly include a file above the webdir with the information. (I hope you're using a function to return a DB connection, much easier)

 

[Crusty]

Are you using their IPN system? If so you could just have the incoming POST requests be sent to server B and process the payments there.

 

[suklee]

If you can make a new user on server B you could have a MySQL user just setup to write to that table.

That's a good idea, I didn't know users could be restricted to certain tables (or did you mean database?) I'll do some Googling on this.

But I don't think there's a way to do it without the user/pass. What kind of security are we talking? You could possibly include a file above the webdir with the information. (I hope you're using a function to return a DB connection, much easier)

The existing code I am working with is a bit archaic with inline / hardcoded db connections :\
But hopefully not too difficult to get around.

Are you using their IPN system? If so you could just have the incoming POST requests be sent to server B and process the payments there.

Yep, using IPN, but what we want is for users browsing on server A not to be 'aware' of server B.

 

[Crusty]

That's a good idea, I didn't know users could be restricted to certain tables (or did you mean database?) I'll do some Googling on this.


The existing code I am working with is a bit archaic with inline / hardcoded db connections :\
But hopefully not too difficult to get around.


Yep, using IPN, but what we want is for users browsing on server A not to be 'aware' of server B.

They won't, that's not how IPN works.

The flow should be user goes to 'server A'. Clicks on Pay, gets redirected to Paypal. They checkout and get redirected back to 'server A'. Then at some point in the future Paypal sends you the IPN notification asynchronously. The address of the IPN posts can be completely different from the redirect back to your site after they pay.

 

[suklee]

Hmm, sounds like that could work then..

So the IPN can be sent to 'server B', at which point my code can add a row to its own table?

 

[jvroig]

On website/server A, I need to take a PayPal payment - once it goes through, I want to insert a row of data into a database residing on server B. What's a good way to do this 'remote insertion'? (I'm not sure what this is called technically)

In a very general sense, discounting for the moment using some specific service such as PayPal, there are two ways to go about this.

The straightforward way is setup a user in Server B for mysql that only has privilege in the necessary database and in the necessary table (privileges can be that specific). You can then use those credentials for your needs in Server A. This way, you aren't exposing any "real" username and password for Server B, since you just made it up for the sole purpose of being used in Server A, and it can only ever work for that specific table. This method will work even if Server B is purely a MySQL server (no httpd/php).

Another way is to just pass parameters to Server B, and let Server B contain the php code that adds the row. You will need to accompany the parameters with some sort of verification key, to make sure only valid requests (those that actually came from Server A) will be accepted by Server B (if two-way communication is possible, this is easy to setup). Calling a URL without having to actually redirect the user is easy, and using SSL for the connection is easy enough to do if Server B is already setup for it. This method will only work if Server B also has its own httpd and php installed - or atleast, PHP installed as CLI (but I am yet to try that myself).

I have not worked with PayPal's payment notification system, but if their IPN is similar to some of my previous clients a few years back (mobile content providers that offer pay-to-download cellphone wallpapers/images/video clips), you can just direct the notifications to your Server B, and let Server B work on it without ever bothering Server A at all (naturally, this also assumes Server B has its own httpd/php). I only bothered to spell out the two methods above in case you (or other members that stumble here) do need to have two servers communicate for a similar purpose sometime in the future, and it is for an in-house project that doesn't rely on any third-party system.