Skip to content

culiops/culiops-skills / skills/sre-investigate

sre-investigate

Unverified

FileKindSize
SKILL.mdskill md71.5 KB
access/standalone.mdmarkdown19.0 KB
agents/evidence.mdmarkdown8.1 KB
agents/helper.mdmarkdown3.9 KB
analytics/bin/logs-fetchexecutablescript229 B
analytics/bin/logs-inventoryexecutablescript209 B
analytics/bin/logs-statusexecutablescript192 B
analytics/bin/sqlqexecutablescript212 B
analytics/lib/s3logs/__init__.pyscript62 B
analytics/lib/s3logs/awscli.pyscript10.0 KB
analytics/lib/s3logs/budget.pyscript9.6 KB
analytics/lib/s3logs/cli_fetch.pyscript34.0 KB
analytics/lib/s3logs/cli_inventory.pyscript7.0 KB
analytics/lib/s3logs/cli_sqlq.pyscript18.4 KB
analytics/lib/s3logs/cli_status.pyscript3.0 KB
analytics/lib/s3logs/config.pyscript7.4 KB
analytics/lib/s3logs/convert.pyscript23.6 KB
analytics/lib/s3logs/duck.pyscript11.4 KB
analytics/lib/s3logs/layout.pyscript8.8 KB
analytics/lib/s3logs/paths.pyscript4.9 KB
analytics/lib/s3logs/redact.pyscript22.1 KB
analytics/lib/s3logs/s5.pyscript9.1 KB
analytics/lib/s3logs/sqlguard.pyscript11.8 KB
analytics/lib/s3logs/validate.pyscript4.4 KB
analytics/logs.env.exampleother4.2 KB
analytics/sql/alb/by_minute.sqlother918 B
analytics/sql/alb/lb_vs_target_status.sqlother629 B
analytics/sql/alb/status_by_target.sqlother887 B
analytics/sql/alb/summary.sqlother1.2 KB
analytics/sql/alb/target_latency_pct.sqlother1.4 KB
analytics/sql/alb/top_clients.sqlother429 B
analytics/sql/alb/top_paths_5xx.sqlother673 B
analytics/sql/cloudfront/aborted.sqlother1.1 KB
analytics/sql/cloudfront/by_day.sqlother499 B
analytics/sql/cloudfront/by_hour.sqlother489 B
analytics/sql/cloudfront/by_object.sqlother544 B
analytics/sql/cloudfront/bytes_per_viewer.sqlother1.1 KB
analytics/sql/cloudfront/cache_results.sqlother823 B
analytics/sql/cloudfront/client_concentration.sqlother707 B
analytics/sql/cloudfront/edges.sqlother434 B
analytics/sql/cloudfront/object_by_day.sqlother599 B
analytics/sql/cloudfront/range_requests.sqlother528 B
analytics/sql/cloudfront/referer_pages.sqlother393 B
analytics/sql/cloudfront/status_codes.sqlother342 B
analytics/sql/cloudfront/summary.sqlother719 B
analytics/sql/cloudfront/top_clients.sqlother353 B
analytics/sql/cloudfront/top_objects.sqlother349 B
analytics/sql/cloudfront/top_referers.sqlother492 B
analytics/sql/cloudfront/user_agents.sqlother533 B
analytics/sql/waf/asns.sqlother378 B
analytics/sql/waf/by_day.sqlother453 B
analytics/sql/waf/by_minute.sqlother314 B
analytics/sql/waf/client_keys.sqlother443 B
analytics/sql/waf/clients.sqlother521 B
analytics/sql/waf/compare_days.sqlother924 B
analytics/sql/waf/concentration.sqlother775 B
analytics/sql/waf/count_rules.sqlother556 B
analytics/sql/waf/countries.sqlother373 B
analytics/sql/waf/fingerprints.sqlother424 B
analytics/sql/waf/header_shape.sqlother425 B
analytics/sql/waf/labels.sqlother347 B
analytics/sql/waf/matched_data.sqlother530 B
analytics/sql/waf/rules_by_minute.sqlother497 B
analytics/sql/waf/summary.sqlother735 B
analytics/sql/waf/top_rules.sqlother361 B
analytics/sql/waf/uris.sqlother402 B
analytics/sql/waf/user_agents.sqlother557 B
analytics/tests/conftest.pyscript4.7 KB
analytics/tests/gen_fixtures.pyscript19.7 KB
analytics/tests/stubs/awsexecutablescript4.9 KB
analytics/tests/stubs/s5cmdexecutablescript4.5 KB
analytics/tests/test_awscli.pyscript16.0 KB
analytics/tests/test_budget.pyscript13.3 KB
analytics/tests/test_config.pyscript9.1 KB
analytics/tests/test_convert.pyscript25.9 KB
analytics/tests/test_duck.pyscript10.6 KB
analytics/tests/test_fetch.pyscript42.8 KB
analytics/tests/test_gen_fixtures.pyscript1.9 KB
analytics/tests/test_inventory.pyscript12.9 KB
analytics/tests/test_layout.pyscript9.8 KB
analytics/tests/test_named_queries.pyscript26.7 KB
analytics/tests/test_paths.pyscript3.2 KB
analytics/tests/test_redact.pyscript32.0 KB
analytics/tests/test_s5.pyscript17.8 KB
analytics/tests/test_sqlguard.pyscript10.8 KB
analytics/tests/test_sqlq.pyscript37.2 KB
analytics/tests/test_status.pyscript5.8 KB
analytics/tests/test_validate.pyscript2.9 KB
environment.template.mdmarkdown7.1 KB
playbooks/changes.mdmarkdown37.2 KB
playbooks/errors.mdmarkdown10.4 KB
playbooks/external.mdmarkdown15.4 KB
playbooks/infra-state.mdmarkdown13.7 KB
playbooks/logs-s3.mdmarkdown29.5 KB
playbooks/logs.mdmarkdown21.3 KB
playbooks/metrics.mdmarkdown39.0 KB
playbooks/other-backends.mdmarkdown7.2 KB
playbooks/traces.mdmarkdown16.2 KB
playbooks/waf.mdmarkdown17.2 KB
templates/incident.mdmarkdown3.9 KB
templates/ledger.mdmarkdown9.7 KB
templates/lesson.mdmarkdown2.7 KB
templates/profile.mdmarkdown4.5 KB
templates/report.mdmarkdown6.5 KB

analytics/tests/test_sqlguard.py

View on GitHub
"""Layer 1 of the SQL defences: tokenizer, statement shape, deny keywords and functions."""
from __future__ import annotations

import pytest

from s3logs import sqlguard
from s3logs.validate import Refused

DENY_KEYWORDS = ("COPY ATTACH DETACH INSTALL LOAD PRAGMA SET RESET EXPORT IMPORT CREATE INSERT UPDATE "
                 "DELETE DROP ALTER CALL CHECKPOINT VACUUM USE PREPARE EXECUTE").split()


def refused(sql, check=sqlguard.check):
    with pytest.raises(Refused) as e:
        check(sql)
    return str(e.value)


# ---- statement shape ------------------------------------------------------------------------

def test_refuses_second_statement():
    refused("SELECT 1; DROP TABLE x")
    refused("SELECT 1; SELECT 2")
    refused(";SELECT 1")
    refused("SELECT 1;;")
    refused("SELECT 1; -- c\nSELECT 2")


def test_one_trailing_semicolon_allowed_and_stripped():
    assert sqlguard.check("SELECT 1;") == "SELECT 1"
    assert sqlguard.check("  SELECT 1 ;  \n") == "SELECT 1"
    assert sqlguard.check("SELECT 1; -- done\n") == "SELECT 1"
    assert sqlguard.check("SELECT 1; /* done */") == "SELECT 1"


def test_semicolon_inside_string_ok():
    assert sqlguard.check("SELECT ';' AS x") == "SELECT ';' AS x"
    assert sqlguard.check("SELECT 'a'';b' AS x") == "SELECT 'a'';b' AS x"
    assert sqlguard.check('SELECT 1 AS "a;b"') == 'SELECT 1 AS "a;b"'


def test_comment_hiding_keyword_refused():
    refused("SELECT 1 /* x */; COPY (SELECT 1) TO '/tmp/x'")
    refused("SELECT 1 /* x */ COPY (SELECT 1) TO '/tmp/x'")
    refused("SELECT 1 -- x\n; COPY (SELECT 1) TO '/tmp/x'")
    refused("/* leading */ COPY (SELECT 1) TO '/tmp/x'")


def test_keywords_in_comments_and_strings_do_not_trigger():
    assert sqlguard.check("SELECT 'COPY', 'read_csv(x)' -- DROP TABLE x") == "SELECT 'COPY', 'read_csv(x)'"
    assert sqlguard.check("SELECT /* SET x */ 1") == "SELECT   1"
    assert sqlguard.check('SELECT "set", "copy" FROM logs') == 'SELECT "set", "copy" FROM logs'


def test_comment_is_replaced_by_whitespace_not_glued():
    assert sqlguard.check("SELECT 1/**/+/**/2") == "SELECT 1 + 2"
    # a trailing line comment cannot swallow whatever the caller appends
    assert "--" not in sqlguard.check("SELECT 1 -- x")


@pytest.mark.parametrize("first", ["SELECT 1", "select 1", "WITH a AS (SELECT 1) SELECT * FROM a",
                                   "DESCRIBE logs", "SUMMARIZE logs", "FROM logs", "describe logs"])
def test_allowed_first_keywords(first):
    assert sqlguard.check(first)


@pytest.mark.parametrize("sql", ["INSERT INTO x VALUES (1)", "VALUES (1)", "(SELECT 1)", "EXPLAIN SELECT 1",
                                 "SHOW TABLES", "TABLE logs", "TRUNCATE x", "MERGE INTO x USING y ON 1=1",
                                 "1", "'SELECT'", "", "   ", "-- only a comment", "/* c */"])
def test_other_first_tokens_refused(sql):
    refused(sql)


@pytest.mark.parametrize("kw", DENY_KEYWORDS)
def test_deny_keywords_refused_anywhere(kw):
    refused("SELECT 1 %s" % kw)
    refused("SELECT 1 %s" % kw.lower())
    refused("WITH a AS (SELECT 1) SELECT * FROM a WHERE x IN (%s)" % kw)


def test_refuses_set_and_attach_statements():
    refused("SET enable_external_access=true")
    refused("ATTACH '/tmp/x.db' AS x")
    refused("COPY logs TO '/tmp/x.csv'")
    refused("PRAGMA database_list")
    refused("INSTALL httpfs")
    refused("LOAD httpfs")
    refused("CALL pragma_version()")
    refused("CREATE TABLE x AS SELECT 1")
    refused("WITH x AS (SELECT 1) INSERT INTO t SELECT * FROM x")


# ---- functions -------------------------------------------------------------------------

@pytest.mark.parametrize("sql", [
    "SELECT * FROM read_parquet('/x')",
    "SELECT * FROM READ_CSV('/x')",
    "SELECT * FROM read_csv_auto ('/x')",
    "SELECT * FROM read_text('/x')",
    "SELECT * FROM read_blob('/x')",
    "SELECT * FROM read_json_objects('/x')",
    "FROM glob('*')",
    "FROM GLOB ('*')",
    "SELECT * FROM parquet_scan('/x')",
    "SELECT * FROM csv_scan('/x')",
    "SELECT * FROM anything_scan('/x')",
    "SELECT * FROM sniff_csv('/x')",
    "SELECT * FROM query('select 1')",
    "SELECT * FROM query_table('logs')",
    "SELECT getenv('HOME')",
    "SELECT * FROM main.read_csv('/x')",
    'SELECT * FROM "read_csv"(\'/x\')',
    'SELECT * FROM main."read_csv" (\'/x\')',
    "SELECT * FROM read_csv /* c */ ('/x')",
    "SELECT * FROM read_csv -- c\n('/x')",
    "SELECT * FROM parquet_metadata('/x')",
    "SELECT * FROM duckdb_settings()",
    "SELECT * FROM pragma_database_list()",
    "SELECT current_setting('home_directory')",
    "SELECT json_execute_serialized_sql('{}')",
])
def test_refuses_read_functions(sql):
    refused(sql)


def test_identifiers_named_like_functions_are_not_refused():
    assert sqlguard.check("SELECT read_csv, glob, query, getenv FROM logs")
    assert sqlguard.check("SELECT x AS read_things FROM logs WHERE y GLOB 'a*'")
    assert sqlguard.check("SELECT 'read_csv(' AS s")
    assert sqlguard.check("SELECT count(*), lower(x), getvariable('object') FROM logs")


# ---- lexer agreement with DuckDB ------------------------------------------------------------

@pytest.mark.parametrize("sql", [
    "SELECT E'\\''; COPY (SELECT 1) TO '/tmp/x' --'",
    "SELECT e'a'",
    "SELECT U&'\\0041'",
    'SELECT * FROM U&"read\\005fcsv"(\'/x\')',
    "SELECT $$a$$",
    "SELECT $tag$a$tag$",
    "SELECT $1",
    "SELECT 1 /* a /* b */ COPY x */",
    "SELECT 1 /* never closed",
    "SELECT 'never closed",
    'SELECT "never closed',
    "SELECT 1\x00",
    "SELECT 1\x1b[0m",
    "SELECT 1\x7f",
    "SELECT ('a'",
    "SELECT 1)",
    "SELECT 1) AS q UNION ALL SELECT 2 --",
])
def test_ambiguous_or_malformed_text_refused(sql):
    refused(sql)


def test_dollar_and_e_inside_strings_are_fine():
    assert sqlguard.check("SELECT regexp_matches(x, '^a$') FROM logs")
    assert sqlguard.check("SELECT 'E', 'e''x' AS e FROM logs")
    assert sqlguard.check("SELECT type, ee, x_e FROM logs")      # identifiers ending in e/E next to nothing quoted


@pytest.mark.parametrize("sql", [
    "SELECT 1.FROM '/x'",
    "SELECT 1.getenv()",
    "SELECT 1.read_csv('/x')",
    "SELECT 1.COPY",
    "SELECT 1e5.getenv('HOME')",
    "SELECT 0x1F.read_csv('/x')",
    "SELECT .5.read_csv('/x')",
    "SELECT 1_000.glob('*')",
    "SELECT 0b101.query('select 1')",
    "SELECT 1read_csv('/x')",
])
def test_numbers_do_not_swallow_following_words(sql):
    refused(sql)


def test_ordinary_numbers_still_lex():
    for sql in ("SELECT 1, 1.5, .5, 1e5, 1.5E-3, 0x1F, 0b101, 1_000, 5. FROM logs",
                "SELECT x FROM logs WHERE a > 10 AND b < .25 LIMIT 5"):
        assert sqlguard.check(sql)


@pytest.mark.parametrize("sql", [
    "SELECT * FROM logs, '/path/x.csv'",
    "SELECT * FROM logs , '/path/x.csv'",
    "SELECT * FROM logs AS l, '/path/x.csv' AS r",
    "SELECT * FROM logs, \"/path/x.csv\"",
    "SELECT * FROM logs JOIN other ON 1 = 1, '/path/x.csv'",
    "SELECT * FROM (SELECT 1) AS a, '/path/x.csv'",
    "SELECT * FROM logs AS a, logs AS b, '/path/x.csv'",
    "FROM logs, '/path/x.csv' SELECT *",
    "WITH a AS (SELECT 1) SELECT * FROM a, '/path/x.csv'",
    "SELECT * FROM (SELECT * FROM logs, '/path/x.csv') AS q",
    "DESCRIBE '/path/x.csv'",
    "DESCRIBE \"/path/x.csv\"",
    "SUMMARIZE '/path/x.csv'",
    "SUMMARIZE \"x.parquet\"",
    "PIVOT '/path/x.csv' ON a USING sum(b)",
    "UNPIVOT '/path/x.csv' ON a",
    "SELECT * FROM logs PIVOT '/path/x.csv' ON a",
])
def test_string_in_table_position_refused(sql):
    refused(sql)


def test_commas_outside_a_from_clause_are_fine():
    for sql in ("SELECT 'a', 'b', x FROM logs",
                "SELECT x FROM logs WHERE y IN ('a', 'b')",
                "SELECT x FROM logs GROUP BY x, 'k'",
                "SELECT x FROM logs ORDER BY x, 'k'",
                "SELECT x, 'k' FROM logs LIMIT 3",
                "SELECT x FROM logs QUALIFY row_number() OVER (PARTITION BY x, 'k') = 1",
                "SELECT concat(x, ',', 'b') FROM logs",
                "SELECT x FROM logs HAVING count(*) > 1 AND max(y) IN ('a', 'b')",
                "SELECT * FROM logs, logs AS other WHERE other.x IN ('a', 'b')",
                "SELECT * FROM logs UNION ALL SELECT 'a', 'b', 'c', 'd'",
                "WITH a AS (SELECT 1), b AS (SELECT 2) SELECT 'x', 'y' FROM a, b",
                "DESCRIBE logs",
                "FROM logs SELECT 'a', 'b'"):
        assert sqlguard.check(sql), sql


def test_string_or_path_after_from_is_refused():
    refused("SELECT * FROM '/etc/passwd'")
    refused("SELECT * FROM logs JOIN 'x.csv' ON 1=1")
    refused('SELECT * FROM "/etc/passwd"')
    refused('SELECT * FROM "x.parquet"')
    assert sqlguard.check('SELECT * FROM "logs"')
    assert sqlguard.check("SELECT * FROM logs WHERE a = 'FROM' AND b = '/api'")
    assert sqlguard.check("SELECT extract(year FROM ts) FROM logs")


# ---- dot-commands ---------------------------------------------------------------------

@pytest.mark.parametrize("sql", [
    ".shell id",
    "  .shell id",
    "\t.shell id",
    "SELECT 1\n.shell id",
    "SELECT 1\n   .shell id",
    "SELECT 1\r.shell id",
    "SELECT 1\r\n.shell id",
    "SELECT 1\x0b.shell id",
    "SELECT 1\x0c.shell id",
    "SELECT 1\x85.shell id",
    "SELECT 1
.shell id",
    "SELECT 1
.shell id",
    "SELECT 1\x1c.shell id",
    "SELECT 1\n.shell id\n",
    "SELECT 1 /* x\n.shell id */",
    "SELECT 'x\n.shell id'",
    "SELECT 1 -- c\n.shell id",
    "SELECT 1;\n.shell id",
    "SELECT 1;\r.shell id",
    "SELECT 1\n.",
])
def test_dot_command_lines_refused(sql):
    refused(sql)


def test_dot_in_middle_of_line_is_fine():
    assert sqlguard.check("SELECT q.a FROM logs AS q")
    assert sqlguard.check("SELECT 1.5, 'a.b' FROM logs")


def test_dot_line_created_by_comment_removal_is_refused():
    # raw line 2 starts with '/', but once the comment is gone the line would start with '.'
    refused("SELECT q\n/* c */ .a FROM logs AS q")


# ---- limits ---------------------------------------------------------------------------------

def test_overlong_sql_refused():
    refused("SELECT '" + "a" * sqlguard.MAX_SQL_CHARS + "'")


def test_non_string_refused():
    refused(None)       # type: ignore[arg-type]
    refused(b"SELECT 1")  # type: ignore[arg-type]


# ---- named files (light check) --------------------------------------------------------------

def test_named_check_accepts_plain_select_with_comment_header():
    sql = "-- purpose\nSELECT a FROM logs WHERE b = getvariable('b')\n"
    assert sqlguard.check_named(sql) == "SELECT a FROM logs WHERE b = getvariable('b')"
    assert sqlguard.check_named("WITH a AS (SELECT 1) SELECT * FROM a;")
    assert sqlguard.check_named("FROM logs")


@pytest.mark.parametrize("sql", [
    "COPY logs TO '/tmp/x'",
    "DESCRIBE logs",
    "SUMMARIZE logs",
    "SELECT 1; SELECT 2",
    "SELECT 1\n.shell id",
    "SELECT 1\r.shell id",
    "SELECT (1",
    "",
    "-- only a comment\n",
    "SELECT 'x",
])
def test_named_check_fails_closed(sql):
    refused(sql, sqlguard.check_named)
slack-gif-creatoranthropics/skillsKnowledge and utilities for creating animated GIFs optimized for Slack. Provides constraints, validation tools, and animation concepts. Use when users request animated GIFs for Slack like "make me a GIF of X doing Y for Slack."Stars: 198kUpdated Claude CodeCodexVerifiedInstall
ponytailDietrichGebert/ponytailLazy senior dev mode for any coding task: the smallest change that fully solves it, and a reply a busy human understands in one read.Stars: 194kUpdated Claude CodeCodexVerifiedInstall
ponytail-debtDietrichGebert/ponytailHarvest every shortcut: comment into one debt ledger, so deferrals get tracked instead of forgotten. One-shot report.Stars: 194kUpdated Claude CodeCodexVerifiedInstall
ponytail-debtDietrichGebert/ponytailList every `shortcut:` comment (and older `ponytail:` ones) as a debt ledger. One-shot report, changes nothing. Use for "ponytail debt", "what did ponytail defer", "list the shortcuts", /ponytail-debt.Stars: 194kUpdated Claude CodeCodexVerifiedInstall