微调/改进asp中的参数化查询？ [英] Fine-tune/improve Parametized query in asp?
问题描述
我正在努力提高ASP 3.0中参数化的sql
语句的健壮性和优雅性，因为它们会传递给sql server SP。
有没有人能告诉我，在我编写
以下代码的方式中是否存在弱点？我已经包含了asp代码和sql存储的
proceducre来把事情绑在一起......我很欣赏这方面的任何建议。它
基本上是一个应用程序来管理我们网站上的静态新闻故事，通过
跟踪并在表格中组织元数据。
非常感谢你的评论。
ASP PARAMERT QUERY
----------------------- -----
如果oRS.eof那么
''//安全插入故事....
创建过程spr_AddStory
oCmd.Parameters.append oCmd.CreateParameter（" StoryTitle"，adVarChar，
adParamInput，100，pStoryTitle）
oCmd.Parameters.append oCmd.CreateParameter（" StoryURL"，adVarChar，
adParamInput，150，pStoryURL）
oCmd.Parameters.append oCmd.CreateParameter（ " StoryBlurb"，adVarChar，
adParamInput，1200，PStoryURL）
oCmd.Parameters.append oCmd.CreateParameter（" StoryBrokerID int"，adInteger，
adParamInput，4，pStoryBrokerID）
oCmd.Parameters.append oCmd.CreateParameter（" StoryCompanyID int& quot;，adInteger，
adParamInput，4，pStoryCompanyID）
oCmd.Parameters.append oCmd.CreateParameter（" StoryCategoryID int"，
adInteger，adParamInput，4，pStoryCategoryID）
oCmd.Parameters.append oCmd.CreateParameter（" StoryDeptID int"，adInteger，
adParamInput，4，pStoryDeptID）
oCmd.Parameters.append oCmd.CreateParameter（" StoryKeyword1"，adVarChar，
adParamInput，50，pStoryKeyword1）
oCmd.Parameters.append oCmd。 CreateParameter（" StoryKeyword2"，adVarChar，
adParamInput，50，pStoryKeyword2）
oCmd.Parameters.append oCmd.CreateParameter（" StoryKeyword3"，adVarChar，
adParamInput，50，pStoryKeyword3）
oCmd.Parameters.append oCmd.CreateParameter（" RelatedURL1"，adVarChar，
adParamInput，150，pRelatedURUL1）
oCmd.Parameters.append oCmd.CreateParameter（" RelatedURL2"，adVarChar，
adParamInput，150， pRelatedURUL1）
oCmd.Parameters.append oCmd.CreateParameter（" RelatedURL3"，adVarChar，
adParamInput，150，pRelatedURUL1）
oCmd。 Parameters.append oCmd.CreateParameter（" StoryDate datetime，
adParamInput，150，pStoryImageURL）
oCmd.Parameters.append oCmd.CreateParameter（" StoryBLN int"，adInteger，
adParamInput，4，pStoryBLN）
set oReturn = oCmd.CreateParameter（" u_id"，adInteger，adParamOutput）
oCmd.Parameters.append oReturn
oCmd.execute（）
''//结果...
如果oReturn.value = -1则
Response.writeFAILURE：无法执行插入语句
response.end
其他
Response.write成功：插入语句成功<
结束如果
SQL SERVER STORED PROC EDURE
----------------------------------------- -
创建程序spr_AddStory
@StoryTitle varchar（100），
@StoryURL varchar（150），
@StoryBlurb varchar（1200），
@StoryBrokerID int，
@StoryCompanyID int，
@StoryCategoryID int，
@StoryDeptID int，
@ StoryKeyword1 varchar（50），
@ StoryKeyword2 varchar（50），
@ StoryKeyword3 varchar（50），
@ RelatedURL1 varchar（150），
@ RelatedURL2 varchar（150），
@ RelatedURL3 varchar（150），
@StoryDate datetime，
@StoryImageURL varchar（150），
@StoryBLN int
AS
插入故事（StoryTitle，StoryURL，StoryBlurb，StoryBrokerID
，
StoryCompanyID，StoryCategoryID，StoryDeptID，StoryKeyword1，StoryKeyword2，
StoryKeyword3，RelatedURL1，RelatedURL2，RelatedURL3，StoryDate，
StoryImageURL，StoryBLN）
VALUES
（@ StoryTitle，@ StoryURL，@ StoryBlurb，@ StoryBrokerID，@ StoryCompanyID，@ StoryCategoryID，@ StoryDeptID，@ Sto ryKeyword1，@ StoryKeyword2，@ StoryKeyword3，
@ RelatedURL1 ，@ RelatedURL2，@ RelatedURL3，@ StoryDate，@ StoryImageURL，@ StoryBLN）
GO
I am trying to improve the robustness and elegance of my parametized sql
statements in ASP 3.0 as they get passed to the sql server SP.
Could anyone tell me if there are weaknessess in the way I have written the
following code? I have included both the asp code and the sql stored
proceducre to tie things togoether....I appreciate any advice on this. It
basically is a application to manage static news stories on our site by
tracking and organising the meta data in a table.
Many thanks for you comments.
ASP PARAMERT QUERY
----------------------------
If oRS.eof then
''// SAFE TO INSERT STORY....
CREATE Procedure spr_AddStory
oCmd.Parameters.append oCmd.CreateParameter("StoryTitle", adVarChar,
adParamInput,100,pStoryTitle)
oCmd.Parameters.append oCmd.CreateParameter("StoryURL", adVarChar,
adParamInput,150,pStoryURL)
oCmd.Parameters.append oCmd.CreateParameter("StoryBlurb", adVarChar,
adParamInput,1200, PStoryURL)
oCmd.Parameters.append oCmd.CreateParameter("StoryBrokerID int", adInteger,
adParamInput,4,pStoryBrokerID)
oCmd.Parameters.append oCmd.CreateParameter("StoryCompanyID int", adInteger,
adParamInput,4,pStoryCompanyID)
oCmd.Parameters.append oCmd.CreateParameter("StoryCategoryID int",
adInteger, adParamInput,4,pStoryCategoryID)
oCmd.Parameters.append oCmd.CreateParameter("StoryDeptID int", adInteger,
adParamInput,4,pStoryDeptID)
oCmd.Parameters.append oCmd.CreateParameter("StoryKeyword1", adVarChar,
adParamInput,50,pStoryKeyword1)
oCmd.Parameters.append oCmd.CreateParameter("StoryKeyword2", adVarChar,
adParamInput,50,pStoryKeyword2)
oCmd.Parameters.append oCmd.CreateParameter("StoryKeyword3", adVarChar,
adParamInput,50,pStoryKeyword3)
oCmd.Parameters.append oCmd.CreateParameter("RelatedURL1", adVarChar,
adParamInput,150,pRelatedURUL1)
oCmd.Parameters.append oCmd.CreateParameter("RelatedURL2", adVarChar,
adParamInput,150,pRelatedURUL1)
oCmd.Parameters.append oCmd.CreateParameter("RelatedURL3", adVarChar,
adParamInput,150,pRelatedURUL1)
oCmd.Parameters.append oCmd.CreateParameter("StoryDate datetime,
oCmd.Parameters.append oCmd.CreateParameter("StoryImageURL", adVarChar,
adParamInput,150,pStoryImageURL)
oCmd.Parameters.append oCmd.CreateParameter("StoryBLN int", adInteger,
adParamInput,4,pStoryBLN)
set oReturn = oCmd.CreateParameter("u_id", adInteger, adParamOutput)
oCmd.Parameters.append oReturn
oCmd.execute()
''//RESULT...
if oReturn.value=-1 then
Response.write "FAILURE: Insert statement could not be carried out"
response.end
else
Response.write "SUCCESS: Insert statement was successfull"
End if
SQL SERVER STORED PROCEDURE
-------------------------------------------
CREATE Procedure spr_AddStory
@StoryTitle varchar(100),
@StoryURL varchar(150),
@StoryBlurb varchar(1200),
@StoryBrokerID int,
@StoryCompanyID int,
@StoryCategoryID int,
@StoryDeptID int,
@StoryKeyword1 varchar(50),
@StoryKeyword2 varchar(50),
@StoryKeyword3 varchar(50),
@RelatedURL1 varchar(150),
@RelatedURL2 varchar(150),
@RelatedURL3 varchar(150),
@StoryDate datetime,
@StoryImageURL varchar(150),
@StoryBLN int
AS
INSERT INTO Story (StoryTitle, StoryURL, StoryBlurb, StoryBrokerID
,
StoryCompanyID, StoryCategoryID, StoryDeptID, StoryKeyword1, StoryKeyword2,
StoryKeyword3, RelatedURL1, RelatedURL2, RelatedURL3, StoryDate,
StoryImageURL, StoryBLN)
VALUES
(@StoryTitle,@StoryURL,@StoryBlurb,@StoryBrokerID, @StoryCompanyID,@StoryCategoryID,@StoryDeptID,@Sto ryKeyword1,@StoryKeyword2,@StoryKeyword3,
@RelatedURL1,@RelatedURL2,@RelatedURL3,@StoryDate, @StoryImageURL,@StoryBLN)
GO
推荐答案
ja***@catamaranco.com 写道：
我正在努力提高我的健壮性和优雅性ASP 3.0中的sql语句在传递给sql server SP时被参数化。
有人能告诉我，我的编写方式是否存在弱点？
如果你告诉我们你要解决的问题，也许会有所帮助。
因为你需要读取返回参数的值，这是执行程序的最有效方式。
一种效率低下的方法，从不推荐，是使用
cmd.Parameters.Refresh
而不是自己构建Parameters集合。但由于这需要额外的数据库之旅，这不是你想要做的事情。
我已经包含了asp代码和存储的sql 程序性把事情绑在一起......我很感激有关这个的建议。它主要是通过在表格中跟踪和组织元数据来管理我们
网站上的静态新闻故事的应用程序。
非常感谢您的评论。
ASP PARAMERT QUERY
----------------------------
如果是oRS。 eof然后
'//安全插入故事....
创建程序spr_AddStory
？这是复制/粘贴错误吗？
< snip> set oReturn = oCmd.CreateParameter（" u_id"，adInteger，adParamOutput）
oCmd.Parameters.append oReturn
I am trying to improve the robustness and elegance of my parametized
sql statements in ASP 3.0 as they get passed to the sql server SP.
Could anyone tell me if there are weaknessess in the way I have
written the following code?
Perhaps it would help if you tell us what problem you''re trying to solve.
Since you need to read the value of the return parameter, this is the most
efficient way to execute your procedure.
A less efficient way, which is never recommended, is to use
cmd.Parameters.Refresh
instead of building the Parameters collection yourself. But since this
requires an extra trip to the database, it is not something you want to do.
I have included both the asp code and the sql stored
proceducre to tie things togoether....I appreciate any advice on
this. It basically is a application to manage static news stories on our
site
by tracking and organising the meta data in a table.
Many thanks for you comments.
ASP PARAMERT QUERY
----------------------------
If oRS.eof then
''// SAFE TO INSERT STORY....
CREATE Procedure spr_AddStory
? Is this a copy/paste error?
<snip> set oReturn = oCmd.CreateParameter("u_id", adInteger, adParamOutput)
oCmd.Parameters.append oReturn
Return参数需要是FIRST参数附加到
参数集合中。方向常数应为
adParamReturnValue，而不是adParamOutput。输出参数不同于
返回参数。阅读本文以查看差异：
http://groups-beta.google.com/group/...935bd7c531d82b
我不清楚为什么需要读取Return参数值。如果
在插入过程中出现错误，它将被传递回
Connection对象，这将引发vbscript错误。如果没有要求
读取返回参数值，您的程序可以通过以下方式简单（高效）执行更多
：
关于错误恢复下一个
conn.spr_AddStory，pStoryTitle，...，pStoryBLN
如果错误<> 0和conn.errors.count = 0那么
''成功
其他
''失败
结束如果
请参阅 http://tinyurl.com/jyy0
HTH，
鲍勃巴罗斯
-
微软MVP - ASP / ASP.NET
请回复到新闻组。这个电子邮件帐户是我的垃圾邮件陷阱所以我
不经常检查它。如果您必须离线回复，请删除
没有垃圾邮件
The Return parameter needs to be the FIRST parameter appended to the
Parameters collection. And the direction constant should be
adParamReturnValue, not adParamOutput. Output parameters are different from
Return parameters. Read this to see the difference:
http://groups-beta.google.com/group/...935bd7c531d82b
I''m not clear about why you need to read the Return parameter value. If
there is an error during the insert, it will be passed back to the
Connection object which will raise a vbscript error. Without the requirement
to read the return parameter value, your procedure can be executed more
simply (and efficiently) by:
on error resume next
conn.spr_AddStory,pStoryTitle, ..., pStoryBLN
if err<>0 and conn.errors.count=0 then
''success
else
''failure
end if
See http://tinyurl.com/jyy0
HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don''t check it very often. If you must reply off-line, then remove the
"NO SPAM"
哇，鲍勃...我还有其他相关问题到你的最后一个帖子，但是对于
那一刻我只想专注于你的这个应用程序：
http://www.thrasherwebdesign.com/ind...asp&c =& a =清除
哇...太酷了！
这就是它为我吐出来的东西（见下文）......但是......我仍然试图确定
是否使用a：
1.返回值
2.输出值
我基本上只想确认插入语句是否正确完成
并且可能返回唯一标识符...这是正确的路线？
Dim cmd，param
设置cmd = server.CreateObject（" ADODB.Command"）
使用cmd
.CommandType = adcmdstoredproc
.CommandText =" spr_AddStory"
set .ActiveConnection = cnSQL
set param = .createparameter（" ; @ RETURN_VALUE"，adInteger，
adParamReturnValue，0）
.parameters.append param
set param = .createparameter（" @ StoryTitle" ;，adVarChar，adParamInput，100，
[此处放置值]）
.parameters.append param
set param = .createparameter（" ; @ StoryURL"，adVarChar，adParamInput，150，
[此处放置值]）
.parameters.append param
set param =。 createparameter（" @ StoryBlurb"，adVarChar，adParamInput，1200，
[put value here]）
.parameters.append param
set param = .createparameter（" @ StoryBrokerID"，adInteger，adParamInput，0，
[put value here]）
.parameters.append param
set param = .createparameter（" @ StoryCompanyID"，adInteger，adParamIn put，
0，[这里放置值]）
.parameters.append param
set param = .createparameter（" @ StoryCategoryID" ，adInteger，adParamInput，
0，[此处放置值]）
.parameters.append param
set param = .createparameter（" @ StoryDeptID"，adInteger，adParamInput，0，
[put value here]）
.parameters.append param
set param = .createparameter （" @ StoryKeyword1"，adVarChar，adParamInput，
50，[此处放置价值]）
.parameters.append param
set param = .createparameter（" @ StoryKeyword2"，adVarChar，adParamInput，
50，[put value here]）
.parameters.append param
set param = .createparameter（" @ StoryKeyword3"，adVarChar，adParamInput，
50，[put value here]）
.parameters.append param
set param = .createparameter（" @ RelatedURL1"，adVarChar，adParamInput，150，
[put value h ere]）
.parameters.append param
set param = .createparameter（" @ RelatedURL2"，adVarChar，adParamInput，150，
[把价值放在这里]）
.parameters.append param
set param = .createparameter（" @ RelatedURL3"，adVarChar，adParamInput，150，
[把价值放在这里]）
.parameters.append param
set param = .createparameter（" @ StoryDate"，adDBTimeStamp，adParamInput，0，
[把价值放在这里]）
.parameters.append param
set param = .createparameter（" @ StoryImageURL"，adVarChar，adParamInput，
150，[这里放置值]）
.parameters.append param
set param = .createparameter（" @ StoryBLN"，adInteger， adParamInput，0，[此处
值]）
.parameters.append param
.execute ,, adexecutenorecords
结束
Wow, Bob...I have other questions relating to your last thread but for the
moment I just want to focus on this app of yours:
http://www.thrasherwebdesign.com/ind...asp&c=&a=clear
Whoa...that is cool!
This is what it spat out for me (See below)...but...I still trying to figure
out whether or not to use either a:
1. Return value
2. Output value
I bascially just want to confirm that the insert statement is done correctly
and possibly return the unique identifier...which is the correct route?
Dim cmd, param
Set cmd=server.CreateObject("ADODB.Command")
With cmd
.CommandType=adcmdstoredproc
.CommandText = "spr_AddStory"
set .ActiveConnection=cnSQL
set param = .createparameter("@RETURN_VALUE", adInteger,
adParamReturnValue, 0)
.parameters.append param
set param = .createparameter("@StoryTitle", adVarChar, adParamInput, 100,
[put value here])
.parameters.append param
set param = .createparameter("@StoryURL", adVarChar, adParamInput, 150,
[put value here])
.parameters.append param
set param = .createparameter("@StoryBlurb", adVarChar, adParamInput, 1200,
[put value here])
.parameters.append param
set param = .createparameter("@StoryBrokerID", adInteger, adParamInput, 0,
[put value here])
.parameters.append param
set param = .createparameter("@StoryCompanyID", adInteger, adParamInput,
0, [put value here])
.parameters.append param
set param = .createparameter("@StoryCategoryID", adInteger, adParamInput,
0, [put value here])
.parameters.append param
set param = .createparameter("@StoryDeptID", adInteger, adParamInput, 0,
[put value here])
.parameters.append param
set param = .createparameter("@StoryKeyword1", adVarChar, adParamInput,
50, [put value here])
.parameters.append param
set param = .createparameter("@StoryKeyword2", adVarChar, adParamInput,
50, [put value here])
.parameters.append param
set param = .createparameter("@StoryKeyword3", adVarChar, adParamInput,
50, [put value here])
.parameters.append param
set param = .createparameter("@RelatedURL1", adVarChar, adParamInput, 150,
[put value here])
.parameters.append param
set param = .createparameter("@RelatedURL2", adVarChar, adParamInput, 150,
[put value here])
.parameters.append param
set param = .createparameter("@RelatedURL3", adVarChar, adParamInput, 150,
[put value here])
.parameters.append param
set param = .createparameter("@StoryDate", adDBTimeStamp, adParamInput, 0,
[put value here])
.parameters.append param
set param = .createparameter("@StoryImageURL", adVarChar, adParamInput,
150, [put value here])
.parameters.append param
set param = .createparameter("@StoryBLN", adInteger, adParamInput, 0, [put
value here])
.parameters.append param
.execute ,,adexecutenorecords
end with
ja***@catamaranco.com 写道：
哇，鲍勃......我还有其他问题与你的最后一个帖子有关，但目前我只想关注你的这个应用程序：
http://www.thrasherwebdesign.com/ind...asp&c=&a=clear
哇...太酷了！
谢谢。你有源代码，所以你可以自定义它，如果你不喜欢我用于变量等的名字。
这就是它的争吵我（见下文）......但......我仍然试图弄清楚是否要使用：
1.返回价值
2.产值
我基本上只是想确认插入语句是否正确完成并且可能返回唯一标识符......这是正确的路径？
Wow, Bob...I have other questions relating to your last thread but
for the moment I just want to focus on this app of yours:
http://www.thrasherwebdesign.com/ind...asp&c=&a=clear
Whoa...that is cool!
Thanks. You have the source code so you can customize it if you don''t like
the names I used for the variables, etc.
This is what it spat out for me (See below)...but...I still trying to
figure out whether or not to use either a:
1. Return value
2. Output value
I bascially just want to confirm that the insert statement is done
correctly and possibly return the unique identifier...which is the
correct route?
我没看到你在原来的
程序中返回一个唯一标识符的位置。你打算重写这个程序吗？您是否要求
帮助？
因为返回的值是数据，而不是状态或错误
代码，我会使用输出参数。有些人建议通过
a SELECT语句返回它，但是当我不需要光标功能时，我对使用记录集有点偏见。
Bob Barrows
-
Microsoft MVP - ASP / ASP.NET
请回复新闻组。这个电子邮件帐户是我的垃圾邮件陷阱所以我
不经常检查它。如果您必须离线回复，请删除
无垃圾邮件
I did not see where you were returning a unique identifier in your original
procedure. Are you planning to rewrite the procedure? Are you asking for
help with that?
Because the value being returned is data, rather than a status or error
code, I would use an output parameter. Some would recommend returning it via
a SELECT statement, but I''m a little biased against using a recordset when I
have no need for cursor functionality.
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don''t check it very often. If you must reply off-line, then remove the
"NO SPAM"
这篇关于微调/改进asp中的参数化查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
