Search for a command to run...
No comments yet. Be the first to comment.
Recently, I asked an AI cybersecurity expert what they believe was the most critical skill a professional in this field should possess. They emphasized that while experience is important, understandin
PortSwigger has taken an important step towards understanding LLM attacks. I studied this topic and wrote down the key points to better understand it. 1. Fundamental Concepts Learn a little more about
Recently, I started reviewing an incredible document: the "AI Security Assessment Blueprint". It has truly opened a new window of knowledge for me, answering so many of my deepest questions about AI v
You received a normal email. No malicious links. No suspicious attachments. But that single email was enough for your company's AI assistant to silently send all your confidential data to an attacker!
AI Security Hub
7 posts
My study on secure architecture awareness continues! This time, I examined the most fundamental layer: Database Schema Design.
Are you a Back-End Developer? What security considerations do you make when modeling your tables? Or are you a Security Expert? How familiar are you with secure database schema architecture?
Secure schema design is a set of principles and policies that protect data at the architectural layer—long before any migration code is written. In this article, we’ll focus on Securing Database Schema Design, not Database Hardening!
Designing a secure schema before coding is the foundation of data security throughout the software lifecycle.
Here are the core principles every architect should follow:
· Non-disclosure of Sequential Primary Keys (Sequential IDs): Using predictable, automatically numbered (like BigInt) for public-facing primary keys (PK) introduces IDOR (Insecure Direct Object Reference) vulnerabilities and allows attackers to guess user or resource IDs via API requests to access unauthorized data.
· Use UUIDv4 or ULID for Public IDs: Ensure that keys exposed in APIs and URLs are unguessable (UUID/ULID). If you rely on bigint for internal join performance, implement a Dual-ID Strategy: keep bigint internally and expose a separate public_id column of type UUID to the outside world.
Scenario (IDOR & Business Logic):
An application exposes an endpoint GET /api/v1/orders/1005. An attacker increments the integer to 1006 to attempt viewing another user's invoice. Also, a competitor creates an account today (ID: 50) and another tomorrow (ID: 70), only to discover that the platform gained only 20 new users that day. Easy peasy lemon squeezy!
Solution:
Keep internal IDs fast with integers; keep public IDs safe with UUIDs.
· Isolate Sensitive Data: Storing Personally Identifiable Information (PII)—such as full names, national IDs, phone numbers, and email addresses—in the same table as non-sensitive operational data expands the risk of data leaks. Isolate PII into dedicated, access-controlled tables.
· Column-Level Encryption & Hashing: Clearly designate at the schema level which columns require Application-Level Encryption or Database-Level Encryption at rest. Ensure authentication credentials (e.g., passwords or tokens) are strictly stored as salted cryptographic hashes (e.g., Argon2id or bcrypt).
Scenario(Over-privileged Access & Unencrypted PII):
A developer runs a simple SELECT * FROM users query to display public usernames on a leaderboard. Because sensitive PII—such as phone numbers, national IDs, and email addresses—is stored in the same table without encryption, this private data is accidentally exposed to analytics tools, application logs, or internal staff.
Solution:
Separate public account data from sensitive PII into dedicated tables, and explicitly define encryption rules for sensitive fields in your data model.
· Enforce Strict String Lengths: Avoid using unbounded text types (e.g., unlimited TEXT or VARCHAR)) for standard user input fields. Defining strict max lengths reduces ReDoS (Regular Expression Denial of Service), Buffer Overflow vectors, and excessive storage consumption.
· Database-Level CHECK Constraints & Nullability: Never rely only on application-level validation. Enforce NOT NULL constraints by default and use database CHECK constraints to validate formats (e.g., regex patterns for URLs/emails or value ranges) directly at the database engine level.
Scenario (Payload Injection & Application Bypass):
If you rely only on frontend or backend code to validate input, an attacker can bypass those checks—or a developer might forget them in a new API. This allows harmful data, like malicious scripts or oversized text, to be injected directly into your database.
Solution:
Set strict data types and check constraints directly inside the database table definition as a final safety step.
· Always explicitly define Foreign Key rules (ON DELETE / ON UPDATE). Automatically deleting related records using CASCADE can accidentally wipe historical data (for example, invoices), while missing rules can create orphaned data. Choose safe constraints, like SET NULL or RESTRICT, to preserve data integrity.
Scenario (Orphaned Data & Clear Financial History):
An application allows users to delete their accounts. If the foreign key on the orders table uses CASCADE, deleting a user instantly clears all their order history, destroying financial records and audit logs. On the other hand, without a foreign key rules definition, deleting the user leaves orphaned order records attached to a non-existent user_id.
Solution:
Explicitly set foreign key constraints (such as ON DELETE RESTRICT or ON DELETE SET NULL) to protect critical business records while maintaining referential integrity across related tables.
· System Auditability Columns: All critical tables must explicitly include created_at and updated_at metadata columns, automatically populated by the system using CURRENT_TIMESTAMP. This ensures reliable event tracking and prevents developers or users from manually tampering with timestamp history.
· Soft Delete Policy (deleted_at): Implement a soft delete strategy using a deleted_at column to safeguard data against accidental human errors or SQL Injection attacks. Hard deletes should never happen immediately. Instead, automated background tasks should remove the data only after the required legal retention period ends.
Soft Delete: Marks records as deleted by setting a deleted_at timestamp while keeping the data intact for recovery and auditing.
Hard Delete: Permanently purges records from the storage disk, making data recovery impossible without backups.
Scenario (Irreversible Data Loss & Missing Audit Trail):
A system administrator accidentally runs an unconstrained DELETE FROM users query, or a compromised backend account executes a destructive SQL Injection. Without a soft delete mechanism, critical business records are immediately lost. Furthermore, without system-managed timestamp metadata, security teams cannot determine when records were created or modified during an incident investigation.
Solution:
Implement immutable created_at/updated_at timestamps using database-level defaults (CURRENT_TIMESTAMP), and apply a soft delete pattern (deleted_at IS NULL) across all application queries. This preserves data for auditability and allows instant recovery from unauthorized or accidental deletion.
· Row/Column Level Security (RLS/CLS) Architecture: Design database schemas to natively support Row-Level Security (RLS) and Column-Level Security (CLS). For instance, including a mandatory tenant_id column across multi-tenant tables enforces strict data isolation at the database level, ensuring users can only access their authorized records.
· Secure Views Abstraction: Avoid giving application users or external integrations direct read access to underlying base tables containing sensitive attributes. Instead, expose restricted Secure Views that mask or exclude sensitive columns (such as PII, hashed passwords, or financial data).
Scenario (Cross-Tenant Data Exposure & Excessive Privilege):
In a SaaS application, a developer forgets to add WHERE tenant_id = ? in a complex search query, or an attacker manipulates API parameters. As a result, User A gains unauthorized access to Customer B's confidential data. Similarly, granting a reporting dashboard direct table access exposes raw password hashes or credit card metadata.
Solution:
Enforce Row-Level Security (RLS) directly in the database engine using policies tied to session variables (e.g., tenant_id). Regardless of application-level bugs, the database automatically filters out unauthorized rows. Additionally, grant the reporting service access only to a Secure View that exposes sanitized, un-sensitive metrics.
· Decoupling Sensitive and Non-Sensitive Attributes: Avoid placing sensitive data (e.g., credit card numbers, IBANs) in the same table as non-sensitive operational attributes (e.g., user preferences or UI themes) just for denormalization or performance optimization. Grouping these attributes within a single row forces access control systems to grant read permissions over sensitive columns even when a service only requires non-sensitive data.
· Schema Normalization for partial access: Normalize database schemas to physically isolate high-risk attributes into dedicated, restricted tables. This separation ensures that application modules operate strictly under the Principle of Least Privilege (PoLP), limiting exposure whether from an over-privileged internal query or a cyber attack.
Scenario (Over-privileged Access via Denormalization):
Imagine a single user's table that stores non-sensitive data (like username and theme_color) right next to sensitive financial data (like credit_card_hash).
When the application's UI service queries the table just to get the user's preferred theme, it reads the entire row—which includes the credit card information. If an attacker exploits a flaw in the UI service or executes a SQL injection through the theme settings, they instantly gain access to the sensitive financial data sitting in that same row.
Solution:
Separate the schema into two distinct tables: a user_preferences table (for UI settings) and a restricted user_payment_methods table (for financial data).
By giving the UI service access only to the user_preferences table, even a successful attack on the UI module cannot reach the payment data—effectively stopping the breach at the database level.
· Data Expiration & Retention Architecture: Database schemas must natively support automated data lifecycle management to comply with privacy regulations and minimize breach impact. Temporary and short-lived records—such as authentication tokens, user sessions, temporary audit logs, and One-Time Passwords (OTPs)—should explicitly incorporate timestamp columns (e.g., expires_at) or Time-To-Live (TTL) mechanisms.
· Automated Purging for Storage Limitation: Storing stale or expired sensitive data creates unnecessary liability and increases the attack surface. Schemas must be architected so that background cleanup processes, database TTL indexes, or automated partitioning policies can efficiently identify and hard-delete expired records without degrading database performance.
Scenario (Indefinite Storage of Temporary Credentials):
A system stores password reset tokens and active session IDs in a user_sessions table without defined expiration timestamps or background cleanup jobs. Over time, millions of expired tokens accumulate in the database. If an attacker breaches the database via an unpatched vulnerability, they can execute offline analysis or session hijacking using valid-looking residual tokens that were never properly invalidated or erased.
Solution:
Design the user_sessions schema with an indexed expires_at column (or leverage native TTL mechanisms like Redis TTL or MongoDB TTL indexes). A scheduled background process or automated database policy continuously purges rows where expires_at < CURRENT_TIMESTAMP. This ensures that even in a worst-case database leak, no usable or expired authentication credentials remain on disk.
· Information Leakage via Database Indexes: Creating database indexes (e.g., CREATE INDEX) directly on encrypted or hashed sensitive attributes can unintentionally expose data patterns or raw values. Standard, unsalted hashes always produce the same output for the same input. If an attacker gains access to these database indexes, they can easily use precomputed dictionary lists (Rainbow Tables) or pattern analysis to reverse the hashes and reveal the original sensitive information.
· Secure Indexing Architecture: To enable efficient searching over sensitive records without compromising confidentiality, build search indexes using strong, keyed cryptographic constructions—such as Keyed-Hash Message Authentication Code (HMAC) with a secret key or blind indexing techniques. This ensures the index remains cryptographically secure and unsearchable to unauthorized parties even if database files are leaked.
Scenario (Standard Hashes are Easily Guessed):
To protect users' National IDs while still allowing search, a developer saves a "hashed" (scrambled) version of the IDs in the database index. The problem is that standard hashes always produce the same output for the same ID. If hackers steal the database, they can use a "Rainbow Table"—a massive, pre-calculated cheat sheet of all possible IDs and their hashes—to easily translate the scrambled text back into real National IDs.
Solution:
Instead of a standard hash, use a Keyed-Hash (like HMAC). This method mixes the National ID with a unique "Secret Key" before hashing it, and only stores this final result in the index. The secret key is kept safely outside the database. Now, even if hackers steal the database, their cheat sheets (Rainbow Tables) are completely useless because they don't have your secret key to reverse the data.
· Automated & Version-Controlled Schema Migrations: All database schema modifications must be executed through version-controlled migration scripts tracked in source control (e.g., Git) rather than making manual and direct changes in production environments. Changing database structures manually or without code review greatly increases the risk of security mistakes—such as accidentally exposing private columns, removing access restrictions, or breaking compliance rules.
· Traceability & Migration Safety Controls: Applying infrastructure-as-code principles to database schemas ensures complete auditability and repeatability across environments. Automated CI/CD deployment pipelines must validate, test, and apply migration scripts, eliminating human error and ensuring that schema changes adhere to defined security standards before reaching production.
Scenario (Production Misconfiguration via Manual Changes):
During an urgent system update, a database administrator manually alters a production table using a raw SQL statement to add a new column for temporary debugging. In the process, they forget to apply the necessary Row-Level Security (RLS) policies or column permissions. Because the change was made manually outside of source control, it bypasses code review, goes unrecorded, and leaves a persistent security hole in the live environment.
Solution:
Enforce a strict policy where production databases accept schema changes only via automated migration tools (like Flyway, Liquibase, or ORM migrations) built into an automated CI/CD pipeline.
Under this approach, every schema update is saved as a version-controlled code file in Git. It must pass a peer code review and be tested in a staging environment before going live. This process creates a clear audit trail and guarantees that no manual changes ever happen in production.
Following these rules won't solve all your life problems, but at least your database won't end up on the front page of the news tomorrow!
