Find Invalid IP Addresses - Practice Coding Problems

Find Invalid IP Addresses - Problem

Database Hard

You're working as a security analyst reviewing server logs to identify potentially malicious traffic. One crucial step is validating IP addresses in the access logs to ensure data quality and security.

Given a table logs containing server access information, you need to identify all invalid IPv4 addresses and count how many times each appears.

An IPv4 address is considered invalid if it meets ANY of these conditions:

Contains numbers greater than 255 in any octet (e.g., 192.168.1.300)
Has leading zeros in any octet (e.g., 192.168.01.1)
Has less or more than 4 octets (e.g., 192.168.1 or 192.168.1.1.1)

Your task is to return all invalid IP addresses along with their occurrence count, ordered by invalid_count and ip in descending order.

Input & Output

example_1.sql — Basic Invalid IPs

$ Input: logs table: +--------+---------------+-------------+ | log_id | ip | status_code | +--------+---------------+-------------+ | 1 | 192.168.1.300 | 200 | | 2 | 192.168.01.1 | 404 | | 3 | 192.168.1.1 | 200 | | 4 | 192.168.1.300 | 500 | +--------+---------------+-------------+

› Output: +---------------+---------------+ | ip | invalid_count | +---------------+---------------+ | 192.168.1.300 | 2 | | 192.168.01.1 | 1 | +---------------+---------------+

💡 Note: 192.168.1.300 appears twice and is invalid (300 > 255). 192.168.01.1 appears once and is invalid (leading zero in '01'). 192.168.1.1 is valid and not included.

example_2.sql — Various Invalid Patterns

$ Input: logs table: +--------+-----------------+-------------+ | log_id | ip | status_code | +--------+-----------------+-------------+ | 1 | 192.168.1 | 200 | | 2 | 192.168.1.1.1 | 404 | | 3 | 192.168..1 | 403 | | 4 | 192.168.1 | 500 | +--------+-----------------+-------------+

› Output: +---------------+---------------+ | ip | invalid_count | +---------------+---------------+ | 192.168.1 | 2 | | 192.168.1.1.1 | 1 | | 192.168..1 | 1 | +---------------+---------------+

💡 Note: 192.168.1 has only 3 octets (appears 2 times). 192.168.1.1.1 has 5 octets. 192.168..1 has empty octet between dots.

example_3.sql — Edge Cases

$ Input: logs table: +--------+---------------+-------------+ | log_id | ip | status_code | +--------+---------------+-------------+ | 1 | 000.000.000.000| 200 | | 2 | 255.255.255.255| 404 | | 3 | 256.1.1.1 | 403 | +--------+---------------+-------------+

› Output: +---------------+---------------+ | ip | invalid_count | +---------------+---------------+ | 256.1.1.1 | 1 | | 000.000.000.000| 1 | +---------------+---------------+

💡 Note: 000.000.000.000 is invalid due to leading zeros. 255.255.255.255 is valid (boundary case). 256.1.1.1 is invalid (256 > 255).

Constraints

1 ≤ number of log entries ≤ 10⁵
1 ≤ log_id ≤ 10⁹
1 ≤ ip.length ≤ 15
100 ≤ status_code ≤ 599
IP addresses may contain any characters, not just valid IPv4 format

Visualization

Tap to expand

Understanding the Visualization

Input Stream

Log entries flow into the validation pipeline

Pattern Gates

Each IP passes through validation checkpoints

Classification

IPs are classified as valid or invalid

Counting

Invalid IPs are counted and sorted

Key Takeaway

🎯 Key Insight: SQL pattern matching allows parallel validation of all rules in O(n) time, making it far more efficient than sequential character-by-character parsing.

Asked in

G Google 23 a Amazon 31 f Meta 18 ⊞ Microsoft 15

The optimal solution uses SQL pattern matching with REGEXP and built-in functions to validate IP addresses in a single query. Key techniques include checking octet count with string length manipulation, using REGEXP for leading zeros and invalid characters, and CAST operations for range validation. This achieves O(n) time complexity with database optimization.

Common Approaches

Approach	Time	Space	Notes
✓ SQL Pattern Matching (Optimal)	O(n)	O(k)	Use SQL REGEXP and string functions to efficiently validate and count in one query
Brute Force String Parsing	O(n * m)	O(n)	Parse each IP address character by character to validate all rules

SQL Pattern Matching (Optimal) — Algorithm Steps

Use REGEXP to check for invalid patterns
Split IP by dots using string functions
Validate octet count and numeric ranges
Group by IP and count occurrences
Order by count and IP in descending order

Visualization

Tap to expand

Step-by-Step Walkthrough

Pattern compilation

SQL compiles all regex patterns for efficient matching

Parallel validation

All validation rules checked simultaneously

Aggregation

Group by IP and count occurrences in one pass

Sorting

Order by count and IP using database indexes

Code -

solution.c — C

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <regex.h>

const char* SQL_QUERY = "\
SELECT \
    ip, \
    COUNT(*) as invalid_count \
FROM logs \
WHERE \
    (LENGTH(ip) - LENGTH(REPLACE(ip, '.', ''))) != 3 \
    OR ip REGEXP '(^|\\.)0[0-9]' \
    OR ip REGEXP '[^0-9.]' \
    OR ip REGEXP '(\\.\\.|^\\.|\\.$)' \
    OR ( \
        CAST(SUBSTRING_INDEX(ip, '.', 1) AS UNSIGNED) > 255 \
        OR CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(ip, '.', 2), '.', -1) AS UNSIGNED) > 255 \
        OR CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(ip, '.', 3), '.', -1) AS UNSIGNED) > 255 \
        OR CAST(SUBSTRING_INDEX(ip, '.', -1) AS UNSIGNED) > 255 \
    ) \
GROUP BY ip \
ORDER BY invalid_count DESC, ip DESC";

typedef struct {
    regex_t ipBasic;
    regex_t leadingZeros;
    regex_t nonDigits;
    regex_t emptyOctets;
} Patterns;

Patterns* initPatterns() {
    Patterns* p = malloc(sizeof(Patterns));
    regcomp(&p->ipBasic, "^([0-9]{1,3}\\.){3}[0-9]{1,3}$", REG_EXTENDED);
    regcomp(&p->leadingZeros, "(^|\\.)0[0-9]", REG_EXTENDED);
    regcomp(&p->nonDigits, "[^0-9.]", REG_EXTENDED);
    regcomp(&p->emptyOctets, "(\\.\\.|^\\.|\\.$)", REG_EXTENDED);
    return p;
}

void freePatterns(Patterns* p) {
    regfree(&p->ipBasic);
    regfree(&p->leadingZeros);
    regfree(&p->nonDigits);
    regfree(&p->emptyOctets);
    free(p);
}

int isInvalidIPOptimized(const char* ip, Patterns* patterns) {
    if (!ip || strlen(ip) == 0) return 1;
    
    if (regexec(&patterns->ipBasic, ip, 0, NULL, 0) != 0) return 1;
    if (regexec(&patterns->leadingZeros, ip, 0, NULL, 0) == 0) return 1;
    if (regexec(&patterns->nonDigits, ip, 0, NULL, 0) == 0) return 1;
    if (regexec(&patterns->emptyOctets, ip, 0, NULL, 0) == 0) return 1;
    
    char temp[20];
    strcpy(temp, ip);
    
    char* octets[4];
    int octetCount = 0;
    
    char* token = strtok(temp, ".");
    while (token != NULL && octetCount < 4) {
        octets[octetCount++] = token;
        token = strtok(NULL, ".");
    }
    
    if (octetCount != 4) return 1;
    
    for (int i = 0; i < 4; i++) {
        int num = atoi(octets[i]);
        if (num > 255) return 1;
    }
    
    return 0;
}

int findInvalidIPsOptimal(LogEntry* logs, int logCount, Result* result) {
    Patterns* patterns = initPatterns();
    
    char uniqueIPs[1000][20];
    int counts[1000];
    int uniqueCount = 0;
    
    for (int i = 0; i < logCount; i++) {
        if (isInvalidIPOptimized(logs[i].ip, patterns)) {
            int found = 0;
            for (int j = 0; j < uniqueCount; j++) {
                if (strcmp(uniqueIPs[j], logs[i].ip) == 0) {
                    counts[j]++;
                    found = 1;
                    break;
                }
            }
            if (!found) {
                strcpy(uniqueIPs[uniqueCount], logs[i].ip);
                counts[uniqueCount] = 1;
                uniqueCount++;
            }
        }
    }
    
    for (int i = 0; i < uniqueCount; i++) {
        strcpy(result[i].ip, uniqueIPs[i]);
        result[i].invalid_count = counts[i];
    }
    
    qsort(result, uniqueCount, sizeof(Result), compareResults);
    
    freePatterns(patterns);
    return uniqueCount;
}

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single pass through data with optimized SQL functions

✓ Linear Growth

Space Complexity

O(k)

Where k is the number of unique invalid IPs

✓ Linear Space

43.6K Views

High Frequency

~25 min Avg. Time

1.8K Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

SQL Pattern Matching (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler