保护数据进入SQL以防止不良行为(Protecting data entered SQL against bad behaviour)

保护数据进入SQL以防止不良行为(Protecting data entered SQL against bad behaviour)

对于ASP.NET（针对SQL Server）应用程序内部的一些功能，我们允许员工用户指定WHERE子句。 然后将此WHERE子句在代码中附加到系统生成的SELECT语句以检索某些数据。

所以从本质上讲，我们最终得到：

SELECT SomeFields FROM SomeTable WHERE UserEnteredWhere

其中粗体是系统， 斜体是用户。 这已经工作了一段时间，并给了我们一些很好的灵活性。

但是，如果用户输入以下内容，显然存在一个很大的问题：

SomeField = 1; DROP TABLE SomeTable;

目前唯一的验证是语句是否以SELECT语句开头，因为我们最终会得到：

SELECT SomeFields FROM SomeTable WHERE SomeField = 1; DROP TABLE SomeTable;

长期解决方案是不允许用户将SQL输入到文本框中，但在短期内我想要支持它。

我能想到的唯一解决方案是在事务中运行每个请求，以确保不会造成长期损害。

有没有人对如何验证用户输入“where子句”以确保它甚至在执行之前没有任何DDL内容有任何建议？

PS。 我确实意识到上述情况很糟糕，不应该这样做，但这是我在遗留系统中所拥有的（我没有写），所以请尽量帮助我解决问题，而不是告诉我不要这样做。 :-)

For some functionality inside our ASP.NET (against SQL Server) app we allow the staff users to specify a WHERE clause. This WHERE clause is then appended in code to a system generated SELECT statement to retrieve some data.

So in essence we end up with:

SELECT SomeFields FROM SomeTable WHERE UserEnteredWhere

where the bold is the system and the italic is the user. This has been working for quite some time and gives us some good flexibility.

However there is clearly a big problem here where if a user enters:

SomeField = 1; DROP TABLE SomeTable;

Currently the only validation is whether the statement starts with a SELECT which will pass because we will end up with:

SELECT SomeFields FROM SomeTable WHERE SomeField = 1; DROP TABLE SomeTable;

The long term solution would be to not allow the user to enter SQL into a text box but in the short term I want to shore this up.

The only solution I can think of is to run each request in a transaction to ensure no long term damage is done.

Has anyone got any suggestions as to how I can validate the user entered "where clause" to ensure that it does not have any DDL content even before it is executed?

PS. I do realise the above is bad and should not be done, but it is what I have in a legacy system (I did not write) so please try and help me with the question instead of telling me don't do that. :-)

最满意答案

您知道正确的答案 - 您已将应用程序暴露给SQL注入。 通常，ad-hoc解决方案并不健全。 而且，将该陈述置于交易中并没有帮助。 毕竟， commit可以是其中一个命令。

一些接口具有“执行单个查询”的等价物。 这几乎可以解决您的问题，因为第二个查询会在应用程序中生成错误。

我建议不要在用户生成的条件中允许以下任何字符/单词：

; update delete insert create exec grant 甚至可能更多 也可能select （除非你想支持子查询）

这可能会在某种程度上限制条件允许的范围。

这不是对注射的保证，而是应该让事情变得更好的事情。

As I needed a quick fix I ultimately ended up tackling this from the SQL Server side of things:

By making use of a separate connection in .NET for these database interactions. T The user is locked down to only allow for SELECT statements as follows:

SQL:

CREATE LOGIN MrReadOnly WITH PASSWORD = 'LetMeIn!'; USE MyDatabase; CREATE USER MrReadOnly FOR LOGIN MrReadOnly; GO EXEC sp_addrolemember N'db_datareader', N'MrReadOnly'