Summary
Snowflake SQL Injection via Compile-Time Constant Folding with SYSTEM$WAIT You have confirmed SQL injection against a Snowflake backend. The injected expression executes. The behavior is repeatable. The database is clearly processing your input. But you cannot extract a single value. Every normal error-based extraction technique returns the same thing: HTTP 200, followed by an empty array. Only malformed payloads, such as an unmatched single quote, return a verbose SQL compilation error and even then, the response reveals parts of the generated query rather than the values you are trying to extract. But perhaps the application is only hiding one category of database error. Snowflake produces another category earlier in the query lifecycle, and that second category still reaches the response. That gives us a path to go deeper and, eventually, reach full extraction. The technique in this write-up is about moving the failure from execution time to compilation time. The Starting Point The vulnerable functionality was a reporting endpoint that generated dashboard data from a JSON request: POST /api/v2/report/generate HTTP/1.1 Host: reports.[REDACTED].com Content-Type: application/json Authorization: Bearer <session_jwt> { … … “filter_type”: “1”, … } Under normal conditions, the endpoint returned several hundred rows. Injecting an expression into filter_type changed the response: { “filter_type”: “1/(SELECT 0)” } The server responded with: HTTP/1.1 200 OK Content-Type: application/json {“data”:[]} There was no stack trace, no database error, and no HTTP 500. Only an empty result. The same thing happened with every standard extraction attempt: 1/(SELECT 0) → 200 {“data”:[]} TO_NUMBER(CURRENT_USER()) → 200 {“data”:[]} CAST(CURRENT_ROLE() AS INT) → 200 {“data”:[]} 1 AND 1=(SELECT 1/0) → 200 {“data”:[]} This is where the injection can appear to become unreadable. The payloads compile, begin executing, fail as intended, and then disappear into the same empty response. So im starting to question further: Can I make Snowflake fail before execution begins? Snowflake runs your query in three phase A useful way to understand this behavior is to divide Snowflake query processing into three broad stages:
- Parsing. Snowflake determines whether the SQL text is syntactically valid.
- Compilation. it resolves identifiers, checks types, folds expressions, and builds the execution plan.
- Execution. the compiled plan runs against the warehouse and accesses data. Steps 1 and 2 happen before any data is touched. They run on the cloud services layer, and no warehouse is involved yet. Step 3 is where your query actually runs against tables, where runtime behavior occurs: scans, conversions involving row values, arithmetic failures, timeouts, and other failures produced while the plan is running. This splits errors into two groups, and Snowflake numbers them differently: Compilation errors → error code 0001, raised in step 2 Execution errors → error code 5001, raised in step 3 A compilation error means the query never ran. An execution error means it started running and then hit a problem. Execution Failure vs Compilation Failure A reporting backend calls the database, and if the query blows up mid-flight it returns an empty result set so the dashboard renders instead of a stack trace. That handler almost always catches the execution failure, because that is the one that happens in normal operation: a bad row, a timeout, a type problem in the data. Conceptually, the handler may behave like this: Run query ↓ Execution succeeds → return rows Execution fails → return [] But compilation failures are different. To the application they look like a bug in its own SQL template, which is not supposed to happen in production. So they often get passed straight back to the caller, or written verbatim into the response’s error field. Conceptually, the handler may behave like this: { “filter_type”: “1’” } The response contained a verbose Snowflake error: HTTP/1.1 200 OK {“error”:“SQL compilation error: syntax error line 7 at position 10 unexpected ”=2 and parent_id=?) OR (1’. … FROM [SCHEMA].[TABLE_A] a JOIN [SCHEMA].[TABLE_B] b ON a.id = b.ref_id … a.COL_1 AS “c1”, a.COL_2 AS “c2”, … …This input had N total errors.“} Table names, join conditions, aliases, and dozens of column names. The application returned all of it, in the same 200 response shape that gave you an empty array a moment ago. Same parameter. Same request. The difference was simply when the query failed. Runtime failures were converted into empty data. Compilation failures were returned to the caller. That asymmetry became the extraction channel. Why Normal Error-Based Payloads Failed Traditional error-based SQL injection works by making the database fail on purpose, in a way that places the value you want to read inside the resulting error message. For example: TO_NUMBER(CURRENT_USER()) The intended sequence is straightforward:
- Evaluate CURRENT_USER() . - Obtain the current username as a string.
- Convert that string to a number.
- Trigger a conversion error that includes the original value. The same principle applies to other common techniques. You might divide by a value you want to test, cast a string into an incompatible type, or place a subquery inside an expression that is expected to fail. The problem is that this happens during execution. Get Alvin Ferdiansyah’s stories in your inbox Join Medium for free to get updates from this writer. Every one of those techniques needs the value first. To obtain it, Snowflake must resolve a session function, evaluate an expression, or read data through a subquery. The payload therefore compiles successfully, moves into the execution phase, and only then produces the error you designed. That is exactly where the application is waiting for it. The runtime error is caught, replaced with an empty result, and returned as: {“data”:[]} This is why the wall feels so solid. The entire standard error-based toolkit fails at the same stage for the same reason, so every attempt produces the same response. Uniform failure may look like filtering, but here it is really a phase problem. The question is no longer how to make the runtime error louder. It is : How do I make the query fail in step 2 ( Compilation ) instead of step 3 ( Execution ) ? The technique: SYSTEMWAIT : SYSTEMWAIT(2) The useful behavior appears when its argument is not a literal constant. Snowflake requires arguments to certain system functions to be known during compilation. When an expression is supplied instead, the compiler attempts to reduce that expression into a constant. This optimizer behavior is commonly known as constant folding. Consider: SYSTEMWAIT(TO_NUMBER(‘ANALYTICS_PROD’, 18, 0)) But SYSTEMWAIT requires a valid constant numeric argument. The string cannot become that number, so compilation stops. More importantly, Snowflake includes the folded expression in the compilation error: { "filter_type": "SYSTEMWAIT(CURRENT_DATABASE())” } The response becomes: SQL compilation error: argument 0 to function SqlIdentifier{… identifierName=SYSTEMWAIT} needs to be constant, found 'TO_NUMBER('ANALYTICS_PROD', 18, 0)' The value appears inside the complaint about the value. That is the extraction channel. The payload never reaches normal execution. Instead, it forces Snowflake to reason about the argument during compilation, then fail while describing what it found. Because the application still returned compilation errors, the folded value reached the response. What Constant Folding Can and Cannot Reveal The technique has a clear limit, and it is important to be precise about it. Constant folding only works on expressions Snowflake can resolve without reading table data. Session functions such as: CURRENT_DATABASE() CURRENT_USER() CURRENT_ROLE() qualify because their values already exist in the session context. Snowflake can resolve them during compilation, before a warehouse is involved. A subquery is different. Reading a table requires a warehouse, which means step 3. The optimizer cannot fold it, so the error never mentions your data: { "filter_type": "SYSTEMWAIT((SELECT COUNT(*) FROM [SCHEMA].[TABLE_A]))” } … … HTTP/1.1 200 OK {“data”:[]} So this channel is bounded. You get session and connection metadata. You do not get rows. That is still worth having, especially from an injection point that had initially appeared confirmed but unreadable. Fixing It
- Bind the parameters. Every reporting framework worth using ships parameterised query elements that enforce a data type, and they exist precisely to stop this. String substitution into a query template is the actual bug; everything above is downstream of it.
- Validate the request body at the entry point and reject fields your own front end never sends. Injection points frequently sit on legacy parameter names no client has referenced in years.
- Do not return database errors to clients at all, not the compilation ones either. The handler here was careful with one error group and passed the other straight through, which is the entire reason any of this was readable. A handler that filters by phase leaks by phase.
- Set a query tag on every query the reporting service issues, so failed compilations in QUERY_HISTORY trace back to the caller and the input. Both channels are loud in query history and neither was attributable. Closing At first, the application appeared to have closed the error channel completely. Every familiar extraction technique ended the same way: HTTP 200, an empty array, and nothing useful returned. But the errors were not disappearing equally. The handler only knew how to hide failures that happened after execution had begun. Compilation errors still followed another path, and SYSTEM$WAIT created a small opening by forcing Snowflake to reason about the value one phase earlier. That was the useful reminder here. When every payload fails in the same way, it does not always mean the database has nothing left to say. Sometimes the response is only showing you where the application stopped listening.