Critical SQL Execution Flaw in LLM Tool When Preview Card Mismatches Apply Action
A developer discovers a severe flaw in an AI-safe SQL tool where the columns shown in approval previews did not match the actual columns written during execution.
Stock photo for illustration only, not from the actual event
- An LLM SQL tool previewed a 1-column change but silently overwrote an unlisted column during application.
- The root cause was conflating the set of changed columns with the set of columns explicitly written by the statement.
- Validation loops skipped rows where target values already matched, leaving modifications unchecked.
- A critical review missed the entire apply.ts production writing file until a targeted secondary audit revealed 23 new findings.
A software developer building a utility called @hyuga/llm-safe-sql on npm designed a system allowing language models to propose an UPDATE statement, execute it inside a transaction, measure actual before-and-after values, and always roll back. A human then reviews the measurements before any final database commit occurs, ensuring what gets approved is the actual database action rather than just the model's SQL description.
However, last week the creator discovered a bug present since the initial release. In version 0.4.0, executing an update for customer ID 1 changed the name from Tanaka to Sato while leaving the postcode as 00100. The approval card accurately described a 1-row, 1-column change since the postcode was assigned the value it already held.
Stock photo for illustration only, not from the actual event
Before the approved plan was applied, another user modified the customer's postcode to 90210. When the approved plan was subsequently executed, it applied successfully with zero warnings, completely wiping out the external fix while the word postcode never once appeared on the approval card or audit records.
This vulnerability highlights the severe risks of mixing display-layer logic with core security verification in database automation tools. When systems fail to distinguish between what data is intended to change versus what database columns are actually touched by a raw SQL statement, defensive programming patterns like assigning values to themselves can create blind spots that bypass validation checks entirely.
The core issue stemmed from treating the set of columns that change and the set of columns the statement writes as the exact same set, using a single variable name for both. Furthermore, when a WHERE clause matched multiple rows and one already held the target value, the verification loop ran zero times for that row, leaving its contents completely unchecked before and after the write operation.
"The set of columns that change and the set of columns the statement writes are different sets, and I had used one name for both."
Tool Developer of @hyuga/llm-safe-sql
The fix involved decoupling the two sets so that PlanRow now tracks every assigned column regardless of whether the value moves. This ensures tamper digests and validation checks run properly on both ends, causing version 0.4.2 to correctly refuse execution with a ROW_CHANGED error if underlying data shifts after plan creation.
Source: Dev.to
Found something wrong in this article? Report an issue with this article
Comments
Leave a Comment