I’m working with a legacy system that creates tons of dynamic SQL queries with hardcoded values. These queries get passed to a central database handler class before execution.
I need to build a parser that can take queries like:
SELECT name, email, status FROM users WHERE id=123 AND category='active'
And automatically convert them to parameterized versions:
SELECT name, email, status FROM users WHERE id=@param1 AND category=@param2
Does anyone know of existing libraries or tools in C# or VB.NET that can handle this conversion? This is meant to be a temporary solution while we work on a bigger refactor.
For context: I inherited a massive ASP.NET application (converted from classic ASP) with thousands of inline SQL statements scattered throughout. The good news is they all go through one data access class, so I can intercept and modify them there. I want to automatically parameterize these queries before they hit the database as a security improvement until we can properly rewrite everything.
what’s your data access layer using - SqlCommand or something else? you might be able to hook into the parameter collection instead of parsing. could be way simpler than rebuilding queries from scratch.
Had the same problem a few years back when I was updating an old financial app. Skip building your own parser - use Microsoft.SqlServer.TransactSql.ScriptDom instead. It’s got solid SQL parsing built in. This library breaks down your SQL into an abstract syntax tree, so you can find literal values and swap them with parameters automatically. Way better than regex since it actually understands SQL - handles nested queries, string escaping, different data types, all that stuff. Then you just rebuild the parameterized query and pull the parameter values into a collection. There’s definitely a learning curve, but it’s rock solid compared to rolling your own solution. Saved me from tons of edge cases that would’ve been a nightmare with regex.
yeah, regex can be super useful, but def take it slow & test a ton! sometimes SQL can be quirky, & edge cases can sneak up on ya. also maybe look into some ORM if it’s a bigger project down the line.