[Kyance]

As the title says, today I'm going to teach you how to create a Ban System in SQLite.
SQLite is already 'built in' PAWNO, so there's no need to download anything.. unless, if you want to view the database(s), then you have to download the SQLite Browser.
Right.. let's get to the tutorial.

#1 - Creating the database
You will have to create a global variable like this;

pawn Код:

new DB:bans;

You can name it what-ever you want to, but it must have the "DB:NAME"!

#2 Opening, closing the database, creating tables
Under your OnGameModeInit(or OnFilterScriptInit), add this;

pawn Код:

bans = db_open("BansList.db");
db_query(bans, "CREATE TABLE IF NOT EXISTS `BANNED` (`NAME`, `IP`, `REASON`, `ADMIN`, `DATE`, `TIME`)");

The "bans = db_open(..." is the database variable you created (new DB:NAME), and the "BanList.db" is the file which will be opened(or created!).
db_query(bans, "CREATE TABLE IF NOT EXISTS `BANNED` (`NAME`, `IP`, `REASON`, `ADMIN`, `DATE`, `TIME`)");
This will create a table called 'BANNED'(If it's not already created!), with the columns 'name', 'ip', 'reason', 'admin', 'date' and 'time'.

Now, on "OnGameModeExit" (or OnFilterScriptExit ), add this;

pawn Код:

db_close(bans);

This will close the database.


#3 Command, stocks
#1 - Default ban command.

pawn Код:

CMD:ban(playerid, params[]) {
    new id, reason[48];
    if(/*AdminEnum here!*/) return SendClientMessage(playerid, -1, "SERVER: You need to be an admin!");
    if(sscanf(params, "us[48]", id, reason)) return SendClientMessage(playerid, -1, "USAGE: /ban PLAYERID REASON");
    if(id == INVALID_PLAYER_ID || !IsPlayerConnected(id)) return SendClientMessage(playerid, -1, "SERVER: Invalid ID");
    BanWithReason(playerid, id, reason);
    return 1;
}

Line #2: Now, in the ban command we 'create' the variable id, and a 'string' reason, with 48 'cells'(lenght).
Line #3: We check if the player is an admin, or not.
If he isn't an admin, he gets an 'error' message, saying that he MUST be an admin to use this command!
NOTE: Replace the '/*AdminEnum here*/' with either "if(!IsPlayerAdmin(playerid))", or your variable "if(PlayerInfo...)".
Line #4: We use sscanf to see, if the admin has written the target id, and the ban reason.
If he hasn't typed the target ID, or the reason, he gets the 'usage' message.
Line #5: We check if the id he entered is valid.
If it isn't valid(INVALID_PLAYER_ID/!IsPlayerConnected), he gets an 'error' message!
But, if everything is written, and the ID is valid, we ban him!
#2 - Offline ban command.

pawn Код:

CMD:offlineban(playerid, params[]) {
    new Query[240], string[112], pName[24], reason[48], DBResult:result, day, month, year, second, minute, hour, datestring[24], timestring[24];
    if(/*AdminEnum here*/) return SendClientMessage(playerid, -1, "SERVER: You need to be an admin!");
    if(sscanf(params, "s[24]s[48]", pName, reason)) return SendClientMessage(playerid, -1, "USAGE: /offlineban PLAYERNAME REASON");

    format(Query, sizeof(Query), "SELECT `NAME` FROM `BANNED` WHERE `NAME` = '%s'", pName);
    result = db_query(bans, Query);
    if(!db_num_rows(result))
    {
        getdate(day, month, year), gettime(hour, minute, second);
        format(datestring, sizeof(datestring), "%i-%i-%i", day, month, year), format(timestring, sizeof(timestring), "%i:%i:%i", hour, minute, second);
        
        format(Query, sizeof(Query), "INSERT INTO `BANNED` (`NAME`, `IP`, `REASON`, `ADMIN`, `DATE`, `TIME`) VALUES ('%s', '0', '%s', '%s', '%s', '%s')", DB_Escape(pName), reason, DB_Escape(GetName(playerid)), datestring, timestring);
        result = db_query(bans, Query);
        if(result)
        {
            format(string, sizeof(string), "BAN: %s has been offline banned by %s(%d) due to %s", pName, GetName(playerid), playerid, reason);
            SendClientMessageToAll(-1, string), string = "\0";

            printf("[ban] [%s]: offlinebanned %s due to %s", GetName(playerid), pName, reason);
        }
        else
        {
            format(string, sizeof(string), "SERVER: Failed to ban '%s'..", pName), SendClientMessage(playerid, -1, string);
            printf("[ban] [%s]: failed to offlineban %s due to %s", GetName(playerid), pName, reason);
        }
    }
    else return SendClientMessage(playerid, -1, "INFO: That username is already banned!");
    db_free_result(result);
    return 1;
}

Okay so, here, we actually create a lot of variables, and do quite a lot of stuff.
First 3 lines are just the same as the 'default' ban command, we create variables, check if the player is an admin or not, and check if he has typed a NAME(not an ID!), and the reason.
We're using 'pName', well.. because offline players don't really have an ID.

Line #4 ( the format(Query) ).
We are looking for the name(pName) in the table 'BANNED'(which we created earlier).
Line #5
We're using result = db_query(...), because we will need to know, if there are any rows(results/data/etc..) found, or not.
Line #6
If we didn't find any rows(results/data/etc..), we proceed to add the ban.
Line #7 and #8 ( the getdate, gettime and format datestring.. ).
We use the 'getdate' and 'gettime' function, to format the date and time string for the ban.
( So we can know where he was banned )
Line #9, #10
We insert the name written earlier into the 'banned' table.
Again, we use result so we can check if the query was successful or not.
Line #11 ( if(result) ).
If the query succeeded, we 'create'(format) a message, and send it to everyone.
Line #12 ( else = if(!result ))
If the query didn't succeed, we send him the message( Failed to ban NAME ).
NOTE: I added 'printf', so the server owner can check we ban logs. ( So some crazy admins don't offlineban everyone.. )
Line #13 ( else ( if db_num_rows ) )
If we did find data on the name (pName), then we send him the message, that the username is already banned.
Line #14
We free the 'query'(to avoid SQL injections and such things..)

#3 - Stocks, timers, additional commands

Explained:

pawn Код:

stock BanWithReason(playerid = INVALID_PLAYER_ID, targetid, reason[])
{
    new Query[240], string[112], DBResult:result, day, month, year, second, minute, hour, datestring[24], timestring[24];
    getdate(day, month, year);
    gettime(hour, minute, second);

    format(datestring, sizeof(datestring), "%i-%i-%i", day, month, year);
    format(timestring, sizeof(timestring), "%i:%i:%i", hour, minute, second);

    if(playerid != INVALID_PLAYER_ID)
    {
        format(Query, sizeof(Query), "INSERT INTO `BANNED` (`NAME`, `IP`, `REASON`, `ADMIN`, `DATE`, `TIME`) VALUES ('%s', '%s', '%s', '%s', '%s', '%s')", DB_Escape(GetName(targetid)), DB_Escape(PlayerIP(targetid)), reason, DB_Escape(GetName(playerid)), datestring, timestring);
        result = db_query(bans, Query);
        if(result)
        {
            format(string, sizeof(string), "BAN: %s(%d) has been banned by %s(%d) due to %s", GetName(targetid), targetid, GetName(playerid), playerid, reason);
            SendClientMessageToAll(-1, string), string = "\0";
            printf("[ban] [%s]: successfully added %s's ban info", GetName(playerid), GetName(targetid));
        }
        else
        {
            format(string, sizeof(string), "SERVER: failed to ban '%s'", GetName(targetid));
            SendClientMessageToAll(-1, string), string = "\0";
            printf("[ban] [%s]: failed to add %s's ban info", GetName(playerid), GetName(targetid));
        }
    }
    /*
        else
    {
        format(Query, sizeof(Query), "INSERT INTO `BANNED` (`NAME`, `IP`, `REASON`, `ADMIN`, `DATE`, `TIME`) VALUES ('%s', '%s', '%s', 'Server', '%s', '%s')", DB_Escape(GetName(targetid)), DB_Escape(PlayerIP(targetid)), reason, datestring, timestring);
        result = db_query(bans, Query);
        format(string, sizeof(string), "BAN: %s(%d) has been banned by the Server due to %s", GetName(targetid), targetid, reason);
        SendClientMessageToAll(COLOR_RED, string), string = "\0";
    }
        for anti-cheat purposes ^
        */
    Query = "\0", db_free_result(result);
    SetTimerEx("KickTimer", 100, false, "i", targetid);
}

So, this is the stock which is used for the 'default' ban command.
The first 5 lines (creating variables, format etc.), are the same as the 'offline ban' command.
We create the variables, get the time and date for the time/date string(which is for the query).
Line #6 ( playerid != INVALID_PLAYER_ID )
Is to check if the playerid(ADMIN) is valid, well, yeah, it isn't needed, but I've added an 'else' statement, incase you want to add some anticheat bans. (BanWithReason(INVALID_PLAYER_ID, ID, reason))
Line #7, #8, #9 and #10
We insert the ban into the database, and we use 'result' to see if the query was successful or not, if it was successful, we send the message to everyone, if it's not, the admin gets an 'error' mesage.
After that, we 'erase' the Query, free the result, and kick the target;

pawn Код:

forward KickTimer(playerid);
public KickTimer(playerid)
{
    Kick(playerid);
    return 1;
}

Not explained:

pawn Код:

stock DB_Escape(text[])
{
    new
        ret[80 * 2],
        ch,
        i,
        j
    ;
    while ((ch = text[i++]) && j < sizeof (ret))
    {
        if (ch == '\'')
        {
            if (j < sizeof (ret) - 2)
            {
                ret[j++] = '\'';
                ret[j++] = '\'';
            }
        }
        else if (j < sizeof (ret))
        {
            ret[j++] = ch;
        }
        else
        {
            j++;
        }
    }
    ret[sizeof (ret) - 1] = '\0';
    return ret;
}
stock GetName(playerid)
{
    new pnameid[24];
    GetPlayerName(playerid,pnameid,sizeof(pnameid));
    return pnameid;
}
stock PlayerIP(playerid)
{
    new str[16];
    GetPlayerIp(playerid, str, sizeof(str));
    return str;
}

Additional commands:

pawn Код:

CMD:searchban(playerid, params[])
{
    if(/*AdminEnum here!*/ return SendClientMessage(playerid, -1, "SERVER: You need to be an admin!");
    else if(isnull(params)) return SendClientMessage(playerid, -1, "USAGE: /searchban PLAYERNAME/PLAYERIP");
    else
    {
        new Query[240], string[180];
        format(Query, sizeof(Query), "SELECT * FROM `BANNED` WHERE `NAME` LIKE '%%%s%%' OR `IP` LIKE '%%%s%%' ORDER BY `DATE` DESC LIMIT 6", params, params);
        new DBResult:Result = db_query(bans, Query);
        if(db_num_rows(Result))
        {
            new BannedBy[MAX_PLAYER_NAME], BannedName[MAX_PLAYER_NAME], BannedIP[MAX_PLAYER_NAME], BannedReason[MAX_PLAYER_NAME*2];
            do
            {
                db_get_field_assoc(Result, "NAME", BannedName, sizeof(BannedName));
                db_get_field_assoc(Result, "IP", BannedIP, sizeof(BannedIP));
                db_get_field_assoc(Result, "ADMIN", BannedBy, sizeof(BannedBy));
                db_get_field_assoc(Result, "REASON", BannedReason, sizeof(BannedReason));

                format(string, sizeof(string), "- {FC4949}%s(IP: %s) {FFFFFF}- {FC4949}banned by %s {FFFFFF}- due to %s", BannedName, BannedIP, BannedBy, BannedReason);
                SendClientMessage(playerid, -1, string);
            }
            while(db_next_row(Result));
        }
        else SendClientMessage(playerid, COLOR_ORANGE, "NOTE: No bans found!");
        db_free_result(Result);
        string = "\0", Query = "\0";
    }
    return 1;
}
CMD:unban(playerid, params[]) {
    new pName[24], reason[50], Query[132], DBResult:Result;
    new stringz[92];
    if(/*AdminEnum here!*/) return SendClientMessage(playerid, -1, "SERVER: You need to be an level 2 admin!");
    if(sscanf(params, "s[24]s[50]", pName, reason)) return SendClientMessage(playerid, -1, "USAGE: /unban [Name] [Reason]");


    format(Query, sizeof(Query), "SELECT * FROM `BANNED` WHERE `NAME` = '%s'", pName);
    Result = db_query(bans, Query);
    if(db_num_rows(Result))
    {
        format(Query, sizeof(Query), "DELETE FROM `BANNED` WHERE `NAME` = '%s'", pName);
        Result = db_query(bans, Query);
        format(stringz, sizeof(stringz), "UNBAN: %s has been unbanned by %s due to: %s", GetName(playerid), pName, reason);
        SendClientMessageToAll(-1, stringz), stringz = "\0";
    }
    else SendClientMessage(playerid, -1, "SERVER: No bans found on that user-name!");
    stringz = "\0", Query = "\0", reason = "\0";
    db_free_result(Result);
    return 1;
}

OnPlayerConnect check;

pawn Код:

new Query[98], DBResult:Result;
        format(Query, sizeof(Query), "SELECT * FROM `BANNED` WHERE `NAME` = '%s' OR `IP` = '%s'", DB_Escape(GetName(playerid)), DB_Escape(PlayerIP(playerid))); // this checks if player's username is in "BANNED" table
        Result = db_query(bans, Query);
        if(db_num_rows(Result))
        {
            new banreason[50], bannedby[24], banname[24], banip[18], string[300];
            db_get_field_assoc(Result, "REASON", banreason, sizeof(banreason)); db_get_field_assoc(Result, "ADMIN", bannedby, sizeof(bannedby));
            db_get_field_assoc(Result, "NAME", banname, sizeof(banname)); db_get_field_assoc(Result, "IP", banip, sizeof(banip));
            format(string, sizeof(string), "{FFFFFF}Our system has detected that your {FC4949}username/IP{FFFFFF}is {FC4949}banned.\n{FFFFFF}Banned Name: {FC4949}%s\n{FFFFFF}Banned IP:{FC4949}%s\nBanned by: {FC4949}%s\n{FFFFFF}Reason: {FC4949}%s\n* If you wish to be unbanned, contact the server owner.", banname, banip, bannedby, banreason);
            ShowPlayerDialog(playerid, 0, DIALOG_STYLE_MSGBOX, "{FC4949}Ban detected.", string, "Close", ""), string = "\0";
            printf("%s has been kicked from OnPlayerConnect - Username ban detection", GetName(playerid));
            SetTimerEx("KickTimer", 75, false, "i", playerid);
            db_free_result(Result);
            string = "\0";
        }

Sorry if something is wrong, this is taken from my some-what old gamemode, and I didn't have much time to work on the tutorial xd.

[Pottus]

This isn't going to work very well yes it does what it is intended to do but there is a lot more to writing an effective ban system than this.

[Djole1337]

pawn Код:

stringz = "\0", Query = "\0", reason = "\0";

Unneeded code.

Wait what? You're escaping players name INSTEAD of escaping admin input aka ban reason?

[Kyance]

Quote:

Originally Posted by Djole1337 pawn Код: stringz = "\0", Query = "\0", reason = "\0"; Unneeded code. Wait what? You're escaping players name INSTEAD of escaping admin input aka ban reason?

wow.
such criticism.
very helpful.
much learning.
much facepalm.
wow.

Seriously, if you're gonna criticize, you should also tell them(me, others) what's wrong, and how to fix/improve it.

@Pottus: For an example?

[Djole1337]

Quote:

Originally Posted by Kyance Seriously, if you're gonna criticize, you should also tell them(me, others) what's wrong, and how to fix/improve it.

I just told you what is wrong. You're not escaping admin's input.

Also there's really no need to reset variables as they aren't global.

[Pottus]

If I were to explain it all I would be giving away too many trade secrets

I will say this, it involves using gpci, geoip and specially crafted queries.

[Kyance]

Quote:

Originally Posted by Djole1337 I just told you what is wrong. You're not escaping admin's input. Also there's really no need to reset variables as they aren't global.

Oh.. I just know that some of my friends use it, to "clear memory", so I used that too ;d Thanks.

Quote:

Originally Posted by Pottus If I were to explain it all I would be giving away too many trade secrets I will say this, it involves using gpci, geoip and specially crafted queries.

Oh..
Never heard of GPCI, might use that someday

[Pottus]

Quote:

Originally Posted by Kyance Oh.. Never heard of GPCI, might use that someday

Works really well for profiling ban evaders again with the right queries.

[TuNiSiAnO1]

Good one, Thank you!

[ReD_DeVi]

Amazing tutorial I really liked it