WebSocket: TEXT stored in a column declared BLOB is base64-decoded on read #288

Summary

On the WebSocket transport with the default websocketBlobFormat (base64-blobs-v1), decodeWebsocketRowsetData base64-decodes every string cell in a column whose declared type contains BLOB. SQLite lets such a column hold TEXT, and those values are base64-decoded too, so the client gets the wrong bytes. What's stored is correct; only the read is wrong.

Version: @sqlitecloud/drivers 1.0.878, Node, WebSocket connection (usewebsocket: true), safe_integer_mode: "mixed".

Reproduce

await db.sql`CREATE TABLE t (id INTEGER PRIMARY KEY, b BLOB)`
await db.sql`INSERT INTO t VALUES (1, 'Hello'), (2, X'48656C6C6F'), (3, '48656c6c6f'), (4, 'aGVsbG8=')`

await db.sql`SELECT id, typeof(b), hex(b) FROM t`
// 1 text 48656C6C6F
// 2 blob 48656C6C6F
// 3 text 34383635366336633666
// 4 text 614756736247383D

await db.sql`SELECT id, b FROM t`
// 1 [29, 233, 101]                       expected the text 'Hello'
// 2 [72, 101, 108, 108, 111]             correct ("Hello")
// 3 [227, 206, 185, 233, 206, 156, 233]  expected the text '48656c6c6f'
// 4 [104, 101, 108, 108, 111]            expected the text 'aGVsbG8=', got the bytes of "hello"

CAST(b AS TEXT), and the same text in a column with no declared BLOB type, read back correctly.

Cause

decodeWebsocketRowsetData in src/drivers/utilities.ts:

const blobColumnIndexes = new Set(metadata.columns.flatMap((column, index) =>
  column.type && BLOB_COLUMN_TYPE_RE.test(column.type) ? [index] : []))
const decodeCell = (value, columnIndex) => {
  if (blobTransferFormat === 'base64-blobs-v1' && blobColumnIndexes.has(columnIndex) && typeof value === 'string') {
    return Buffer.from(value, 'base64')
  }
  return decodeBigIntMarkers(value, safeIntegerMode)
}

With base64-blobs-v1 a BLOB arrives as a base64 string, and nothing marks it apart from a TEXT value: both are strings. The decoder guesses from the column's declared type (metadata.columns[i].type), but SQLite's declared types are only a hint. A BLOB column can hold TEXT, INTEGER or REAL, and a column without BLOB in its type can hold a BLOB (see #281).

Impact

Anyone storing text in a BLOB-declared column sees wrong data. A client that reads a row and writes it back, such as a data editor, would store the wrong bytes.

Related

• decodeBigIntMarkers / encodeBigIntMarkers turn binary values (ArrayBuffer, Uint8Array) into plain objects on the WebSocket transport #281: the reverse case in the same code. BLOBs in columns not declared BLOB come back as {}.
• chore: blobs via websocket #268: "blobs via websocket", closed.