Using SMF's custom action to connect with external database

[e_xrt123]

Hi,
I'm doing a custom action in my SMF  forum to let my users check their stats from the game directly from the forum. I have some pretty good programming experience in languages such as C++ etc. and I also work with MySQL everyday so that's not a problem. But I have never liked web stuff, especially styles I don't think I'll have any serious problems with doing all of this,  but what I ask for is how to make it secure, what is the best way to connect to the database? I guess connecting to the DB each time my login action is executed isn't a good idea (user's nickname and password in the game which I need to check with a mysql query and select their data if it's correct).
I also don't want to let my users "spam" with the MySQL queries/connections since the database is already used in the game, is there a way I could implement some anti-spam mechanism of some kind to prevent this? I guess a captcha would be good enough but I wan't to hear your opinions on this. I will also restrict access to the action for logged-in users only to prevent random guests from spamming.

Returning to the database connection, how should I do this to avoid unneccessary connections? My action will have few other sub-actions that will send queries to the database later on (like changing your in-game password etc), i think the best way would be to connect only once "per session" or something like this, but I have no idea how this should be done in PHP for many different users simultaneously.

And also, how can I "store" the session of my action login so that the user can switch between sub-actions and remain "logged in"? Which variables should I use or whatever should be used for this. I know I could use some custom code to use sessions or cookies (or sessions AND cookies), but is there any way I could use SMF to do this for me easily? I don't need to keep the user logged in for weeks, not even days, just to keep him logged in until he stops browsing.

Thanks in advance!

[Arantor]

What kind of game? Web based? Using some kind of sessions?

Is the forum's database server the same as the game's database server? What kind of actions are you trying to expose to your users? How many simultaneous lookups? (This affects the type of connection but in general the kind of session reuse you're hinting at is usually a bad idea on MySQL connections)

[e_xrt123]

It is a completely stand-alone multiplayer game where players create their accounts etc. The database is located on a completely different server, since it isn't hosted in the same place my forum is.
What I want to expose to the players is the functionality to login to their "in-game" account via SMF to check their stats without being in the game, change some of their account details (nickname, password etc) and probably also handle some micro-payments so they can buy in-game currency for their account (but this is exposed via an API from my game server hosting and all I need to do is just implement their code and make my custom style for it)
I'm not familiar with "optimising" things in PHP, normally on my game server the database connection is done only once on server startup, because the server handles everything for every connected player. As in PHP most of the time I see people just initiate a new connection each time they want to do something with the database, or they make the connection globally in the php file and then use it's handle. I think this is probably the right way - create connection after the user clicks the login button and store the variable globally, but I'm not 100% sure for that.
As for the number of simultaneous lookups/logins this of course shouldn't be limited, but if you are talking about a predictible usage I don't think there will be more than max 20-50 connections at a time. I may also later create another action basing on this for administration, like checking the ban lists, banning players without being in the game etc.

[Arantor]

If you're looking at the 'making a new connection every time' as a metric, please don't. Every individual page load in PHP makes a new connection and closes it at end of page, this is normal and is actually desirable in most scenarios, because the alternative, pooled persistent connections, have a lot of ways to break and don't actually save you much in practice, your problem is more the TCP roundtrip and overhead.

Honestly, for your use case I'd actually be inclined to provide an API and call that - and do things like caching on the API's side rather than trying to optimise it this way; in reality PHP does not necessarily operate the way you think it might. In SMF, for example, the session is managed by each separate page request providing a token (in the form of a cookie) to the server. The token identifies a user id but all details for that user are looked up upon the page's load to get the current data, it isn't kept in session because it could very easily get stale if done so: consider if an admin renamed a user, if the details persisted in session, the user wouldn't see the new name.

The reason I ask about simultaneous lookups isn't to limit it but to get an idea on whether persistent connections might possibly be viable for your use case, but it really isn't with the numbers you're talking - caching and rate limiting on an API for statistics would be far preferable - and for the other things (like changing name), you'd want to expose that via an API in the first place.

[e_xrt123]

To create an API for this I could only do it via sending informations through sockets and own networking code because the game server is written in C++ (which isn't a problem for me because I have some knowledge in this area) but I think this isn't the best idea since it's fairly more complicated. I'm ok with connecting to the database on each page load or obtaining data every time the page is loaded, the question about sessions was just to ensure the user stays "logged in" so when he goes into some sub-action like "change nickname" the code would still know his UID in the database. I'd be more satisfied if I could use some parts of SMF to do this, but if not then I'll just go for sessions (and maybe cookies depending on how it's gonna work out).
Log-in spam would be prevented by a captcha so that's already figured out, mysql connections too. I know direct mysql connections to an external database may not be the best idea, but probably the simplest to implement and I have seen many people do this already for other forum engines, even game servers with thousands of active players so I guess this won't be any big deal for me.

Back to the session storing, any way to do this through SMF internal "mechanisms" or should I better go with custom code?