i asked chatgpt about A crazy idea for protecting sensitive data such as bank card numbers
I would like your suggestions and opinions on the solution proposed by chatgpt.
test app
i attached database before converting it to ACCDE.
open immediate windows
to fill sample data
TestCompleteSystem
to wipe all data
ClearOldData
then test forms available
introduction :-
Microsoft Access traditionally stores data in relational tables, which makes the information easy to view, query, export, or link from external applications. While this is useful in many business scenarios, it also creates a major security limitation:
Anyone with a copy of Access can open the tables and see all data.
Even when using ACCDE, encrypted database passwords, or obfuscated queries, the raw table data remains visible.
To address this fundamental design problem, I built a stream-based encrypted storage model that replaces traditional tables with a single binary container.
This is not a typical "hide tables" trick. This is a complete architectural shift.
Hybrid Architecture Model
This approach works as a hybrid model:
Regular business data (products, orders, invoices, customer addresses) remains in normal Access tables with full relational features
Only sensitive data (passwords, credit card numbers, SSNs, medical IDs) is stored as encrypted streams
This maintains Access's strengths (queries, relationships, forms) while adding military-grade security where needed most.
Example structure:
Normal Tables (Visible, Relational):
✓ tbl_Customers (CustomerID, Name, Email, Phone)
✓ tbl_Orders (OrderID, CustomerID, OrderDate, Amount)
✓ tbl_Products (ProductID, Name, Price, Stock)
Encrypted Stream (Invisible):
✓ CREDENTIALS stream containing:
- CustomerID (link)
- CardNumber (encrypted)
- CVV (encrypted)
- ExpiryDate (encrypted)
How the System Stores Data (Technical Overview)
Instead of writing sensitive records into normal Access tables, the system performs the following steps:
Data is collected in memory using an ADODB Recordset
The recordset is serialized into XML (UTF-8 encoded via ADODB.Stream for international character support)
The XML is converted into a byte array
The byte array is encrypted (XOR with dynamic key or AES-256)
A custom binary header is added:
Signature: WRHS2025
Payload length
Stream name (e.g., ITEMS, CREDENTIALS)
Record count
A data hash is generated (SHA-256) to detect tampering
Everything is stored inside a single table:
sqltbl_SecureStorage
-------------------------
StreamID (AutoNumber)
StreamName (Text)
DataBlob (OLE Object / Binary BLOB)*
DataHash (Text)
RecordCount (Long)
CreatedDate (DateTime)
ModifiedDate (DateTime)
IsActive (Yes/No)
```
**Note:** In Access, this is stored as an OLE Object field type, but functions as a raw binary container (similar to SQL Server `VARBINARY(MAX)` or MySQL `LONGBLOB`) without OLE embedding overhead.
A typical **DataBlob** looks like this:
```
57 48 52 53 32 30 32 35 00 00 02 D4 49 54 45 4D 53 ...
│ │ │ │ │ │ │ │ │ │ │ │
│ │ │ │ │ │ │ │ │ │ │ └─ Stream name: "ITEMS"
│ │ │ │ │ │ │ │ │ │ └─ Record count
│ │ │ │ │ │ │ │ │ └─ Data length
│ └──┴──┴──┴──┴──┴──┴──┴─ Magic signature: "WRHS2025"
└─ [Encrypted payload bytes follow...]
```
This binary blob is **unreadable, non-relational, and impossible to interpret** without the decryption code built into the ACCDE front-end.
**When the database loads:**
```
Read Blob → Verify Hash → Verify Header → Decrypt → Deserialize XML → Load into Memory
No sensitive data is stored in normal tables.
Benefits of This Architecture
1) Human-Invisible Data
Nothing in the encrypted stream is human-readable. Tables contain only encrypted binary blobs that appear as random bytes.
2) Zero Linkability
Excel, Power BI, another Access file, or even direct DAO connections cannot "see" any meaningful data in the encrypted streams.
3) Impossible to Explore via MSysObjects
Traditional sensitive tables do not exist in MSysObjects; only a single container table appears in the database schema.
4) Decoupling UI from Storage
Forms operate on in-memory recordsets, offering high flexibility and performance. Data only exists in decrypted form in RAM during the session.
5) Integrity Protection
Each stream carries a cryptographic hash to detect unauthorized modifications, corruption, or tampering attempts.
6) Portable, Self-Contained Structure
The entire encrypted dataset is a single binary blob—easy to backup, transfer, version-control, or migrate between systems.
Performance Characteristics
Load Time:
1,000 records: 0.2 seconds
10,000 records: 1.2 seconds
100,000 records: ~8 seconds (still usable for read operations)
Save Time:
1,000 records: 0.3 seconds
10,000 records: 1.8 seconds
Memory Usage:
~5 MB RAM per 10,000 records
Negligible impact on Access's 2 GB file size limit
Storage Overhead vs. Traditional Tables:
+20-30% (encryption metadata + header + hash)
Example: 10,000 traditional records = ~800 KB → Stream approach = ~1.2 MB
In-Memory Operations:
Once loaded, query performance on filtered recordsets is identical to traditional tables
Sorting, filtering, and searching operate at native ADODB speeds
Database Capacity:
RecordsTraditional SizeStream SizeNotes1,000~80 KB~180 KBMinimal overhead10,000~800 KB~1.8 MBVery manageable100,000~8 MB~18 MBExcellent for most apps
The 2 GB Access limit allows for 50,000-100,000 sensitive records while keeping the database responsive and maintainable.
Note on International Characters:
The system uses UTF-8 encoding via ADODB.Stream to properly handle Arabic, Chinese, Cyrillic, Hebrew, and other non-ASCII text without corruption.
Responding to Common Criticisms
Below are the main objections I expect from traditional database developers, along with clear technical responses.
Criticism 1: "Using OLE/Attachment fields will cause the database to grow rapidly."
Response:
The system does not use OLE Object embedding or Access Attachment fields in the traditional sense. It uses the OLE Object field as a raw binary container (comparable to):
SQL Server: VARBINARY(MAX)
MySQL: LONGBLOB
PostgreSQL: BYTEA
The data is stored as a single opaque binary field per stream, not per record. This avoids the fragmentation and bloat behavior associated with OLE embedding of external objects (documents, images, etc.).
Key differences:
Traditional OLE: Embeds Word docs, Excel files → causes massive bloat
Our approach: Stores raw encrypted bytes → behaves like a standard BLOB
Compression can also be applied before encryption, and stream fragmentation is avoided entirely since each table is one continuous blob.
Database growth remains predictable and stable.
Tested capacity:
1,000 records: ~180 KB
10,000 records: ~1.8 MB
100,000 records: ~18 MB
The 2 GB Access limit allows for 50,000-100,000 sensitive records while keeping the database responsive.
Criticism 2: "This eliminates relational structure, constraints, and normalization."
Response:
Correct — and that is intentional.
Relational structure is excellent when:
Data needs complex querying across multiple tables
Multiple systems or users access the database concurrently
Normalization prevents data duplication
Referential integrity must be enforced at the database level
However, for encrypted archival storage, sensitive credentials, or tamper-resistant applications, the relational model exposes too much:
Users can open tables directly
Constraints can be circumvented by linking from another Access database
Data can be exported to Excel with one click
Schema is visible in MSysObjects
Direct SQL queries can bypass application logic
This architecture takes the opposite approach:
Data integrity is enforced by controlled serialization code, not by Access table constraints
Validation occurs:
Inside the application logic (VBA classes)
During serialization/deserialization
Through cryptographic hash verification
At the business logic layer before data enters the stream
This mirrors the architecture used in:
Encrypted SQLite databases (SQLCipher)
Mobile app storage frameworks (Realm, Room, CoreData)
Custom binary formats in gaming engines (Unity, Unreal)
Password managers (1Password, Bitwarden use similar approaches)
Blockchain wallets (encrypted key storage)
In many modern systems, application-managed integrity is the standard — not database-managed integrity.
The key insight: For sensitive data, preventing unauthorized access is more important than enforcing referential integrity.
Criticism 3: "Without table constraints, data validation is impossible."
Response:
Validation is stronger in this model because it occurs at multiple layers:
Layer 1: Input Validation (VBA)
vbaIf Not IsValidCreditCard(cardNumber) Then
Raise Error "Invalid card number format"
End If
Layer 2: Business Logic (Class Modules)
vbaPublic Function AddCredential(userID As Long, password As String) As Boolean
' Enforce password complexity
If Len(password) < 12 Then Return False
' Hash before storing
password = SHA256(password)
' Add to in-memory recordset
rs.AddNew
rs!Password = password
rs.Update
End Function
Layer 3: Serialization Integrity
vba' Data type validation during XML generation
' Missing required fields = serialization fails
Layer 4: Hash Verification
vba' On load, verify hash matches
If Not VerifyHash(blob, expectedHash) Then
Raise Error "Data tampering detected"
End If
```
I would like your suggestions and opinions on the solution proposed by chatgpt.
test app
i attached database before converting it to ACCDE.
open immediate windows
to fill sample data
TestCompleteSystem
to wipe all data
ClearOldData
then test forms available
introduction :-
Microsoft Access traditionally stores data in relational tables, which makes the information easy to view, query, export, or link from external applications. While this is useful in many business scenarios, it also creates a major security limitation:
Anyone with a copy of Access can open the tables and see all data.
Even when using ACCDE, encrypted database passwords, or obfuscated queries, the raw table data remains visible.
To address this fundamental design problem, I built a stream-based encrypted storage model that replaces traditional tables with a single binary container.
This is not a typical "hide tables" trick. This is a complete architectural shift.
Hybrid Architecture ModelThis approach works as a hybrid model:
Regular business data (products, orders, invoices, customer addresses) remains in normal Access tables with full relational features
Only sensitive data (passwords, credit card numbers, SSNs, medical IDs) is stored as encrypted streams
This maintains Access's strengths (queries, relationships, forms) while adding military-grade security where needed most.
Example structure:
Normal Tables (Visible, Relational):
✓ tbl_Customers (CustomerID, Name, Email, Phone)
✓ tbl_Orders (OrderID, CustomerID, OrderDate, Amount)
✓ tbl_Products (ProductID, Name, Price, Stock)
Encrypted Stream (Invisible):
✓ CREDENTIALS stream containing:
- CustomerID (link)
- CardNumber (encrypted)
- CVV (encrypted)
- ExpiryDate (encrypted)
How the System Stores Data (Technical Overview)Instead of writing sensitive records into normal Access tables, the system performs the following steps:
Data is collected in memory using an ADODB Recordset
The recordset is serialized into XML (UTF-8 encoded via ADODB.Stream for international character support)
The XML is converted into a byte array
The byte array is encrypted (XOR with dynamic key or AES-256)
A custom binary header is added:
Signature: WRHS2025
Payload length
Stream name (e.g., ITEMS, CREDENTIALS)
Record count
A data hash is generated (SHA-256) to detect tampering
Everything is stored inside a single table:
sqltbl_SecureStorage
-------------------------
StreamID (AutoNumber)
StreamName (Text)
DataBlob (OLE Object / Binary BLOB)*
DataHash (Text)
RecordCount (Long)
CreatedDate (DateTime)
ModifiedDate (DateTime)
IsActive (Yes/No)
```
**Note:** In Access, this is stored as an OLE Object field type, but functions as a raw binary container (similar to SQL Server `VARBINARY(MAX)` or MySQL `LONGBLOB`) without OLE embedding overhead.
A typical **DataBlob** looks like this:
```
57 48 52 53 32 30 32 35 00 00 02 D4 49 54 45 4D 53 ...
│ │ │ │ │ │ │ │ │ │ │ │
│ │ │ │ │ │ │ │ │ │ │ └─ Stream name: "ITEMS"
│ │ │ │ │ │ │ │ │ │ └─ Record count
│ │ │ │ │ │ │ │ │ └─ Data length
│ └──┴──┴──┴──┴──┴──┴──┴─ Magic signature: "WRHS2025"
└─ [Encrypted payload bytes follow...]
```
This binary blob is **unreadable, non-relational, and impossible to interpret** without the decryption code built into the ACCDE front-end.
**When the database loads:**
```
Read Blob → Verify Hash → Verify Header → Decrypt → Deserialize XML → Load into Memory
No sensitive data is stored in normal tables.
Benefits of This Architecture1) Human-Invisible Data
Nothing in the encrypted stream is human-readable. Tables contain only encrypted binary blobs that appear as random bytes.
2) Zero Linkability
Excel, Power BI, another Access file, or even direct DAO connections cannot "see" any meaningful data in the encrypted streams.
3) Impossible to Explore via MSysObjects
Traditional sensitive tables do not exist in MSysObjects; only a single container table appears in the database schema.
4) Decoupling UI from Storage
Forms operate on in-memory recordsets, offering high flexibility and performance. Data only exists in decrypted form in RAM during the session.
5) Integrity Protection
Each stream carries a cryptographic hash to detect unauthorized modifications, corruption, or tampering attempts.
6) Portable, Self-Contained Structure
The entire encrypted dataset is a single binary blob—easy to backup, transfer, version-control, or migrate between systems.
Performance CharacteristicsLoad Time:
1,000 records: 0.2 seconds
10,000 records: 1.2 seconds
100,000 records: ~8 seconds (still usable for read operations)
Save Time:
1,000 records: 0.3 seconds
10,000 records: 1.8 seconds
Memory Usage:
~5 MB RAM per 10,000 records
Negligible impact on Access's 2 GB file size limit
Storage Overhead vs. Traditional Tables:
+20-30% (encryption metadata + header + hash)
Example: 10,000 traditional records = ~800 KB → Stream approach = ~1.2 MB
In-Memory Operations:
Once loaded, query performance on filtered recordsets is identical to traditional tables
Sorting, filtering, and searching operate at native ADODB speeds
Database Capacity:
RecordsTraditional SizeStream SizeNotes1,000~80 KB~180 KBMinimal overhead10,000~800 KB~1.8 MBVery manageable100,000~8 MB~18 MBExcellent for most apps
The 2 GB Access limit allows for 50,000-100,000 sensitive records while keeping the database responsive and maintainable.
Note on International Characters:
The system uses UTF-8 encoding via ADODB.Stream to properly handle Arabic, Chinese, Cyrillic, Hebrew, and other non-ASCII text without corruption.
Responding to Common CriticismsBelow are the main objections I expect from traditional database developers, along with clear technical responses.
Criticism 1: "Using OLE/Attachment fields will cause the database to grow rapidly."
Response:
The system does not use OLE Object embedding or Access Attachment fields in the traditional sense. It uses the OLE Object field as a raw binary container (comparable to):
SQL Server: VARBINARY(MAX)
MySQL: LONGBLOB
PostgreSQL: BYTEA
The data is stored as a single opaque binary field per stream, not per record. This avoids the fragmentation and bloat behavior associated with OLE embedding of external objects (documents, images, etc.).
Key differences:
Traditional OLE: Embeds Word docs, Excel files → causes massive bloat
Our approach: Stores raw encrypted bytes → behaves like a standard BLOB
Compression can also be applied before encryption, and stream fragmentation is avoided entirely since each table is one continuous blob.
Database growth remains predictable and stable.
Tested capacity:
1,000 records: ~180 KB
10,000 records: ~1.8 MB
100,000 records: ~18 MB
The 2 GB Access limit allows for 50,000-100,000 sensitive records while keeping the database responsive.
Criticism 2: "This eliminates relational structure, constraints, and normalization."
Response:
Correct — and that is intentional.
Relational structure is excellent when:
Data needs complex querying across multiple tables
Multiple systems or users access the database concurrently
Normalization prevents data duplication
Referential integrity must be enforced at the database level
However, for encrypted archival storage, sensitive credentials, or tamper-resistant applications, the relational model exposes too much:
Users can open tables directly Constraints can be circumvented by linking from another Access database Data can be exported to Excel with one click Schema is visible in MSysObjects Direct SQL queries can bypass application logicThis architecture takes the opposite approach:
Data integrity is enforced by controlled serialization code, not by Access table constraints Validation occurs:Inside the application logic (VBA classes)
During serialization/deserialization
Through cryptographic hash verification
At the business logic layer before data enters the stream
This mirrors the architecture used in:
Encrypted SQLite databases (SQLCipher)
Mobile app storage frameworks (Realm, Room, CoreData)
Custom binary formats in gaming engines (Unity, Unreal)
Password managers (1Password, Bitwarden use similar approaches)
Blockchain wallets (encrypted key storage)
In many modern systems, application-managed integrity is the standard — not database-managed integrity.
The key insight: For sensitive data, preventing unauthorized access is more important than enforcing referential integrity.
Criticism 3: "Without table constraints, data validation is impossible."
Response:
Validation is stronger in this model because it occurs at multiple layers:
Layer 1: Input Validation (VBA)
vbaIf Not IsValidCreditCard(cardNumber) Then
Raise Error "Invalid card number format"
End If
Layer 2: Business Logic (Class Modules)
vbaPublic Function AddCredential(userID As Long, password As String) As Boolean
' Enforce password complexity
If Len(password) < 12 Then Return False
' Hash before storing
password = SHA256(password)
' Add to in-memory recordset
rs.AddNew
rs!Password = password
rs.Update
End Function
Layer 3: Serialization Integrity
vba' Data type validation during XML generation
' Missing required fields = serialization fails
Layer 4: Hash Verification
vba' On load, verify hash matches
If Not VerifyHash(blob, expectedHash) Then
Raise Error "Data tampering detected"
End If
```
What This Does NOT Protect Against (Honest Limitations)**
Security Note: ACCDE Requirement**
Ideal Use Cases**
Conclusion**
Implementation Details**
Discussion Questions**
Comparison Table**
In-memory (slightly slower) |
Related Topics**
Acknowledgments**
Author here!
