[Konstantinos]

« Last updated: May 30, 2016 »

Introduction:

First of all, I want to say that it's my first tutorial and that I've seen people using SQLite lately with few incorrect ways and since SQLite is great and it can be used for SA:MP even in nowadays, I decided to write a tutorial about how to use SQLite for a very simple register/login system. I know there are not many tutorials for SQLite and that also encouraged me for writing this tutorial.

You may wonder what is SQLite, where you can find it or how to install it. I'm going to explain everything, so no worries!

- SQLite is a SQL Database system which is an open source.
- SQLite was built into the SA:MP server, so you don't need to search for it, since the server package includes it.
- You don't even need to include "a_sampdb.inc" file, as long as we use "a_samp.inc" and thats in all the cases.

It does not require any plugin or anything else. You can find the database to the scriptfiles directory (when it will be created) and don't forget to create the file extension .db

You can manage your database file with a manager, I recommend SQLite Database Browser. If you ****** it, you'll find results, goto the first one and just download it.

SQLite has been updated and it doesn't use only 10 natives anymoreIf you want to take a look at the functions, you can goto to the SA:MP Wiki (https://sampwiki.blast.hk/wiki/SQLite).
Some of those functions have not been documented though. Those are:

PHP Code:

native db_get_field_int(DBResult:result, field = 0);
// directly returns an integer value according to the field ID specified
native Float:db_get_field_float(DBResult:result, field = 0);
// directly returns a float value according to the field ID specified
native db_get_field_assoc_int(DBResult:result, const field[]);
// directly returns an integer value according to the field name specified
native Float:db_get_field_assoc_float(DBResult:result, const field[]);
// directly returns an float value according to the field name specified
native db_get_mem_handle(DB:db);
// Get memory handle for an SQLite database that was opened with db_open.
native db_get_result_mem_handle(DBResult:result);
// Get memory handle for an SQLite query that was executed with db_query.
native db_debug_openfiles();
native db_debug_openresults(); 


For the last two functions, it contains "debug" on their name so it is pointing out what their use could be but I couldn't seem to be able to get any output after calling them and doing stuff.


Two new console variables were added in 0.3.7 R2:

db_logging - Logs sqlite db_* function errors to the main server_log.
db_log_queries - Logs all sqlite db_query() calls including the query string.


Let's get started:

Whenever a server starts, the database should be opened too. That means that in OnGameModeInit or in OnFilterScriptInit callback, we must open our database. The function we will use is: db_open. I believe the name is self-explanatory. If you read the wiki before, you will notice that this function returns an index (starting at 1) of the database connection (return type for this function has changed since version 0.3.7 R2). We've to store the database connection somewhere. In order to do that, we need to create a variable with the tag DB: before the name of our variable. Remember that it should be a global variable so we can access the id in other callbacks too!

PHP Code:

#include <a_samp> 
// Including samp's main include file is a must (which includes SQLite natives as well).
native WP_Hash(buffer[], len, const str[]); // Including the native of Whirlpool 


PHP Code:

new
    DB: Database; // The variable we mentioned before to store our database connection. Notice the DB: tag before its name
main() {}
public OnGameModeInit()
{
    if ((Database = db_open("server.db")) == DB: 0)  // We open the database with name server.db and store the database connection to the "Database" variable. Directly checking if the connection handle is invalid
    { // If it returns 0, the the database connection failed so let's inform us through console. You may exit the server if you want to.
        print("Failed to open a connection to \"server.db\"");
    }
    else // if it created successfully a connection, let's execute those two queries:
    {
        db_query(Database, "PRAGMA synchronous = OFF"); // Whenever the synchronous is FULL, it writes the data with safety but it takes too long when there are more than 100 rows to insert/update. By turning it OFF, we gain speed but a risk for the file to be currupted in a bad situation. Keep getting backups frequently.
        db_query(Database, "CREATE TABLE IF NOT EXISTS users (userid INTEGER PRIMARY KEY AUTOINCREMENT, username VARCHAR(24) COLLATE NOCASE, password VARCHAR(129), admin INTEGER DEFAULT 0 NOT NULL)");
    }
    return 1;
} 


I would strongly recommend to execute the query about creating the table only once so it doesn't have to execute it everytime the server starts.

You may wonder why do we need the index of the database connection and why don't we create a local variable to open the database. The example of code bellow will explain it itself. We send a query to create the table "users" if it does not exist. And we have the fields: userid, username, password and admin. As you can see, we use INTEGER PRIMARY KEY AUTOINCREMENT for the "userid" field and that will be auto increased everytime we insert a new row. Do not worry if there is a table inside the database. It was created by SQLite to store the last row in a specific table. The "username" is string, so we use VARCHAR(lenght). The max lenght of the player's name is 24; however when a player connects, any name length beyond 20 characters gets rejected. The length specified doesn't really matter though as it is ignored by SQLite (it converts VARCHAR(N) to TEXT) anyway. We have to use the lenght inside parentheses such as (24) for our case . You will also notice the COLLATE NOCASE. That's very important to use it because if a player registers with the name "Zeus" and then he changed his name to "ZeuS", just the case of the letters, it will force him to register again. As long as we use "COLLATE NOCASE", "Zeus" and "ZEUS" or anything else is the same! Same goes for the "password" field about VARCHAR datatype. 129 lenght like Whirlpool (Actually it is 128 characters + NULL for PAWN but as we said, the length itself is ignored). Last, you will see that the field "admin" has the following INTEGER DEFAULT 0 NOT NULL. We set the default value to 0, so we don't need to insert 0 when a player will register. That's a very useful thing when you get more columns and the query's size will be long enough.

PHP Code:

public OnGameModeExit()
{
    db_close(Database);// We need the index of the database connection in order to close the database!
    return 1;
} 


We need to close the database when the server closes too. That is done using db_close function which requires the index of the database connection we stored previously to "Database" variable.

We have the basic at the moment, opening/closing our database. We need to store player's variables for the Userid, name, password and admin. It's just an example and those are very basic. You can create your own after reading this tutorial (I hope you will understand and I will explain it as simple as I can). I also use Whirlpool to hash the passwords - security of our players IS important! Having the password saved as plain text is really bad idea. I would also recommend using Salt along with the hash and there are tutorials for that on ****** if you want to learn more about it.
We'll need the enumeration and a variable (array) to store the data for the players.

PHP Code:

enum USER_DATA
{
    USER_ID,
    USER_NAME[MAX_PLAYER_NAME],
    USER_PASSWORD[129],
    USER_ADMIN
};
new
    User[MAX_PLAYERS][USER_DATA]; 


One other thing to consider doing is re-defining MAX_PLAYERS as by default is defined as 1000. Anyway, I would suggest you to add those under the defines/includes. Don't forget to reset the variables when a player connects/disconnects, just for safety. People can join with the same playerid and get the statistics of the previous user who might was owner! Using this method is the best in my opinion to save time writing all the data to reset then and it is evenly fast!

PHP Code:

public OnPlayerConnect(playerid)
{
    new 
        tmp[USER_DATA];
    User[playerid] = tmp;
    return 1;
} 


PHP Code:

public OnPlayerDisconnect(playerid, reason)
{
    new 
        tmp[USER_DATA];
    User[playerid] = tmp;
    return 1;
} 


It's time to check whether a player is registered or not! But before that, let's define the dialog IDs. I find myself using an enumerator more convinient than messing with #define pattern.

PHP Code:

enum
{
    DIALOG_REGISTER, // DIALOG_REGISTER is 0
    DIALOG_LOGIN // DIALOG_LOGIN is 1
}; 


A new specifier was added to format function by the way and that's %q. It escapes strings for SQLite. If you do not use it, then you may be victim of SQL injection. Safety after all.

PHP Code:

public OnPlayerConnect(playerid)
{
    // Resetting our variables, like we said before
    new 
        tmp[USER_DATA];
    User[playerid] = tmp;
    // ---
    GetPlayerName(playerid, User[playerid][USER_NAME], MAX_PLAYER_NAME); // We store player's name to that variable, just to prevent from getting the name all the time. It saves time!
    // It's also a better method than calling a function that returns the name. Imagine now if you want to use the player's name a couple of times, you'll have to keep calling the function - not efficient at all.
    new
        Query[82], // A string to store our query. You can always calculate the query's length + the length of our arguments passed to the specifiers + 1 for NULL if you are not lazy. Always though make sure the size is long enough to store the whole query otherwise it will fail.
        DBResult: Result; // The result. Notice the DBResult: tag before the variable, just like the DB: tag we saw before. Those are necessary otherwise you'll get warning for tag mismatch.
    format(Query, sizeof Query, "SELECT password FROM users WHERE username = '%q' LIMIT 1", User[playerid][USER_NAME]); // We select the password only so we can check later on if the passwords match, only if the player is already registered of course.
    Result = db_query(Database, Query); // We store the result index returned by db_query so we can retrieve the rows of that result and also free the result.
    
    if (db_num_rows(Result))// If there are rows (in our case the maximum number of rows in 1 as there cannot exist more players with that name), that means the player is registered
    {
        db_get_field_assoc(Result, "password", User[playerid][USER_PASSWORD], 129);// And we store the password to our variable, so we'll be able to check if the password is equal to the password the player enters later in the dialog input
        ShowPlayerDialog(playerid, DIALOG_LOGIN, DIALOG_STYLE_PASSWORD, "Login", "Type in your password below to log in.", "Login", "Leave");// Showing the dialog for login.
    }
    else // Otherwise.. meaning that there are not rows, so the player is going to register
    { 
        ShowPlayerDialog(playerid, DIALOG_REGISTER, DIALOG_STYLE_PASSWORD, "Register", "Type in a password below to register an account.", "Register", "Leave");// Showing the dialog to the player to register
    }
    db_free_result(Result);// And we free the result
    return 1;
} 


db_num_rows and db_get_field_assoc are now protected against NULL references and they will not crash the server like they used to. db_get_field_assoc also returns 0 if the column index is not available.

It's not necessary to use the grave accent character (`) like some other people do, it's just a habbit. It's not a rule to use it and neither a rule not to use them at all. Both cases work just fine! You should remember that when you insert a string into a query, you must escape the text especially if the text was passed was inputted by a person to avoid SQL Injection as we mentioned before. When you use for string, we have to use apostrophe ' ' around; however, it's not necessary to use it for integers and floats.
Using LIMIT clause will stop searching for more results if the number of maximum rows specified is reached. Another thing to take into consideration is using indexes - it can surely speed up the query. You can create an index for the "username" field but you better not have it in the script itself. You can execute it through the SQLite Browser. In case you are interested, here's how it would look:

PHP Code:

CREATE INDEX index_name ON table_name (column_name);
// in our case, that would be:
CREATE INDEX index_username ON users (username); 


You may as well create it as UNIQUE INDEX since there will be no more than one user with that name.

Anyway, we execute the SQL query; however, we store the result to our variable "Result". After that, we use the db_num_rows function which returns the number of rows from the SQL query. It takes as parameter a result, and that's the result we stored when executing the query.
In case we have rows (not 0), the player is registered. We want to get the result from the query. We selected the "password" field and we will store it to our variable that stores the hashed password. Then we're going to compare the two passwords and check if the password is correct in order to load the data to the player, else if the password is incorrect, it keeps sending the dialog with the message that the password was incorrect. You may add that the player has 3 attempts to give the correct password, but it's up to you. I will only explain you how SQLite works and how you can use it for a register/login system. Let's take a look below:

PHP Code:

db_get_field_assoc(Result, "password", User[playerid][USER_PASSWORD], 129); 


If there are rows, the player is registered, and we get the data that the "password" field contains.
The parameters are:

PHP Code:

(DBResult:dbresult, const field[], result[], maxlength) 


Our result, the field's name "password", somewhere to store it - a destination array, the max lenght (the variable's size is 129 which is 128 by WP_Hash output + 1 NULL).
Bare in mind that using [I]db_get_field[I] function is faster because it doesn't have to search for the name of the field we specified if we know the order.


That's it. If there are rows, it stores the password to User[playerid][USER_PASSWORD] and shows us the dialog to login, otherwise to register. But it's not the end of it. We used a SELECT statement, and that needs a result.

WARNING: Whenever we use SELECT statement, we must free the result.

PHP Code:

db_free_result(Result); 


in order to free the memory. Otherwise, we're going to have memory leaks. If you execute an UPDATE or INSERT INTO statement, you don't need to free any result since we don't have one.

Let's move on! The callback for the dialog's response:

PHP Code:

public OnDialogResponse(playerid, dialogid, response, listitem, inputtext[])
{
    switch (dialogid)
    {
        case DIALOG_REGISTER:
        {
            // if the player did not response to the dialog, meaning pressed either Esc or ckicked on "Leave" button, will be kicked
            if (!response) return Kick(playerid);
            
            if (!(3 <= strlen(inputtext) <= 20))
            { // if the length of the desired password is not beetween 3-20 characters
                SendClientMessage(playerid, 0xFF0000FF, "[ERROR]: Invalid length on the password. It should be between 3-20 characters" );// send the message error for the valid lenght of the password
                ShowPlayerDialog(playerid, DIALOG_REGISTER, DIALOG_STYLE_PASSWORD, "Register", "Type in a password below to register an account.", "Register", "Leave" );// re-show the register dialog
                return 1; // stop the code below from being executed
            }
            
            new // a query, remember to change the size of it if you add more stuff
                Query[208];
                
            WP_Hash(User[playerid][USER_PASSWORD], 129, inputtext); // Hash the password from the inputtext and store it to User[playerid][USER_PASSWORD]
            format(Query, sizeof Query, "INSERT INTO users (username, password) VALUES ('%q', '%s')", User[playerid][USER_NAME], User[playerid][USER_PASSWORD]);// Insert into users the name and the password. The userid gets increased automatically and the admin is by default 0 value. We don't have to escape password as the hashed output provided by Whirlpool contains only alphabet characters and numbers.
            db_query(Database, Query);// execute the SQL query
            SendClientMessage(playerid, 0x00FF00FF, "[SERVER]: You have just registered to our server! You have been automatically logged in!");//send a message just to inform the player that he's now registered!
            // We have to retrieve the value generated from the Auto Increment for the "userid" field.
            // last_insert_rowid() function returns the last inserted row in the last session as its name suggests already.
            new
                DBResult: Result;
                
            Result = db_query(Database, "SELECT last_insert_rowid()"); // it will return the value for field "userid" generated
            User[playerid][USER_ID] = db_get_field_int(Result); // The difference in this function except that it doesn't support the name of the field but the field ID instead, it is that returns an integer directly without having to store it to a temporary string and then use strval function to convert string to integer.
            // There is a second parameter "field" which is by default 0. We know that only 1 row and 1 field will be selected so we may not specify it.
            // REMEMBER! Fields starts from 0.
            db_free_result(Result); // Last, we free the result memory
        }
        case DIALOG_LOGIN:
        {
            // if the player did not response to the dialog, meaning pressed either Esc or ckicked on "Leave" button, will be kicked
            if (!response) return Kick(playerid);
            
            new
                buf[129]; //make a string to store the hashed password we entered
            WP_Hash(buf, 129, inputtext); // Hash the password the player inputs
            if (strcmp(buf, User[playerid][USER_PASSWORD]))// Comparing if the string1 is not equal to string2. If they're not, it returns a value different from 0.
            { // if the password is incorrect
                SendClientMessage(playerid, 0xFF0000FF, "[ERROR]: Incorrect password");// let them know by sending a message
                ShowPlayerDialog(playerid, DIALOG_LOGIN, DIALOG_STYLE_PASSWORD, "Login", "Type in your password below to log in.", "Login", "Leave" );//re-show the login dialog
                return 1; // stop the code below from being executed
            }
            
            // if the player responded to the dialog and the password is correct, let's load the rest of their data
            new
                DBResult: Result; // to store the result, we have declared a string already above (that being "buf")
            format(buf, sizeof buf, "SELECT * FROM users WHERE username = '%q' LIMIT 1", User[playerid][USER_NAME]);// we select every field this time from the table users using * which means ALL
            Result = db_query(Database, buf); // we store the result index so we can extract the data and free its result later
            
            if (db_num_rows(Result))// if there are rows - number of rows are not 0 in other words
            {
                // get the data from the result where the field is "userid" and return directly its value to store it to our variable
                User[playerid][USER_ID] = db_get_field_assoc_int(Result, "userid");
                User[playerid][USER_ADMIN] = db_get_field_assoc_int(Result, "admin");
                // Same as above, getting the data from the "admin" field and store it to the variable associated with the player's admin level
                SendClientMessage(playerid, 0x00FF00FF, "[SERVER]: You have successfully logged in to your account!");// and a message to let them know they have successfully logged in!
            }
            db_free_result(Result);//freeing the memory result, REMEMBER "SELECT" statement!
        }
        default: return 0; // dialog not found, search in other scripts
    }
    return 1; // dialog was found
} 


As of SA-MP 0.3x, any action (client messages, dialogs etc) taken directly before Kick function will not be sent/shown. If you want to show a message to the player, you will have to delay the kick. An example can be found on the SA:MP Wiki.

The only thing left right now is updating the player whenever they disconnect from the server.

PHP Code:

public OnPlayerDisconnect(playerid, reason)
{
    new
        Query[51]; // a string to store the query
    format(Query, sizeof Query, "UPDATE users SET admin = %d WHERE userid = %d", User[playerid][USER_ADMIN], User[playerid][USER_ID]);// UPDATE table SET field = value WHERE field = something
    // We update the table users and we set to the "admin" field the value we insert. If the value has changed before in-game, it will update it with the new one. Where the userid is the unique ID per-player.
    db_query(Database, Query);//executing the query
    // --- 
    // Resetting our variables..
    new 
        tmp[USER_DATA];
    User[playerid] = tmp;
    return 1;
} 


Keep in mind that you should only update data that are needed when a player disconnect. I had to use an example for you, readers, but it's recommended that if you later on have a command that sets the player's level to update the field in the table directly there so you won't have to update it when a player disconnects.

You have also noticed that we update according to player's unique ID in our database because as you have probably figured out searching by integers is much faster than strings.

Another example could be the player's IP in which in this tutorial I have not used. Updating the IP to the table after player logs in is the best place to do it.

That's the end of the tutorial I guess! I wish I explained them well for you and you can now make your own register/login system.

[Konstantinos]

Thank you very much. I hope the tutorial was well-explained for everyone. It took me an hour to write it, but I believe it's worth since there're not many tutorials about SQLite or not well-explained like people would like!

[Niko_boy]

i never used DB_Escape tbh , but idk how to check for SQL injection actually i dont know what it is
i am a regular SQLIte user btw
nice tutorial anyways

[Konstantinos]

You should. I will give you an example of what SQL Injection is.

Let's say your mode has a dialog (input) and someone insert a "name" to check something for a user. He can input:

pawn Code:

"Zeus';DROP TABLE users;"

And it will just delete your table "users". DB_Escape is used for this reason. To prevent someone from doing something bad to your database. You can also read more about DB_Escape/SQL Injection to the wiki (https://sampwiki.blast.hk/wiki/Escaping_Strings_SQLite) or wikipedia (http://en.wikipedia.org/wiki/SQL_injection)

You just need to check any string you insert in a query with:

pawn Code:

// An example:
"SELECT username FROM users WHERE ip = '%s'", DB_Escape( ip ) // etc..

Glad to hear that you use SQLite, it's great and I can confirm it because I know that a very popular server in the past used SQLite and had over 500,000 registered users and everything worked fine!

Thanks for your kind words.

[Yordan_Kronos]

Excellent 10/10

[DaRk_RaiN]

Awesome tutorial, i'll start reading.
Way to go Dwane

[StreetGT]

Good job!
It's really useful for people starting with SQL!
Approved!

[Konstantinos]

Quote:

Originally Posted by Yordan_Kronos Excellent 10/10

Quote:

Originally Posted by DaRk_RaiN Awesome tutorial, i'll start reading. Way to go Dwane

Quote:

Originally Posted by StreetGT Good job! It's really useful for people starting with SQL! Approved!

Thank you! I really appreciate it.

@DaRk_RaiN - Glad to see you remember my old name, dude!

[Red_Dragon.]

Pretty awesome!

[Zeyo]

Yay, its awesome. Thank you Zeus_

[Konstantinos]

Quote:

Originally Posted by Red_Dragon. Pretty awesome!

Quote:

Originally Posted by Zeyo Yay, its awesome. Thank you Zeus_

Thank you!

[Konstantinos]

Glad to see you understood SQL Injection. Memory leaks are caused by the results, if you do not free the memory.

I tried to use Slice's include but found it "too heavy" for me and to be honest, if someone is careful with their mode, then they will never have problems. I've been using SQLite for 1 year and it never crashed due to that or anything else related to SQLite. Absolutely nothing!

[IPrototypeI]

Good Tutorial but you can add LIMIT 1 to your SELECT-query, because if the server found one result in his database he stopped and don`t go one with searching after results. With your query it is that the mysql-server go one with the searching after he found a result. With this you can optimize your SELECT-query a little bit.

A other point is that you can reset the values from your acc-array with a for-loop.

Example:

Code:

for(new i; i < _:USER_DATA; ++i)User[playerid][USER_DATA:i]= 0;

or

Code:

for(new i; USER_DATA:i < USER_DATA; i++) User[playerid][USER_DATA:i] = 0;

[Konstantinos]

That's a good suggestion about the LIMIT 0, 1 and I'm going to add it to the tutorial! Thank you.

To be honest, I didn't know that method to reset variables. However, does it reset only integers? What if I have in an enumeration with strings, integers, floats, booleans?

EDIT: I tested it and it reset everything inside the enumeration. Thanks a lot man, that was something I had no idea and it's great!

Added both.

[NoahF]

Awesome TUT!

[Konstantinos]

Thanks, NoahF!

[RedFusion]

Nice tutorial!

[Darnell]

The 'ShowDialog', where are we supposed to get that?

[Konstantinos]

Thank you, RedFusion.

@Darnell, I mentioned that I use "easydialog" include (https://sampforum.blast.hk/showthread.php?tid=377140). Though, if you don't want to use it and you want the default way instead you can tell me that! I don't mind to post it.

[Kaperstone]

Will be nice if you'll write a tutorial for SQLitei as well.
Anyway,nice tutorial !