Parsing all rows from a SQL Table.

Hello all again,

So i’m trying to parse a complete table to manage player bans (yes i’m remaking gmod banning system).
Here is what i’m using
This code is in the intialization of my ban mod :



	timer.Create("RRP_BanManagerUpdater", 3600, 0, function() 
		local tbl = sql.Query("SELECT * FROM rrp_banmanager")
		for _, v in pairs(tbl) do // Here crashing
			if (v.hours == 0) then
				RRP_BanManager:UnBanPlayer(v.id)
				continue
			end
			if (v.hours != -1) then
				local hr = v.hours - 1
				sql.Query("UPDATE rrp_banmanager SET hours = '" .. hr .. "' WHERE id = '" .. v.id .. "'")
			end
			if (v.hours == 0) then
				RRP_BanManager:UnBanPlayer(v.id)
				continue
			end
		end
	end)


And i get after 1 hour ingame : Timer Failed bad argument #1 to pairs (table expected got nil).

I hope one of you can explain me why the sql.Query(“SELECT * FROM rrp_banmanager”) is returning nil value. The table already exists (i already checked that).

After, i’ve another question : Is there anyway to get the player SteamID when he connects (not spawns). Indead, instead of kicking the banned player when he spawns, i’d like to disconnect him when he intialize the connection to the server (GM:PlayerConnect).

In the hope of a quick answer,
Yuri6037

gameevent.Listen is the earliest you can access the SteamID

This is a very inefficient way to manage bans and will slow your server down as your ban list grows.

Why not insert the current time and the time that they will be unbanned at in to the database and then just work out whether they are banned or not when they connect?

@Blasteh
Because getting the system time is only possible in C++, and my compiler just compile for Windows, my dedicated server runs under Linux…

@kibble
Thanks i just didn’t knew there were a function for that !!
Thank you.

print( os.time() )

Insert that at ban time and then add seconds for ban length, easy to work with.

Thank you, again i didn’t knew there were this function !

Now one problem reminds : how to cancel the gameevent player_connect, so the player get instantly disconnected with ban message ?

I don’t think you can with that event, try this one instead:

Thank you so much !
Everything works perfectly now…

Just the steamID provided by GM:CheckPassword function is invalid ; it’s corresponding to nothing, i’ve searched it on the steam id finder, but no ones corresponds to it, even if i’m connecting. So i think i will ask Garry to fix that…

works fine for me, what does your code look like and what value are you getting for the steamid?



function GM:CheckPassword(steamID, ipAddress, svPassword, clPassword, name)
	if (steamID == "STEAM_0:0:66023671") then //Yuri6037 has connected
		GameGlobal:BroadcastColoredMessage("255, 0, 0", "The owner of the server has connected !")
		GameGlobal:BroadcastColoredMessage("255, 0, 0", "Le créateur de ce serveur s'est connecté !")
		return true, nil
	end
	local isBanned, kckMsg = RRP_BanManager:RunBanCheckForSteamID(steamID)
	if (isBanned) then
		GameGlobal:PrintMessage("Steam " .. steamID .. " is banned !")
		return false, kckMsg
	else
		GameGlobal:PrintMessage("Steam " .. steamID .. " isn't banned !")
		return true, nil
	end
end


I’m getting “76561198092313070” instead of “STEAM_0:0:66023671” when i connect.

That’s SteamID64, you can use this function to make that into a valid SteamID.

[lua]
util.SteamIDFrom64( steamID )
[/lua]

Alright; there are many ways to make a ban system. This is what I do…

On ban, I insert the steamid, reason, expiry… if expiry is 0 it is permanent. otherwise it is os.time( ) + duration.

The query is: SELECT * FROM bans WHERE steamid=SQLStr( _steamid ) AND ( expiry=0 OR expiry>os.time( ) )

So, expired bans can be ignored and are left as “logs”. Only relevant bans are listed in the query…

I check the current steamid, and I also check to see if the user is family sharing … if so, then it checks that steamid too… I also get vac history, 1 or more bans with last one less than 30 days old = restricted access. I also check coderhire bans… If banned for “leak” “chargeback” “scam” “fraud” etc, banned.

I’ll be releasing my system fairly soon. If you need help with yours, feel free to add me on steam.

Other systems actually “unban / remove” the SQL record on unban… I’d recommend only checking on CheckPassword or player_connect either way, never use a timer unless you are managing website bans via cron-job.

IE running: DELETE * FROM bans WHERE expiry<os.time( ) AND expiry!=0; every 15 minutes is cheaper than running a check every time a user connects to the website…

Ow wow ! I’ts interesting ! Can you tell me how you get the VAC ban history of a player… Like that i can block them from accessing the server !

Sure. The basics of it is using http.Fetch on a specific URL. The base url is:

The additive urls are ( without API Key ): https://dl.dropboxusercontent.com/u/26074909/tutoring/_utilities/_steam_api/nonkey_api_list.txt
Cleaned list: https://dl.dropboxusercontent.com/u/26074909/tutoring/_utilities/_steam_api/nonkey_api_list_cleaned.txt

and ( with API key ): https://dl.dropboxusercontent.com/u/26074909/tutoring/_utilities/_steam_api/keyed_api_list.txt
Cleaned list: https://dl.dropboxusercontent.com/u/26074909/tutoring/_utilities/_steam_api/keyed_api_list_cleaned.txt

Those urls get plugged into my steam “api” system ( will be released soon ).

The basics of it is:


//
// Database
//
steam = { //
	// Key Data
	KEY 			= "XXX";
	OWNER_STEAM 	= "STEAM_0:1:4173055";
	OWNER_STEAM64 	= "76561197968611839";
	KEY_DOMAIN 		= "127.0.0.1";
	KEY_OUTPUT 		= "http://steamcommunity.com/dev/apikey";

	// Queries
	output_formats 	= { xml = "xml"; json = "json"; vdf = "vdf"; };
	query_url 		= "http://api.steampowered.com/";
};

KEY_OUTPUT is where you get your API key needed to do some of the queries. query_url is the prefix to the cleaned urls above. non-keyed are queries that can be done without an API Key. the keyed list requires a key. I believe we’re limited to 100,000 queries per 24 hour period, or per day.

The released version will have a lot of simplified functions because there is a lot of repeated code so I removed the repetitions and opted for helpers ( as I normally do ). But the vac ban function ( used in conjunction with the above data ):


//
// Returns VAC Ban History
//
function steam:GetBanHistory( _steam, _callback )
	local _steamid, _steamid64, _steamuid = util.TranslateSteamID( _steam );
	if ( _steamid == "BOT" ) then return false; end
	local _varargs = { self.KEY, _steamid64 };
	local _urlstring = "ISteamUser/GetPlayerBans/v0001/?key=%s&steamids=%s&format=json";
	local _url = string.format( self.query_url .. _urlstring, unpack( _varargs ) );
	http.Fetch( _url, function( _data )
		local _tab = util.JSONToTable( _data );
		local _pdata = _tab.players[ 1 ];
		if ( _callback ) then
			_callback( _pdata.CommunityBanned, _pdata.VACBanned, _pdata.EconomyBan, _pdata.NumberOfVACBans, _pdata.DaysSinceLastBan )
		end
	end );
end

One of the helper functions I use up there: util.TranslateSteamID( _steam ); is available on my Skeletonized Dev Base. It takes any format of SteamID ( 32: STEAM_0:1:4173055, 64… and the new Steam3: [U:x:1234] ) and returns in the same order… 32, 64 and 3 as seen above in the function… https://bitbucket.org/Acecool/acecooldev_base/raw/master/gamemode/shared/core/util_translatesteamid.lua?at=master

One thing to know about VAC bans. They’re a joke; I wouldn’t recommend banning someone off your server just because of a VAC Ban. I’d recommend getting the count, and when the last ban was. If the last ban is 90 days or newer, notify the admins when the user joins just in case something is up. Or if the user has more than 1 ban.

They can’t be taken seriously; my other steam account ( although Steam says this isn’t the case… ) was VAC Banned for binding my left mouse to +attack;wait;wait;+lookdown;wait;wait;-lookdown or something around that. A lot of people don’t remember the VAC Beta days when you could be banned just based on your key binds, and they were permanent back then… scary stuff.

Something you may also want to look up is the family sharing. A lot of people bypass bans by using FamilySharing because it shows a different steamid if I recall correctly. Using the family sharing check, if they are then not only check the steamid they’re playing on, but also the family sharing id…


//
// Lookup SteamID to see whether user is using FamilySharing / Owns the game or not...
//
function steam:IsFamilySharing( _steam, _gameid, _callback )
	local _steamid, _steamid64, _steamuid = util.TranslateSteamID( _steam );

	// Ignore bots
	if ( _steamid == "BOT" ) then return false; end

	local _url = string.format( self.query_url .. self.queries.family_sharing.url, self.KEY, self.output_formats.json, _steamid64, _gameid );

	http.Fetch( _url, function( _data )
		local _tab = util.JSONToTable( _data );
		local _lenderID = _tab.response.lender_steamid;

		// Callback, return false if self-owned, or steamid of lender...
		if ( _callback ) then
			local _isShared = tobool( _lenderID ) && _lenderID || false;
			_callback( _steamid, _steamid64, _isShared, _lenderID );
		end
	end );
end

The entire skeletonized base is available here:
AcecoolDev_Base Skeletonized Dev Base Game-Mode ( Never worry about Include or AddCSLuaFile ever again; comes with New Hooks, Console Commands, Meta-Table Objects, Helper Functions, Extended Functionality, and more! )
https://dl.dropboxusercontent.com/u/26074909/tutoring/___welcome_docs/_welcome_acecooldev_base_gamemode_info.lua.html

It’ll be uploaded there soon; but hopefully that is more than enough to get you started.

Thank you very much…
Indead, i thing VAC bans record will not be implemented as a check… However your note about IsFamilySharing is terribly interesting, much more than getting VAC ban history !

Indead, my server is getting problems with stupid guys from other servers that come on the server, kill everyone (FREEKILL), and then make server lagging, and get banned by admins, but the problem is that they’re comming back all the time even if i’m banning them permanently !

But can your function work even if our steam profile is completely private ?

The Family Sharing query should work regardless of profile privacy setting.