> ## Documentation Index
> Fetch the complete documentation index at: https://notes.kodekloud.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Demo Database Security Audit

> A database security audit of an Express login demo identifying vulnerabilities and recommending prioritized fixes for TLS, secrets, queries, permissions, logging, and tenant isolation.

This lesson walks through a database security audit for an Express login demo application. The objective is to inspect every database interaction, surface security gaps, and produce prioritized remediation steps so the app can be safely promoted to production.

Initial CLI session used to launch the auditing tool:

```bash theme={null}
jeremy@MACSTUDIO Express-login-demo % claude

* Welcome to Claude Code!

/help for help, /status for your current setup

cwd: /Users/jeremy/Repos/Claude Code Course/Express-login-demo

> Try "how does <filepath> work?"

? for shortcuts

In INPUT_VALIDATION_SECURITY_REPORT.md
```

We follow a structured checklist covering parameterized queries, secret management, permissions, encryption, PII controls, timeouts/pools, and audit/logging.

<Frame>
  <img src="https://mintcdn.com/kodekloud-c4ac6d9a/XqMRTrEG2GqQdgEv/images/Claude-Code-For-Beginners/Security-Auditing-with-Claude-Code/Demo-Database-Security-Audit/vscode-database-security-checklist.jpg?fit=max&auto=format&n=XqMRTrEG2GqQdgEv&q=85&s=afcdb4b95064a8b75a6642298516faaf" alt="A dark-themed Visual Studio Code window with a file explorer on the left and a central terminal/editor pane showing a database security checklist (parameterized queries, connection string security, user permissions, encryption, PII handling, timeouts, connection pool settings, etc.). The project file tree and a highlighted security report file are visible in the sidebar." width="1920" height="1080" data-path="images/Claude-Code-For-Beginners/Security-Auditing-with-Claude-Code/Demo-Database-Security-Audit/vscode-database-security-checklist.jpg" />
</Frame>

## High-level audit focus areas

* Parameterized queries / safe ORM usage to prevent SQL/NoSQL injection
* Connection string protection, TLS/SSL, and secret management
* Database user permissions (principle of least privilege)
* Encryption at rest and TLS in transit
* PII handling, retention, and logging hygiene
* Query timeouts, connection-pool limits, and transaction discipline
* Audit logging for sensitive operations and schema changes
* NoSQL injection hardening and row/tenant isolation for multi-tenant apps

Principle of least privilege: give each DB account only the privileges it needs — separate read-only, migration, and admin accounts; avoid SUPERUSER-level credentials unless explicitly required.

We verify that critical fields are minimized, encrypted or tokenized where needed, and that PII is redacted from logs.

Example checklist items (condensed):

* Verify parameterized queries or ORM APIs are used instead of concatenating user input.
* Ensure connection strings and credentials are stored in a secrets manager and rotated.
* Confirm DB connections use TLS/SSL (validate server certificate).
* Validate query timeouts and pool limits are configured at driver and server levels.
* Confirm audit logging for schema and privilege changes, failed logins, and sensitive table access.
* Enforce row-level security (or server-side tenant scoping) for multi-tenant data isolation.

<Frame>
  <img src="https://mintcdn.com/kodekloud-c4ac6d9a/XqMRTrEG2GqQdgEv/images/Claude-Code-For-Beginners/Security-Auditing-with-Claude-Code/Demo-Database-Security-Audit/vscode-numbered-security-checklist.jpg?fit=max&auto=format&n=XqMRTrEG2GqQdgEv&q=85&s=a600cb9249035ca9832b03c4df1e76a0" alt="A Visual Studio Code window with the explorer on the left and a text file open in the main pane. The file shows a numbered security checklist covering items like audit logging, NoSQL injection hardening, row/tenant isolation, TLS, secret management, and schema controls." width="1920" height="1080" data-path="images/Claude-Code-For-Beginners/Security-Auditing-with-Claude-Code/Demo-Database-Security-Audit/vscode-numbered-security-checklist.jpg" />
</Frame>

## Row / tenant isolation

* Enforce server-side row-level security (e.g., Postgres RLS: [https://www.postgresql.org/docs/current/ddl-rowsecurity.html](https://www.postgresql.org/docs/current/ddl-rowsecurity.html)) or strict ownership/tenant filters in queries.
* Never rely on client-side filtering for multi-tenant isolation — always scope queries server-side.

## NoSQL sanitization (Mongoose example)

For MongoDB/Mongoose, avoid passing raw user input directly into query filters:

```javascript theme={null}
// For Mongoose, enable sanitizeFilter to avoid passing raw user input into query filters
const mongoose = require('mongoose');
mongoose.set('sanitizeFilter', true);
```

See Mongoose migration docs for sanitizeFilter: [https://mongoosejs.com/docs/migration.html#sanitizeFilter](https://mongoosejs.com/docs/migration.html#sanitizeFilter)

## TLS / secret management and schema controls to check

* DB connections must use TLS/SSL (for Postgres: use `sslmode=require` or `verify-full`; see Postgres libpq SSL mode docs: [https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNECT-SSLMODE](https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNECT-SSLMODE)).
* Store credentials in a secrets manager (e.g., AWS Secrets Manager: [https://aws.amazon.com/secrets-manager/](https://aws.amazon.com/secrets-manager/), HashiCorp Vault: [https://www.vaultproject.io/](https://www.vaultproject.io/)) and rotate them periodically.
* Do not commit plaintext passwords in `.env`. Keep a `.env.example` for local development only. (See the dotenv package: [https://www.npmjs.com/package/dotenv](https://www.npmjs.com/package/dotenv))
* Enforce foreign keys, unique constraints, and NOT NULL where appropriate.
* Avoid `SELECT *` — always fetch only the required fields.

### Example: enabling SSL/TLS in node-postgres Pool (illustrative)

```javascript theme={null}
// javascript
const { Pool } = require('pg');

const pool = new Pool({
  host: process.env.DB_HOST,
  port: Number(process.env.DB_PORT || 5432),
  database: process.env.DB_NAME,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  // Connection pool limits and timeouts
  max: Number(process.env.DB_POOL_MAX || 20),
  idleTimeoutMillis: Number(process.env.DB_IDLE_TIMEOUT || 30000),
  connectionTimeoutMillis: Number(process.env.DB_CONN_TIMEOUT || 5000),
  // TLS options (for production)
  ssl: {
    rejectUnauthorized: true, // ensure server certificate is validated
    // ca: process.env.DB_SSL_CA, // if using a custom CA
  },
});
```

See node-postgres pooling docs: [https://node-postgres.com/features/pooling](https://node-postgres.com/features/pooling)

<Frame>
  <img src="https://mintcdn.com/kodekloud-c4ac6d9a/XqMRTrEG2GqQdgEv/images/Claude-Code-For-Beginners/Security-Auditing-with-Claude-Code/Demo-Database-Security-Audit/vscode-express-security-audit-checklist.jpg?fit=max&auto=format&n=XqMRTrEG2GqQdgEv&q=85&s=ec6dd97f942d92640cdbf19c6d019452" alt="A dark-themed Visual Studio Code window showing a project explorer on the left and an editor/terminal pane with a security-audit checklist for an Express app in the center. The text highlights critical flags (e.g., direct string concatenation) and lists a structured findings/report template." width="1920" height="1080" data-path="images/Claude-Code-For-Beginners/Security-Auditing-with-Claude-Code/Demo-Database-Security-Audit/vscode-express-security-audit-checklist.jpg" />
</Frame>

## Critical flags to watch for

* Direct string concatenation with user input in SQL or NoSQL queries — extremely high severity.
* Passing user JSON directly into NoSQL query filters without sanitization (see OWASP NoSQL Injection: [https://owasp.org/www-community/attacks/NoSQL\_Injection](https://owasp.org/www-community/attacks/NoSQL_Injection)).
* Hard-coded credentials or placeholder secrets committed to the repository (e.g., `JWT_SECRET=your_jwt_secret_key_here`, `DB_PASSWORD=your_db_password`).

## Useful audit commands (examples)

* Search for environment files and secrets:

```bash theme={null}
# search for env files
find /Users/jeremy/Repos/Claude\ Code\ Course/Express-login-demo -name "*.env*" -o -name ".env*" 2>/dev/null
```

* Check for console logging (may leak PII):

```bash theme={null}
# grep for console logging patterns
grep -r "console\.log\|console\.error" /Users/jeremy/Repos/Claude\ Code\ Course/Express-login-demo/ --include="*.js" --exclude-dir=node_modules
```

* Generate a bcrypt test hash:

```bash theme={null}
# bash
node -e "console.log(require('bcrypt').hashSync('password123', 12))"
# Use the output in test data; use cost factor 12+ for production
```

See bcrypt: [https://www.npmjs.com/package/bcrypt](https://www.npmjs.com/package/bcrypt)

## Audit findings (executive summary)

* Strengths: parameterized queries, bcrypt password hashing, and input validation (express-validator) are implemented.
* Critical gaps: no TLS/SSL for DB connections, placeholder secrets in `.env`, missing connection pool limits/timeouts, and no centralized audit logging.
* Risk Score (current): 6.5 / 10 (Medium-High). After remediation, target \~2.5 / 10 (Low Risk).

### Example critical finding (evidence & remediation)

* CRITICAL: No TLS/SSL Encryption in Transit
  * Evidence (example): `config/database.js:3-9` — Pool configuration missing SSL settings.
  * Impact: Cleartext DB credentials and payloads on the network (CWE-319: [https://cwe.mitre.org/data/definitions/319.html](https://cwe.mitre.org/data/definitions/319.html)).
  * Priority fixes:
    1. Enable TLS/SSL for DB connections and validate certificates.
    2. Remove `.env` from git and add `.env.example`.
    3. Replace placeholder secrets with strong values stored in a secrets manager.
    4. Add pool limits, timeouts, and resource caps.

## Security checklist assessment

| Resource / Control                       | Status           | Notes                                    |
| ---------------------------------------- | ---------------- | ---------------------------------------- |
| Parameterized queries                    | Pass             | Queries use placeholders like `$1`       |
| Connection string security (TLS/Secrets) | Fail             | No SSL and weak defaults detected        |
| Database user permissions                | Unable to verify | Requires DB server access                |
| Encryption at rest                       | Unable to verify | Requires DB server access                |
| PII handling                             | Partial          | No retention/deletion policy visible     |
| Query timeouts                           | Fail             | No application-level timeouts configured |
| Connection pool settings                 | Fail             | Missing limits/idle timeouts             |
| Transaction handling                     | Partial / N/A    | Mostly single-query flows                |
| Audit logging                            | Fail             | No centralized immutable logs observed   |
| Row/tenant isolation                     | Fail             | No RLS or server-side tenant scoping     |
| Secret management                        | Fail             | Placeholder secrets committed to repo    |
| Schema integrity                         | Pass             | Foreign keys/constraints present         |
| Field minimization                       | Pass             | App selects only required fields         |
| Backup/restore security                  | Unable to verify | Requires infra access                    |
| Migration safety                         | Unable to verify | No migration framework found             |
| ORM raw-query review                     | Pass             | Raw queries parameterized                |

## Priority remediation plan (recommended timeline)

|                 Timeline | Actions (high-level)                                                                                                                                                                                                |
| -----------------------: | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
|       Immediate (Week 1) | Enable TLS/SSL and validate certs; remove `.env` from VCS and add `.env.example`; issue strong production secrets and move to a secrets manager; configure connection pool limits and timeouts.                     |
|     Short-term (Month 1) | Implement centralized audit logging for auth and schema changes; enforce application-level query timeouts; sanitize error logs; regenerate test password hashes with proper bcrypt cost factor.                     |
| Medium-term (Months 2–3) | Adopt a secrets manager (HashiCorp Vault or AWS Secrets Manager); add DB monitoring, alerting, anomaly detection; implement retention/deletion policies for PII; add row-level security for multi-tenant use cases. |

## Risk score justification (summary)

* Missing TLS and credential management are the largest contributors to risk (+3).
* Good fundamentals (parameterized queries, hashing, validation) reduce risk (-2).
* Missing enterprise controls (audit, timeouts, monitoring) add risk (+1).
* Completing immediate remediation should significantly lower overall risk.

## Conclusion & recommendations

* The app uses solid fundamentals against injection and password attacks, but it is not production-ready as-is.
* Complete immediate remediation items (TLS, secrets, pool/timeouts, logging hygiene) before production deployment.
* Audit every place that constructs queries — a single unsafe concatenation can be catastrophic.
* Enforce server-side tenant scoping for multi-tenant apps and ensure backups/exports scrub or encrypt PII.

<Callout icon="warning" color="#FF6B6B">
  Do not commit secrets or placeholder credentials to source control. Replace them with values stored in a proper secrets manager and use `.env.example` for local setup instructions.
</Callout>

<Callout icon="lightbulb" color="#1CB2FE">
  Parameterized queries and input validation significantly reduce injection risk — maintain these practices while hardening transport, secrets, and logging layers.
</Callout>

Next: API and infrastructure security auditing — focus on authentication flows, token handling, rate limiting, and network-level protections (VPCs, security groups, firewall rules).

References and further reading:

* Postgres row-level security: [https://www.postgresql.org/docs/current/ddl-rowsecurity.html](https://www.postgresql.org/docs/current/ddl-rowsecurity.html)
* Postgres libpq SSL modes: [https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNECT-SSLMODE](https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNECT-SSLMODE)
* Mongoose sanitizeFilter migration: [https://mongoosejs.com/docs/migration.html#sanitizeFilter](https://mongoosejs.com/docs/migration.html#sanitizeFilter)
* node-postgres pooling: [https://node-postgres.com/features/pooling](https://node-postgres.com/features/pooling)
* AWS Secrets Manager: [https://aws.amazon.com/secrets-manager/](https://aws.amazon.com/secrets-manager/)
* HashiCorp Vault: [https://www.vaultproject.io/](https://www.vaultproject.io/)
* OWASP NoSQL Injection: [https://owasp.org/www-community/attacks/NoSQL\_Injection](https://owasp.org/www-community/attacks/NoSQL_Injection)
* CWE-319: Cleartext Transmission of Sensitive Information: [https://cwe.mitre.org/data/definitions/319.html](https://cwe.mitre.org/data/definitions/319.html)

<CardGroup>
  <Card title="Watch Video" icon="video" cta="Learn more" href="https://learn.kodekloud.com/user/courses/claude-code-for-beginners/module/eaf75a67-f28f-4217-a3f9-92d411403129/lesson/39c105db-4e1b-4917-89e4-c08e853bb5db" />
</CardGroup>