bradfitz · June 23, 2021 17:23
diff --git a/sqlplay.go b/sqlplay.go
 package main

 import (
 	"database/sql"
 	"flag"
 	"fmt"
 	"html"
 	"io"
 	"io/ioutil"
 	"log"
 	"net/http"
 	"os"
 	"path/filepath"
 	"strings"

 	_ "github.com/lib/pq"
 )

 var db *sql.DB

 var (
 	listen = flag.String("listen", defaultListen(), "listen address")
 )

 func defaultListen() string {
 	if os.Getuid() == 0 {
 		return ":80"
 	}
 	return ":8219"
 }

 func main() {
 	flag.Parse()

 	log.Printf("starting")

 	connStr, err := ioutil.ReadFile(filepath.Join(os.Getenv("HOME"), "keys", "sqlplay-ro"))
 	if err != nil {
 		log.Fatal(err)
 	}
 	db, err = sql.Open("postgres", strings.TrimSpace(string(connStr)))
 	if err != nil {
 		log.Fatal(err)
 	}

 	log.Printf("connected to postgres; listening on %v...", *listen)
 	http.HandleFunc("/", serve)
 	log.Fatal(http.ListenAndServe(*listen, nil))
 }

 func serve(w http.ResponseWriter, r *http.Request) {
 	sql := r.FormValue("q")
 	if sql == "" {
 		r.FormValue("sql") // old way for compat
 	}
 	fmt.Fprintf(w, `<html>
 <head><style>

 tr:nth-child(even){background-color: #f2f2f2;}

 td, th {
  border: 1px solid #ddd;
  padding: 8px;
 }

 table {
    border-collapse: collapse;
    width: 100%%;
 }

 tr:hover {background-color: #ddd;}

 th {
  padding-top: 12px;
  padding-bottom: 12px;
  text-align: left;
  background-color: #04AA6D;
  color: white;
 }

 </style></head>
 <body>
   <form method=GET>
   <textarea name=q rows=5 cols=80 style='width: 100%%'>%s</textarea>
   <p><input type=submit value="query">
          [<a href="https://github.com/tailscale/xxxx#L28">schema</a>]
          [<a href="https://docs.google.com/document/d/xxxxxx/edit#">sample queries</a>]
   </form>
 </body>`, html.EscapeString(sql))

 	if sql != "" {
 		rows, err := db.Query(sql)
 		if err != nil {
 			http.Error(w, err.Error(), 500)
 			return
 		}
 		io.WriteString(w, `<html><body><table><tr>`)
 		cols, _ := rows.Columns()
 		for _, c := range cols {
 			fmt.Fprintf(w, "<th>%s</th>\n", html.EscapeString(c))
 		}
 		io.WriteString(w, `</tr>`)

 		for rows.Next() {
 			val := make([]interface{}, len(cols))
 			valPtr := make([]interface{}, len(cols))
 			for i := range cols {
 				valPtr[i] = &val[i]
 			}
 			if err := rows.Scan(valPtr...); err != nil {
 				http.Error(w, err.Error(), 500)
 				return
 			}
 			io.WriteString(w, `<tr>`)

 			for _, v := range val {
 				fmt.Fprintf(w, "<td>%s</td>\n", colHTML(v))
 			}
 			io.WriteString(w, "</tr>\n")
 		}
 		io.WriteString(w, "</table>\n")
 		return
 	}

 }

 func colFmt(v interface{}) string {
 	switch v := v.(type) {
 	case []byte:
 		return string(v)
 	default:
 		s := fmt.Sprint(v)
 		s = strings.TrimSuffix(s, " 00:00:00 +0000 +0000") // so a time.Time of a single day formats nicely
 		return s
 	}
 }

 func colHTML(v interface{}) string {
 	s := colFmt(v)
 	h := html.EscapeString(s)
 	if strings.HasPrefix(s, "cus_") {
 		return fmt.Sprintf("<a href=\"https://dashboard.stripe.com/customers/%s\" rel=\"noopener noreferrer\">%s</a>", h, h)
 	}
 	return h
 }
	package main

	import (
	"database/sql"
	"flag"
	"fmt"
	"html"
	"io"
	"io/ioutil"
	"log"
	"net/http"
	"os"
	"path/filepath"
	"strings"

	_ "github.com/lib/pq"
	)

	var db *sql.DB

	var (
	listen = flag.String("listen", defaultListen(), "listen address")
	)

	func defaultListen() string {
	if os.Getuid() == 0 {
	return ":80"
	}
	return ":8219"
	}

	func main() {
	flag.Parse()

	log.Printf("starting")

	connStr, err := ioutil.ReadFile(filepath.Join(os.Getenv("HOME"), "keys", "sqlplay-ro"))
	if err != nil {
	log.Fatal(err)
	}
	db, err = sql.Open("postgres", strings.TrimSpace(string(connStr)))
	if err != nil {
	log.Fatal(err)
	}

	log.Printf("connected to postgres; listening on %v...", *listen)
	http.HandleFunc("/", serve)
	log.Fatal(http.ListenAndServe(*listen, nil))
	}

	func serve(w http.ResponseWriter, r *http.Request) {
	sql := r.FormValue("q")
	if sql == "" {
	r.FormValue("sql") // old way for compat
	}
	fmt.Fprintf(w, `<html>
	<head><style>

	tr:nth-child(even){background-color: #f2f2f2;}

	td, th {
	border: 1px solid #ddd;
	padding: 8px;
	}

	table {
	border-collapse: collapse;
	width: 100%%;
	}

	tr:hover {background-color: #ddd;}

	th {
	padding-top: 12px;
	padding-bottom: 12px;
	text-align: left;
	background-color: #04AA6D;
	color: white;
	}

	</style></head>
	<body>
	<form method=GET>
	<textarea name=q rows=5 cols=80 style='width: 100%%'>%s</textarea>
	<p><input type=submit value="query">
	[<a href="https://github.com/tailscale/xxxx#L28">schema</a>]
	[<a href="https://docs.google.com/document/d/xxxxxx/edit#">sample queries</a>]
	</form>
	</body>`, html.EscapeString(sql))

	if sql != "" {
	rows, err := db.Query(sql)
	if err != nil {
	http.Error(w, err.Error(), 500)
	return
	}
	io.WriteString(w, `<html><body><table><tr>`)
	cols, _ := rows.Columns()
	for _, c := range cols {
	fmt.Fprintf(w, "<th>%s</th>\n", html.EscapeString(c))
	}
	io.WriteString(w, `</tr>`)

	for rows.Next() {
	val := make([]interface{}, len(cols))
	valPtr := make([]interface{}, len(cols))
	for i := range cols {
	valPtr[i] = &val[i]
	}
	if err := rows.Scan(valPtr...); err != nil {
	http.Error(w, err.Error(), 500)
	return
	}
	io.WriteString(w, `<tr>`)

	for _, v := range val {
	fmt.Fprintf(w, "<td>%s</td>\n", colHTML(v))
	}
	io.WriteString(w, "</tr>\n")
	}
	io.WriteString(w, "</table>\n")
	return
	}

	}

	func colFmt(v interface{}) string {
	switch v := v.(type) {
	case []byte:
	return string(v)
	default:
	s := fmt.Sprint(v)
	s = strings.TrimSuffix(s, " 00:00:00 +0000 +0000") // so a time.Time of a single day formats nicely
	return s
	}
	}

	func colHTML(v interface{}) string {
	s := colFmt(v)
	h := html.EscapeString(s)
	if strings.HasPrefix(s, "cus_") {
	return fmt.Sprintf("<a href=\"https://dashboard.stripe.com/customers/%s\" rel=\"noopener noreferrer\">%s</a>", h, h)
	}
	return h
	}