In this document, we will demonstrate the interaction between a cybersecurity expert and SQL databases. In this scenario, we will be looking at discovering possible security issues involving log-in attempts and employee machines. We will review data from an organization database and use various filter functions such as AND, OR, and NOT to allow for easier consumption and assessment of data gathered from the database.
In this request, we asked the MariaDB to look in the organization database, within the log_in_attempts table, to find all log-ins that happened after 6:00 PM and all of the attempts that resulted in a failure “0” result.
We can see that there are 19 different attempts shown in this result.
In this next example, we will focus on the date of 05-09-2022 as the date a suspicious event occurred. We will run a request in SQL to review all log-in attempts that occurred between 05-08-2022 and 05-09-2022 to better picture the events that occurred.
We will use the query: SELECT * FROM log_in_attempts WHERE login_date = '22-05-09' OR login_date = '22-05-08';
We can review all 75 instances of both failed and successful log-in attempts between 05-08-2022 and 05-09-2022.
In this next query, we will run a request to find all log_in_attempts that have not been attempted in Mexico. We will use the line: SELECT * FROM log_in_attempts WHERE NOT country LIKE 'MEX%';
We will use the % wildcard to assume all entries that are from “MEX” and “MEXICO” both mean Mexico.
We ended up with 144 results, which are all of the login attempts that occurred outside of Mexico.
Next, we will look at all employees in the Marketing department. We will be using the employees table. We will query using the following request:
SELECT * FROM employees WHERE department = 'Marketing';
Very similar to the previous example of filtering just one department, we will instead look at Finance or the Sales department.
We will query with the following request:
SELECT * FROM employees WHERE department = 'Finance' OR department = 'Sales';
Lastly, in the employees table, we will query all employees that are not in the IT department with the following query:
The query results in 161 results, only showing the departments that are all except ‘Information Technology’.
Overall, as a cybersecurity professional, it is important to understand the core concept of SQL to allow yourself to be able to fully unlock the entire toolkit of filters and outputs that the database will provide. Understanding filters will assist cybersecurity professionals in better understanding the data provided and allows for users to hone in on details that will make their organization's databases and networks formidable entities.