obsidian/wiki/concepts/sqlite-not-null-as-boolean.md

title	aliases	tags	sources	created	updated
SQLite: IS NOT NULL AS Boolean	sqlite-not-null-boolean sqlite-derived-boolean	sqlite sql security pattern	sandbox	2026-05-05	2026-05-05

SQLite: IS NOT NULL AS Boolean

When a column contains sensitive data (e.g. a password hash), never select it directly to answer a yes/no question. Use (column IS NOT NULL) AS flag to return a derived boolean instead.

Key Takeaways

• (password_hash IS NOT NULL) AS has_password → returns 1 or 0, never the hash
• Hash never leaves the database layer — client gets the correct boolean
• Standard pattern for any column that must stay server-side

Pattern

-- BAD: leaks the hash to the application layer
SELECT id, email, password_hash FROM users WHERE id = ?;

-- GOOD: returns a boolean, hash stays in DB
SELECT id, email, (password_hash IS NOT NULL) AS has_password FROM users WHERE id = ?;

In SQLite, the expression evaluates to 1 (true) or 0 (false). Map to a JS/Python boolean at the application layer:

// Node.js / better-sqlite3
const user = db.prepare(`
  SELECT id, email, (password_hash IS NOT NULL) AS has_password
  FROM users WHERE id = ?
`).get(userId);

// SQLite returns 1/0; coerce to boolean
user.hasPassword = Boolean(user.has_password);

Bug This Fixes

The userPublic() helper was selecting u.password_hash directly. The result object included the raw hash — or, worse, undefined when the column was NULL — causing hasPassword to always evaluate to false (because the hash value was truthy but the field name wasn't mapped correctly).

Fix: replace the direct column with (password_hash IS NOT NULL) AS has_password.

Generalisation

The same pattern applies to any secret or large binary:

• (api_key IS NOT NULL) AS has_api_key
• (avatar_blob IS NOT NULL) AS has_avatar
• (refresh_token IS NOT NULL) AS is_linked

Related

• wiki/concepts/fastapi-response-model-silent-field-strip — similar idea: strip sensitive fields at the serialization layer