hi, i’d like to construct a query that uses curl_certificate on all locally listening ports. I envision a query like:

select c.serial_number from curl_certificate as c where hostname="localhost" and port in (Select port from listening_ports where path="");

but i instead need to combine “localhost” and port into one field for hostname. Is there a way of doing this in SQL without the curl_certificate table having support for a separate port field? thx

You can use || to concatenate. I do this for files like so:

SELECT path, mtime, size, sha256, (select directory from users where directory like '/Users/%') u FROM file JOIN hash USING (path) WHERE path like u || '/tmp/%/'

 

|| 'c.txt';

sorry trying to unpack your query to understand how it works. is this looking for files in /Users/[username]/tmp/c.txt (for all usernames?)

It is looking for all c.txt in /Users/(all usernames)/tmp/(all subdirectories nonrecursively)/c.txt So it will find e.g. /Users/shanford/tmp/ABCD/c.txt and /Users/shanford/tmp/EFGH/c.txt

oh i see % is. wildcard

/%% would be recursive, so tmp/ABCD/EFGH/c.txt. In my experience, it was incredibly problematic for that query

ok, it ended up being this:

select c.* from curl_certificate as c where hostname in (Select distinct "localhost:"||port from listening_ports where protocol=6 and (address!="127.0.0.1" and address!="::1") ORDER BY port);