在SQL中参数化二级变量 [英] Parameterising secondary variables in SQL
问题描述
嗨
我的任务是参数化旧的经典ASP网络应用程序。应用程序中有数千个SQL字符串。我的问题是：是否有必要参数化二级/派生变量/数据？例如，假设有一个表单由用户提交。显然，所有用户的输入都需要参数化。示例：插入Tbl1（Col1，Col2）值（？，？）但是，在许多页面上，应用程序根据刚刚捕获的表单数据（我称之为辅助/派生数据）调用数据库中的其他数据然后将其用于更新/插入其他表。示例：从Tbl1中选择Value1，Value2，其中Id =（从Tbl1中选择最大值（Id））然后插入Tbl2（Col1，Col2）值（Value1？，Value2？），其中Value1？和Value2？由select语句返回。
那么，是否需要参数化二级/派生变量？我的想法是，这不是必需的，因为任何SQL注入都会导致第一个SQL插入失败，因此后续的SQL调用将不会被执行。这是正确的还是有缺陷的？
我尝试过：
目前，我正在参数化所有输入 - 主要，次要以及介于两者之间的所有内容，但如果我可以通过参数化主要用户输入来减少工作量，我将非常感激。
Hi
I have the task of parameterising an old Classic ASP web app. There are literally thousands of SQL strings in the app. My question is: Is it necessary to parameterise secondary/derived variables/data? For example, assuming there is a form that gets submitted by the user. Clearly all the user's input needs to be parameterised. Example: "Insert into Tbl1 (Col1, Col2) values (?, ?)" However, on many pages the app calls other data from the database based on the form data just captured (what I've called secondary/derived data) and this is then used to update/insert into other tables. Example: "Select Value1, Value2 from Tbl1 where Id=(Select Max(Id) from Tbl1)" then "Insert into Tbl2 (Col1, Col2) values (Value1?, Value2?)" where Value1? and Value2? were returned by the select statement.
So, is it necessary to parameterise secondary/derived variables? My thinking is that this is NOT necessary because any SQL injection would have caused the first SQL insert to fail and so the subsequent SQL calls will not get executed. Is this think right or flawed?
What I have tried:
Currently, I'm parameterising all input - primary, secondary and everything in between, but if I can reduce the workload by just parameterising the primary user input, I'd be most grateful.
推荐答案
一般来说，我会说参数化一切。
想一想：如果你选择一个用户从数据库输入的字符串，那么在连接时会有相同的风险进入INSERT命令时，就像它首次出现在DB中一样。获取
Generally, I'd say "Parameterise everything".
Think about it: if you SELECT a string that the user entered from a DB, that has the same risk when concatenated into an INSERT command as it was when it was first presented to the DB. Getting
x';DROP TABLES Clients;--来自数据库列不会比从一个数据库获取它更危险文本框。
我当然要参数化DATETIME值，以避免不同系统设置之间的混淆：传递原始DateTime值比传递stringified版本更安全。
和我参数化数字，因为在将来的某个时间，它可能会被更改为数字，如果它已经是一个不会导致问题的参数。
使用插入到Tbl2（Col1，Col2）值（Value1？，Value2？）查询，它是保存 - 只要这些值不以任何方式连接（但通常最好使用INSERT INTO SELECT）而不是将两者分开：
from a DB column doesn't make it any less dangerous than fetching it from a text box.
I parameterise DATETIME values as a matter of course, to avoid confusion between different system settings: passing a raw DateTime value is safer than passing a "stringified" version.
And I parameterise numbers because at some time in the future, it might get changed to a numeric and if it's already a parameter that doesn't cause a problem.
With your "Insert into Tbl2 (Col1, Col2) values (Value1?, Value2?)" query, it's save - provided that the values don't get concatenated in any way (but generally it's better to go with a INSERT INTO SELECT rather than separate the two:
INSERT INTO Tbl2 (Col1, Col2) SELECT Value1, Value2 FROM Tbl1 WHERE Id=(SELECT MAX(Id) FROM Tbl1)
我看不到不使用参数的任何好处，而是改变编程风格引入了几个问题：
1.程序更改，而在一开始可能是某些语句只能由程序在内部使用，如果设计发生变化会怎样。您可能拥有完全有效，可重复使用的声明，并且可以在直接用户输入的地方使用它。
2.选择的复杂性，您将始终需要在决定是否使用参数之前仔细考虑语句的使用位置。我会说这会浪费时间。
3.需要进行转换，考虑小数，日期，时间，特殊字符，如'等等。在构建语句时，您需要正确考虑所有这些方面。例如，如果我将区域设置连接到SQL语句，那么它会失败，因为我的分隔符dfor是逗号。
4.计划可重用性，当使用参数时，数据库可以更容易地使用现有的执行计划。如果使用文字，数据库可能需要单独对每个语句进行优化，或者至少做额外的工作以在使用文字时将语句标识为现有语句。对于更复杂的语句，浪费的时间量很大。
5.语句可重用性，如果您需要使用不同的值多次执行相同的语句，您可以使用exiting语句和命令对象。只需更改参数的值并执行即可。否则你至少需要更改命令的语句，这会影响客户端命令缓存（如果适用）。
如上所述有几个原因可以使用参数，而我想不出没有使用参数的任何好处。所以：
I can't see any benefit of not using parameters, instead varying programming style introduces several problems:
1. Program changes, while in the beginning it could be that some of the statements would only be used internally by the program, what if the design changes. You may have completely valid, reusable statement and one could use it in a place where it's run with direct user input.
2. Complexity of choice, you would always need to carefully consider where the statement is used before deciding whether to use parameters or not. I'd say this would be waste of time.
3. Need to do conversions, think about decimals, dates, times, special characters such as ' and so on. You would need to properly take all these aspects into account when building the statement. For example with my regional settings if I'd concatenate a decimal number to an SQL statement, it would fail miserably since my delimiter dfor decimals is comma.
4. Plan re-usability, when parameters are used the database can more easily use an existing execution plan. If you use literals the database may need to do optimization for each statement separately or at least do extra work to identify the statement as an existing one when literals are used. With more complex statements the amount of time wasted is significant.
5. Statement re-usability, in case you need to execute the same statement several times with different values, you can use an exiting statement and command object. Just change the values of the parameters and execute. Otherwise you would need to at least change the statement of the command and this affects client side command cache (if applicable).
As said there are several reasons why to use parameters while I cannot think of any benefits of not using parameters. So:
抵抗是徒劳的。你将被同化。
：）
你的想法是有缺陷的。参数化查询不是为了防止SQL注入攻击，但在某些情况下它确实有帮助。
您出于各种原因参数化查询，其中一些好处是您的代码写作经验，代码可维护性，更简单的调试体验，查询性能，数据完整性和一致性......
为什么不参数化查询？我不能告诉你。很久以前我给了那个垃圾。
Your thinking is flawed. Parameterizing queries is not for preventing SQL injection attacks, though in some cases it does help.
You parameterize queries for a variety of reasons, some benefits of which are your code writing experience, code maintainability, easier debugging experience, query performance, data integrity and consistency, ...
Why should you NOT parameterize a query? I couldn't tell you. I gave that crap up a very long time ago.
这篇关于在SQL中参数化二级变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
