Google Cybersecurity: SQL Query Filters

Walked through SQL filters, compound conditions, pattern matching, and subqueries to isolate suspicious network and access-log records in illustrative coursework data; query matches alone did not establish compromise.

On this page
  1. Project Overview
  2. Scenario
  3. Implementation
    4 subsections
    1. Basic Filtering with WHERE Clause
    2. Using Multiple Conditions with AND/OR
    3. Using the LIKE Operator for Pattern Matching
    4. Advanced Filtering with Subqueries
  4. Security Findings
    1 subsections
    1. Key Findings
  5. Conclusion

Wide tables and code examples scroll sideways. Focus a block to scroll with the keyboard.

Project Overview

This coursework walkthrough presents SQL filters, operators, and query examples for extracting security-relevant records. The sample tables illustrate filtering behavior; they do not establish production experience, a dataset-size benchmark, or unaided query authorship.

By applying specific filters to SQL queries, security professionals can isolate suspicious activities, detect anomalies, and generate targeted reports from security logs and databases.

Scenario

In the training scenario, an analyst reviews network and access logs for records that need further investigation. The examples cover how to:

  • Filter traffic from specific IP addresses flagged as suspicious
  • Identify unusual access patterns during non-business hours
  • Generate summary reports of failed login attempts
  • Detect potential data exfiltration by analyzing outbound traffic volumes

Implementation

The walkthrough uses the following SQL filtering techniques:

Basic Filtering with WHERE Clause

Query to identify all traffic from a suspicious IP address:

mysql> SELECT * 
    -> FROM network_logs 
    -> WHERE source_ip = '192.168.1.137'
    -> ORDER BY timestamp DESC;

34 rows returned (showing first 3)

id timestamp source_ip destination_ip port protocol bytes_sent
1082437 2024-05-03 02:14:28 192.168.1.137 203.0.113.42 22 SSH 4096
1082302 2024-05-03 02:12:15 192.168.1.137 203.0.113.42 22 SSH 8192
1082105 2024-05-03 01:57:33 192.168.1.137 203.0.113.42 22 SSH 2048

Using Multiple Conditions with AND/OR

Identify potential unauthorized access during non-business hours:

mysql> SELECT * 
    -> FROM access_logs 
    -> WHERE (HOUR(access_time) < 8 OR HOUR(access_time) > 18)
    -> AND access_status = 'success'
    -> AND user_role != 'admin'
    -> ORDER BY access_time;

17 rows returned (showing first 3)

log_id user_id access_time resource_accessed access_status user_role
45291 user047 2024-05-02 05:12:47 /admin/config success standard
45387 user023 2024-05-02 19:34:12 /reports/security success standard
45412 user047 2024-05-02 22:03:19 /admin/users success standard

Using the LIKE Operator for Pattern Matching

Find attempts to access sensitive directories:

mysql> SELECT COUNT(*) as attempt_count, user_id, resource_accessed
    -> FROM access_logs
    -> WHERE resource_accessed LIKE '%/admin/%' 
    -> OR resource_accessed LIKE '%/config/%'
    -> OR resource_accessed LIKE '%/secure/%'
    -> GROUP BY user_id, resource_accessed
    -> HAVING attempt_count > 5
    -> ORDER BY attempt_count DESC;

4 rows returned

attempt_count user_id resource_accessed
27 user047 /admin/config
14 user023 /admin/users
8 user015 /secure/keys
6 user047 /config/database

Advanced Filtering with Subqueries

Identify users with failed login attempts followed by successful access:

mysql> SELECT DISTINCT l1.user_id, l1.ip_address, l1.timestamp 
    -> as failed_time, l2.timestamp as success_time
    -> FROM login_logs l1
    -> JOIN login_logs l2 ON l1.user_id = l2.user_id AND l1.ip_address = l2.ip_address
    -> WHERE l1.status = 'failed' 
    -> AND l2.status = 'success'
    -> AND l2.timestamp > l1.timestamp
    -> AND TIMESTAMPDIFF(MINUTE, l1.timestamp, l2.timestamp) < 30
    -> ORDER BY l1.user_id, l1.timestamp;

7 rows returned (showing first 3)

user_id ip_address failed_time success_time
user015 192.168.1.54 2024-05-03 14:23:42 2024-05-03 14:24:15
user023 192.168.1.78 2024-05-02 19:30:58 2024-05-02 19:34:12
user047 192.168.1.137 2024-05-03 01:45:27 2024-05-03 01:47:38

Security Findings

The example query results illustrate records that could prompt further review:

Key Findings

  • Potential Brute Force Attempts: Identified multiple failed login attempts followed by successful logins within short time periods
  • Unauthorized Access Patterns: Detected unusual access patterns from non-admin users attempting to access sensitive resources
  • Data Exfiltration Indicators: Found suspicious outbound data transfers during non-business hours

These example records would prompt further investigation of user047. Query matches alone do not establish account compromise or data exfiltration.

Conclusion

The examples show how filters, pattern matching, and joins narrow a set of records for investigation. They support a coursework explanation of query behavior, with findings kept separate from conclusions that would require additional evidence.