Working MySQL User Authentication with Global Ban support

[MrPresident]

First off.. this is provided AS IS... It should be fairly straight forward for anyone with any knowledge in these things. I MIGHT provide some support if anyone has any questions, but this is not a release and none should be expected.
Second... this is not a release. It's a little sloppy and was coded to suite MY needs, I just thought I'd share it with you guys since a LOT of people have been asking for it.


This requires the MySQL module found HERE

I swiped some of the connection code from UBan. I didn't think Megiddo would mind, especially since it's for personal use. So that being said... thanks to him for the code to connect and query the database.


The database needs to be set up with 4 columns. (steamid, pname, pgroup, banned)

steamid = Player's SteamID
pname = Last known name the player connected with.
pgroup = their ULib user group
banned = boolean value. If the player is banned globally. This works a little different than UBan as it does not add the player to the ban table. It simply kicks them on authentication. kind of a hacky way, but I wanted to display a custom message to banned users telling them to visit our website to appeal, which as far as I know, can't be done with regular banning protocol.

Please make sure you edit the code accordingly. (I.E: remove/change the ban message where it points them to MY website. The concommands are all g4p_ you might want to change that if you want to.)


Anyways.. here is the code. It works... I'm running it on 2 servers right now with plans to put it on at least 3 more, so I know it works. If you can't get it working and are not retarded, reply and I'll try and help you.

Code: Lua

1.  
2. --------------------
3. --     Config     --
4. --------------------
5. local host = "127.0.0.1"
6. local username = "root"
7. local password = ""
8. local database = "gmodtest"
9. local port = 3306
10. local table = "test"
11.  
12. local persistent = true -- Use a persistent MySQL connection?
13.  
14. require( "mysql" )
15. local db
16.  
17.  
18. function DoQuery( query, type )
19.         local result, isok, err = mysql.query( db, query, type or mysql.QUERY_NUMERIC )
20.  
21.         if not isok and err == "" then isok = true end -- False positive
22.  
23.         if not isok then
24.                 error( tostring( err ), 2 )
25.                 return nil
26.         end
27.  
28.         if result then
29.                 -- print( query ) -- For debug
30.                 -- PrintTable( result )
31.         end
32.  
33.         return result
34. end
35.  
36. function Connect()
37.         if db then return db end -- Still connected
38.  
39.         db, err = mysql.connect( host, username, password, database, port )
40.         if db == 0 then
41.                 db = nil
42.                 error( tostring( err ), 1 )
43.                 return
44.         end
45.  
46.         return db
47. end
48.  
49. function Disconnect( force )
50.         if not db then return end -- Already disconnected
51.         if persistent and not force then return end -- Don't disconnect, persistent
52.  
53.         local succ, err = mysql.disconnect( db )
54.         if not succ then
55.                 error( tostring( err ), 2 )
56.         end
57.        
58.         db = nil
59. end
60. hook.Add( "ShutDown", "G4P_SQL", function() Disconnect( true ) end ) -- Force closed on shutdown.
61.  
62. function Escape( str )
63.         if not db then
64.                 Msg( "Not connected to DB.\n" )
65.                 return
66.         end
67.        
68.         if not str then return end
69.  
70.         local esc, err = mysql.escape( db, str )
71.         if not esc then
72.                 error( tostring( err ), 2 )
73.                 return nil
74.         end
75.  
76.         -- print( "esc=" .. esc ) -- For debug
77.         return esc
78. end
79.  
80. -- Because we use this a lot
81. function Format( str )
82.         if not str then return "NULL" end
83.         return string.format( "%q", str )
84. end
85.  
86. function G4PSQL_Auth( ply, sid, uid )
87.         Connect()
88.         local results = DoQuery( "SELECT * FROM " .. table .. " WHERE steamid = '" .. sid .. "'" )
89.         Disconnect()
90.        
91.         if not results[1] then
92.                 ServerLog("No Results Found ... Creating Entry")
93.                 --print("INSERT INTO " .. table .. " ( steamid, pname, pgroup, banned ) VALUES( " ..Format( Escape( sid ) ) .. ", " ..Format( Escape( ply:Nick() ) ) .. ", " ..Format( Escape( "user" ) ) .. ", " ..Format( Escape( "FALSE" ) ).. " )")
94.                 local result = DoQuery( "INSERT INTO " .. table .. " ( steamid, pname, pgroup, banned ) VALUES( " ..Format( Escape( sid ) ) .. ", " ..Format( Escape( ply:Nick() ) ) .. ", " ..Format( Escape( "user" ) ) .. ", " ..Format( Escape( "FALSE" ) ).. " )" )
95.         else
96.                 ServerLog("Results Found ... Loading Results")
97.                 ply.steamid = results[1][1]
98.                 ply.name = results[1][2]
99.                 ply.group = results[1][3]
100.                 ply.banned = results[1][4]
101.                 ServerLog("Name: " .. ply.name)
102.                 ServerLog("SteamID: " .. ply.steamid)
103.                 ServerLog("Group: " .. ply.group)
104.                 ServerLog("Banned?: " .. ply.banned)
105.                
106.                 if ply.name != ply:Nick() then
107.                
108.                         ServerLog( "Updating name for player: " ..ply:Nick() )
109.                         --print("UPDATE " .. table .. " SET pname="..Format( Escape( ply:Nick() ) ) .. " WHERE sid=" ..Format( Escape( sid ) ) )
110.                         local result3 = DoQuery( "UPDATE " .. table .. " SET pname="..Format( Escape( ply:Nick() ) ) .. " WHERE steamid=" ..Format( Escape( sid ) ) )
111.                        
112.                 end
113.                
114.                 ServerLog( "AUTHING PLAYER: " ..ply:Nick().. " in group (" ..ply.group..")." )
115.                 ULib.ucl.addUser( ply:SteamID(), _, _, ply.group )
116.  
117.         if ply.banned == "TRUE" then
118.                 ULib.kick( ply, "BANNED! Visit our website at WEBSITE HERE to dispute this ban." )
119.         end
120.                
121.         end
122.        
123. end
124. hook.Add( "PlayerAuthed", "G4PSQL_Auth", G4PSQL_Auth )
125.  
126.  
127. function G4PSQL_ChangeGroup( ply, cmd, args )
128.         local sid = args[1]
129.         local group = string.lower(args[2])
130.        
131.        
132.         if !IsValid( ply ) or ply:IsAdmin() then
133.  
134.                
135.                 if #args != 2 then
136.                         if ply then
137.                                 ULib.tsay(ply, "Not enough arguements!", true)
138.                         else
139.                                 ServerLog("Not enough arguements!")
140.                         end
141.                 end
142.        
143.                 Connect()
144.                 local results = DoQuery( "SELECT * FROM " .. table .. " WHERE steamid = '" .. sid .. "'" )
145.                 Disconnect()
146.                
147.                 if not results[1] then
148.                         Connect()
149.                         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" ) ).. " )" )
150.                         Disconnect()
151.                         if ply then
152.                                 ULib.tsay(ply, "created player: (" ..sid.. ") with group (" ..group.. ")", true)
153.                                 ServerLog("created player: (" ..sid.. ") with group (" ..group.. ")")
154.                         else
155.                                 ServerLog("created player: (" ..sid.. ") with group (" ..group.. ")")
156.                         end
157.                 else
158.                         Connect()
159.                         --print( "UPDATE " .. table .. " SET group="..Format( Escape( group ) ) .. " WHERE steamid=" ..Format( Escape( sid ) ) )
160.                         local result = DoQuery( "UPDATE " .. table .. " SET pgroup="..Format( Escape( group ) ) .. " WHERE steamid=" ..Format( Escape( sid ) ) )
161.                         Disconnect()
162.                         if ply then
163.                                 ULib.tsay(ply, "set player: (" ..sid.. ") to group (" ..group.. ")", true)
164.                                 ServerLog("set player: (" ..sid.. ") to group (" ..group.. ")")
165.                         else
166.                                 ServerLog("set player: (" ..sid.. ") to group (" ..group.. ")")
167.                         end
168.                 end
169.                 ULib.ucl.addUser( sid, _, _, group )
170.                
171.         else
172.                 ULib.tsay(ply, "This command is reserved for administrators only!", true)
173.         end
174. end
175. concommand.Add( "g4p_setgroup", G4PSQL_ChangeGroup)
176.  
177. function G4PSQL_Ban( ply, cmd, args )
178.         local sid = args[1]
179.        
180.         if !IsValid( ply ) or ply:IsAdmin() or ply:IsUserGroup("moderator") then
181.  
182.                
183.                 if #args != 1 then
184.                         if ply then
185.                                 ULib.tsay(ply, "Not enough arguements!", true)
186.                         else
187.                                 ServerLog("Not enough arguements!")
188.                         end
189.                 end
190.        
191.                 Connect()
192.                 local results = DoQuery( "SELECT * FROM " .. table .. " WHERE steamid = '" .. sid .. "'" )
193.                 Disconnect()
194.                
195.                 if not results[1] then
196.                         if ply then
197.                                 ULib.tsay(ply, "Player with SteamID (" ..sid..") does not exist!", true)
198.                                 ServerLog("Player with SteamID (" ..sid..") does not exist!")
199.                         else
200.                                 ServerLog("Player with SteamID (" ..sid..") does not exist!")
201.                         end
202.                 else
203.                         Connect()
204.                         local result = DoQuery( "UPDATE " .. table .. " SET banned="..Format( Escape( "TRUE" ) ) .. " WHERE steamid=" ..Format( Escape( sid ) ) )
205.                         Disconnect()
206.                         if ply then
207.                                 ULib.tsay(ply, "Banned player with SteamID: (" ..sid.. ")", true)
208.                                 ServerLog("Banned player with SteamID: (" ..sid.. ")")
209.                         else
210.                                 ServerLog("Banned player with SteamID: (" ..sid.. ")")
211.                         end
212.                         ULib.tsay(_, "Player with SteamID (" ..sid.. ") was globally banned!", true)
213.                         for _, v in pairs( player.GetAll() ) do
214.                                 if v:SteamID() == sid then
215.                                         ULib.kick( v, "BANNED! Visit our website at WEBSITE HERE to dispute this ban." )
216.                                 end
217.                         end
218.                 end
219.                
220.                
221.         else
222.                 ULib.tsay(ply, "This command is reserved for administrators only!", true)
223.         end
224. end
225. concommand.Add( "g4p_gban", G4PSQL_Ban)
226.  
227. function G4PSQL_UnBan( ply, cmd, args )
228.         local sid = args[1]
229.        
230.         if !IsValid( ply ) or ply:IsAdmin() or ply:IsUserGroup("moderator") then
231.  
232.                
233.                 if #args != 1 then
234.                         if ply then
235.                                 ULib.tsay(ply, "Not enough arguements!", true)
236.                         else
237.                                 ServerLog("Not enough arguements!")
238.                         end
239.                 end
240.        
241.                 Connect()
242.                 local results = DoQuery( "SELECT * FROM " .. table .. " WHERE steamid = '" .. sid .. "'" )
243.                 Disconnect()
244.                
245.                 if not results[1] then
246.                         if ply then
247.                                 ULib.tsay(ply, "Player with SteamID (" ..sid..") does not exist!", true)
248.                                 ServerLog("Player with SteamID (" ..sid..") does not exist!")
249.                         else
250.                                 ServerLog("Player with SteamID (" ..sid..") does not exist!")
251.                         end
252.                 else
253.                         Connect()
254.                         local result = DoQuery( "UPDATE " .. table .. " SET banned="..Format( Escape( "FALSE" ) ) .. " WHERE steamid=" ..Format( Escape( sid ) ) )
255.                         Disconnect()
256.                         if ply then
257.                                 ULib.tsay(ply, "Unbanned player with SteamID: (" ..sid.. ")", true)
258.                                 ServerLog("Unbanned player with SteamID: (" ..sid.. ")")
259.                         else
260.                                 ServerLog("Unbanned player with SteamID: (" ..sid.. ")")
261.                         end
262.                         ULib.tsay(_, "Player with SteamID (" ..sid.. ") was globally unbanned!", true)
263.                 end
264.                
265.                
266.         else
267.                 ULib.tsay(ply, "This command is reserved for administrators only!", true)
268.         end
269. end
270. concommand.Add( "g4p_gunban", G4PSQL_UnBan)
271.  

Create a lua file in garrysmod/lua/autorun and past the above code into it. Happy Administrating!

edit: Fixed something in the code.
edit - JamminR; Corrected URL of SQL module to remove extra http (apparently, SMF doesn't need quotes around the url bb tagged address.)

[JamminR]

Thanks for sharing. Sure many will find it useful.

[Megiddo]

Awesome work.

[gameguysz]

Its not working for me.... :/

I joined the server and nothing happens...

Nothing gets inserted into the database

[Megiddo]

#1, did you remember to CREATE the database? You must do this yourself.

#2, if you did create the table, you probably have a mod blocking PlayerAuthed. An old revision of DarkRP, perhaps?

[gameguysz]

i did create the table and for the second part im not really sure.. I used the svn download so its up to date but how do i fix it?

[Megiddo]

Are you getting any errors in server console?

[JamminR]

It also requires the sql module MrPresident has in his original post. Make sure you have it.
Make sure SVN ULib and SVN ULX work for you non-SQL, then try tinkering by adding this.
This still relies on ULib to be working properly.
MrP shows what must be in the table for it to work.

If that's still not working, MrP has debug printout code in it commented out.
Several lines in the code have "--" in front of them.
Those are debug lines. If you remove those --, you'll see what's going on in the script.

Also... never say, ever say, "it doesn't work"
Unless the developer originally says "this code doesn't work, it's just an idea or a basis for someone else to build off of", it worked for the person who posted the code.
Always post errors, or what you did step by step that didn't work, but, really, errors are best.

[Edit= - As I was writing this, Megiddo posted...again, like he asks and I say, never say it doesn't work, always give errors.

[gameguysz]

I got it working :] My table for banned was messed up .

 But it says im a user? My guys the root_user with the ULX mod..

How do i fix that?

[MrPresident]

did you add yourself to superadmin using this mod? It doesn't pull in anything from the users.txt or from your existing ULX users file.


with or without yourself in game... type the following..

Code: [Select]

g4p_setgroup "STEAMID" superadminnote that the steamid is in quotes.

This is, also, unless you changed the concommands.

Also.. if you have access to the SQL database you can manually change your group there. You'd need to reconnect to the server if you do it this way though. (assuming you were connected when you made the change)

[gameguysz]

did you add yourself to superadmin using this mod? It doesn't pull in anything from the users.txt or from your existing ULX users file.


with or without yourself in game... type the following..

Code: [Select]

g4p_setgroup "STEAMID" superadminnote that the steamid is in quotes.

This is, also, unless you changed the concommands.

Also.. if you have access to the SQL database you can manually change your group there. You'd need to reconnect to the server if you do it this way though. (assuming you were connected when you made the change)

And now that user will be a superadmin via ULX mod right? Like he will be able to use the ULX commands?

[MrPresident]

Yep. This sql module effectively replaces the built in ULib user authentication. Instead of reading from the text file it reads from the database. If the user doesnt exist on the database but exists on the text file it will create a new entry in the database and default to user and then overwrite the text file.

The text file will still exist and will still be updated.. this is just as a backup. In case something happens to your database you'll still be able to uninstall the sql module and all of your users will still work.

[gameguysz]

Oh COOL!!!!!! Okay last question!!
 :]


I want to be able to refer the steam id and username and so forth from the data base in other files.

I actually made a npc shop and in the database i made another table to money. I want to be able to access that. will i need to refer this file?

(Im still kinda new to mysql and lua i normally program in html and php but even then im not pro)

[MrPresident]

it's actually really easy.


What I do is I have the database create entity variables when the player joins..

so the information saved in the database is stored as such..

Name: ply.name
SteamID: ply.steamid
Group: ply.group
Banned: ply.banned


So you can easily reference this information with any other serverside lua script by simply reading that variable off of the player entity.

Hopefully that is straight forward enough for you to understand?



EDIT: I just re-read your post.. what did you call the money table.. you'll need to modify my file a little bit. I'll help you out just tell me what you called the money table. Also.. did you make a new table or a new column in the current table? It would be much easier if you just make a new column.

[gameguysz]

it's actually really easy.


What I do is I have the database create entity variables when the player joins..

so the information saved in the database is stored as such..

Name: ply.name
SteamID: ply.steamid
Group: ply.group
Banned: ply.banned


So you can easily reference this information with any other serverside lua script by simply reading that variable off of the player entity.

Hopefully that is straight forward enough for you to understand?



EDIT: I just re-read your post.. what did you call the money table.. you'll need to modify my file a little bit. I'll help you out just tell me what you called the money table.

Kinda, btw thanks for helping me i know it can get annoying


So what your saying is, ill have those same variables but at the bottom ill add something like this

GCcash: ply.gccash


and lets say in a completely different folder like im making a derma menu what would that command be to take out a certain amount? And would i also need to connect to the database again? Its stuff like this that confuses me XD