> For the complete documentation index, see [llms.txt](https://adamcysec.gitbook.io/csapp/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://adamcysec.gitbook.io/csapp/threat-hunting/useful-sql-queries.md).

# Useful SQL Queries

Some SQL queries to get you started

Get creative! Mix and match filters to achieve your desired results.

## Wildcard matching

```
SELECT latest_upload_date,first_upload_date,name,rank,description,latest_release_published_at,package_manager_url,repository_url,maintainers,homepage,stars,latest_release_number,forks,total_versions
FROM pypi
WHERE name LIKE '%atlass%'
```

This search attempts to find packages typo squatting on the name atlassian.

## Filtering by `first_upload_date`

```
SELECT first_upload_date,name,rank,description,latest_release_published_at,package_manager_url,repository_url,maintainers,homepage,stars,latest_release_number,forks,total_versions
FROM pypi
WHERE first_upload_date < '2023-06-12' AND first_upload_date > '2023-06-04'
```

This is useful for reviewing recent packages uploaded.

## Filtering by `repository_url`

```
SELECT first_upload_date,name,rank,description,latest_release_published_at,package_manager_url,repository_url,maintainers,homepage,stars,latest_release_number,forks,total_versions
FROM pypi
WHERE repository_url='https://github.com/pypa/sampleproject'
```

This is useful for finding packages linking to the same Github project.

## Filtering by `stars`

```
SELECT first_upload_date,name,rank,description,latest_release_published_at,package_manager_url,repository_url,maintainers,homepage,stars,latest_release_number,forks,total_versions
FROM pypi
WHERE stars>1000
```

This is useful for reviewing packages with a large amount of Github stars.