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.
我有一个项目(私有,ASP.net网站,密码用https保护),其中一个要求是用户能够输入将直接查询数据库的Sql查询。我需要能够允许这些查询,同时防止它们对数据库本身造成损害,以及访问或更新它们无法访问/更新的数据。
I have come up with the following rules for implementation:
我已经提出了以下实施规则:
- 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).
- Verify that the statement begins with the words "Select" or "Update"
- 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).
- 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).
使用仅具有Select Table / View和Update Table权限的db用户(因此任何其他命令,如drop / alter / truncate / insert / delete都不会运行)。
验证语句以“选择”或“更新”字样开头
验证(使用正则表达式)语句中没有半冒号实例,这些实例未被单引号,空格和字母包围。 (这里的想法是,他们可以包含第二个查询的唯一方法是使用不是输入字符串一部分的分号结束第一个查询。
验证(使用Regex)用户是否有权访问正在查询/更新的表,包含在连接中等。这包括任何子查询。 (实现这一目标的部分方式是用户将使用一组实际上不存在于数据库中的表名,部分查询解析将用正确的相应表名替换为查询) 。
Am I missing anything?
我错过了什么吗?
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).
目标是用户能够以他们认为合适的任何方式查询/更新他们有权访问的表,并防止任何意外或恶意企图破坏数据库。 (并且由于要求是用户生成sql,我无法使用我所知道的任何内置工具对查询进行参数化或清理它)。
15 个解决方案
#1
3
Well, you already have enough people telling you "dont' do this", so if they aren't able to dissuade you, here are some ideas:
好吧,你已经有足够的人告诉你“不要这样做”,所以如果他们不能劝阻你,这里有一些想法:
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.
您已经在另一条评论中提到过,您正在寻找一个用户友好的表名列表,并替换实际的架构表名。这就是我所说的 - 如果你打算这样做,那么也要用字段名称来做。
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.
我仍然倾向于某种形式的图形用户界面:选择要在此处查看的表格,选择要在此处查看的字段,使用一些下拉列表来构建where子句,等等。痛苦,但仍然可能更容易。
#2
16
This is a bad idea, and not just from an injection-prevention perspective. It's really easy for a user that doesn't know any better to accidentally run a query that will hog all your database resources (memory/cpu), effectively resulting in a denial of service attack.
这是一个坏主意,而不仅仅是从注射预防的角度来看。对于不知道更好的用户来说,如果不小心运行会占用所有数据库资源(内存/ CPU)的查询,实际上很容易,从而导致拒绝服务攻击。
If you must allow this, it's best to keep a completely separate server for these queries, and use replication to keep it pretty close to an exact mirror of your production system. Of course, that won't work with your UPDATE requirement.
如果必须允许这样做,最好为这些查询保留一个完全独立的服务器,并使用复制使其非常接近生产系统的精确镜像。当然,这不适用于您的UPDATE要求。
But I want to say again: this just won't work. You can't protect your database if users can run ad hoc queries.
但我想再说一遍:这是行不通的。如果用户可以运行即席查询,则无法保护数据库。
#3
4
what about this stuff, just imagine the select is an EXEC
那些东西怎么样,想想选择是EXEC
select convert(varchar(50),0x64726F70207461626C652061)
#4
4
My gut reaction is that you should focus on setting the account privileges and grants as tightly as possible. Look at your RDBMS security documentation thoroughly, there may well be features you are not familiar with that would prove helpful (e.g. Oracle's Virtual Private Database, I believe, may be useful in this kind of scenario).
我的直觉反应是你应该专注于尽可能紧密地设置帐户权限和授予。彻底查看您的RDBMS安全文档,可能有一些您不熟悉的功能会证明是有用的(例如,Oracle的虚拟专用数据库,我相信在这种情况下可能会有用)。
In particular, your idea to "Verify (using Regex) that the user has permission to access the tables being queried/updated, included in joins, etc." sounds like you would be trying to re-implement security functionality already built into the database.
特别是,您的想法是“验证(使用正则表达式)用户有权访问正在查询/更新的表,包括在连接中等”。听起来你会试图重新实现已经内置到数据库中的安全功能。
#5
3
What you're missing is the ingenuity of an attacker finding holes in your application.
您所缺少的是攻击者在您的应用程序中找到漏洞的独创性。
I can virtually guarantee you that you won't be able to close all the holes if you allow this. There might even be bugs in the database engine you don't know about but they do that allows an SQL statement you deem safe to wreck havoc in your system.
我几乎可以保证,如果允许的话,你将无法关闭所有的洞。甚至可能存在您不知道的数据库引擎中的错误,但是他们这样做允许您认为安全的SQL语句可以破坏系统中的破坏。
In short: This is a monumentally bad idea!
简而言之:这是一个非常糟糕的主意!
#6
2
As the others indicate, letting end-users do this is not a good idea. I suspect the requirement isn't really that the user really needs ad-hoc SQL, but rather a way to get and update data in ways not initially forseen. To allow queries, do as Joel suggests and keep a "read only" database, but use a reporting application such as Microsoft Reporting Services or Data Dynamics Active reports to allow users to design and run ad-hoc reports. Both I believe have ways to present users with a filtered view on "their" data.
正如其他人指出的那样,让最终用户这样做并不是一个好主意。我怀疑这个要求并不是用户真正需要临时SQL,而是一种以最初没有预期的方式获取和更新数据的方法。要允许查询,请按照Joel建议并保留“只读”数据库,但使用报告应用程序(如Microsoft Reporting Services或Data Dynamics Active报告)来允许用户设计和运行临时报告。我相信两者都有办法向用户呈现“他们的”数据的过滤视图。
For the updates, it is more tricky- I don't know of existing tools to do this. One option may be to design your application so that developers can quickly write plugins to expose new forms for updating data. The plugin would need to expose a UI form, code for checking that the current user can execute it, and code for executing it. Your application would load all plugins and expose the forms that a user has access to.
对于更新,它更棘手 - 我不知道现有的工具来做到这一点。一种选择可能是设计您的应用程序,以便开发人员可以快速编写插件以公开用于更新数据的新表单。该插件需要公开UI表单,用于检查当前用户是否可以执行它的代码,以及用于执行它的代码。您的应用程序将加载所有插件并公开用户有权访问的表单。
#7
2
Event seemingly secure technology like Dynamic LINQ, is not safe from code injection issues and you are talking about providing low-level access.
事件看似安全的技术,如Dynamic LINQ,对于代码注入问题并不安全,而且您正在谈论提供低级访问。
No matter how hard you sanitize queries and tune permissions, it probably will still be possible to freeze your DB by sending over some CPU-intensive query.
无论您如何努力清理查询和调整权限,都可能仍然可以通过发送一些CPU密集型查询来冻结您的数据库。
So one of the "protection options" is to show up a message box telling that all queries accessing restricted objects or causing bad side-effects will be logged against user's account and reported to the admins immediately.
因此,其中一个“保护选项”是显示一个消息框,告知所有访问受限对象或导致不良副作用的查询都将记录在用户的帐户中并立即报告给管理员。
Another option - just try to look for a better alternative (i.e. if you really need to process & update data, why not expose API to do this safely?)
另一个选择 - 只是尝试寻找更好的替代方案(即如果你真的需要处理和更新数据,为什么不公开API来安全地做到这一点?)
#8
1
One (maybe overkill) option could be use a compiler for a reduced SQL language. Something like using JavaCC with a modified SQL grammar that only allows SELECT statements, then you might receive the query, compile it and if it compiles you can run it.
一个(可能是矫枉过正的)选项可以使用编译器来减少SQL语言。像使用带有修改的SQL语法的JavaCC只允许SELECT语句,然后你可能会收到查询,编译它,如果它编译,你可以运行它。
For C# i know Irony but never used it.
对于C#我知道反讽但从未使用它。
#9
1
You can do a huge amount of damage with an update statement.
您可以使用更新声明进行大量破坏。
I had a project similar to this, and our solution was to walk the user through a very annoying wizard allowing them to make the choices, but the query itself is constructed behind the scenes by the application code. Very laborious to create, but at least we were in control of the code that finally executed.
我有一个与此类似的项目,我们的解决方案是让用户通过一个非常讨厌的向导,允许他们做出选择,但查询本身是由应用程序代码在幕后构建的。创建非常费力,但至少我们控制了最终执行的代码。
#10
1
The question is, do you trust your users? If your users have had to log into the system, you are using HTTPS & taken precautions against XSS attacks then SQL Injection is a smaller issue. Running the queries under a restricted account ought to be enough if you trust the legitimate users. For years I've been running MyLittleAdmin on the web and have yet to have a problem.
问题是,你相信你的用户吗?如果您的用户必须登录系统,您正在使用HTTPS并采取预防XSS攻击的措施,那么SQL注入是一个较小的问题。如果您信任合法用户,则在受限制的帐户下运行查询应该足够了。多年来我一直在网上运行MyLittleAdmin并且还没有遇到问题。
If you run under a properly restricted SQL Account select convert(varchar(50),0x64726F70207461626C652061) won't get very far and you can defend against resource hogging queries by setting a short timeout on your database requests. People could still do incorrect updates, but then that just comes back to do you trust your users?
如果您在正确受限的SQL帐户下运行,则选择convert(varchar(50),0x64726F70207461626C652061)将不会走得太远,您可以通过在数据库请求上设置短暂超时来防止资源占用查询。人们仍然可以做不正确的更新,但那回来是你相信你的用户?
You are always taking a managed risk attaching any database to the web, but then that's what backups are for.
您总是将管理风险附加到Web上的任何数据库,但那就是备份的用途。
#11
0
If they don't have to perform really advanced queries you could provide a ui that only allows certain choices, like a drop down list with "update,delete,select" then the next ddl would automatically populate with a list of available tables etc.. similar to query builder in sql management studio.
如果他们不必执行真正的高级查询,您可以提供一个只允许某些选择的UI,例如带有“更新,删除,选择”的下拉列表,那么下一个ddl会自动填充可用表的列表等。类似于sql management studio中的查询生成器。
Then in your server side code you would convert these groups of ui elements into sql statements and use a parametrized query to stop malicious content
然后在您的服务器端代码中,您将这些ui元素组转换为sql语句并使用参数化查询来阻止恶意内容
#12
0
This is a terribly bad practice. I would create a handful of stored procedures to handle everything you'd want to do, even the more advanced queries. Present them to the user, let them pick the one they want, and pass your parameters.
这是一个非常糟糕的做法。我会创建一些存储过程来处理您想要做的所有事情,甚至是更高级的查询。将它们呈现给用户,让他们选择他们想要的那个,并传递您的参数。
The answer above mine is also extremely good.
我上面的答案也非常好。
#13
0
Although I agree with Joel Coehoorn and SQLMenace, some of us do have "requirements". Instead of having them send ad Hoc queries, why not create a visual query builder, like the ones found in the MS sample applications found at asp.net, or try this link.
虽然我同意Joel Coehoorn和SQLMenace,但我们中的一些人确实有“要求”。不要让他们发送ad Hoc查询,为什么不创建一个可视化查询构建器,就像在asp.net上找到的MS示例应用程序中找到的那样,或者尝试这个链接。
I am not against the points made by Joel. He is correct. Having users (remember we are talking users here, they could care less about what you want to enforce) throw queries is like an app without a "Business Logic Layer", not to mention the additional questions to be answered when certain results does not match other supporting application results.
我并不反对乔尔提出的观点。他是对的。有用户(记住我们在这里谈论用户,他们可能不太关心你想要强制执行什么)抛出查询就像没有“业务逻辑层”的应用程序,更不用说当某些结果不匹配时需要回答的其他问题其他支持申请结果。
#14
0
here is another example
这是另一个例子
the hacker doesn't need to know the real table name, he/she can run undocumented procs like this
黑客不需要知道真正的表名,他/她可以像这样运行无证的procs
sp_msforeachtable 'print ''?'''
just instead of print it will be drop
只是而不是打印它会下降
#15
0
Plenty of answers saying that it's a bad idea but somethimes that's what the requirements insist on. There is one gotcha that I haven't spotted mentioned in the "If you have to do it anyway" suggestions though: Make sure that any update statements include a WHERE clause. It's all too easy to run
大量的答案说这是一个坏主意,但有些时候需要坚持的东西。有一个问题我没有在“如果你必须这样做”的建议中提到但是:确保任何更新语句都包含一个WHERE子句。这太容易了
UPDATE ImportantTable
SET VitalColumn = NULL
and miss out the important
并错过重要的
WHERE UserID = @USER_NAME
If an update is required across the whole table then it's easy enough to add
如果整个表需要更新,那么添加就很容易了
WHERE 1 = 1
Requiring the where clause doesn't stop a malicious user from doing bad things but it should reduce accidental whole table changes.
要求where子句不会阻止恶意用户做坏事,但它应该减少意外的整个表更改。
#1
3
Well, you already have enough people telling you "dont' do this", so if they aren't able to dissuade you, here are some ideas:
好吧,你已经有足够的人告诉你“不要这样做”,所以如果他们不能劝阻你,这里有一些想法:
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.
您已经在另一条评论中提到过,您正在寻找一个用户友好的表名列表,并替换实际的架构表名。这就是我所说的 - 如果你打算这样做,那么也要用字段名称来做。
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.
我仍然倾向于某种形式的图形用户界面:选择要在此处查看的表格,选择要在此处查看的字段,使用一些下拉列表来构建where子句,等等。痛苦,但仍然可能更容易。
#2
16
This is a bad idea, and not just from an injection-prevention perspective. It's really easy for a user that doesn't know any better to accidentally run a query that will hog all your database resources (memory/cpu), effectively resulting in a denial of service attack.
这是一个坏主意,而不仅仅是从注射预防的角度来看。对于不知道更好的用户来说,如果不小心运行会占用所有数据库资源(内存/ CPU)的查询,实际上很容易,从而导致拒绝服务攻击。
If you must allow this, it's best to keep a completely separate server for these queries, and use replication to keep it pretty close to an exact mirror of your production system. Of course, that won't work with your UPDATE requirement.
如果必须允许这样做,最好为这些查询保留一个完全独立的服务器,并使用复制使其非常接近生产系统的精确镜像。当然,这不适用于您的UPDATE要求。
But I want to say again: this just won't work. You can't protect your database if users can run ad hoc queries.
但我想再说一遍:这是行不通的。如果用户可以运行即席查询,则无法保护数据库。
#3
4
what about this stuff, just imagine the select is an EXEC
那些东西怎么样,想想选择是EXEC
select convert(varchar(50),0x64726F70207461626C652061)
#4
4
My gut reaction is that you should focus on setting the account privileges and grants as tightly as possible. Look at your RDBMS security documentation thoroughly, there may well be features you are not familiar with that would prove helpful (e.g. Oracle's Virtual Private Database, I believe, may be useful in this kind of scenario).
我的直觉反应是你应该专注于尽可能紧密地设置帐户权限和授予。彻底查看您的RDBMS安全文档,可能有一些您不熟悉的功能会证明是有用的(例如,Oracle的虚拟专用数据库,我相信在这种情况下可能会有用)。
In particular, your idea to "Verify (using Regex) that the user has permission to access the tables being queried/updated, included in joins, etc." sounds like you would be trying to re-implement security functionality already built into the database.
特别是,您的想法是“验证(使用正则表达式)用户有权访问正在查询/更新的表,包括在连接中等”。听起来你会试图重新实现已经内置到数据库中的安全功能。
#5
3
What you're missing is the ingenuity of an attacker finding holes in your application.
您所缺少的是攻击者在您的应用程序中找到漏洞的独创性。
I can virtually guarantee you that you won't be able to close all the holes if you allow this. There might even be bugs in the database engine you don't know about but they do that allows an SQL statement you deem safe to wreck havoc in your system.
我几乎可以保证,如果允许的话,你将无法关闭所有的洞。甚至可能存在您不知道的数据库引擎中的错误,但是他们这样做允许您认为安全的SQL语句可以破坏系统中的破坏。
In short: This is a monumentally bad idea!
简而言之:这是一个非常糟糕的主意!
#6
2
As the others indicate, letting end-users do this is not a good idea. I suspect the requirement isn't really that the user really needs ad-hoc SQL, but rather a way to get and update data in ways not initially forseen. To allow queries, do as Joel suggests and keep a "read only" database, but use a reporting application such as Microsoft Reporting Services or Data Dynamics Active reports to allow users to design and run ad-hoc reports. Both I believe have ways to present users with a filtered view on "their" data.
正如其他人指出的那样,让最终用户这样做并不是一个好主意。我怀疑这个要求并不是用户真正需要临时SQL,而是一种以最初没有预期的方式获取和更新数据的方法。要允许查询,请按照Joel建议并保留“只读”数据库,但使用报告应用程序(如Microsoft Reporting Services或Data Dynamics Active报告)来允许用户设计和运行临时报告。我相信两者都有办法向用户呈现“他们的”数据的过滤视图。
For the updates, it is more tricky- I don't know of existing tools to do this. One option may be to design your application so that developers can quickly write plugins to expose new forms for updating data. The plugin would need to expose a UI form, code for checking that the current user can execute it, and code for executing it. Your application would load all plugins and expose the forms that a user has access to.
对于更新,它更棘手 - 我不知道现有的工具来做到这一点。一种选择可能是设计您的应用程序,以便开发人员可以快速编写插件以公开用于更新数据的新表单。该插件需要公开UI表单,用于检查当前用户是否可以执行它的代码,以及用于执行它的代码。您的应用程序将加载所有插件并公开用户有权访问的表单。
#7
2
Event seemingly secure technology like Dynamic LINQ, is not safe from code injection issues and you are talking about providing low-level access.
事件看似安全的技术,如Dynamic LINQ,对于代码注入问题并不安全,而且您正在谈论提供低级访问。
No matter how hard you sanitize queries and tune permissions, it probably will still be possible to freeze your DB by sending over some CPU-intensive query.
无论您如何努力清理查询和调整权限,都可能仍然可以通过发送一些CPU密集型查询来冻结您的数据库。
So one of the "protection options" is to show up a message box telling that all queries accessing restricted objects or causing bad side-effects will be logged against user's account and reported to the admins immediately.
因此,其中一个“保护选项”是显示一个消息框,告知所有访问受限对象或导致不良副作用的查询都将记录在用户的帐户中并立即报告给管理员。
Another option - just try to look for a better alternative (i.e. if you really need to process & update data, why not expose API to do this safely?)
另一个选择 - 只是尝试寻找更好的替代方案(即如果你真的需要处理和更新数据,为什么不公开API来安全地做到这一点?)
#8
1
One (maybe overkill) option could be use a compiler for a reduced SQL language. Something like using JavaCC with a modified SQL grammar that only allows SELECT statements, then you might receive the query, compile it and if it compiles you can run it.
一个(可能是矫枉过正的)选项可以使用编译器来减少SQL语言。像使用带有修改的SQL语法的JavaCC只允许SELECT语句,然后你可能会收到查询,编译它,如果它编译,你可以运行它。
For C# i know Irony but never used it.
对于C#我知道反讽但从未使用它。
#9
1
You can do a huge amount of damage with an update statement.
您可以使用更新声明进行大量破坏。
I had a project similar to this, and our solution was to walk the user through a very annoying wizard allowing them to make the choices, but the query itself is constructed behind the scenes by the application code. Very laborious to create, but at least we were in control of the code that finally executed.
我有一个与此类似的项目,我们的解决方案是让用户通过一个非常讨厌的向导,允许他们做出选择,但查询本身是由应用程序代码在幕后构建的。创建非常费力,但至少我们控制了最终执行的代码。
#10
1
The question is, do you trust your users? If your users have had to log into the system, you are using HTTPS & taken precautions against XSS attacks then SQL Injection is a smaller issue. Running the queries under a restricted account ought to be enough if you trust the legitimate users. For years I've been running MyLittleAdmin on the web and have yet to have a problem.
问题是,你相信你的用户吗?如果您的用户必须登录系统,您正在使用HTTPS并采取预防XSS攻击的措施,那么SQL注入是一个较小的问题。如果您信任合法用户,则在受限制的帐户下运行查询应该足够了。多年来我一直在网上运行MyLittleAdmin并且还没有遇到问题。
If you run under a properly restricted SQL Account select convert(varchar(50),0x64726F70207461626C652061) won't get very far and you can defend against resource hogging queries by setting a short timeout on your database requests. People could still do incorrect updates, but then that just comes back to do you trust your users?
如果您在正确受限的SQL帐户下运行,则选择convert(varchar(50),0x64726F70207461626C652061)将不会走得太远,您可以通过在数据库请求上设置短暂超时来防止资源占用查询。人们仍然可以做不正确的更新,但那回来是你相信你的用户?
You are always taking a managed risk attaching any database to the web, but then that's what backups are for.
您总是将管理风险附加到Web上的任何数据库,但那就是备份的用途。
#11
0
If they don't have to perform really advanced queries you could provide a ui that only allows certain choices, like a drop down list with "update,delete,select" then the next ddl would automatically populate with a list of available tables etc.. similar to query builder in sql management studio.
如果他们不必执行真正的高级查询,您可以提供一个只允许某些选择的UI,例如带有“更新,删除,选择”的下拉列表,那么下一个ddl会自动填充可用表的列表等。类似于sql management studio中的查询生成器。
Then in your server side code you would convert these groups of ui elements into sql statements and use a parametrized query to stop malicious content
然后在您的服务器端代码中,您将这些ui元素组转换为sql语句并使用参数化查询来阻止恶意内容
#12
0
This is a terribly bad practice. I would create a handful of stored procedures to handle everything you'd want to do, even the more advanced queries. Present them to the user, let them pick the one they want, and pass your parameters.
这是一个非常糟糕的做法。我会创建一些存储过程来处理您想要做的所有事情,甚至是更高级的查询。将它们呈现给用户,让他们选择他们想要的那个,并传递您的参数。
The answer above mine is also extremely good.
我上面的答案也非常好。
#13
0
Although I agree with Joel Coehoorn and SQLMenace, some of us do have "requirements". Instead of having them send ad Hoc queries, why not create a visual query builder, like the ones found in the MS sample applications found at asp.net, or try this link.
虽然我同意Joel Coehoorn和SQLMenace,但我们中的一些人确实有“要求”。不要让他们发送ad Hoc查询,为什么不创建一个可视化查询构建器,就像在asp.net上找到的MS示例应用程序中找到的那样,或者尝试这个链接。
I am not against the points made by Joel. He is correct. Having users (remember we are talking users here, they could care less about what you want to enforce) throw queries is like an app without a "Business Logic Layer", not to mention the additional questions to be answered when certain results does not match other supporting application results.
我并不反对乔尔提出的观点。他是对的。有用户(记住我们在这里谈论用户,他们可能不太关心你想要强制执行什么)抛出查询就像没有“业务逻辑层”的应用程序,更不用说当某些结果不匹配时需要回答的其他问题其他支持申请结果。
#14
0
here is another example
这是另一个例子
the hacker doesn't need to know the real table name, he/she can run undocumented procs like this
黑客不需要知道真正的表名,他/她可以像这样运行无证的procs
sp_msforeachtable 'print ''?'''
just instead of print it will be drop
只是而不是打印它会下降
#15
0
Plenty of answers saying that it's a bad idea but somethimes that's what the requirements insist on. There is one gotcha that I haven't spotted mentioned in the "If you have to do it anyway" suggestions though: Make sure that any update statements include a WHERE clause. It's all too easy to run
大量的答案说这是一个坏主意,但有些时候需要坚持的东西。有一个问题我没有在“如果你必须这样做”的建议中提到但是:确保任何更新语句都包含一个WHERE子句。这太容易了
UPDATE ImportantTable
SET VitalColumn = NULL
and miss out the important
并错过重要的
WHERE UserID = @USER_NAME
If an update is required across the whole table then it's easy enough to add
如果整个表需要更新,那么添加就很容易了
WHERE 1 = 1
Requiring the where clause doesn't stop a malicious user from doing bad things but it should reduce accidental whole table changes.
要求where子句不会阻止恶意用户做坏事,但它应该减少意外的整个表更改。