SQLi is /so/ Last ShmooCon

SQLi is /so/ Last ShmooCon

Speakers: Falcon Darkstar Momot

Conference: ShmooCon

YouTube: https://www.youtube.com/watch?v=5YHcw-qj094

Overview

In "SQLi is /so/ Last ShmooCon," Falcon Darkstar Momot, a Product Security Manager at a database company, challenges the persistent prevalence of SQL injection vulnerabilities and proposes a fundamental shift in how applications interact with databases. The talk argues that the traditional model of sending raw, programmable SQL statements from application servers to highly privileged database users is inherently insecure and outdated. Momot advocates for an architectural pattern that re-imagines the database itself as a secure, well-defined API, leveraging advanced PostgreSQL features to enforce granular access control and significantly reduce the attack surface.

This presentation is particularly relevant in an era where SQL injection consistently ranks on the OWASP Top 10, despite decades of known mitigation techniques. Momot demonstrates that common "solutions" like prepared statements and Object-Relational Mappers (ORMs) often fall short, failing to address the root cause: the over-privileging of application users within the database and the implicit trust placed in the application layer. The core thesis is that by externalizing and formalizing the data interaction logic directly within the database, organizations can achieve a more robust and defensible posture against one of the oldest and most damaging web vulnerabilities.

The talk is crucial for developers, architects, and security professionals who are grappling with the enduring challenge of SQL injection. It provides a detailed, actionable framework for designing database interactions that are secure by default, moving beyond superficial fixes to a deeper, architectural solution. By embracing the database's inherent capabilities for access control and procedural logic, Momot presents a compelling vision for making SQL injection a relic of the past.

Background

▶ Watch: Why SQL injection persists: Programmable data interchange. (1:00)

The enduring problem of SQL injection (SQLi) stems from a fundamental design flaw in how many applications handle data interaction: treating SQL statements as mere data interchange formats rather than executable code. As Momot emphasizes, SQL is a fully programmable language, and allowing user-controlled input to directly influence its construction creates an "all-powerful interface" that attackers can exploit. This issue is particularly acute given that database-oriented stacks underpin virtually all modern applications, often handling the most sensitive data.

Traditional approaches to mitigating SQLi have proven insufficient. Prepared statements, while a crucial first line of defense, are not a panacea. They primarily protect against injecting malicious data into parameters, but they don't guard against dynamic query construction (e.g., variable table names in search filters), nor do they prevent attacks leveraging bugs in SQL libraries or application-level vulnerabilities like code execution or credential theft. If an attacker can obtain database credentials, prepared statements offer no protection.

Similarly, Object-Relational Mappers (ORMs) are often touted as a solution, abstracting SQL away from developers. However, Momot points out that ORMs themselves have been found to contain SQL injection vulnerabilities due to their internal SQL generation logic. Moreover, ORMs don't address the underlying issue of over-privileged database users. Many applications, following patterns established by systems like WordPress, run their database connections as "root" or highly privileged users (e.g., postgres superuser), capable of performing any operation, including schema definition (DDL), data modification (DML), and data querying (DQL). This "run all code as root" mentality is a severe anti-pattern in any other system environment but remains common practice for databases.

PostgreSQL, like other sophisticated database management systems, offers a rich set of access control mechanisms that are frequently underutilized in typical application development. These include:

• Roles: Database users with specific permissions.
• Granular Permissions: The ability to grant or deny specific permissions (e.g., SELECT, INSERT, UPDATE, DELETE) on individual relations (tables), columns, or even rows (row-based access control).
• Views: Virtual tables that can expose a subset of data or columns, abstracting the underlying tables.
• Functions and Stored Procedures: Pre-defined blocks of SQL code that encapsulate complex logic and can be executed by users with appropriate permissions.

The core problem, then, is not a lack of defensive capabilities within the database, but a failure to leverage them. Applications are typically granted broad permissions, effectively bypassing the database's native security features and leaving the application layer as the sole enforcement point for data access policies—a point that is notoriously vulnerable to injection attacks.

Key Findings

▶ Watch: The anti-pattern of constant root user database access. (4:10)

Momot's key findings revolve around a paradigm shift: treating the database not merely as a storage disk, but as a robust, secure API where interactions are explicitly defined and strictly controlled. This approach leverages underutilized, yet powerful, features within PostgreSQL to build a defense-in-depth model that fundamentally limits the impact of SQL injection.

The central discovery is that by combining roles with minimal privileges and SECURITY DEFINER functions, developers can create a secure abstraction layer. The application's database user is granted only the EXECUTE permission on a limited set of pre-defined functions. These functions, in turn, are defined with SECURITY DEFINER, allowing them to execute with the elevated privileges of their definer (e.g., a database superuser) rather than the restricted privileges of the caller. This effectively acts as a setuid bit for SQL, enabling the application to perform necessary operations without ever needing direct, broad access to underlying tables. For instance, an application user might only be able to call update_profile(json_data), and this function, running as its superuser definer, safely performs the UPDATE operation on the profile table, ensuring that the operation adheres to pre-defined logic and parameters.

Another crucial finding is the integration of stateless authentication and authorization directly into the database using libraries like PG JWT. This allows the database to validate JSON Web Tokens (JWTs), extract user identity, and make fine-grained access control decisions (e.g., row-based access control) without relying on the application server to enforce user-specific permissions. The application server merely passes the JWT to the database; the database then trusts the JWT, not the application server, for user identity. This significantly reduces the trust placed in the application layer, making it harder for an attacker who has compromised the application server to escalate privileges or access unauthorized data, provided they cannot tamper with or harvest the JWTs.

By defining all allowed data interactions as stored procedures and enforcing access through these procedures and JWT validation, the system effectively creates a database-native API. This API dictates precisely what operations can be performed, how data can be manipulated, and under what conditions, thereby eliminating the possibility of arbitrary SQL execution by the application user. If a DELETE procedure is never defined, for example, the application user simply cannot delete records, regardless of how they try to craft a SQL injection payload. This architectural pattern moves much of the security enforcement from the often vulnerable application layer into the more robust and controlled database environment.

Technical Deep Dive

▶ Watch: Granular PostgreSQL roles and permissions for database defense. (4:45)

The technical core of Momot's proposal rests on a sophisticated application of PostgreSQL's security features. To understand the defense, it's helpful to first understand the common PostgreSQL exploitation models that this architecture aims to mitigate. When an attacker gains SUPERUSER privileges (often the default for application connections), they can perform a wide array of damaging actions:

• EXECUTE: The most direct form of SQL injection, allowing arbitrary SQL commands to run.
• SECURITY DEFINER: While a defensive feature in Momot's model, if an attacker can manipulate or create such functions, they can escalate privileges. This allows functions to run with the permissions of their creator, not the caller.
• CREATE EXTENSION: Allows loading .so (shared object) files from disk into the database, enabling Remote Code Execution (RCE). This is a powerful primitive for full system compromise if an attacker has SUPERUSER and can write to disk.
• COPY TO PROGRAM: Essentially a system() call, allowing the execution of arbitrary operating system commands, piping query data into them. Another RCE vector for SUPERUSER.
• CREATE SERVER FOREIGN DATA WRAPPER: Enables connections to external servers, potentially facilitating data exfiltration or lateral movement.
• PG LARGE OBJECTS IMPORT/EXPORT: Provides file read/write primitives, crucial for uploading .so files for CREATE EXTENSION or exfiltrating sensitive files.

Momot's strategy is to prevent the application from ever reaching a state where it can leverage these powerful primitives. This is achieved by restricting the application's database role to only EXECUTE specific stored procedures or functions.

Consider the implementation of an API-centric database interaction:

1. Defining the Database API with Functions:

Instead of allowing direct UPDATE or INSERT statements from the application, all data modification and retrieval operations are encapsulated within PostgreSQL functions. For example, an update_profile function might look like this:

The critical part here is SECURITY DEFINER. This clause ensures that the update_profile function will execute with the privileges of the user who created it (e.g., a database administrator), not the privileges of the application user who calls it. The application's database role would only have GRANT EXECUTE ON FUNCTION update_profile(...) TO application_role; and no direct permissions on the users table itself. This means even if an attacker achieves SQL injection, they can only execute the update_profile function with its predefined logic and parameters, not arbitrary UPDATE statements against the users table.

1. Stateless User Binding with PG JWT:

To enforce user-specific authorization within these functions, Momot introduces the concept of binding the database request to the application user in a stateless manner using PG JWT. The PG JWT library allows PostgreSQL to validate JSON Web Tokens directly, eliminating the need for the application server to manage session state or implicitly trust its own user context.

The flow is:

• User authenticates with an Identity Provider (IDP).
• IDP issues a JWT to the client.
• Client sends the JWT along with its request to the application server.
• The application server passes the JWT directly to the database.
• The database uses PG JWT to verify the token's signature, check its expiry, and extract claims (e.g., user ID).

An example check_user function, which can be called by other SECURITY DEFINER functions, demonstrates this:

This check_user function, also SECURITY DEFINER, ensures that only valid, authenticated users can proceed. Subsequent API functions can then call check_user to obtain a trusted user ID, which can then be used in WHERE clauses for row-based access control:

This architecture ensures that the database itself makes the ultimate authorization decision, based on a trusted JWT and predefined logic, rather than relying on potentially compromised application-level checks.

1. Limitations and Caveats:

While powerful, this pattern is not a silver bullet. Momot warns about "injectable stored procedures." If the SQL within a SECURITY DEFINER function itself is dynamically constructed using user input without proper sanitization (e.g., using EXECUTE format(...) in an unsafe way), it can still lead to SQL injection, potentially allowing privilege escalation to the superuser who defined the function. This highlights the need for static analysis of stored procedure code and careful design. The pattern primarily protects against injection into application-level queries, shifting the potential vulnerability surface to the stored procedure definitions themselves.

This approach creates a robust defense-in-depth model where the application's database user has minimal privileges, and all critical operations are mediated by secure, predefined, and authorization-aware database functions.

Demo / Proof of Concept

▶ Watch: Using views and functions as an abstraction layer for security. (5:40)

While Falcon Darkstar Momot's talk did not feature a live, interactive "demo" in the traditional sense of executing code on stage, the presentation itself served as an architectural proof of concept. Momot provided detailed conceptual examples and pseudocode snippets for implementing the proposed secure database API.

The examples, such as the update_profile function utilizing SECURITY DEFINER and the check_user function integrating PG JWT for stateless authentication, clearly illustrate how the core components of this architecture would function. These code examples, combined with the comprehensive explanation of PostgreSQL's underlying capabilities (roles, permissions, views, functions, row-based access control), demonstrate the feasibility and effectiveness of the proposed pattern. The talk effectively "walks through" the implementation of a secure API directly within the database, showing how an application user with minimal privileges can still perform complex, authorized operations through carefully constructed and secured stored procedures.

Defensive Implications

▶ Watch: Understanding PostgreSQL exploitation: Security Definer and Create Extension. (6:00)

The architectural pattern proposed by Falcon Darkstar Momot offers profound defensive implications, fundamentally altering the security posture of database-driven applications.

1. Enforced Principle of Least Privilege (PoLP): This is the cornerstone. The application's database user is granted only the absolute minimum necessary permissions: EXECUTE on a select set of functions. It has no direct SELECT, INSERT, UPDATE, or DELETE permissions on underlying tables. This drastically reduces the impact of a successful SQL injection, as an attacker can only call the predefined functions, not arbitrary SQL.
2. Reduced SQL Injection Surface: By abstracting all data interaction behind a fixed set of functions, the opportunity for injecting arbitrary SQL into application-generated queries is almost entirely eliminated. The "API" of the database becomes the only interaction point, and this API is designed to be injection-resistant.
3. Database-Native Authorization: Integrating PG JWT or similar stateless authentication mechanisms directly into the database allows the database itself to validate user identity and enforce row-based access control. This removes the reliance on the application server for critical authorization decisions, preventing privilege escalation even if the application server is compromised (assuming JWTs are not harvested or tampered with).
4. Simplified Security Auditing: With all data access funneled through well-defined functions, auditing becomes simpler. Instead of parsing potentially complex and dynamic application-generated SQL, security teams can focus on reviewing the logic within the stored procedures themselves, ensuring they are free from vulnerabilities and enforce correct business rules.
5. Enhanced Defense in Depth: The model encourages layering security controls. For instance, the application database role can be further restricted to prevent DDL (Data Definition Language) operations, adding extensions, or using file read/write primitives (PG LARGE OBJECTS, COPY TO PROGRAM). This makes it significantly harder for an attacker to pivot from a SQL injection to RCE or file system access.
6. Potential for "Serverless" Database Interaction: As Momot hints, with a sufficiently robust and well-defined database API, it might be possible to expose the database connection directly to the internet (with extreme caution and robust network controls), effectively eliminating layers of the traditional web application stack and their associated vulnerabilities. Tools like PostgREST demonstrate this concept for trusted microservice environments, but Momot's approach makes it potentially viable for untrusted public access if the API is sufficiently hardened.
7. Focus on Stored Procedure Security: While the attack surface for application-level SQLi is reduced, a new focus emerges: securing the stored procedures themselves. Static analysis tools become crucial for identifying potential "injectable stored procedures" where dynamic SQL within the function might reintroduce vulnerabilities. Developers must be vigilant when using constructs like EXECUTE format(...) within SECURITY DEFINER functions.
8. Greenfield vs. Brownfield Applications: Implementing this pattern is easiest for new ("greenfield") applications. For existing ("brownfield") applications, it can be a significant undertaking, requiring a gradual refactoring of database interaction logic. However, Momot suggests that it's not necessary to "boil the ocean" and that even partial adoption can yield security benefits.

By shifting much of the security enforcement to the database layer, this model provides a powerful framework for building applications that are inherently more resilient to SQL injection and associated data breaches.

Key Takeaways

• SQL injection persists because applications often treat SQL as mere data and run with excessive database privileges, failing to leverage robust database-native security features.
• By designing database interactions as a strict API using PostgreSQL functions and stored procedures, applications can enforce granular control over data access.
• The SECURITY DEFINER clause is crucial, allowing restricted application users to execute privileged database operations safely by running the function with the definer's (e.g., superuser's) permissions.
• Integrating PG JWT enables stateless, database-native authentication and authorization, allowing the database to validate user identity and enforce row-based access control independently of the application server.
• This architectural pattern significantly reduces the attack surface for SQL injection, moving security enforcement from the application layer to the more controlled database environment.
• While powerful, developers must still be cautious of "injectable stored procedures" where dynamic SQL within SECURITY DEFINER functions could reintroduce vulnerabilities; static analysis is key here.

About the Speaker(s)

Falcon Darkstar Momot is a Product Security Manager at Ivan, a database company. He brings a unique blend of technical and business acumen, having recently completed an MBA alongside his Master's and Bachelor's degrees in accounting. With a background in exploring data platforms, Momot is passionate about addressing fundamental security challenges in database interactions. He has previously spoken on related topics, including the necessity of using compilers to safely output SQL, demonstrating a long-standing interest in robust data interchange security.