I am running into a problem where I am getting a significant performance increase in my SQL Server queries by replaceing parameters with hard values. For example:
我遇到的问题是,通过用硬值替换参数,我的SQL Server查询性能显着提高。例如:
SELECT * FROM Table1 WHERE RowNumber >= 101 AND ID < 200
is significantly faster than the following:
明显快于以下:
DECLARE @Start int = 101;
DECLARE @End Start int = 200;
SELECT * FROM Table1 WHERE RowNumber >= @Start AND RowNumber < @End
The first one runs in about 2s where the 2nd one runs in 15s. So my question is, am I safe to use hard values if they are numeric and pnly parameterize strings? I do not believe this is a matter of indices because the RowNumber column is already indexed as a non clustered index. I am not that familiar with all of the SQL injection techniques, but I have been using parameters for all input values for years in an attempt to avoid SQL injection attacks. I never quite realized there was any potential for decreased performance such as this by using parameters.
第一个在大约2s运行,第二个在15s运行。所以我的问题是,如果它们是数字并且pnly参数化字符串,我是否可以安全地使用硬值?我不相信这是索引的问题,因为RowNumber列已经被索引为非聚集索引。我并不熟悉所有的SQL注入技术,但是我多年来一直在使用所有输入值的参数来试图避免SQL注入攻击。我从未意识到通过使用参数可能会降低性能。
I have also seen some questions on SO about performance decreases using parameters but I have not seen any definitive answers on how to resolve the problem.
我还看到了一些关于使用参数降低性能的问题,但我还没有看到任何关于如何解决问题的明确答案。
3 个解决方案
#1
3
Regardless of anything else, I'd say that given this statement "I am not that familiar with all of the SQL injection techniques" then you should always play it safe and continue with your parameterised queries.
无论如何,我都会说,鉴于这句话“我并不熟悉所有的SQL注入技术”,那么你应该始终保持安全并继续参数化查询。
I do a talks on SQL Injection Attacks at user groups and I don't consider that I know everything. I thought I knew a lot and about the various ways an attack can take place, but I even had one guy come up to me after a talk and tell me about an attack that happened at a company where he worked where the attack came via a paper form that was read by an OCR machine. I would never have ever considered that form of attack previously.
我在用户组讨论SQL注入攻击,我不认为我知道一切。我以为我知道了很多,并且可以通过各种方式进行攻击,但是我甚至让一个人在谈话之后来找我并告诉我他在一家公司发生的攻击事件,由OCR机器读取的纸张形式。我以前从未考虑过这种形式的攻击。
Attacks happen in all sorts of strange ways, and even if you've thought of everything you can, some one else will always manage to think of some other way to attack the system.
攻击以各种奇怪的方式发生,即使你已经想到了所有可能的事情,其他人总会设法想到一些其他攻击系统的方法。
#2
0
SQL Server evaluates some constant expressions early to improve query performance. This is referred to as constant folding.
SQL Server会尽早评估一些常量表达式,以提高查询性能。这被称为恒定折叠。
In your query above, as before compiling the query the constants will be evaluated and optimizer does not need to do anything.But in case of parameters the optimizer has to compile it at every run time.
在上面的查询中,与编译查询之前一样,将评估常量,并且优化器不需要执行任何操作。但是在参数的情况下,优化器必须在每次运行时编译它。
#3
0
Numeric fields are dangerous because comments (// or /) and spaces serve to make SQL injection (attacks)
数字字段很危险,因为注释(//或/)和空格用于进行SQL注入(攻击)
Many sites let a user enter in a reserved area if user and password match.
如果用户和密码匹配,许多站点允许用户进入保留区域。
Suppose a crazy site that uses a numeric field for the password:
假设一个使用数字字段作为密码的疯狂网站:
..... Where User='James' and Pass=123
Even if one validates a user input in Javascript, it's very easy change the post values: So, one can "inject" the below piece instead of 123
即使一个人在Javascript中验证用户输入,也很容易更改帖子值:因此,可以“注入”下面的部分而不是123
123 or 1=1
All records are returned. Many sites detect this situation, however it's very common that the user field be "user" or "username". So one can try "user" and other common field names in the structure down
返回所有记录。许多站点检测到这种情况,但是用户字段是“用户”或“用户名”是很常见的。因此,可以在结构中尝试“用户”和其他常见字段名称
12 or 1=1 and User='James'
In this case, only the desired record will be returned!
在这种情况下,只返回所需的记录!
A simple solution for number fields is use single quotations:
数字字段的简单解决方案是使用单引号:
Select User='James' and Pass='123'
I've verified that SQL Server, MySQL and SQLite accept this syntax, there may be a slight overcharge. Avoid to use single quotations in strings is harder because of e-mail, names, etc.
我已经验证了SQL Server,MySQL和SQLite接受这种语法,可能会有轻微的过度收费。由于电子邮件,名称等原因,避免在字符串中使用单引号更难。
#1
3
Regardless of anything else, I'd say that given this statement "I am not that familiar with all of the SQL injection techniques" then you should always play it safe and continue with your parameterised queries.
无论如何,我都会说,鉴于这句话“我并不熟悉所有的SQL注入技术”,那么你应该始终保持安全并继续参数化查询。
I do a talks on SQL Injection Attacks at user groups and I don't consider that I know everything. I thought I knew a lot and about the various ways an attack can take place, but I even had one guy come up to me after a talk and tell me about an attack that happened at a company where he worked where the attack came via a paper form that was read by an OCR machine. I would never have ever considered that form of attack previously.
我在用户组讨论SQL注入攻击,我不认为我知道一切。我以为我知道了很多,并且可以通过各种方式进行攻击,但是我甚至让一个人在谈话之后来找我并告诉我他在一家公司发生的攻击事件,由OCR机器读取的纸张形式。我以前从未考虑过这种形式的攻击。
Attacks happen in all sorts of strange ways, and even if you've thought of everything you can, some one else will always manage to think of some other way to attack the system.
攻击以各种奇怪的方式发生,即使你已经想到了所有可能的事情,其他人总会设法想到一些其他攻击系统的方法。
#2
0
SQL Server evaluates some constant expressions early to improve query performance. This is referred to as constant folding.
SQL Server会尽早评估一些常量表达式,以提高查询性能。这被称为恒定折叠。
In your query above, as before compiling the query the constants will be evaluated and optimizer does not need to do anything.But in case of parameters the optimizer has to compile it at every run time.
在上面的查询中,与编译查询之前一样,将评估常量,并且优化器不需要执行任何操作。但是在参数的情况下,优化器必须在每次运行时编译它。
#3
0
Numeric fields are dangerous because comments (// or /) and spaces serve to make SQL injection (attacks)
数字字段很危险,因为注释(//或/)和空格用于进行SQL注入(攻击)
Many sites let a user enter in a reserved area if user and password match.
如果用户和密码匹配,许多站点允许用户进入保留区域。
Suppose a crazy site that uses a numeric field for the password:
假设一个使用数字字段作为密码的疯狂网站:
..... Where User='James' and Pass=123
Even if one validates a user input in Javascript, it's very easy change the post values: So, one can "inject" the below piece instead of 123
即使一个人在Javascript中验证用户输入,也很容易更改帖子值:因此,可以“注入”下面的部分而不是123
123 or 1=1
All records are returned. Many sites detect this situation, however it's very common that the user field be "user" or "username". So one can try "user" and other common field names in the structure down
返回所有记录。许多站点检测到这种情况,但是用户字段是“用户”或“用户名”是很常见的。因此,可以在结构中尝试“用户”和其他常见字段名称
12 or 1=1 and User='James'
In this case, only the desired record will be returned!
在这种情况下,只返回所需的记录!
A simple solution for number fields is use single quotations:
数字字段的简单解决方案是使用单引号:
Select User='James' and Pass='123'
I've verified that SQL Server, MySQL and SQLite accept this syntax, there may be a slight overcharge. Avoid to use single quotations in strings is harder because of e-mail, names, etc.
我已经验证了SQL Server,MySQL和SQLite接受这种语法,可能会有轻微的过度收费。由于电子邮件,名称等原因,避免在字符串中使用单引号更难。