Encrypting sensitive data at the DAL boundary in .NET — what Lpd demonstrates and what production security requires

Scope: persistence design versus information security

Lpd demonstrates a data-access design pattern: callers pass ordinary DTOs and identifiers; the DAL transforms sensitive fields before persistence and returns decrypted values upon authorized reads. The same principle applies to relational and document databases, while sensitive payloads in diagnostic logs should also be protected and actions recorded in a separate audit trail.

This is not a key-management system or a turnkey regulatory compliance solution. Configuration-based KeyAndIV in examples exists only to make the sample reproducible. In production, key provisioning, access control, rotation, revocation and storage—including KMS/HSM when appropriate—must be designed and reviewed by information-security specialists. That operational subject is outside the DAL tutorial; App.config is not presented as a production secret store.

The existing AES format reuses a supplied IV and provides no ciphertext authentication. This remains a material cryptographic limitation that requires an independent security review before production use, even though secure key custody and algorithm selection are separate from the article's main teaching objective. Distinguishing responsibilities does not make these limitations irrelevant to actual protection.

GDPR, PCI DSS, NIST publications and California CCPA/CPRA differ in scope and obligations. Application-layer encryption alone does not establish a lawful basis, satisfy individual rights, govern retention or cross-border transfers, meet data residency rules, provide authorization or reliable audit integrity, or permit storage of otherwise prohibited payment information. Data-localization rules depend on jurisdiction and data type. No legal or security certification is claimed.

Validation: source review only, not a completed build or test suite. The LiteDB database field is now instance-scoped; applications still need to define connection disposal and dependency lifetimes. These samples are not a production-hardened DAL.

A practical repository-layer walkthrough using Lpd, EF Core, LiteDB, and Serilog. Based on the library source code and included demos. Publication draft for DEV Community, Medium, or C# Corner.

The problem: data leaks through more than the database

A business service handles ordinary, readable domain objects. Persisting names, identifiers, addresses, or account-related records may create regulatory and security obligations. Even when a database encrypts its files, application logs may accidentally retain unprotected values. A useful design draws an explicit boundary: service code operates on domain values; the data access layer (DAL) transforms protected fields before storage and after retrieval; audit events record who, what, when, outcome without storing secrets in the clear.

Lpd offers helpers for strings, byte arrays, streams, files and object graphs. Its examples show SQLite/EF Core, LiteDB and Serilog. The notable idea is one consistent protection policy across persistence and diagnostic channels, not any claim that a NuGet package by itself certifies compliance.

The central principle

API / UI → Authorization → Service / business rules
                         ↓          ↓
                  DAL boundary     Audit events
                  encrypt/write    protect sensitive content
                  read/decrypt     independent protected log sink
                         ↓          ↓
                    SQL / NoSQL    log storage

Every read and write path must apply the same policy. Do not allow application code to bypass the repository via direct raw queries, bulk imports or diagnostics. Keep the audit trail separate from potentially sensitive diagnostic payloads.

The existing Lpd API: simple building blocks

using Lpd.Classes;
using Lpd.Helpers;
using Lpd.Helpers.ForObjects.AsExt;

// Inject from secured configuration / key management; never embed actual keys in source.
KeyAndIV keyAndIV = /* provisioned outside source control */ null!;

string ciphertext = StringsEncryptionHelper.Encrypt("example", keyAndIV);
string plaintext = StringsEncryptionHelper.Decrypt(ciphertext, keyAndIV);

SimplePersonDTO encrypted = dto.Encrypt(keyAndIV);
SimplePersonDTO restored = encrypted.Decrypt(keyAndIV);

The Encrypt and Decrypt object methods return copies; EncryptAsAction and DecryptAsAction operate in place. The reflection routine transforms string and byte[] properties, recursively traverses supported nested objects, List<T> and Dictionary<K,V>, and honors [CiphIgnored]. It does not encrypt every possible .NET type. Treat object graph shape and exclusions as part of the persistence schema.

SQLite + EF Core: representative repository from the demo

public SimplePersonDTO? Find(string id) =>
    _context.People.AsNoTracking()
        .FirstOrDefault(x => x.Id == StringsEncryptionHelper.Encrypt(id, keyAndIV))
        ?.Decrypt(keyAndIV);

public void Add(SimplePersonDTO person)
{
    _context.People.Add(person.Encrypt(keyAndIV));
    _context.SaveChanges();
}

public void Delete(string id)
{
    var encryptedId = StringsEncryptionHelper.Encrypt(id, keyAndIV);
    var stored = _context.People.FirstOrDefault(x => x.Id == encryptedId);
    if (stored is null) return;
    _context.People.Remove(stored);
    _context.SaveChanges();
}

Important API trap: id.Encrypt(keyAndIV) is not equivalent to StringsEncryptionHelper.Encrypt(id, keyAndIV); the generic object helper skips root strings.

Notice the distinction between an encrypted stored entity and a decrypted detached DTO. EF Core change tracking should track stored values, not DTOs mutated into plaintext. The source demo's FindAll projection calls client-side Decrypt inside an EF expression; for portability and predictable behavior, materialize with AsNoTracking().ToList() before decrypting.

LiteDB: encrypt identifiers consistently

public SimplePersonDTO? Find(string id)
{
    string encryptedId = StringsEncryptionHelper.Encrypt(id, KeyAndIV);
    return Collection.FindOne(x => x.Id == encryptedId)?.Decrypt(KeyAndIV);
}

public void Delete(string id) =>
    Collection.Delete(StringsEncryptionHelper.Encrypt(id, KeyAndIV));

This reflects the existing demo contract. It works only when equality on encrypted IDs is stable for the same key and IV. That is possible with Lpd's current construction, but it is a security trade-off. A safer new design would use a separate stable surrogate identifier, or a carefully keyed lookup token distinct from randomized authenticated encryption used for payloads.

Logs: do not confuse encryption with auditability

string logPayload = StringsEncryptionHelper.Encrypt(message, keyAndIV);
logger.Information("Protected payload: {Ciphertext}", logPayload);

// Audit separately; stable event type and outcome remain queryable.
auditLogger.Information("{EventType} {EntityType} {Outcome}",
    "RecordRead", "Person", "Success");

The source demo encrypts the entire formatted log message, including contextual identifiers. This demonstrates protection, but sacrifices normal searchable logging and does not by itself establish tamper resistance, identity attribution, immutability, retention enforcement or review procedures. Prefer structured audit metadata with strictly limited details, plus a separate protected diagnostic payload if truly required.

Security limits identified by source review

Critical for new deployments: BytesEncryptionHelper creates Aes, assigns the caller-supplied IV and key and uses a symmetric CryptoStream without an authentication tag or per-operation nonce construction. Using the same fixed IV with the same key across many records makes encrypted identifiers deterministic but reveals repeated plaintext patterns and is not a recommended approach for new production systems. It also does not establish ciphertext integrity. Consider a versioned wire format using AES-GCM with a fresh nonce for every encryption, or another vetted authenticated-encryption scheme. Existing ciphertext cannot simply be reinterpreted as AES-GCM: plan an explicit authenticated migration and key rotation.

Other findings: KeyAndIV.ToString() outputs Base64 key material; never log or serialize it as diagnostics. Algo.ApplyToFields uses reflection with setters and no reference-cycle detection, and list handling assumes compatible mutable collections. RepoBasic uses a static LiteDB connection, tying all instances to the first configuration; own and dispose connections per scope as appropriate. EF/LiteDB demos mix tracking and detached objects; non-existent record handling and delete-by-ID require discipline. The sample is educational, not an independently reviewed crypto implementation.

Mapping to regulatory controls without overstating coverage

Framework	Relevant security concern	What Lpd can demonstrate	What remains outside Lpd
GDPR, Article 32	Risk-appropriate security; encryption may be appropriate	Application-layer confidentiality	Lawful basis, minimization, rights, retention, access governance, incident handling, testing
PCI DSS 4.x	Protection of stored cardholder data; access and logging	Encrypting specific stored fields and log payloads	Approved strong crypto design, key management, scope, access controls, integrity of audit logs, assessments; never store prohibited post-authorization SAD even encrypted
NIST SP 800-38D / 800-53	Authenticated encryption and control families	Shows a motivation and integration boundary	Current Lpd is not GCM; validation, key lifecycle, risk assessment and control implementation
California CCPA/CPRA	Consumer rights and reasonable security obligations	Helps reduce exposure if correctly engineered	Notices, rights workflows, retention, contracts, reasonable safeguards, regulatory scope

Importantly, encryption does not automatically remove cardholder data from PCI DSS scope. Nor does a functioning AES routine establish FIPS 140 validation.

Production-oriented architecture checklist

Classify fields: encrypt only what requires confidentiality, and document any plaintext searchable identifiers.
Use vetted authenticated encryption with unique nonces; version encrypted payloads and document migrations.
Provision and rotate keys outside source control; restrict decryption privileges and audit access to key material.
Separate stable lookup tokens from encrypted payloads. Avoid reusing deterministic encryption across sensitive columns.
Ensure all persistence paths (CRUD, batch, migrations, exports, cache) follow the same boundary.
Log audit events about actions, actors, timestamps and outcomes; do not log plaintext secrets or full user-provided payloads.
Enforce authorization before repository operations; test denial and cross-tenant isolation.
Validate integrity failure handling, backup/restore, retention, erasure, redaction and recovery workflows.
Run security review and integration tests before representing any deployment as compliant.

Where the other Lpd packages fit

Lpd.ObjectFaker generates synthetic objects and collections for tests. It is related conceptually to Bogus, but has its own API and scope; inspect the demos before selecting based on advanced faker requirements. Lpd.ImageFaker.Core and Lpd.ImageFaker.Windows render images from text/objects in their respective environments. Lpd.Exts.ObjectBinarization is an object serialization extension. These utilities are separate from the encryption compliance boundary.

Final perspective

The durable contribution of Lpd's original design is the architectural boundary: persist protected values and avoid leaking them through logging. To carry this idea into a new regulated application, combine the boundary with authenticated cryptography, stable lookup design, controlled key management, integrity-aware audit records, access control, retention and verifiable operational procedures. The provided GitHub demos are an illustration of the design, not a certification of regulatory conformity.