Raised This Month: $51 Target: $400
 12% 

Solved [CS:GO][PAID] Passing data to SQL Database


Post New Thread Reply   
 
Thread Tools Display Modes
Author Message
Lubricant Jam
AlliedModders Donor
Join Date: Oct 2016
Location: United Kingdom
Old 02-25-2019 , 03:36   [CS:GO][PAID] Passing data to SQL Database
Reply With Quote #1

Hello all,

I'm need in someone with SQL database experience within plugins to essentially send data to an SQL database on first join then update the data every join after that.

Quite a simple basis however it will need to pull certain data regarding their steam profile.

Contact me for additional information,
Lubricant Jam.
__________________

Last edited by Lubricant Jam; 02-25-2019 at 16:26.
Lubricant Jam is offline
NomisCZ
AlliedModders Donor
Join Date: Mar 2014
Location: Czech_Republic
Old 02-25-2019 , 04:18   Re: [CS:GO] Passing data to SQL Database
Reply With Quote #2

Hello,
below is an example of INSERT / UPDATE and FETCH db user id on client connect.

PHP Code:
public void OnClientPostAdminCheck(int client)
{
    if(
IsValidClient(client)) {

        
Database_OnClientConnect(client);
    }
}

public 
void Database_OnClientConnect(int client)
{
    if(
g_hDatabase != null) {

        
char szBuffer[1024];
        
char szSteamId[64];
        
char szClientName[128];
        
char szClientNameEscaped[128];

        
GetClientName(clientszClientNamesizeof(szClientName));
        
g_hDatabase.Escape(szClientNameszClientNameEscapedsizeof(szClientNameEscaped));
        
GetClientAuthId(clientAuthId_SteamID64szSteamIdsizeof(szSteamId));

        
Format(szBuffersizeof(szBuffer), "INSERT INTO users (name, steamid, created_at, updated_at) VALUES ('%s', '%s', NOW(), NOW()) ON DUPLICATE KEY UPDATE name = '%s', updated_at = NOW()"szClientNameEscapedszSteamIdszClientNameEscaped);
        
g_hDatabase.Query(Database_OnClientConnectedszBufferGetClientUserId(client));
    }
}

public 
void Database_OnClientConnected(Database dbDBResultSet results, const char[] errorany data)
{
    
int client GetClientOfUserId(data);

    if(
results == null) {

        
LogError("[2] Query failed! %s"error);

    } else {

        if(
IsValidClient(client)) {

            
char szBuffer[256];
            
char szSteamId[64];
            
            
GetClientAuthId(clientAuthId_SteamID64szSteamIdsizeof(szSteamId));

            
Format(szBuffersizeof(szBuffer), "SELECT user.id FROM users user WHERE user.steamid = '%s'"szSteamId);
            
g_hDatabase.Query(Database_OnGetClientUserIdszBufferGetClientUserId(client));
        }
    }
}

public 
void Database_OnGetClientUserId(Database dbDBResultSet results, const char[] errorany data)
{
    
int client GetClientOfUserId(data);

    if(
results == null) {

        
LogError("[3] Query failed! %s"error);

    } else {

        if(
IsValidClient(client)) {

            if(
results.FetchRow()) {

                
char szBuffer[256];

                
g_iUserDbId[client] = results.FetchInt(0);
            }
        }
    }

Table:
PHP Code:
CREATE TABLE IF NOT EXISTS `users`(`idINT UNSIGNED NOT NULL AUTO_INCREMENT ,`nameVARCHAR(255NOT NULL ,`steamidVARCHAR(18NOT NULL ,`created_atTIMESTAMP NULL ,`updated_atTIMESTAMP NULL ,PRIMARY KEY (`id`), UNIQUE(`steamid`)) ENGINE InnoDB;")

created_at and updated_at is good for find active players / delete inactive players 
Full code: https://github.com/ESK0/eTweaker/blo...wc/database.sp (DB/queries is my work ...).
__________________

Last edited by NomisCZ; 02-25-2019 at 04:19.
NomisCZ is offline
Lubricant Jam
AlliedModders Donor
Join Date: Oct 2016
Location: United Kingdom
Old 02-25-2019 , 04:24   Re: [CS:GO] Passing data to SQL Database
Reply With Quote #3

Quote:
Originally Posted by NomisCZ View Post
Hello,
below is an example of INSERT / UPDATE and FETCH db user id on client connect.

PHP Code:
public void OnClientPostAdminCheck(int client)
{
    if(
IsValidClient(client)) {

        
Database_OnClientConnect(client);
    }
}

public 
void Database_OnClientConnect(int client)
{
    if(
g_hDatabase != null) {

        
char szBuffer[1024];
        
char szSteamId[64];
        
char szClientName[128];
        
char szClientNameEscaped[128];

        
GetClientName(clientszClientNamesizeof(szClientName));
        
g_hDatabase.Escape(szClientNameszClientNameEscapedsizeof(szClientNameEscaped));
        
GetClientAuthId(clientAuthId_SteamID64szSteamIdsizeof(szSteamId));

        
Format(szBuffersizeof(szBuffer), "INSERT INTO users (name, steamid, created_at, updated_at) VALUES ('%s', '%s', NOW(), NOW()) ON DUPLICATE KEY UPDATE name = '%s', updated_at = NOW()"szClientNameEscapedszSteamIdszClientNameEscaped);
        
g_hDatabase.Query(Database_OnClientConnectedszBufferGetClientUserId(client));
    }
}

public 
void Database_OnClientConnected(Database dbDBResultSet results, const char[] errorany data)
{
    
int client GetClientOfUserId(data);

    if(
results == null) {

        
LogError("[2] Query failed! %s"error);

    } else {

        if(
IsValidClient(client)) {

            
char szBuffer[256];
            
char szSteamId[64];
            
            
GetClientAuthId(clientAuthId_SteamID64szSteamIdsizeof(szSteamId));

            
Format(szBuffersizeof(szBuffer), "SELECT user.id FROM users user WHERE user.steamid = '%s'"szSteamId);
            
g_hDatabase.Query(Database_OnGetClientUserIdszBufferGetClientUserId(client));
        }
    }
}

public 
void Database_OnGetClientUserId(Database dbDBResultSet results, const char[] errorany data)
{
    
int client GetClientOfUserId(data);

    if(
results == null) {

        
LogError("[3] Query failed! %s"error);

    } else {

        if(
IsValidClient(client)) {

            if(
results.FetchRow()) {

                
char szBuffer[256];

                
g_iUserDbId[client] = results.FetchInt(0);
            }
        }
    }

Table:
PHP Code:
CREATE TABLE IF NOT EXISTS `users`(`idINT UNSIGNED NOT NULL AUTO_INCREMENT ,`nameVARCHAR(255NOT NULL ,`steamidVARCHAR(18NOT NULL ,`created_atTIMESTAMP NULL ,`updated_atTIMESTAMP NULL ,PRIMARY KEY (`id`), UNIQUE(`steamid`)) ENGINE InnoDB;")

created_at and updated_at is good for find active players / delete inactive players 
Full code: https://github.com/ESK0/eTweaker/blo...wc/database.sp (DB/queries is my work ...).
Thanks for this, I will look at this when I can. Do you know if it's possible to pull someones profileurl & profileimg for example;

HTML Code:
https://steamcdn-a.akamaihd.net/steamcommunity/public/images/avatars/af/aff0c5bb68643aea1828cc4eaec039417d10b436_full.jpg
__________________

Last edited by Lubricant Jam; 02-25-2019 at 04:24.
Lubricant Jam is offline
NomisCZ
AlliedModders Donor
Join Date: Mar 2014
Location: Czech_Republic
Old 02-25-2019 , 15:22   Re: [CS:GO] Passing data to SQL Database
Reply With Quote #4

Quote:
Originally Posted by Lubricant Jam View Post
Thanks for this, I will look at this when I can. Do you know if it's possible to pull someones profileurl & profileimg for example;

HTML Code:
https://steamcdn-a.akamaihd.net/steamcommunity/public/images/avatars/af/aff0c5bb68643aea1828cc4eaec039417d10b436_full.jpg
You can fetch user data via Steam API - https://developer.valvesoftware.com/...es_.28v0002.29 (SteamWorks GET request), but I don't recommend it ingame (in plugin - on connect).

If you need these data (avatar, personastate, ...) eg. on the website, you can get it after Steam Login (https://github.com/SmItH197/SteamAuthentication, https://github.com/invisnik/laravel-steam-auth) or without login
- Steam API (https://github.com/DaMitchell/steam-api-php, https://github.com/syntaxerrors/Steam).

So ingame (in plugin) only create user - steamid, name, created_at, updated_at and on the website after login fetch another information (avatar, personastate, ...).


Here is an example of SteamWorks GET request to Steam API (steamid, personaname, personastate, avatar):

PHP Code:

// https://steamcommunity.com/dev/apikey
#define STEAM_API_KEY "xxxxxxxxxxxx"
public void OnClientPostAdminCheck(int client)
{
    if(
IsValidClient(client)) {

        
Api_OnClientConnect(client);
    }
}

public 
void Api_OnClientConnect(int client)
{
    
char sURLAddress[1024];
    
char szSteamId[64];
    
    
GetClientAuthId(clientAuthId_SteamID64szSteamIdsizeof(szSteamId));

    
Format(sURLAddresssizeof(sURLAddress), "http://api.steampowered.com/ISteamUser/GetPlayerSummaries/v0002/?key=%s&steamids=%s&format=vdf"STEAM_API_KEYszSteamId);

    
Handle hHTTPRequest SteamWorks_CreateHTTPRequest(k_EHTTPMethodGETsURLAddress);
    
bool bNetwork SteamWorks_SetHTTPRequestNetworkActivityTimeout(hHTTPRequest10);
    
bool bCallback SteamWorks_SetHTTPCallbacks(hHTTPRequestApi_OnClientCallback);
    
    if (
bNetwork == false || bHeader == false || bCallback == false) {

        
delete hHTTPRequest;
        return;
    }

    
bool bRequest SteamWorks_SendHTTPRequest(hHTTPRequest);
    
    if (
bRequest == false) {

        
delete hHTTPRequest;
        return;
    }
    
    
SteamWorks_PrioritizeHTTPRequest(hHTTPRequest);
}

public 
int Api_OnClientCallback(Handle hRequestbool bFailurebool bRequestSuccessfulEHTTPStatusCode eStatusCodeany data)
{
    if (
bFailure || !bRequestSuccessful) {

        
delete hRequest;
        return;
    }
    
    
int iBodySize;
    
    if (!
SteamWorks_GetHTTPResponseBodySize(hRequestiBodySize)) {

        
delete hRequest;
        return;
    }

    
char[] szBody = new char[iBodySize 1];
    
    if (!
SteamWorks_GetHTTPResponseBodyData(hRequestszBodyiBodySize)) {

        
delete hRequest;
        return;
    }

    
Api_FetchUserData(szBody);
}


public 
void Api_FetchUserData(const char[] responseBody)
{
    
KeyValues hKeyValues = new KeyValues("response");
    
    if (
hKeyValues.ImportFromString(responseBody)) {

        if (
hKeyValues.JumpToKey("players")) {
        
            if (
hKeyValues.GotoFirstSubKey()) {
            
                
char sPersonastate[2];
                
char sPersonaName[128];
                
char sSteamId64[18];
                
char sAvatarFull[256];
                
                
hKeyValues.GetString("steamid"sSteamId64sizeof(sSteamId64));
                
hKeyValues.GetString("personaname"sPersonaNamesizeof(sPersonaName));
                
hKeyValues.GetString("avatarfull"sAvatarFullsizeof(sAvatarFull));
                
hKeyValues.GetString("personastate"sPersonastatesizeof(sPersonastate));
                
                
Database_OnClientConnect(sSteamId64sPersonaNamesPersonastatesAvatarFull);
            }
        }
    }

    
delete hKeyValues;
}

public 
void Database_OnClientConnect(const char[] sSteamId64, const char[] sPersonaName, const char[] sPersonastate, const char[] sAvatarFull)
{
    if(
g_hDatabase != null) {

        
char szBuffer[1024];
        
char szClientNameEscaped[128];

        
g_hDatabase.Escape(sPersonaNameszClientNameEscapedsizeof(szClientNameEscaped));

        
Format(szBuffersizeof(szBuffer), "INSERT INTO users (name, steamid, persona_state, avatar, created_at, updated_at) VALUES ('%s', '%s', '%s', '%s', NOW(), NOW()) ON DUPLICATE KEY UPDATE name = '%s', avatar = '%s' updated_at = NOW()"szClientNameEscapedsSteamId64sPersonastatesAvatarFullszClientNameEscapedsAvatarFull);
        
g_hDatabase.Query(Database_OnClientConnectedszBuffer);
    }
}

... 
the rest of the code 
Steam API response (VDF format):

PHP Code:
"response"
{
    
"players"
    
{
        
"0"
        
{
            
"steamid"    "76561198364662582"
            "communityvisibilitystate"    "3"
            "profilestate"    "1"
            "personaname"    "NomisCZ (-N-)"
            "lastlogoff"    "1551118716"
            "commentpermission"    "1"
            "profileurl"    "https://steamcommunity.com/id/olympic-nomis/"
            "avatar"    "https://steamcdn-a.akamaihd.net/steamcommunity/public/images/avatars/fb/fbaed0beb2e56a2d30abf2e0b2aeaeb4db513dbd.jpg"
            "avatarmedium"    "https://steamcdn-a.akamaihd.net/steamcommunity/public/images/avatars/fb/fbaed0beb2e56a2d30abf2e0b2aeaeb4db513dbd_medium.jpg"
            "avatarfull"    "https://steamcdn-a.akamaihd.net/steamcommunity/public/images/avatars/fb/fbaed0beb2e56a2d30abf2e0b2aeaeb4db513dbd_full.jpg"
            "personastate"    "1"
            "primaryclanid"    "103582791430395180"
            "timecreated"    "1486318143"
            "personastateflags"    "0"
            "loccountrycode"    "CZ"
        
}
    }


PHP (Laravel) Steam login & user update example (Controller):
PHP Code:
<?php

namespace App\Http\Controllers\Auth;

use 
App\Http\Controllers\Controller;
use 
Illuminate\Foundation\Auth\AuthenticatesUsers;
use 
Illuminate\Http\Request;

use 
Invisnik\LaravelSteamAuth\SteamAuth;
use 
App\User;

class 
SteamController extends Controller
{
    
/*
    |--------------------------------------------------------------------------
    | Login Controller
    |--------------------------------------------------------------------------
    |
    | This controller handles authenticating users for the application and
    | redirecting them to your home screen. The controller uses a trait
    | to conveniently provide its functionality to your applications.
    |
    */

    
use AuthenticatesUsers;

    
/**
     * Where to redirect users after login.
     *
     * @var string
     */
    
protected $redirectTo '/home';

    
/**
     * @var object
     */
    
private $steam;
    private 
$user;

    
/**
     * SteamController constructor.
     * @param SteamAuth $steam
     * @param User $user
     */
    
public function __construct(SteamAuth $steamUser $user)
    {
        
$this->middleware('guest')->except('logout');
        
$this->steam $steam;
        
$this->user $user;
    }

    public function 
login()
    {
        
// Steam login success
        
if ($this->steam->validate()) {
        
            
// Get user data
            
$steamUserData $this->steam->getUserInfo();

            if (
$steamUserData) {

                
// Get user from DB
                
$user $this->user->getBySteam($steamUserData->steamID64);


                if (!
$user) {
                    return 
redirect()->route('auth.login')->with(['message_status' => 'info''flash_message' => 'auth.steam.failed']);
                }
                
                
// Update user
                
$user->update([
                    
'name' => $steamUserData->personaname,
                    
'avatar'   => $steamUserData->avatarfull,
                ]);

                
// Login user (session)
                
auth()->login($userfalse);
                return 
redirect()->route('index');
            }
        }

        return 
$this->steam->redirect();
    }
}
__________________

Last edited by NomisCZ; 02-25-2019 at 16:07.
NomisCZ is offline
Lubricant Jam
AlliedModders Donor
Join Date: Oct 2016
Location: United Kingdom
Old 02-25-2019 , 16:25   Re: [CS:GO][PAID] Passing data to SQL Database
Reply With Quote #5

This has now been sorted, thank you for your information, greatly appreciated .
__________________
Lubricant Jam is offline
NomisCZ
AlliedModders Donor
Join Date: Mar 2014
Location: Czech_Republic
Old 02-25-2019 , 16:44   Re: [CS:GO][PAID] Passing data to SQL Database
Reply With Quote #6

Quote:
Originally Posted by Lubricant Jam View Post
This has now been sorted, thank you for your information, greatly appreciated .
:-) I gladly helped.
__________________

Last edited by NomisCZ; 02-25-2019 at 16:44.
NomisCZ is offline
DarkDeviL
SourceMod Moderator
Join Date: Apr 2012
Old 02-26-2019 , 00:10   Re: [CS:GO][PAID] Passing data to SQL Database
Reply With Quote #7

Quote:
Originally Posted by NomisCZ View Post
but I don't recommend it
I wouldn't recommend neither using nor spreading code that doesn't conform 100% with the SourceMod API:

Quote:
Originally Posted by NomisCZ View Post
Here is an example of SteamWorks GET request to Steam API (steamid, personaname, personastate, avatar):

PHP Code:
// [...]    
    
GetClientAuthId(clientAuthId_SteamID64szSteamIdsizeof(szSteamId));
// [...] 
PHP Code:
// [...]
    
bool bNetwork SteamWorks_SetHTTPRequestNetworkActivityTimeout(hHTTPRequest10);
    
bool bCallback SteamWorks_SetHTTPCallbacks(hHTTPRequestApi_OnClientCallback);
    
    if (
bNetwork == false || bHeader == false || bCallback == false) {
// [...] 
^

Seems like you are doing boolean checks of the SteamWorks functions?

Why no proper boolean check of GetClientAuthId?

Both the GetClientAuthId API (since SourceMod 1.6.0) as well as the older GetClientAuthString API (since SourceMod 1.0.0.1946) says:

Quote:
Notes:
Retrieves a client's authentication string (SteamID).

Return:
True on success, false otherwise.
As you can see from your own code, it takes so little do to things the right way (e.g. proper boolean checks of boolean returning functions), so some advice for you future could would be to do things a little better regarding GetClientAuthId.

PHP Code:
if (GetClientAuthId(clientAuthId_SteamID64szSteamIdsizeof(szSteamId))) {
    
// YAY, We have a good Steam ID
} else {
    
// Some (optional else) handler if we do not have a proper Steam ID, - depending on use case, maybe try again in 60 seconds?

You can obviously argue about OnClientPostAdminCheck and that a valid Steam ID *should* be here at this stage, well, it should (most likely) not fail after this point.

But that's still not a good excuse for not following the SourceMod API documentations, and especially documentation that is more than 5 years old!

Doing the proper checks, and consistently, no matter which function it is used within, will the best for all parties.

Quote:
Originally Posted by NomisCZ View Post
:-) I gladly helped.
Just my two cents of constructive criticism..
__________________
Mostly known as "DarkDeviL".

Dropbox FastDL: Public folder will no longer work after March 15, 2017!
For more info, see the [SRCDS Thread], or the [HLDS Thread].
DarkDeviL is offline
Reply



Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -4. The time now is 21:13.


Powered by vBulletin®
Copyright ©2000 - 2024, vBulletin Solutions, Inc.
Theme made by Freecode