General > Developers Corner
Working MySQL User Authentication with Global Ban support
DJWolf:
I have the script loaded, but when someone connects, it says
--- Code: ---No Results Found ... Creating Entry
Lua Error: ERROR: Hook 'SQL_Auth' Failed: [@lua\autorun\ugroup.lua:93] Invalid connection!
Lua Error: Removing Hook 'SQL_Auth'
--- End code ---
this is what my edited version looks like
--- Code: -----------------------
-- Config --
--------------------
local host = ""
local username = ""
local password = ""
local database = ""
local port = 3306
local table = "ugroup"
local persistent = false -- Use a persistent MySQL connection?
require( "mysql" )
local db
function DoQuery( query, type )
local result, isok, err = mysql.query( db, query, type or mysql.QUERY_NUMERIC )
if not isok and err == "" then isok = true end -- False positive
if not isok then
error( tostring( err ), 2 )
return nil
end
if result then
-- print( query ) -- For debug
-- PrintTable( result )
end
return result
end
function Connect()
if db then return db end -- Still connected
db, err = mysql.connect( host, username, password, database, port )
if db == 0 then
db = nil
error( tostring( err ), 1 )
return
end
return db
end
function Disconnect( force )
if not db then return end -- Already disconnected
if persistent and not force then return end -- Don't disconnect, persistent
local succ, err = mysql.disconnect( db )
if not succ then
error( tostring( err ), 2 )
end
db = nil
end
hook.Add( "ShutDown", "SQL", function() Disconnect( true ) end ) -- Force closed on shutdown.
function Escape( str )
if not db then
Msg( "Not connected to DB.\n" )
return
end
if not str then return end
local esc, err = mysql.escape( db, str )
if not esc then
error( tostring( err ), 2 )
return nil
end
-- print( "esc=" .. esc ) -- For debug
return esc
end
-- Because we use this a lot
function Format( str )
if not str then return "NULL" end
return string.format( "%q", str )
end
function SQL_Auth( ply, sid, uid )
Connect()
local results = DoQuery( "SELECT * FROM " .. table .. " WHERE steamid = '" .. sid .. "'" )
Disconnect()
if not results[1] then
ServerLog("No Results Found ... Creating Entry")
--print("INSERT INTO " .. table .. " ( steamid, pname, pgroup, banned ) VALUES( " ..Format( Escape( sid ) ) .. ", " ..Format( Escape( ply:Nick() ) ) .. ", " ..Format( Escape( "user" ) ) .. ", " ..Format( Escape( "FALSE" ) ).. " )")
local result = DoQuery( "INSERT INTO " .. table .. " ( steamid, pname, pgroup, banned ) VALUES( " ..Format( Escape( sid ) ) .. ", " ..Format( Escape( ply:Nick() ) ) .. ", " ..Format( Escape( "user" ) ) .. ", " ..Format( Escape( "FALSE" ) ).. " )" )
else
ServerLog("Results Found ... Loading Results")
ply.steamid = results[1][1]
ply.name = results[1][2]
ply.group = results[1][3]
ply.banned = results[1][4]
ServerLog("Name: " .. ply.name)
ServerLog("SteamID: " .. ply.steamid)
ServerLog("Group: " .. ply.group)
ServerLog("Banned?: " .. ply.banned)
if ply.name != ply:Nick() then
ServerLog( "Updating name for player: " ..ply:Nick() )
--print("UPDATE " .. table .. " SET pname="..Format( Escape( ply:Nick() ) ) .. " WHERE sid=" ..Format( Escape( sid ) ) )
local result3 = DoQuery( "UPDATE " .. table .. " SET pname="..Format( Escape( ply:Nick() ) ) .. " WHERE steamid=" ..Format( Escape( sid ) ) )
end
ServerLog( "AUTHING PLAYER: " ..ply:Nick().. " in group (" ..ply.group..")." )
ULib.ucl.addUser( ply:SteamID(), _, _, ply.group )
if ply.banned == "TRUE" then
ULib.kick( ply, "BANNED! Visit our website at WEBSITE HERE to dispute this ban." )
end
end
end
hook.Add( "PlayerAuthed", "SQL_Auth", SQL_Auth )
function SQL_ChangeGroup( ply, cmd, args )
local sid = args[1]
local group = string.lower(args[2])
if !IsValid( ply ) or ply:IsAdmin() then
if #args != 2 then
if ply then
ULib.tsay(ply, "Not enough arguements!", true)
else
ServerLog("Not enough arguements!")
end
end
Connect()
local results = DoQuery( "SELECT * FROM " .. table .. " WHERE steamid = '" .. sid .. "'" )
Disconnect()
if not results[1] then
Connect()
local result = DoQuery( "INSERT INTO " .. table .. " ( steamid, pname, pgroup, banned ) VALUES( " ..Format( Escape( sid ) ) .. ", " ..Format( Escape( "SET BY CONSOLE" ) ) .. ", " ..Format( Escape( group ) ) .. ", " ..Format( Escape( "FALSE" ) ).. " )" )
Disconnect()
if ply then
ULib.tsay(ply, "created player: (" ..sid.. ") with group (" ..group.. ")", true)
ServerLog("created player: (" ..sid.. ") with group (" ..group.. ")")
else
ServerLog("created player: (" ..sid.. ") with group (" ..group.. ")")
end
else
Connect()
--print( "UPDATE " .. table .. " SET group="..Format( Escape( group ) ) .. " WHERE steamid=" ..Format( Escape( sid ) ) )
local result = DoQuery( "UPDATE " .. table .. " SET pgroup="..Format( Escape( group ) ) .. " WHERE steamid=" ..Format( Escape( sid ) ) )
Disconnect()
if ply then
ULib.tsay(ply, "set player: (" ..sid.. ") to group (" ..group.. ")", true)
ServerLog("set player: (" ..sid.. ") to group (" ..group.. ")")
else
ServerLog("set player: (" ..sid.. ") to group (" ..group.. ")")
end
end
ULib.ucl.addUser( sid, _, _, group )
else
ULib.tsay(ply, "This command is reserved for administrators only!", true)
end
end
concommand.Add( "setgroup", SQL_ChangeGroup)
--- End code ---
i took out the ban and unban part mostly because i don't need it since i have uban running
Mathew75:
Is there maybe a SQL for the tables? I'm not such a good MySQL guy.
Megiddo:
Mathew, do you have PHPMySQL? That's the easiest way to setup tables by hand.
Digital Spit:
I know I'm bumping an old thread but for future users I would just like to say you can host a freemysql database here:http://www.freemysql.net/
I don't know how long they will have signing up open to the public.
It allows remote access so this is perfect for anyone who is wanting to use this mod.
You do get phpmyadmin as well
This is what I use for my servers!
Also the owner of the website is very good at responding to your emails or your support tickets!
havejack:
sorry to bump this old thread, but could someone update the code to work with this
it would be really hand if someone could Because the module he trys to use no longer has a download link
Navigation
[0] Message Index
[*] Previous page
Go to full version