Parameters to Command Object!

[Arpan]

Microsoft advises not to pass parameters to the Command object in the
Execute statement. Why?

Thanks,

Arpan

 

[Jon]

Because the command object has it's own paramaters command
(command.paramaters) that's why. See
http://support.microsoft.com/kb/165156/EN-US for info on this.

I presume another reason is because it may create a huge security flaw

 

[Bob Barrows [MVP]]

Microsoft advises not to pass parameters to the Command object in the
Execute statement. Why?

Where did you see this advice? It's hard to answer such a question in a
vacuum.

One possible reason is the "late-bound"/"early-bound" argument. In compiled
languages such as VB, C++, etc. using variants (which is ultimately what you
are doing when you pass a variant array containing parameter values via the
Execute statement) impairs performance. However, in vbscript, ALL variables
are Variant, so this is not as much a consideration. As Eric Lippert
constantly says: "if you care about maximizing performance, using a
late-bound unoptimized bytecode-interpreted dynamically-typed language is
probably a bad choice." Of course, he fails to address that script languages
are pretty much the only choice in classic ASP, even when using your own
compiled dll's (you have to use script to instantiate them, don't you?)

The other reason of course, is that retrieving output parameter values is
not possible when using this method to pass parameter values to a stored
procedure.

Bob Barrows

 

[Bob Barrows [MVP]]

How do you get from the information in 165156 to the "huge security flaw"
statement? I recognize that you may be using "presume" as a synonym for
"guess", but there must be some basis for coming to this presumption ...

Please explain.

Bob Barrows

 

[Egbert Nierop \(MVP for IIS\)]

How do you get from the information in 165156 to the "huge security flaw"
statement? I recognize that you may be using "presume" as a synonym for
"guess", but there must be some basis for coming to this presumption ...

Please explain.

If you do not use parameter objects, you have to encode single qoutes (')
and check each parameter on typevalidity. Second, you have to write
your -own- tools to convert dateformats and to format money etc in the
correct format. I've seen much Dutch programmers loozing time writing such
tools (SQL server and non-language-compatible configured systems switch
decimal symbols). Serious, this is a waste of time and possibly a security
problem if you program like this

myADO.execute "exec myProc " + request("myParam")

 

[Bob Barrows [MVP]]

If you do not use parameter objects, you have to encode single qoutes
(') and check each parameter on typevalidity.

Not quite true. You can pass the parameter values using a variant array as
the second argument in the Execute method without using the Parameters
collection.

http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e


And even if you do use the parameter objects, it is a good idea to check the
type/validity of the values being passed in order to avoid raising errors,
which is not really a good use of CPU.

Second, you have to
write your -own- tools to convert dateformats and to format money etc in
the
correct format. I've seen much Dutch programmers loozing time writing
such tools (SQL server and non-language-compatible configured systems
switch decimal symbols). Serious, this is a waste of time and
possibly a security problem if you program like this

myADO.execute "exec myProc " + request("myParam")

I certainly concur with this. I'm constantly ranting about dynamic sql for
this very reason. However, this is not what I understood the question to be
about. However, you may be right:

"Microsoft advises not to pass parameters to the Command object in the
Execute statement."

I interpreted this as advice against using the variant array in the Execute
statement. However, it could easily be interpreted as advice against using
the dynamic sql approach, in which case both you and Jon are correct.

To Arpan, here is the reason for the security concern about using dynamic
sql:

http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
http://www.nextgenss.com/papers/advanced_sql_injection.pdf
http://www.nextgenss.com/papers/more_advanced_sql_injection.pdf

Bob Barrows

 

[Jon]

Thank you Egbert. I have been working so I didn't have time to reply. Indeed
Bob this was how I interpreted the question ... though it may have been
wrong. Sorry I couldn't have answered your question earlier!