SQL

Query Databases with SQL

  • Definition of Query: A query is a request for data from a database table or a combination of tables.

  • Importance of SQL: Nearly all relational databases rely on some version of SQL to query data, with minor differences in structure.

  • SQL as a Tool for Security Analysts:

    • Vital for retrieving logs, which are records of events within organizational systems.

    • Essential for identifying improperly configured machines or analyzing website visitor patterns for malicious activity.

  • Efficiency with Large Data Sets:

    • SQL enables analysts to search through millions of data points and extract relevant information quickly, turning complex data retrieval into simple queries that take seconds.

  • Basic Data Analytics:

    • SQL is also used for basic data analytics, which aids in making informed security-related decisions.

    • Analysts can identify unpatched machines, critical for maintaining security against attacks.

    • Helps determine optimal times for machine updates based on usage patterns.

  • Hands-on Experience: The upcoming sessions will focus on making basic queries to a sample database, providing practical SQL experience.

SQL Filtering VS Linux Filtering

Accessing SQL via Linux Command Line

  • Multiple interfaces and versions available for SQL access.

  • Use Linux command line to access SQL; enter command specific to the desired SQL version (e.g., sqlite3 for SQLite).

  • Subsequent commands will interact with SQL rather than Linux.

Differences Between Linux and SQL Filtering

  • Purpose:

    • Linux: Filters data within files and directories; useful for searching files, adjusting permissions, and managing processes.

    • SQL: Filters data within a database management system; utilized for querying and manipulating table-stored data based on criteria.

  • Syntax:

    • Linux: Utilizes a variety of commands and options specific to each filtering tool (e.g., find, sed, cut, grep).

    • SQL: Uses Structured Query Language (SQL), a standardized language with keywords/clauses such as WHERE, SELECT, JOIN.

  • Structure:

    • SQL: Provides a more organized structure with data separated into columns, facilitating easier analysis.

    • Linux: Outputs data as plain text, leading to less organization and more difficulty in column-specific analysis.

  • Joining Tables:

    • SQL allows for joining multiple tables for complex queries essential for security-related assessments, while Linux lacks this capability, limiting data integration.

Best Use Cases

  • Security analysts must discern appropriate tool usage:

    • SQL is beneficial for structured data in databases.

    • Use Linux for filtering data in formats unsuitable for SQL (e.g., plain text files).

Key Takeaways

  • Linux filtering is tailored to managing files and directories, while SQL focuses on comprehensive data manipulation within structured databases.

  • SQL boasts advantages in data structuring and the ability to join multiple tables, making it suitable for more complex data analysis tasks.

BASIC QUERIES

  • Objective: Determine which computer is assigned to a specific employee using the employees table.

  • Employees Table Structure: Contains five columns; relevant columns for this query are employee_id and device_id.

Key SQL Keywords

  • SELECT: Specifies which columns to return in the query result.

  • FROM: Indicates the table from which to retrieve data.

    • Example: You can ask a friend to "select apples and bananas" akin to how SELECT functions in SQL.

Writing the Query

  1. Compose the SQL statement:

    • SELECT employee_id, device_id FROM employees;

  2. Keywords are not case-sensitive (e.g., select can be written as SELECT).

  3. Semicolons are necessary at the end of SQL statements, indicating completion.

Running the Query

  • Execute the query to retrieve the assigned computer information. The output will contain data that matches employees to their assigned devices.

Expanding the Query

  • To obtain additional information (like department or username), an asterisk (*) can be used to select all columns:

    • Example: SELECT * FROM employees;

BASIC FILTERING ON SQL QUERIES

  • Filtering Data: Selecting data that matches specific conditions in SQL queries.

    • Example: Choosing only fresh apples from a fruit cart.

    • In security analysis, filtering log-in attempts can reveal attempts from a specific country (e.g., Canada).

  • Operators in SQL: Symbols or keywords representing operations in queries.

    • Example: The equal to operator (=) is used to filter records for specific values (e.g., country = 'USA').

  • Using the WHERE Clause: Adds conditions to SQL queries to filter results.

    • Syntax: SELECT columns FROM table WHERE condition;

    • Filters ensure only relevant data (e.g., log-in attempts from the USA) are returned.

  • Pattern Matching: Conditions can be complex, using wildcards for broader searches.

    • Example: To find all offices in the East building, use LIKE with a wildcard (%), as in office LIKE 'East%' . This will search for all the words that start with “East”

  • Combining Multiple Filters: Handle various representations of data efficiently, for example, distinguishing between 'US' and 'USA'.

    • In such cases, use LIKE to cover patterns (e.g., country LIKE 'US%' will return any entries starting with 'US').

  • Precision in Data Queries: SQL allows for accurate data retrieval using single queries that can filter and pattern-search effectively, providing security analysts the ability to extract pertinent information swiftly and accurately.

SQL FILTER DATES AND NUMBERS

  • Overview of Data Types: SQL utilizes three primary data types:

    • String: An ordered sequence of characters for identifiers and usernames (e.g., analyst10).

    • Numeric: Represents numbers that allow for mathematical operations essential for analytics (e.g., counts of log-in attempts).

    • Date and Time: Represents specific dates and times, crucial for time-based filtering in queries.

- **Filtering Numeric and Date Data**:

- **Use Cases**: Useful for security analysts to filter log-in attempts or patch dates. - **Operators**: Commonly used operators for filtering include: - Equals `=` - Greater than `>` - Less than `<` - Not equal to `<>` - Greater than or equal to `>=` - Less than or equal to `<`

  • Filtering for Suspicious Activity

    • To identify log-in attempts occurring after business hours (for instance, after 6 PM), SQL can utilize the greater than operator to establish the desired conditions in the query:

SELECT * FROM log_in_attempts WHERE time > '18:00';

  • Using the BETWEEN Operator

    • The BETWEEN operator is effective for filtering a range of values, which is especially helpful for date ranges.

    • Example: To query for patches that were installed during a specific time frame:

SELECT * FROM machines WHERE OS_patch_date BETWEEN '2021-03-01' AND '2021-09-01';

SQL FILTERS ‘AND, OR, NOT’

  • Filtering for Multiple Conditions:

    • SQL allows for filtering based on multiple conditions, essential for identifying vulnerabilities that may depend on various factors.

    • Example: Finding machines using both a specific email client and a specific operating system.

  • AND Operator:

    • Utilized to specify that both conditions in a query must be met simultaneously.

    • Analogy: Asking to select only large and fresh apples; resulting in outputs that meet both criteria.

    • Example Query:

      SELECT * FROM machines WHERE OS = 'OS 1' AND email_client = 'Email Client 1';

  • OR Operator:

    • Used when either condition can be met, allowing for more flexibility in the query.

    • It selects rows that satisfy at least one of the conditions.

    • Example Query:

      SELECT * FROM machines WHERE OS = 'OS 1' OR OS = 'OS 3';

  • NOT Operator:

    • Negates a condition, returning all entries that do not match the specified criterion.

    • Example Query:

      SELECT * FROM machines WHERE NOT OS = 'OS 3';

  • Conclusion:

    • These filtering techniques enhance SQL's capability for data manipulation, crucial for security analysis.

    • Future lessons will cover combining and joining tables for advanced queries.

SQL JOIN TABLES

  • Purpose of Joining Tables:

    • Combine information from different tables, such as security vulnerabilities and machine details.

    • Create a comprehensive list of vulnerable machines by linking tables.

  • Syntax of Joins:

    • Use the table name followed by a period and the column name (e.g., employees.employee_id).

    • Essential for distinguishing between columns with the same name in different tables.

  • Shared Columns for Joining:

    • Need a primary key in one table (e.g., employee_id in employees) and a foreign key in another (e.g., employee_id in machines).

    • Primary keys must contain unique values without nulls, while foreign keys may not meet these criteria.

  • Types of Joins:

    • INNER JOIN:

      • Returns rows that match on a specified column in both tables.

      • Example: Rows from both tables where employee_id equals 1188 or 1189 are returned.

    • Handling NULL Values:

      • Understand that NULL represents missing values which can affect join results.

  • Constructing a Join Query:

    • Aim to select specific columns (e.g., username, office, operating system) without displaying the join key (e.g., employee_id) in the results.

    • Example Query Structure:

      SELECT username, office, operating_system  
FROM employees  
INNER JOIN machines ON employees.employee_id = machines.employee_id;

    • The query specifies which table to select from and how to join using the common column.

  • Output of the Join:

    • Displays records that match based on the join condition, including only the selected columns.

    • Subsequent lessons will cover other types of joins that do not require matching entries.

SQL Outer Joins

  • There are three types of outer joins: LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.

  • These joins combine two tables together without needing a match between columns to return a row.

LEFT JOIN

  • Description: Returns all records from the first (left) table and only matching rows from the second (right) table on a specified column.

  • Example: If combining an employees table and a machines table on employee_id, SQL will return:

    • Matching records from both tables.

    • All records from the employees table, with NULL values in columns from machines for non-matching entries.

RIGHT JOIN

  • Description: Returns all records from the second (right) table and only matching rows from the first (left) table on a specified column.

  • Example: With the same employees and machines scenario, SQL will:

    • Return all rows from the machines table.

    • Include matching rows from the employees table, with NULL values for non-matching entries.

FULL OUTER JOIN

  • Description: Returns all records from both tables. If no matching row exists in one of the tables, it returns NULL for those columns.

  • Example: In the machines table, if there is no row with a specific employee_id, the output will include the employee's details with NULL values for the missing columns from the machines table.

Syntax for Joins

  • Uses the same structure as INNER JOIN but substitutes the respective join keyword:

    • LEFT JOIN: SELECT columns FROM table1 LEFT JOIN table2 ON condition;

    • RIGHT JOIN: SELECT columns FROM table1 RIGHT JOIN table2 ON condition;

    • FULL OUTER JOIN: SELECT columns FROM table1 FULL OUTER JOIN table2 ON condition;

  • Important for security analysts to understand the appropriate use of joins without memorizing all specifics, as they can refer to resources as needed.