chore(dx): improve query assertion errors

farnabaz · farnabaz · commit 52dbc3c4d8b8 · 2025-11-28T17:54:07.000+01:00
diff --git a/src/runtime/internal/security.ts b/src/runtime/internal/security.ts
@@ -13,19 +13,19 @@ const SQL_SELECT_REGEX = /^SELECT (.*) FROM (\w+)( WHERE .*)? ORDER BY (["\w,\s]
  */
 export function assertSafeQuery(sql: string, collection: string) {
   if (!sql) {
-    throw new Error('Invalid query')
+    throw new Error('Invalid query: Query cannot be empty')
   }
 
   const cleanedupQuery = cleanupQuery(sql)
 
   // Query is invalid if the cleaned up query is not the same as the original query (it contains comments)
   if (cleanedupQuery !== sql) {
-    throw new Error('Invalid query')
+    throw new Error('Invalid query: SQL comments are not allowed')
   }
 
   const match = sql.match(SQL_SELECT_REGEX)
   if (!match) {
-    throw new Error('Invalid query')
+    throw new Error('Invalid query: Query must be a valid SELECT statement with proper syntax')
   }
 
   const [_, select, from, where, orderBy, order, limit, offset] = match
@@ -38,43 +38,44 @@ export function assertSafeQuery(sql: string, collection: string) {
       && !columns[0]?.match(SQL_COUNT_REGEX)
       && !columns[0]?.match(/^"[a-z_]\w+"$/i)
     ) {
-      throw new Error('Invalid query')
+      throw new Error(`Invalid query: Column '${columns[0]}' has invalid format. Expected *, COUNT(), or a quoted column name`)
     }
   }
   else if (!columns.every(column => column.match(/^"[a-z_]\w+"$/i))) {
-    throw new Error('Invalid query')
+    throw new Error('Invalid query: Multiple columns must be properly quoted and alphanumeric')
   }
 
   // FROM
   if (from !== `_content_${collection}`) {
-    throw new Error('Invalid query')
+    const collection = String(from || '').replace(/^_content_/, '')
+    throw new Error(`Invalid query: Collection '${collection}' does not exist`)
   }
 
   // WHERE
   if (where) {
     if (!where.startsWith(' WHERE (') || !where.endsWith(')')) {
-      throw new Error('Invalid query')
+      throw new Error('Invalid query: WHERE clause must be properly enclosed in parentheses')
     }
     const noString = cleanupQuery(where, { removeString: true })
     if (noString.match(SQL_COMMANDS)) {
-      throw new Error('Invalid query')
+      throw new Error('Invalid query: WHERE clause contains unsafe SQL commands')
     }
   }
 
   // ORDER BY
   const _order = (orderBy + ' ' + order).split(', ')
   if (!_order.every(column => column.match(/^("[a-zA-Z_]+"|[a-zA-Z_]+) (ASC|DESC)$/))) {
-    throw new Error('Invalid query')
+    throw new Error('Invalid query: ORDER BY clause must contain valid column names followed by ASC or DESC')
   }
 
   // LIMIT
   if (limit !== undefined && !limit.match(/^ LIMIT \d+$/)) {
-    throw new Error('Invalid query')
+    throw new Error('Invalid query: LIMIT clause must be a positive number')
   }
 
   // OFFSET
   if (offset !== undefined && !offset.match(/^ OFFSET \d+$/)) {
-    throw new Error('Invalid query')
+    throw new Error('Invalid query: OFFSET clause must be a positive number')
   }
 
   return true

Original file line number	Diff line number	Diff line change
`@@ -13,19 +13,19 @@ const SQL_SELECT_REGEX = /^SELECT (.) FROM (\w+)( WHERE .)? ORDER BY (["\w,\s]`
`13`	`13`	`*/`
`14`	`14`	`export function assertSafeQuery(sql: string, collection: string) {`
`15`	`15`	`if (!sql) {`
`16`		`- throw new Error('Invalid query')`
	`16`	`+ throw new Error('Invalid query: Query cannot be empty')`
`17`	`17`	`}`
`18`	`18`
`19`	`19`	`const cleanedupQuery = cleanupQuery(sql)`
`20`	`20`
`21`	`21`	`// Query is invalid if the cleaned up query is not the same as the original query (it contains comments)`
`22`	`22`	`if (cleanedupQuery !== sql) {`
`23`		`- throw new Error('Invalid query')`
	`23`	`+ throw new Error('Invalid query: SQL comments are not allowed')`
`24`	`24`	`}`
`25`	`25`
`26`	`26`	`const match = sql.match(SQL_SELECT_REGEX)`
`27`	`27`	`if (!match) {`
`28`		`- throw new Error('Invalid query')`
	`28`	`+ throw new Error('Invalid query: Query must be a valid SELECT statement with proper syntax')`
`29`	`29`	`}`
`30`	`30`
`31`	`31`	`const [_, select, from, where, orderBy, order, limit, offset] = match`
`@@ -38,43 +38,44 @@ export function assertSafeQuery(sql: string, collection: string) {`
`38`	`38`	`&& !columns[0]?.match(SQL_COUNT_REGEX)`
`39`	`39`	`&& !columns[0]?.match(/^"[a-z_]\w+"$/i)`
`40`	`40`	`) {`
`41`		`- throw new Error('Invalid query')`
	`41`	+ throw new Error(`Invalid query: Column '${columns[0]}' has invalid format. Expected *, COUNT(), or a quoted column name`)
`42`	`42`	`}`
`43`	`43`	`}`
`44`	`44`	`else if (!columns.every(column => column.match(/^"[a-z_]\w+"$/i))) {`
`45`		`- throw new Error('Invalid query')`
	`45`	`+ throw new Error('Invalid query: Multiple columns must be properly quoted and alphanumeric')`
`46`	`46`	`}`
`47`	`47`
`48`	`48`	`// FROM`
`49`	`49`	if (from !== `_content_${collection}`) {
`50`		`- throw new Error('Invalid query')`
	`50`	`+ const collection = String(from \|\| '').replace(/^_content_/, '')`
	`51`	+ throw new Error(`Invalid query: Collection '${collection}' does not exist`)
`51`	`52`	`}`
`52`	`53`
`53`	`54`	`// WHERE`
`54`	`55`	`if (where) {`
`55`	`56`	`if (!where.startsWith(' WHERE (') \|\| !where.endsWith(')')) {`
`56`		`- throw new Error('Invalid query')`
	`57`	`+ throw new Error('Invalid query: WHERE clause must be properly enclosed in parentheses')`
`57`	`58`	`}`
`58`	`59`	`const noString = cleanupQuery(where, { removeString: true })`
`59`	`60`	`if (noString.match(SQL_COMMANDS)) {`
`60`		`- throw new Error('Invalid query')`
	`61`	`+ throw new Error('Invalid query: WHERE clause contains unsafe SQL commands')`
`61`	`62`	`}`
`62`	`63`	`}`
`63`	`64`
`64`	`65`	`// ORDER BY`
`65`	`66`	`const _order = (orderBy + ' ' + order).split(', ')`
`66`	`67`	`if (!_order.every(column => column.match(/^("[a-zA-Z_]+"\|[a-zA-Z_]+) (ASC\|DESC)$/))) {`
`67`		`- throw new Error('Invalid query')`
	`68`	`+ throw new Error('Invalid query: ORDER BY clause must contain valid column names followed by ASC or DESC')`
`68`	`69`	`}`
`69`	`70`
`70`	`71`	`// LIMIT`
`71`	`72`	`if (limit !== undefined && !limit.match(/^ LIMIT \d+$/)) {`
`72`		`- throw new Error('Invalid query')`
	`73`	`+ throw new Error('Invalid query: LIMIT clause must be a positive number')`
`73`	`74`	`}`
`74`	`75`
`75`	`76`	`// OFFSET`
`76`	`77`	`if (offset !== undefined && !offset.match(/^ OFFSET \d+$/)) {`
`77`		`- throw new Error('Invalid query')`
	`78`	`+ throw new Error('Invalid query: OFFSET clause must be a positive number')`
`78`	`79`	`}`
`79`	`80`
`80`	`81`	`return true`