> For the complete documentation index, see [llms.txt](https://simon-6.gitbook.io/simoncyber/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://simon-6.gitbook.io/simoncyber/cyber/sql-server/sql-server.md). # SQL Server ### Preface This project originates from from work. The core is the issue is that a staff member is manually resolving tickets. This comes from a person who requests for a piece of software supported by the staff's company through an email. The staff then needs to verify the person account, and then retrieve a software license to give it to them. Except, all the transaction all needs to be recorded somewhere. Hence, we'll be solving the latter part of the issue by creating an SQL Server. {% hint style="warning" %} Due to this project being apart of my work, I won't discuss the actual names and data of the project being used in my work system (just in case). However, I'll be using place holders and still showing the code as it were just another side project. {% endhint %} ### Creating the Server I won't go too in depth about the actual server implementation, rather I will be talking about coding the server in MySQL. But if you want a quick synopsis, since my work uses virtualization, we basically request a instance from a branch to open up a VM. Anyways, I'll be using my home computer for this, with MySQL. We first create a database: I gave it a generic name.
We then created tables to pair alongside that database:
The first table is the USER. This keeps track of all current users who have a license under their name. From including DataIssued, the name, and the License Key, it pretty much should keep track of everything needed. The License Table is a little more simpler. However, it should be noted of the ENUM type. {% hint style="info" %} **ENUM** is a database column type that restricts a value to **one of a predefined set of strings**. {% endhint %} It also includes a few constraints. To name one, PRIMARY KEY means that the values in this column are unique, and each value can be used to identify a single row in this table. ### Loading Licenses Let's load up some example licenses:
Notice we didn't include a FALSE at each value. This is because it already defaults to FALSE, to being assigned. ### Learning Procedures This is a little off topic to the project, but since I never created any procedures, I'll be learning them while creating this project. I'll be using the following sources to learn. (Also side tangent, there's so much mixed resources on how to create a stored procedure that it's very hard to learn.) {% embed url="" %} #### What is a Stored Procedure? > By definition, the stored procedure is a set of declarative SQL statements stored in the MySQL server. It is something like a function in any programming language. Think of a Stored Procedure like a function in a language like Python or C. Suppose you have this long, long query you want to use more than a few times. It's very inefficient to rewrite the code again and again, hence you would use a Stored Procedure to call the statements with your own parameters. The framework of a Stored Procedure would be this: ```sql DELIMITER $$ CREATE PROCEDURE procedure_name ( [IN | OUT | INOUT] param1 datatype, [IN | OUT | INOUT] param2 datatype ) [procedure characteristics] BEGIN -- declarations -- executable statements -- control flow END$$ DELIMITER ; ``` I'll explain each part of this function. CREATE PROCEDURE is the statement. For the parameters, there are three types of parameters – IN, OUT, INOUT. The IN parameter specifies the input value that you are passing to the procedure from the procedure call. The OUT parameter is the value that the procedure will return. On the other hand, the INOUT performs combined work of the IN and OUT parameters. We'll be prominently using the IN and OUT feature. ### IN vs OUT It might be a little confusing between the parameter IN and OUT. It might not be confusing from the description I've previously mention, but I assure you...it's confusing. **Input Parameters (IN):** When you declare a parameter as **IN**, it means that the procedure can receive a value from the calling program, but the procedure cannot modify the value of the parameter. It’s used for passing values into the procedure. **Output Parameters (OUT):** When you declare a parameter as **OUT**, it means that the procedure can modify the value of the parameter, and this modified value will be accessible to the calling program after the procedure execution. It’s used for passing values out of the procedure. An `OUT` parameter is a variable that the procedure fills in for you. Imagine you hand a empty cup to a stand. After the procedure, you get the same cup, but now filled. If that make sense, you use an out parameter when you want to get something from the function and pass it on to the variable @(variable), and you use a in parameter if you want that function to do something with that value.
*** At the start and end of every procedure, we include **Delimiters**. Delimiters are used when we need to define the stored procedures as well as to create triggers. Without a delimiter, the database can’t reliably parse input especially when multiple statements are sent together. Default delimiter is semicolon. So since we not put it as a $$, once we type it again at the end, it ends like a semicolon! Also, you might notice that sometimes people use // and $$ to signal a delimiter. We'll just be using // for simplicity. To then finally call this procedure we would then use: ```sql CALL proc_name([parameters]) CALL GetCustomerInfo(5, @name, @total); (out variables are @) ``` Here's another example specifically for MySQL: ```sql DELIMITER $$ CREATE PROCEDURE get_account_balance ( IN id INT, OUT bal DECIMAL(10,2) ) BEGIN SELECT balance INTO bal FROM accounts WHERE id = uid; END$$ DELIMITER ; ``` Here we see the delimiters and the procedure statement. Keep in mind, the `DELIMITER` is **not SQL.** The MySQL server never sees this line, but it's good to keep practice. "id" is the parameter used for the id. bal is for the balance, and the `DECIMAL(10,2)` is specifying the **data type** of the OUT parameter. (Max digits of 10, with a leading decimal of 2) *** Now that you know a little bit more, let's see a example! ```sql CREATE PROCEDURE update_data( IN uid INT, INOUT amount FLOAT) BEGIN DECLARE uBalance FLOAT; SELECT balance INTO uBalance FROM accounts WHERE id=uid; IF amount > uBalance THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT="Insufficient balance"; ELSE UPDATE accounts SET balance= uBalance-amount WHERE id=uid; END IF; SELECT balance into amount from accounts WHERE id= uid; END // ``` The DECLARE uBalance FLOAT; is used to declare a local variable to store the user balance in that procedure. The SIGNAL SQLSTATE '45000' SET MESSAGE\_TEXT = "Insufficient balance"; is used to throw an error. The SELECT balance INTO amount FROM accounts WHERE id = uid; puts the new balance into the INOUT amount. Nice! We learnt quite a bit about stored procedures so let's try to make one for this project. *** ### Creating the Procedure {% hint style="info" %} Note, I know this can be optimized greatly by using row indexes, but from the nature of this project, I don't think it's necessary as we would call this procedure only a few times a day. Additionally, I will be updating the code along the way, so previous screen shots likely won't match screenshots later on this blog. The most updated code will be found on the bottom. {% endhint %} Before creating the procedure, I realized the fake dataset I created were over the max characters I set in the License Table Schema. So I needed to fix that by creating additional UPDATE statements (since just changing the SQL code won't fix it)
Let's move on to actually creating the procedure.
We first start with creating the delimiter (not shown in the screenshot) and the statement. We then follow it up with some parameters, and 2 OUT variables. These will be used to display the status of exchange, on whether it was successful or not.
The next part is declaring temporary variables. I needed like a local variable to act as a placeholder to store the count of licenses and the actual license itself. To check if there's remaining license, I just did a simple count in the license table, and inserted whatever results came out of it into temp\_LicenseCount. The next segment checked if was 0. If it was, then it set the OUT variables to tell that there's no more licenses.
The next part is just simply updating/inserting data in the License/User table. Nothing too fancy, but it gets kind of confusing managing all of these variables. However, one thing to note is the LIMIT 1. If we didn't include the LIMIT 1, we would've gotten all the rows that matched the WHERE statement. But we just need 1 license for it to work. A few things I didn't implement were a way to continuously check if it's been a year since the user received the license. If so, their license needed to be And that's basically it in regards of creating the main procedure. However, we should probably create a few more procedures.
This works as we put in 3 variables, and using them we just perform a count to where the variables match up. We then group them, so each parameter we put has their own counts. *** ### Final Code At the end we're left with 2 blocks of code. ```sql -- Creating the database/procedures CREATE DATABASE IF NOT EXISTS ServerExample; USE ServerExample; -- User Table CREATE TABLE IF NOT EXISTS USERS_table ( id INT AUTO_INCREMENT PRIMARY KEY, NETID VARCHAR(9) NOT NULL, FirstName VARCHAR(16), LastName VARCHAR(16), ApplicationName VARCHAR(10), LicenseKey VARCHAR(45), DateIssued DATE, SNOWCaseNumber VARCHAR(10) ); -- License Table CREATE TABLE IF NOT EXISTS LICENSE_table ( id INT AUTO_INCREMENT PRIMARY KEY, ProductType ENUM('1', '2') NOT NULL, ProductVersion VARCHAR(15), Version VARCHAR(5), LicenseKey VARCHAR(45) UNIQUE NOT NULL, IsAssigned BOOLEAN DEFAULT FALSE, AssignedDate DATE NULL ); -- Example -- -- Stored Procedure to Assign License to User DELIMITER $$ DROP PROCEDURE IF EXISTS AssignLicenseToUser$$ CREATE PROCEDURE AssignLicenseToUser( #State the parameters/variables needed IN userNETID VARCHAR(8), IN userFirstName VARCHAR(16), IN userLastName VARCHAR(16), IN userApplicationName VARCHAR(10), IN userProductType ENUM('1', '2'), IN userProductVersion VARCHAR(15), IN userVersion VARCHAR(5), IN userSNOWCaseNumber VARCHAR(10), OUT userAssignedLicenseKey VARCHAR(45), OUT userStatusMessage VARCHAR(255) ) BEGIN -- Declare Temporary Variables DECLARE temp_LicenseKey VARCHAR(45); DECLARE temp_LicenseCount INT; -- Find if there's remaining licenses SELECT COUNT(*) INTO temp_LicenseCount FROM LICENSE_table WHERE ProductType = userProductType AND ProductVersion = userProductVersion AND Version = userVersion AND IsAssigned = FALSE LIMIT 1; -- Set OUT parameters to not found IF temp_LicenseCount = 0 THEN SET userStatusMessage = CONCAT('Error: No available licenses for ProductType ', userProductType, ' Version ', userProductVersion); SET userAssignedLicenseKey = NULL; -- Get an available license key ELSE SELECT LicenseKey INTO temp_LicenseKey FROM LICENSE_table WHERE ProductType = userProductType AND ProductVersion = userProductVersion AND Version = userVersion AND IsAssigned = FALSE LIMIT 1; -- Update the License Table UPDATE LICENSE_table SET IsAssigned = TRUE, AssignedDate = CURDATE() WHERE LicenseKey = temp_LicenseKey; -- Insert into the User Table INSERT INTO USERS_table ( NETID, FirstName, LastName, ApplicationName, LicenseKey, DateIssued, SNOWCaseNumber ) VALUES ( userNETID, userFirstName, userLastName, userApplicationName, temp_LicenseKey, CURDATE(), userSNOWCaseNumber ); -- Set output parameters SET userAssignedLicenseKey = temp_LicenseKey; SET userStatusMessage = 'Success: License assigned successfully'; -- Prints it out (this part comes from the call statement, which correlates to the user parameters)) SELECT @licenseKey AS AssignedLicenseKey, @status AS StatusMessage; END IF; END$$ DELIMITER ; -- Procedure to Check Available Licenses DELIMITER $$ DROP PROCEDURE IF EXISTS CheckAvailableLicenses$$ CREATE PROCEDURE CheckAvailableLicenses( IN userProductType ENUM('1', '2'), IN userProductVersion VARCHAR(15), IN userVersion VARCHAR(5) ) BEGIN SELECT ProductType, ProductVersion, Version, COUNT(*) AS AvailableCount FROM LICENSE_table WHERE ProductType = userProductType AND ProductVersion = userProductVersion AND Version = userVersion AND IsAssigned = FALSE GROUP BY ProductType, ProductVersion, Version; END$$ DELIMITER ; ``` ```sql USE ServerExample; INSERT INTO LICENSE_table (ProductType, ProductVersion, Version, LicenseKey) VALUES ('1','ProductA30','30','ABCD1-EFGH2-IJKL3-MNOP4-QRS5'), ('1','ProductA30','30','WXYZ6-ABCD7-EFGH8-IJKL9-MNO0'), ('1','ProductA30','30','PQRS1-TUVW2-XYZA3-BCDE4-FGH5'), ('1','ProductA30','30','IJKL6-MNOP7-QRST8-UVWX9-YZA0'), ('1','ProductA29','30','BCDE1-FGHI2-JKLM3-NOPQ4-RST5'), ('1','ProductA29','29','UVWX6-YZAB7-CDEF8-GHIJ9-KLM0'), ('1','ProductA29','29','NOPQ1-RSTU2-VWXY3-ZABC4-DEF5'), ('2','ProductB29','29','GHIJ6-KLMN7-OPQR8-STUV9-WXY0'); CALL AssignLicenseToUser( 'slee4852', -- NETID 'Simon', -- FirstName 'Lee', -- LastName 'ProductA', -- ApplicationName '1', -- ProductType 'ProductA30', -- ProductVersion '30', -- Version 'INC0012345', -- SNOWCaseNumber @licenseKey, -- OUT: Assigned license key @status -- OUT: Status message ); -- Shows the License that got assigned SELECT * FROM LICENSE_table WHERE IsAssigned = TRUE; -- Shows the user SELECT * FROM USERS_table WHERE NETID = 'slee4852'; ``` If we run the code we can then see the results!
If you're asking about the null row, I'm guessing it's a placeholder row to allow inline editing or insertion. So I'm assuming it should be fine... This code isn't optimized at all since I'm a fairly new beginner, but this taught me a lot about SQL, and especially about procedures. This code is likely missing a bit of security features, but it shouldn't be too much of a worry since it'll really only be used by higher staff. Additionally, I'll need to adjust a lot of the names and VARCHAR() after this. Hope you learned just a little bit!