I’m working with SQL Server and need to ensure that only one SELECT statement runs at a time. The problem is that SQL Server allows multiple commands in one batch without any issues.
For instance, this kind of statement executes successfully:
SELECT 'hello' INSERT INTO users (name) VALUES ('test')
I have a string variable that should contain only a SELECT query, but I want to make sure no other SQL commands can be executed along with it. Is there a way to validate this without manually parsing the string and checking for every possible SQL keyword like INSERT, UPDATE, DELETE, etc?
I’m looking for a built-in method or a more elegant solution to enforce this restriction.
Had this exact problem when I built a reporting dashboard that let users write custom queries. Best solution I found was using Microsoft.SqlServer.TransactSql.ScriptDom - it’s a SQL parser library that breaks down your SQL string into an abstract syntax tree. You can then check if the parsed result only contains SelectStatement objects and reject everything else. Way more reliable than searching for keywords since it handles tricky stuff like comments, string literals, and nested queries. The parser follows SQL Server syntax rules, so users can’t sneak in other commands with formatting tricks.
have you tried a read-only database connection? most db libraries let you set up connections with restricted permissions - they can’t run insert/update/delete commands. way cleaner than parsing sql strings yourself
Interesting challenge, maya! What about using stored procedures? You could whitelist specific SELECT queries and only allow those to run. I’m curious though - what’s your actual use case? Are users submitting queries directly, or is this coming through an application layer?