The application I am currently working on generates a lot of SQL inline queries. All generated SQL is then handed off to a database execution class. I want to write a parsing service for the data execution class that will take a query like this:
我目前正在开发的应用程序生成许多SQL内联查询。然后将生成的所有SQL传递给数据库执行类。我想为数据执行类编写一个解析服务，它将接受如下查询:
SELECT field1, field2, field3 FROM tablename WHERE foo=1 AND bar="baz"
and turn it into something like this:
把它变成这样
SELECT field1, field2, field3 FROM tablename WHERE foo=@p1 AND bar=@p2 blah blah blah
Any thing already written that will accomplish this for me in c# or vb.net? This is intended as a stop gap prior to refactoring the DAL for this project.
在c#或vb.net中有任何已经写好的东西可以帮我完成这个任务吗?这是在重构这个项目的DAL之前的一个停止间隙。
UPDATE: Guys I have a huge application that was ported from Classic ASP to ASP.NET with literally thousands of lines of inline SQL. The only saving grace is all of the generated sql is handed off to a data execution class. I want to capture the sql prior to execution and parameterize them on the fly as a stop gap to rewriting the whole app.
更新:伙计们，我有一个从经典ASP移植到ASP的大型应用程序。包含数千行内联SQL的网络。惟一的可取之处是所有生成的sql都被传递给数据执行类。我想在执行之前捕获sql并动态参数化它们，作为重写整个应用程序的停止间隙。
6 个解决方案
#1
2
Refactor now.
重构了。
You're fooling yourself if you think this one abstraction layer is going to be able to come in quicker and easier. Deep down, you know it increases risk and uncertainty on the project, but you want to kill the SQL injection problem or whatever problem you are fighting with a magic bullet.
如果您认为这个抽象层能够更快更容易地进入，那么您就是在欺骗自己。深入下去，您知道它增加了项目的风险和不确定性，但是您想要杀死SQL注入问题或任何您正在使用魔弹进行战斗的问题。
The time you would be taking to write this new parsing subsystem and regression testing the system, you could probably replace all the inline code with calls to relatively fewer code-generated and tested SPs on your DB. Plus you can do it piece by piece.
在编写这个新的解析子系统和对系统进行回归测试所需的时间内，您可能需要调用相对较少的DB上生成和测试的SPs来替换所有内联代码。而且你可以一块一块地做。
Why build a significant piece of throwaway code which will be hard to debug and isn't really inline with what you want the final architecture to look like?
为什么要构建一段非常重要的一次性代码，这段代码很难调试，并且与您希望最终架构的外观并不一致?
#2
3
Don't do this. This is way too much work. Plus, there are loads of security risks with this approach.
不要这样做。这工作量太大了。此外，这种方法也存在大量的安全风险。
Look into Command objects and parameterized queries, at the minimum.
至少要查看命令对象和参数化查询。
这里有一个小教程。
#3
0
I would second the suggestion to use the Command parameters to do what you want. Any kind of SQL query string parsing is just asking for someone do play an SQL injection game with you. A sample code is below. The Parameters collection is easy to manipulate in the normal way
我建议您使用命令参数来执行所需的操作。任何类型的SQL查询字符串解析都只是要求某人与您玩SQL注入游戏。下面是一个示例代码。参数的收集很容易以正常的方式操作
command.CommandText = "SELECT * FROM table WHERE key_field='?'"
command.Parameters.Append command.CreateParameter(, 8, , , "value") '8 is adBSTR value
set rsTemp = command.Execute
#4
0
o think your task is too much honerous... you should create a very robust parser... i think it's better and easier starting to rewrite application, finding points where queries are generated and refactoring code.
o认为你的任务太过棘手……您应该创建一个非常健壮的解析器……我认为，重新编写应用程序、找到查询生成的点和重构代码会变得更好、更容易。
Good Loock!
Loock好!
#5
0
I can only think of one benefit that parameterizing queries on the fly would bring: it would reduce your application's current vulnerability to SQL injection attacks. In every other way, the best you could possibly hope for is that this hypothetical on-the-fly parser/interpreter wouldn't break anything.
我只能想到动态参数化查询的一个好处:它将减少应用程序当前对SQL注入攻击的脆弱性。在其他方面，您可能希望的最好结果是，这个假想的动态解析器/解释器不会破坏任何东西。
Even if you didn't have to write such a thing yourself (and I bet you do), that's a pretty significant risk to introduce into a production system, especially since it's a stopgap measure that will be discarded when you refactor the app. Is the risk of a SQL injection attack high enough to justify that?
即使你不需要自己写这样的事(我打赌你),这是一个很重大的风险引入到生产系统中,特别是因为它是权宜之计,重构应用程序时将被丢弃。是SQL注入攻击的风险足以证明吗?
#6
0
Have you considered running a substitution regex on the old code? Something that will extract values from the current queries, replace them with parameters, and append after the query line a Command.Parameters.AddWithValue(paramName, paramValue) call might be possible if the current inline SQL all follow the same value (or if nearly all of them do, and you can fix up the remainder in your favorite editor).
您是否考虑在旧代码上运行替换regex ?从当前查询中提取值，用参数替换它们，并在查询行之后附加一个Command.Parameters。如果当前的内联SQL都遵循相同的值(或者几乎所有的都遵循相同的值，并且您可以在您最喜欢的编辑器中修复其余的值)，则可以调用AddWithValue(paramName、paramValue)。
#1
2
Refactor now.
重构了。
You're fooling yourself if you think this one abstraction layer is going to be able to come in quicker and easier. Deep down, you know it increases risk and uncertainty on the project, but you want to kill the SQL injection problem or whatever problem you are fighting with a magic bullet.
如果您认为这个抽象层能够更快更容易地进入，那么您就是在欺骗自己。深入下去，您知道它增加了项目的风险和不确定性，但是您想要杀死SQL注入问题或任何您正在使用魔弹进行战斗的问题。
The time you would be taking to write this new parsing subsystem and regression testing the system, you could probably replace all the inline code with calls to relatively fewer code-generated and tested SPs on your DB. Plus you can do it piece by piece.
在编写这个新的解析子系统和对系统进行回归测试所需的时间内，您可能需要调用相对较少的DB上生成和测试的SPs来替换所有内联代码。而且你可以一块一块地做。
Why build a significant piece of throwaway code which will be hard to debug and isn't really inline with what you want the final architecture to look like?
为什么要构建一段非常重要的一次性代码，这段代码很难调试，并且与您希望最终架构的外观并不一致?
#2
3
Don't do this. This is way too much work. Plus, there are loads of security risks with this approach.
不要这样做。这工作量太大了。此外，这种方法也存在大量的安全风险。
Look into Command objects and parameterized queries, at the minimum.
至少要查看命令对象和参数化查询。
这里有一个小教程。
#3
0
I would second the suggestion to use the Command parameters to do what you want. Any kind of SQL query string parsing is just asking for someone do play an SQL injection game with you. A sample code is below. The Parameters collection is easy to manipulate in the normal way
我建议您使用命令参数来执行所需的操作。任何类型的SQL查询字符串解析都只是要求某人与您玩SQL注入游戏。下面是一个示例代码。参数的收集很容易以正常的方式操作
command.CommandText = "SELECT * FROM table WHERE key_field='?'"
command.Parameters.Append command.CreateParameter(, 8, , , "value") '8 is adBSTR value
set rsTemp = command.Execute
#4
0
o think your task is too much honerous... you should create a very robust parser... i think it's better and easier starting to rewrite application, finding points where queries are generated and refactoring code.
o认为你的任务太过棘手……您应该创建一个非常健壮的解析器……我认为，重新编写应用程序、找到查询生成的点和重构代码会变得更好、更容易。
Good Loock!
Loock好!
#5
0
I can only think of one benefit that parameterizing queries on the fly would bring: it would reduce your application's current vulnerability to SQL injection attacks. In every other way, the best you could possibly hope for is that this hypothetical on-the-fly parser/interpreter wouldn't break anything.
我只能想到动态参数化查询的一个好处:它将减少应用程序当前对SQL注入攻击的脆弱性。在其他方面，您可能希望的最好结果是，这个假想的动态解析器/解释器不会破坏任何东西。
Even if you didn't have to write such a thing yourself (and I bet you do), that's a pretty significant risk to introduce into a production system, especially since it's a stopgap measure that will be discarded when you refactor the app. Is the risk of a SQL injection attack high enough to justify that?
即使你不需要自己写这样的事(我打赌你),这是一个很重大的风险引入到生产系统中,特别是因为它是权宜之计,重构应用程序时将被丢弃。是SQL注入攻击的风险足以证明吗?
#6
0
Have you considered running a substitution regex on the old code? Something that will extract values from the current queries, replace them with parameters, and append after the query line a Command.Parameters.AddWithValue(paramName, paramValue) call might be possible if the current inline SQL all follow the same value (or if nearly all of them do, and you can fix up the remainder in your favorite editor).
您是否考虑在旧代码上运行替换regex ?从当前查询中提取值，用参数替换它们，并在查询行之后附加一个Command.Parameters。如果当前的内联SQL都遵循相同的值(或者几乎所有的都遵循相同的值，并且您可以在您最喜欢的编辑器中修复其余的值)，则可以调用AddWithValue(paramName、paramValue)。