How to make a query call parametric

[PiGei]

Hi all,
I'm trying to build a function that - providing the dbname and the query
name - show the results.

I don't know how to solve this problem...

when I try to insert the variable into this call

cnnSimple.x_qry rstSimple

where x_qry is the variable I get the error "Type mismatch: 'x_qry'"
obviously because the x_qry is a string...

I can't find how to cast the value in order to get the function work
correctly.
My target is to have a function like that: ShowTable(dbname,queryname)

Thanks
PGei

 

[Bob Barrows [MVP]]

Hi all,
I'm trying to build a function that - providing the dbname and the
query name - show the results.

I don't know how to solve this problem...

when I try to insert the variable into this call

cnnSimple.x_qry rstSimple

where x_qry is the variable I get the error "Type mismatch: 'x_qry'"
obviously because the x_qry is a string...

I can't find how to cast the value in order to get the function work
correctly.
My target is to have a function like that: ShowTable(dbname,queryname)

This should answer your immediate question:
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/b3d322b882a604bd

This will provide a little more information:
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&[email protected]

Bob Barrows

 

[Bob Barrows [MVP]]

Hi all,
I'm trying to build a function that - providing the dbname and the
query name - show the results.

I don't know how to solve this problem...

when I try to insert the variable into this call

cnnSimple.x_qry rstSimple

where x_qry is the variable I get the error "Type mismatch: 'x_qry'"
obviously because the x_qry is a string...

I can't find how to cast the value in order to get the function work
correctly.
My target is to have a function like that: ShowTable(dbname,queryname)

Oops, ignore the last message. I did not recognize that x_qry was a variable
containing the name of a saved query ...

In order to do what you want (specify the name of the query in the argument
to the function), you will need to either use dynamic sql, or a Command
object. My preference is the latter, due to security concerns.

Dynamic SQL approach:

const adCmdText = 1
sSQL = "Exec " & x_qry
Set rstSimple = cnnSimple.Execute(sSQL,,adCmdText)

Hopefully, if you are using this approach, you will validate that x_qry
contains a valid query name before executing it. This will mitigate the
dangers of sql injection and cross-site scripting, two techniques that
hackers can use to gain access to your system. You can use ADOX to get the
names of your views (non-parameterized saved queries) and procedures
(parameterized saved queries). You can store them in an array or xml
document (recommended) in Application (using appliction_onstart in
global.asa) so you don't have to query the database every time you want to
use this function to execute a saved query.


Command object approach:

const adCmdStoredProc = 4
Set cmd = createobject("adodb.command")
cmd.CommandText=x_qry
cmd.CommandType = adCmdStoredProc
Set cmd.ActiveConnection = cnnSimple
Set rstSimple = cmd.Execute

Advantage: no chance of sql injection using this approach. No need to do
extra processing to validate x_qry. Just catch the error that occurs if a
hacker attempts to pass a sql statement to this function.


More about the dynamic SQL approach:
http://www.aspfaq.com/show.asp?id=2201

And the reasons I dislike that approach:
http://tinyurl.com/jyy0

Bob Barrows

 

[PiGei]

Thanks again for your help Bob
PGei

Oops, ignore the last message. I did not recognize that x_qry was a
variable containing the name of a saved query ...

In order to do what you want (specify the name of the query in the
argument to the function), you will need to either use dynamic sql, or a
Command object. My preference is the latter, due to security concerns.

Dynamic SQL approach:

const adCmdText = 1
sSQL = "Exec " & x_qry
Set rstSimple = cnnSimple.Execute(sSQL,,adCmdText)

Hopefully, if you are using this approach, you will validate that x_qry
contains a valid query name before executing it. This will mitigate the
dangers of sql injection and cross-site scripting, two techniques that
hackers can use to gain access to your system. You can use ADOX to get the
names of your views (non-parameterized saved queries) and procedures
(parameterized saved queries). You can store them in an array or xml
document (recommended) in Application (using appliction_onstart in
global.asa) so you don't have to query the database every time you want to
use this function to execute a saved query.


Command object approach:

const adCmdStoredProc = 4
Set cmd = createobject("adodb.command")
cmd.CommandText=x_qry
cmd.CommandType = adCmdStoredProc
Set cmd.ActiveConnection = cnnSimple
Set rstSimple = cmd.Execute

Advantage: no chance of sql injection using this approach. No need to do
extra processing to validate x_qry. Just catch the error that occurs if a
hacker attempts to pass a sql statement to this function.


More about the dynamic SQL approach:
http://www.aspfaq.com/show.asp?id=2201

And the reasons I dislike that approach:
http://tinyurl.com/jyy0

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"