PiGei wrote:
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
--
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"