1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
|
package main
import (
"crypto/rand"
"crypto/subtle"
"database/sql"
"encoding/base64"
"errors"
"fmt"
"time"
)
// ErrNotFound is returned for a token that names no live submission. It is
// deliberately the same error for "never existed", "mistyped" and "deleted":
// see submissionByToken.
var ErrNotFound = errors.New("not found")
// tokenBytes is the size of the capability. 256 bits is far past what is
// needed to make guessing hopeless, and costs 43 characters in a URL that is
// only ever clicked, never typed.
const tokenBytes = 32
// Submission is one submitted recording.
type Submission struct {
ID int64
Token string
ReceivedAt time.Time
ByteSize int64
MIME string
Filename string
DurationMs sql.NullInt64
Consented bool
Transcript sql.NullString
// TranscriptError is why there is no transcript, when something was
// attempted and did not work. It is what lets the review page tell a
// failure apart from a submission that was never permitted to be
// transcribed; see setTranscriptError.
TranscriptError sql.NullString
Note string
SourceIP string
}
// newToken returns a fresh capability token.
//
// crypto/rand only: a token derived from the time, the filename or the
// submitter's address would be guessable by whoever supplied those, which is
// the one thing it must not be. base64url so it survives a URL and a mail
// client's linkifier without escaping.
func newToken() (string, error) {
b := make([]byte, tokenBytes)
if _, err := rand.Read(b); err != nil {
return "", fmt.Errorf("generating token: %w", err)
}
return base64.RawURLEncoding.EncodeToString(b), nil
}
// insertSubmission stores a recording and returns it with its token.
func insertSubmission(db *sql.DB, s *Submission, audio []byte) error {
token, err := newToken()
if err != nil {
return err
}
now := time.Now()
res, err := db.Exec(`
INSERT INTO submission
(token, received_at, audio, byte_size, mime, filename,
duration_ms, consented, note, source_ip)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
token, now.Unix(), audio, int64(len(audio)), s.MIME, s.Filename,
s.DurationMs, boolToInt(s.Consented), s.Note, s.SourceIP,
)
if err != nil {
return fmt.Errorf("insert submission: %w", err)
}
id, err := res.LastInsertId()
if err != nil {
return fmt.Errorf("insert submission: %w", err)
}
s.ID = id
s.Token = token
s.ReceivedAt = now
s.ByteSize = int64(len(audio))
return nil
}
// submissionByToken looks up a live submission.
//
// The lookup is by the unique index on token, and a miss is reported as
// ErrNotFound whatever the reason — no such token, or a token whose submission
// was deleted. Distinguishing them would turn the endpoint into an oracle that
// confirms a token was once valid, and there is nothing a caller could
// usefully do with the difference anyway.
func submissionByToken(db *sql.DB, token string) (*Submission, error) {
if !plausibleToken(token) {
return nil, ErrNotFound
}
var (
s Submission
receivedAt int64
consented int
)
err := db.QueryRow(`
SELECT id, token, received_at, byte_size, mime, filename,
duration_ms, consented, transcript, transcript_error,
note, source_ip
FROM submission
WHERE token = ? AND deleted_at IS NULL`, token,
).Scan(&s.ID, &s.Token, &receivedAt, &s.ByteSize, &s.MIME, &s.Filename,
&s.DurationMs, &consented, &s.Transcript, &s.TranscriptError,
&s.Note, &s.SourceIP)
if errors.Is(err, sql.ErrNoRows) {
return nil, ErrNotFound
}
if err != nil {
return nil, fmt.Errorf("load submission: %w", err)
}
s.ReceivedAt = time.Unix(receivedAt, 0)
s.Consented = consented != 0
return &s, nil
}
// audioByToken returns the stored bytes. Kept separate from the metadata
// lookup so rendering a page does not pull megabytes of audio into memory.
func audioByToken(db *sql.DB, token string) ([]byte, string, error) {
if !plausibleToken(token) {
return nil, "", ErrNotFound
}
var (
audio []byte
mime string
)
err := db.QueryRow(
`SELECT audio, mime FROM submission WHERE token = ? AND deleted_at IS NULL`,
token,
).Scan(&audio, &mime)
if errors.Is(err, sql.ErrNoRows) {
return nil, "", ErrNotFound
}
if err != nil {
return nil, "", fmt.Errorf("load audio: %w", err)
}
if audio == nil {
return nil, "", ErrNotFound
}
return audio, mime, nil
}
// deleteSubmission marks a submission gone and drops its audio.
//
// The row survives so the token cannot be told apart from one that never
// existed; the blob does not, because reclaiming the space is the entire point
// of deleting.
func deleteSubmission(db *sql.DB, token string) error {
if !plausibleToken(token) {
return ErrNotFound
}
res, err := db.Exec(`
UPDATE submission
SET deleted_at = ?, audio = NULL
WHERE token = ? AND deleted_at IS NULL`,
time.Now().Unix(), token)
if err != nil {
return fmt.Errorf("delete submission: %w", err)
}
n, err := res.RowsAffected()
if err != nil {
return fmt.Errorf("delete submission: %w", err)
}
if n == 0 {
return ErrNotFound
}
return nil
}
// setTranscript records the transcription of a submission. It is written after
// the row is committed, so a failure to transcribe never costs the recording.
//
// It clears transcript_error in the same statement. The two columns answer the
// same question — why is there no transcript — so a row must never hold both:
// a transcript sitting next to the reason there is none is a page that
// contradicts itself.
func setTranscript(db *sql.DB, token, transcript string) error {
_, err := db.Exec(
`UPDATE submission SET transcript = ?, transcript_error = NULL
WHERE token = ?`, transcript, token)
if err != nil {
return fmt.Errorf("store transcript: %w", err)
}
return nil
}
// transcriptErrorMax bounds a recorded reason. The message comes from Google
// and is unbounded; it is shown on a page and stored in the database the quota
// is measured against, so it gets a ceiling like everything else a third party
// supplies.
const transcriptErrorMax = 500
// setTranscriptError records why a submission has no transcript.
//
// Without this the review page cannot tell a transcription that failed from
// one that was never permitted: both leave transcript NULL, and the reason
// used to exist only in the log and in the notification mail — neither of which
// is at hand when looking at the submission weeks later.
//
// Called for a failed attempt and for a consented submission on a server with
// no key configured, which is the other way to arrive at silence.
func setTranscriptError(db *sql.DB, token, msg string) error {
_, err := db.Exec(
`UPDATE submission SET transcript_error = ? WHERE token = ?`,
truncate(msg, transcriptErrorMax), token)
if err != nil {
return fmt.Errorf("store transcript error: %w", err)
}
return nil
}
// setDuration records how long a submission is, once it has been measured.
func setDuration(db *sql.DB, token string, ms int64) error {
_, err := db.Exec(
`UPDATE submission SET duration_ms = ? WHERE token = ?`, ms, token)
if err != nil {
return fmt.Errorf("store duration: %w", err)
}
return nil
}
// setRemuxedAudio replaces a submission's container with an equivalent one that
// declares its duration, and records that duration.
//
// byte_size is updated in the same statement as the blob, and this matters more
// than it looks: byte_size is what usedBytes sums, so it is what the 50 MB
// quota is enforced against. Writing a blob of one size while the quota
// believes another would make the ceiling drift away from the actual contents
// of the database, in whichever direction the remux happened to go.
//
// The write is skipped for a submission that has since been deleted — the
// deleted_at guard — so a remux that finishes after someone has already deleted
// the recording cannot resurrect its bytes.
func setRemuxedAudio(db *sql.DB, token string, audio []byte, ms int64) error {
_, err := db.Exec(`
UPDATE submission
SET audio = ?, byte_size = ?, duration_ms = ?
WHERE token = ? AND deleted_at IS NULL`,
audio, int64(len(audio)), ms, token)
if err != nil {
return fmt.Errorf("store remuxed audio: %w", err)
}
return nil
}
// unmeasured lists live submissions whose length is not known.
//
// This exists for the recordings that arrived before anything measured them.
// Bounded by the 50 MB ceiling on the inbox as a whole, so the list is short by
// construction, and it shrinks to nothing as they are measured.
func unmeasured(db *sql.DB) ([]string, error) {
rows, err := db.Query(`
SELECT token FROM submission
WHERE deleted_at IS NULL AND audio IS NOT NULL AND duration_ms IS NULL
ORDER BY id`)
if err != nil {
return nil, fmt.Errorf("list unmeasured submissions: %w", err)
}
defer rows.Close()
var tokens []string
for rows.Next() {
var t string
if err := rows.Scan(&t); err != nil {
return nil, fmt.Errorf("list unmeasured submissions: %w", err)
}
tokens = append(tokens, t)
}
return tokens, rows.Err()
}
// usedBytes is the total size of live submissions, which is what the quota is
// measured against.
func usedBytes(db *sql.DB) (int64, error) {
var n sql.NullInt64
if err := db.QueryRow(
`SELECT SUM(byte_size) FROM submission WHERE deleted_at IS NULL`,
).Scan(&n); err != nil {
return 0, fmt.Errorf("compute used bytes: %w", err)
}
return n.Int64, nil
}
// plausibleToken rejects anything that cannot be a token before it reaches the
// database, so that scanning for SQL injection or path traversal never gets as
// far as a query. The comparison is constant time out of habit rather than
// necessity — the value is looked up by index either way.
func plausibleToken(token string) bool {
if len(token) != base64.RawURLEncoding.EncodedLen(tokenBytes) {
return false
}
decoded, err := base64.RawURLEncoding.DecodeString(token)
if err != nil {
return false
}
return subtle.ConstantTimeEq(int32(len(decoded)), int32(tokenBytes)) == 1
}
func boolToInt(b bool) int {
if b {
return 1
}
return 0
}
|