SQL filtering examples for security-related tasks.

apply-filters-to-sql-queries-enhancing-system-security.md

As part of our organisation's ongoing efforts to bolster system security, I led initiatives to identify and mitigate potential vulnerabilities. This involved conducting thorough security audits, addressing emerging threats, and optimising employee workstation configurations.

Introduction to SQL for Security

SQL (Structured Query Language) plays a vital role in security-related tasks by enabling precise data filtering and analysis. In this project, I demonstrate how SQL filters can be applied to streamline security tasks, ensuring the integrity and confidentiality of our systems.

SQL Basics

Before diving into filtering, let's review basic SQL concepts:

• SELECT: Retrieves data from a database table.
• FROM: Specifies the table(s) to query.
• WHERE: Filters data based on conditions.

Filtering Techniques

SQL provides several filtering techniques:

• AND: Combines conditions (e.g., login_time > '18:00' AND success = FALSE).
• OR: Matches either condition (e.g., department = 'Finance' OR department = 'Sales').
• NOT: Excludes conditions (e.g., country NOT LIKE 'MEX%').
• LIKE: Matches patterns using wildcards (%, _).

Retrieve After-Hours Failed Login Attempts

A potential security incident took place after business hours (post 18:00), and it's crucial to investigate all failed login attempts during this time.

SQL query filtering failed login attempts after 18:00 using WHERE clause with AND operator.

SQL Query: Filtering failed login attempts after 18:00 using WHERE clause with AND operator.

SELECT * 
FROM log_in_attempts 
WHERE login_time > '18:00' 
AND success = FALSE;

Retrieving Login Attempts on Specific Dates

In response to a suspicious event on 2022-05-09, I developed a SQL query to investigate any login activity on that date or the preceding day (2022-05-08).

SQL query filtering login attempts on 2022-05-09 and 2022-05-08 using WHERE clause with OR operator.

SQL Query: Filtering login attempts on 2022-05-09 and 2022-05-08 using WHERE clause with OR operator.

SELECT * 
FROM log_in_attempts 
WHERE login_date = '2022-05-09' 
OR login_date = '2022-05-08';

Retrieving Login Attempts Outside of Mexico

Following an examination of the organisation's login attempt data, I identified a potential issue with attempts outside of Mexico.

SQL query filtering login attempts outside Mexico using WHERE clause with NOT and LIKE operators.

SQL Query: Filtering login attempts outside Mexico using WHERE clause with NOT and LIKE operators.

SELECT * 
FROM log_in_attempts 
WHERE NOT country LIKE 'MEX%';

Retrieve Marketing Department Employees

To update computers for specific Marketing department employees, I needed information on which employee machines to update.

SQL query filtering Marketing department employees in East building using WHERE clause with AND operator.

SQL Query: Filtering Marketing department employees in East building using WHERE clause with AND operator.

SELECT * 
FROM employees 
WHERE department = 'Marketing' 
AND office LIKE 'East%';

Retrieve Finance or Sales Department Employees

For updating machines in the Finance and Sales departments with a distinct security update, I created a SQL query to gather information on employees solely from these two departments.

SQL query filtering Finance and Sales department employees using WHERE clause with OR operator.

SQL Query: Filtering Finance and Sales department employees using WHERE clause with OR operator.

SELECT * 
FROM employees 
WHERE department = 'Finance' 
OR department = 'Sales';

Retrieve Employees not in IT

For a final security update on employees outside the Information Technology department, I crafted a SQL query to gather information on these individuals.

SQL query filtering employees not in Information Technology department using WHERE clause with NOT operator.

SQL Query: Filtering employees not in Information Technology department using WHERE clause with NOT operator.

SELECT * 
FROM employees 
WHERE NOT department = 'Information Technology';

Summary

In this project, I demonstrated the effective use of SQL filters to enhance system security. By applying precise filtering techniques, security professionals can streamline tasks, reduce risk, and ensure the integrity of their systems.

Key Takeaways

• SQL filters enable precise data analysis for security-related tasks.
• AND, OR, and NOT operators can be combined for complex filtering.
• LIKE operator with wildcards (%) facilitates pattern matching.