如何美元，用户生成的SQL查询p $ pvent SQL注入 [英] How to prevent Sql-Injection on User-Generated Sql Queries

问题描述

我有一个项目（私人，ASP.net网站，密码以https保护），其中的要求之一是，用户可以输入将直接查询数据库的SQL查询。我需要能够让这些质疑，而$ P $从做损坏数据库本身pventing他们，访问或更新数据，他们不应该能够访问/更新。

I have a project (private, ASP.net website, password protected with https) where one of the requirements is that the user be able to enter Sql queries that will directly query the database. I need to be able to allow these queries, while preventing them from doing damage to the database itself, and from accessing or updating data that they shouldn't be able to access/update.

我想出了以下规则执行：

I have come up with the following rules for implementation:

1. 使用了一个数据库用户只有选择表/视图和更新表的权限（从而般的落差任何其它命令/修改/截断/插入/删除只是无法运行）。


2. 确认语句开始的话选择或更新


3. 验证（使用正则表达式），有没有在声明中分号不是由单引号，空格和信件包围着的实例。 （这里的想法是，他们可以包括第二查询的唯一方法是将结束先用分号不是输入字符串的一部分）。


4. 验证（使用正则表达式），用户有权访问被查询/更新的表，包含联接等，这包括所有的子查询。 （的，这将实现的方式之一是，用户将被使用的一组表名的实际上不存在于数据库中，则查询解析的一部分将在正确的对应表的名称来代替到查询）

1. Use a db user that only has permission for Select Table/View and Update Table (thus any other commands like drop/alter/truncate/insert/delete will just not run).
2. Verify that the statement begins with the words "Select" or "Update"
3. Verify (using Regex) that there are no instances of semi-colons in the statement that are not surrounded by single-quotes, white space and letters. (The thought here is that the only way that they could include a second query would be to end the first with a semi-colon that is not part of an input string).
4. Verify (using Regex) that the user has permission to access the tables being queried/updated, included in joins, etc. This includes any subqueries. (Part of the way that this will be accomplished is that the user will be using a set of table names that do not actually exist in the database, part of the query parsing will be to substitute in the correct corresponding table names into the query).

我缺少什么？

我们的目标是，用户能够查询/更新表他们有权以任何他们认为合适的访问，并prevent任何意外或恶意企图破坏分贝。 （既然要求是用户生成SQL，我也没有办法参数化查询或使用任何内置的工具，我所知道的消毒吧）

The goal is that the users be able to query/update tables to which they have access in any way that they see fit, and to prevent any accidental or malicious attempts to damage the db. (And since a requirement is that the user generate the sql, I have no way to parametrize the query or sanitize it using any built-in tools that I know of).

推荐答案

好了，你已经有足够多的人告诉你的。不要把做，所以如果他们无法劝阻你，这里有一些想法：

Well, you already have enough people telling you "dont' do this", so if they aren't able to dissuade you, here are some ideas:

提供良好的，不要试图排除不良结果
（我认为正确的术语白名单 VS 黑名单）
到那个，我的意思的不看邪恶的或无效的东西折腾了的（有太多的方面，它可以书面或变相），而不是寻找的有效的东西，包括和折腾出一切。

INCLUDE the Good, Don't try to EXCLUDE the bad
(I think the proper terminology is Whitelisting vs Blacklisting ) By that, I mean don't look for evil or invalid stuff to toss out (there are too many ways it could be written or disguised), instead look for valid stuff to include and toss out everything else.

您已经在另一评论提到，你正在寻找用户友好的表名的列表，而代以实际模式表名。这就是我所说的 - 如果你要做到这一点，然后用字段名做，太

You already mentioned in another comment that you are looking for a list of user-friendly table names, and substituting the actual schema table names. This is what I'm talking about--if you are going to do this, then do it with field names, too.

我仍然在朝着某种形式的图形用户界面倾斜，虽然：选择表格，查看这里，你想看到这里选择字段，用一些下拉菜单来建立一个where子句，等等。一个痛苦，但仍可能更容易。

I'm still leaning toward a graphical UI of some sort, though: select tables to view here, select fields you want to see here, use some drop-downs to build a where clause, etc. A pain, but still probably easier.

这篇关于如何美元，用户生成的SQL查询p $ pvent SQL注入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！