如何在使用C＃执行SQL查询之前验证它？ [英] How do I validate an SQL query before executing it using C#

问题描述

嗨


我有一个文本框，用户输入他的SQL查询。但是，我需要在数据库中执行之前创建一个验证查询的程序。

例如：

假设用户输入SELECT A1，A2，A3 FROM XYZ

现在，在执行查询之前，我需要检查XYZ表中是否存在A1，A2和A3。如果没有，应该向用户显示错误消息。



我无法想到继续进行的方法。

那么，任何人都可以通过示例代码片段提供有关如何进一步处理的基本想法吗？谢谢



我尝试了什么：



我还没试过截至目前的任何事情。我没有得到任何进一步的逻辑

Hi
I have a textbox where the user enters his SQL query. However,I need to make a program that validates the query before executing it in the database.
For Example:
Suppose the user enters, SELECT A1,A2,A3 FROM XYZ
So now, before executing the query, I need to check whether A1,A2 and A3 exist in the table XYZ or not. If not, the user should be shown an error message.

I'm not able to think of a way to proceed.
So Can anyone give a basic idea with a sample code snippet about how to proceed further? Thank you

What I have tried:

I have not tried anything as of now. I am not getting any logic to proceed further

推荐答案

几乎，你不能 - 特别是当涉及到表名和列名而没有实际运行SQL查询时服务器。这是可能的，但在运行查询之前尝试检查每个细节是一项巨大的投入。

最好的方法可能是在服务器上测试运行查询事务并始终回滚事务。这样，如果有任何问题（但其中一部分有效），它不会导致DB的任何更改。

使用 try-catch-finally 阻止，并在 finally 中回滚事务，它应该给你一个成功/失败的指示。

SqlTransaction类（System.Data.SqlClient） [ ^ ]

Pretty much, you can't - particularly when it comes to table and column names without actually running the query against SQL server. It's possible, but it would be a huge investment in effort to try and check every detail before you ran a query.
The best way would probably be to "test run" the query on the server within a transaction and always roll the transaction back. That way, if there were any problems (but part of it had an effect) it would not cause any changes to the DB anyway.
Use a try-catch-finally block, and rollback the transaction in the finally and it should give you a "success" / "fail" indication.
SqlTransaction Class (System.Data.SqlClient)[^]

这篇关于如何在使用C＃执行SQL查询之前验证它？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！