In How SQL Can Unify API Access, I made the case of SQL as a general environment in which to think about data leaking from many different APIs. The main factor for this scenario is SteampipePostgres – based tool with a growing set of API plugins which map APIs to foreign tables in Postgres.

These APIs were originally those provided by AWS, Azure and GCP. Such APIs are usually made more accessible to developers through shells such as boto3. A common SQL interface is maybe a better unifier of the extensive API ecosystems in these clouds and this is undoubtedly true in multi-cloud scenarios. With Postgres under the hood, by the way, you are not limited to SQL: you can connect Python or JavaScript or another language to Postgres and use the common SQL interface from these languages ​​as well.

Steampipe’s ecosystem then expanded with plugins for many other services, including GitHub, Google Workspace, IMAP, Jira, LDAP, Shodan, Slack, Stripe and Zendesk. Joining these APIs is a superpower best demonstrated by this example, which connects Amazon EC2 endpoints with Shodan vulnerabilities in just 10 rows of very basic SQL.

  aws_ec2_instance a
left join
  shodan_host s on a.public_ip_address = s.ip
  a.public_ip_address is not null;

| instance_id         | ports    | vulns              |
| i-0dc60dd191cb84239 | null     | null               |
| i-042a51a815773780d | [80,22]  | null               |
| i-00cf426db9b8a58b6 | [22]     | null               |
| i-0e97f373db42dfa3f | [22,111] | ["CVE-2018-15919"] |

The files are also APIs

But what exactly is an API? Should I always include HTTP requests to service endpoints? More broadly, APIs are data sources that are available in other versions. Web pages are often still de facto APIs. I’ve done more web scraping over the years than I think, and the skill remains useful.

Files are also data sources: configuration files (INI, YAML, JSON), infrastructure files such as code (Terraform, CloudFormation), data filesCSV). As plugins for these sources began to join the mix, Steampipe became even more powerful.

First came the CSV plugin, which unlocked all sorts of useful queries. Consider, for example, how often we pretend that spreadsheets are databases. In this way, we can assume that there is a reference integrity when it does not really exist. If you export spreadsheet data to CSV, you can use SQL for find these misconceptions. And that’s just one of the endless ways I can imagine using SQL to query the world’s leading file format for data exchange.

Copyright © 2022 IDG Communications, Inc.

Previous articleHow to watch the rare total lunar eclipse from “Bloody Blood Moon” this week
Next articleWhen rights are online, words are not enough – information