Parameterized query problem

[MiniEggs]

Hi all

I'm trying to do a really simple query but passing perameters to it so liest
say I have somethin like this

Function ExecParameterisedQueryAndReturnResultSet(sMainQueryString,
sWhereClause, aParamName, aParamType, aParamDirection, aParamSize,
aParamValue)
Dim dbConn, SQLcmd
Dim iNumElements, iLoop

Set dbConn = getConnection()

'Create Command
set SQLcmd = server.createobject("ADODB.Command")
SQLcmd.activeconnection=dbConn
SQLcmd.commandtext=(sMainQueryString & " " & sWhereClause)
SQLcmd.CommandType = adCmdText
SQLcmd.NamedParameters = True

'Create the parameters
iNumElements=UBound(aParamName)
For iLoop = 0 to iNumElements
If aParamName(iLoop) > "!" Then
SQLcmd.Parameters.Append SQLcmd.CreateParameter(_
CStr(aParamName(iLoop)),_
CInt(aParamType(iLoop)), _
CInt(aParamDirection(iLoop)),_
CInt(aParamSize(iLoop)), _
CStr(aParamValue(iLoop)))
End If
Next

Response.Write (sMainQueryString & " " & sWhereClause)

Set ExecParameterisedQueryAndReturnResultSet = SQLcmd.Execute

End Function

and then in the page

aSQLParamName(0) ="@P1"
aSQLParamValue(0) ="D%"
aSQLParamSize(0) =Len(aSQLParamValue(0))
aSQLParamType(0) =(adVarChar)
aSQLParamDirection(0)=(adParamInput)

Set rsResult=ExecParameterisedQueryAndReturnResultSet("Select * from
Customers ","where CompanyName like
'@P1'",aSQLParamName,aSQLParamType,aSQLParamDirection,aSQLParamSize,aSQLParamValue)

No matter what combinations of @P1 '@P1' etc etc things I try I either get
an error 500 or no records returned

Thanks in advance

[Bob Barrows]

MiniEggs wrote:
> Hi all
>
> I'm trying to do a really simple query but passing perameters to it
> so liest say I have somethin like this
>
> Function ExecParameterisedQueryAndReturnResultSet(sMainQueryString,
> sWhereClause, aParamName, aParamType, aParamDirection, aParamSize,
> aParamValue)
> Dim dbConn, SQLcmd
> Dim iNumElements, iLoop
>
> Set dbConn = getConnection()
>
> 'Create Command
> set SQLcmd = server.createobject("ADODB.Command")
> SQLcmd.activeconnection=dbConn
> SQLcmd.commandtext=(sMainQueryString & " " & sWhereClause)
> SQLcmd.CommandType = adCmdText

I assume you have the ADO constants defined in this page somewhere ...
http://www.aspfaq.com/show.asp?id=2112

> SQLcmd.NamedParameters = True

NamedParameters rarely work. You have to pass the parameter values in the
order in which they appear in the string containing the sql statement.

>
> 'Create the parameters
<snip>

> Response.Write (sMainQueryString & " " & sWhereClause)

If the rest of this post does not help solve your problem, show us the
result of this statement.

>
> Set ExecParameterisedQueryAndReturnResultSet = SQLcmd.Execute
>
> End Function
>
>
> and then in the page
>
> aSQLParamName(0) ="@P1"
> aSQLParamValue(0) ="D%"
> aSQLParamSize(0) =Len(aSQLParamValue(0))
> aSQLParamType(0) =(adVarChar)
> aSQLParamDirection(0)=(adParamInput)
>
> Set rsResult=ExecParameterisedQueryAndReturnResultSet("Select * from
> Customers ","where CompanyName like
> '@P1'",aSQLParamName,aSQLParamType,aSQLParamDirection,aSQLParamSize,aSQLParamValue)
>
> No matter what combinations of @P1 '@P1' etc etc things I try I
> either get an error 500 or no records returned
>

Turn off Friendly Errors so you see the real error message:
http://www.aspfaq.com/show.asp?id=2109

I will say that you're going to too much trouble. i would rewrite this
function like this:

'the name of your function is too long (how about GetRSFromParmQuery() ?).
The following should go on one line but it will break in the newsgroup post:
Function ExecParameterisedQueryAndReturnResultSet(sMainQueryString,
sWhereClause, aParamValue)
'****end single line*********************************
Dim dbConn, SQLcmd

Set dbConn = getConnection()

'Create Command
set SQLcmd = server.createobject("ADODB.Command")
SQLcmd.activeconnection=dbConn
SQLcmd.commandtext=(sMainQueryString & " " & sWhereClause)
SQLcmd.CommandType = adCmdText

'the name of your function is too long. the following should go on one line
but it will break in the newsgroup post (note, the comma before the argument
is crucial):
Set ExecParameterisedQueryAndReturnResultSet = SQLcmd.Execute ,aParamValue
'****end single line*********************************

End Function

--
Microsoft MVP - ASP/ASP.NET - 2004-2007
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"

[MiniEggs]

Bob

Yes I have this in the page for ADO constants
<!-- METADATA TYPE="TypeLib" FILE="C:\Program Files\Common
Files\system\ado\msado15.dll" -->

Yes friendly errors are off but I'm still getting the server 500 error. This
is not a server in our office (never did get that SQL login to work for
somereason on the one here)

I've now removed the named parameters again

So are you saying the variable type of parameter does not really matter and
I can use the ? in place of @P1 @P2 etc etc regarless of if it is a
number/string/date etc ?

This is just a starting point at the queries will be more complex with more
parameters

Thanks in advance for your assistance

Andrew

"Bob Barrows" <reb0...@NOyahoo.SPAMcom> wrote in message
news:uAOQi83b...@TK2MSFTNGP02.phx.gbl...

[Bob Barrows]

MiniEggs wrote:
> Bob
>
> Yes I have this in the page for ADO constants
> <!-- METADATA TYPE="TypeLib" FILE="C:\Program Files\Common
> Files\system\ado\msado15.dll" -->
>
> Yes friendly errors are off but I'm still getting the server 500
> error. This is not a server in our office (never did get that SQL login to
> work
> for somereason on the one here)

Give us more details if you want help with this. Did you use SSMS to
configure your SQL Server to use mixed authentication? Did you then create a
SQL Login? Did you then create a user based on that login in your database?

>
> I've now removed the named parameters again
>
> So are you saying the variable type of parameter does not really
> matter and I can use the ? in place of @P1 @P2 etc etc regarless of if it
> is a
> number/string/date etc ?

Yes. In fact, you need to use the ? tokens in order for this technique to
work.
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/b3d322b882a604bd
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

You can still use explicit Parameters, but it's overkill in a vbscript
procedure. Just pass the parameter values in a variant array using the
second argument of the Execute method.

>
> This is just a starting point at the queries will be more complex
> with more parameters
>

You might consider using stored procedures ...
http://groups.google.com/group/microsoft.public.inetserver.asp.general/msg/5d3c9d4409dc1701?hl=en&

[MiniEggs]

Bob

I'll be in touch again next week if I can't get this (?) to work

The reason I started down the @P1 path was beacuse of the Classic ASP
section of this article

http://www.owasp.org/index.php/Reviewing_Code_for_SQL_Injection

With regards to the ....

Give us more details if you want help with this.
Did you use SSMS to configure your SQL Server to use mixed

uthentication? -YES
Did you then create a SQL Login? -not sure
Did you then create a user based on that login in your database? -YES (in
that I created a user and ticked the box with the db name in it)

I'm sure I did someting wrong in the setting up of the user

I'm surprised I can't find any examples with screen shots on how to do this
(specifically for IIS/ASP login) as there must have been millions who've
done it .... but as I say I must be doing something obviously wrong

Thanks

Andrew

"Bob Barrows" <reb0...@NOyahoo.SPAMcom> wrote in message

news:uG0dqc4b...@TK2MSFTNGP06.phx.gbl...

[Bob Barrows]

MiniEggs wrote:
> Bob
>
> I'll be in touch again next week if I can't get this (?) to work
>
> The reason I started down the @P1 path was beacuse of the Classic ASP
> section of this article
>
> http://www.owasp.org/index.php/Reviewing_Code_for_SQL_Injection

As long as you're using parameters, sql injection is impossible. The method
for passing values to those parameters is irrelevant, so you might as well
use the easiest technique.

>
> With regards to the ....
>
> Give us more details if you want help with this.
> Did you use SSMS to configure your SQL Server to use mixed
> uthentication? -YES
> Did you then create a SQL Login? -not sure

Why not? It's simply a matter of going to the Security tab in SSMS for your
server and creating a new login that is not mapped to a Windows login. Call
it ASPlogin and assign it a password.

> Did you then create a user based on that login in your database? -YES

How can you answer "YES" if you're not sure of the previous answer?

> (in that I created a user and ticked the box with the db name in it)
>
> I'm sure I did someting wrong in the setting up of the user
>
> I'm surprised I can't find any examples with screen shots on how to
> do this (specifically for IIS/ASP login) as there must have been
> millions who've done it .... but as I say I must be doing something
> obviously wrong
>

Don't you have SQL Books Online (BOL)?? There are zillions of examples in
there (OK, maybe one or two ... )

[Bob Barrows]

MiniEggs wrote:
> Bob
>
> I'll be in touch again next week if I can't get this (?) to work
>
> The reason I started down the @P1 path was beacuse of the Classic ASP
> section of this article
>
> http://www.owasp.org/index.php/Reviewing_Code_for_SQL_Injection
>

Again, I've never seen the named parameters work, even for SQL Server. They
especially will not work if you are using ODBC rather than a native SQL
Server provider (SQLOLEDB or SQLNACLI) to connect to the database.

[MiniEggs]

Thanks Bob

Point taken about paramerterized queries. I will look into this now

Just been back and checked the server here

Yes there is a user called aspuser in the Database>Security>Login that I
setup

In the Database>Mydb>Security>Users aspuser shows as being
db_owner,db_reader,db_writer

The server is set to use Windows and SQL security

The connection string looks like this

Provider=SQLOLEDB; Data Source=127.0.0.1; Initial Catalog=Mydb; User
Id=aspuser;Password=xxxx

The error I get back is this

Microsoft OLE DB Provider for SQL Server error '80040e4d'
Login failed for user 'aspuser'.

Thanks

"Bob Barrows" <reb0...@NOyahoo.SPAMcom> wrote in message

news:OqGrSC5b...@TK2MSFTNGP05.phx.gbl...

[MiniEggs]

Further to below

I had a typo in the password in the ASP script so I am now back to the
originial error of

Microsoft OLE DB Provider for SQL Server error '80004005'
Cannot open database "Mydb" requested by the login. The login failed.

I tried creating a DSN and using test connection. this worked (that how I
found the typo)

I can even login to SSMS using the aspuser and open the tabale in mydb and
view it (other dbs are not accessable as I would expect)

but this code still returns the above error

<%
Dim conPubs
Set conPubs = Server.CreateObject("ADODB.Connection")
strConn = "Provider=SQLOLEDB; Data Source=127.0.0.1; Initial Catalog=mydb;
User Id=aspuser; Password=xxxx;"
conPubs.Open strConn
If ConPubs.State = adStateOpen Then
Response.Write "Connection to " & conPubs.DefaultDatabase & " created
succeassfully"
else
Response.Write "Connection failed"
end if
conPubs.Close
Set conPubs = Nothing
%>

I used

EXEC sp_helplogins 'aspuser'

and the default database does appear to be mydb

Thanks in advance

"MiniEggs" <b...@a.com> wrote in message
news:uRjaJOac...@TK2MSFTNGP04.phx.gbl...

[MiniEggs]

I've now tried this connection string

Provider=sqloledb; Server=127.0.0.1; Database=mydb; User Id=aspuser;
Password=xxxxx

still no luck

"Bob Barrows" <reb0...@NOyahoo.SPAMcom> wrote in message

news:OqGrSC5b...@TK2MSFTNGP05.phx.gbl...

[Bob Barrows]

MiniEggs wrote:
> Further to below
>
> I had a typo in the password in the ASP script so I am now back to the
> originial error of
>
> Microsoft OLE DB Provider for SQL Server error '80004005'
> Cannot open database "Mydb" requested by the login. The login failed.
>
> I tried creating a DSN and using test connection. this worked (that
> how I found the typo)
>
> I can even login to SSMS using the aspuser and open the tabale in
> mydb and view it (other dbs are not accessable as I would expect)
>
> but this code still returns the above error
>
> <%
> Dim conPubs
> Set conPubs = Server.CreateObject("ADODB.Connection")
> strConn = "Provider=SQLOLEDB; Data Source=127.0.0.1; Initial

My preference would be to use the actual name of the server, or "(local)"
rather than the ip address, but that does not seem to be the problem.

My first inclination was to suggest you specify the tcp/ip network library
in your connection string, like this;
"Network Library=DBMSSOCN;"
but again, that does not seem to be the problem. It can't hurt. especially
if you wish to continue to use the ip address instead of the server name.

You might try using the SQLNCLI provider rather than the SQLOLEDB provider,
but ... again, that does not seem to be the problem. Still, it can't hurt.

> Catalog=mydb; User Id=aspuser; Password=xxxx;"
> conPubs.Open strConn
> If ConPubs.State = adStateOpen Then
> Response.Write "Connection to " & conPubs.DefaultDatabase & "
> created succeassfully"
> else
> Response.Write "Connection failed"
> end if
> conPubs.Close
> Set conPubs = Nothing
> %>
>
> I used
>
> EXEC sp_helplogins 'aspuser'
>
> and the default database does appear to be mydb
>
> Thanks in advance
>

How about trying this in SSMS:
EXECUTE AS login='aspuser'
select * from mydb.dbo.tablename
REVERT

If that works, then I'm stumped. My guess is you missed another typo in the
connection string.

[MiniEggs]

OK Thanks Bob

Lets go at it from this angle to start with

I used the windows local logon to load SSMS and then ran this

EXECUTE AS login='aspuser'
select * from mydb.dbo.tablename
REVERT

which gives me this error .... Invalid object name 'mydb.dbo.tablename'.

If I change it to this

execute as login='aspuser'
use mydb
select * from dbo.tablename
revert

I Get error .... Could not locate entry in sysdatabases for database
'mydb'. No entry found with that name. Make sure that the name is entered
correctly.

Hopefully this now gives us a clue ?

"Bob Barrows" <reb0...@NOyahoo.SPAMcom> wrote in message

news:%23qsUDUb...@TK2MSFTNGP06.phx.gbl...

[Bob Barrows]

MiniEggs wrote:
> OK Thanks Bob
>
> Lets go at it from this angle to start with
>
> I used the windows local logon to load SSMS and then ran this
>
> EXECUTE AS login='aspuser'
> select * from mydb.dbo.tablename
> REVERT
>
> which gives me this error .... Invalid object name
> 'mydb.dbo.tablename'.
> If I change it to this
>
> execute as login='aspuser'
> use mydb
> select * from dbo.tablename
> revert
>
> I Get error .... Could not locate entry in sysdatabases for database
> 'mydb'. No entry found with that name. Make sure that the name is
> entered correctly.
>
> Hopefully this now gives us a clue ?
>
>

It certainly appears to be a msspelling somewhere, probably in the name of
the user that was created in mydb.

Can you run this statement in SSMS and verify that "aspuser" appears in the
results?

use mydb;
exec sp_helpuser;

If not, run this statement:

use mydb;
exec sp_adduser 'aspuser';

If the user does appear in the results, it might be orphaned (as the result
of a database restore or detach/reattach operation). You can fix it using:
EXEC sp_change_users_login 'Update_One', 'aspuser', 'aspuser';

And make sure 'dbo' is the default schema for this user by running:

use mydb;
ALTER USER aspuser WITH DEFAULT_SCHEMA = dbo;

Rather than making this account db_owner, I prefer to grant it rights to all
objects in the dbo schema:

use mydb;
GRANT EXECUTE,SELECT,UPDATE,DELETE, INSERT ON dbo TO aspuser;

[MiniEggs]

> use mydb;
> exec sp_helpuser;

this gives me the ....

Could not locate entry in sysdatabases for database
'mydb'. No entry found with that name. Make sure that the name is entered
correctly.

However if I chosse mydb from the drop down then exec sp_helpuser runs and
shows aspuser in the results

i've tried the sp_changed_users_login and alter user both of which worked.
the grant execute did not though

tried sp_helpuser again and got the same result ie ok if i choose the db
from the drop down otherwise errors on use statement

Thanks

"Bob Barrows" <reb0...@NOyahoo.SPAMcom> wrote in message

news:e3wsuwbc...@TK2MSFTNGP06.phx.gbl...

[MiniEggs]

OK I've just created a new db added a table with a couple of colums, added
the aspuser to that db and can now connect to the db

So there is obviously something odd with the existing mydb so not sure what
I need to change to access it

"MiniEggs" <b...@a.com> wrote in message

news:unRxwCcc...@TK2MSFTNGP02.phx.gbl...

[Bob Barrows]

So that means there is an issue with the database itself.
What can you see when you run this:
select * from master.dbo.sysdatabases

Make sure mydb is in the results. If not, look for the row containing
the mdf file and look at the name column.

--
HTH,
Bob Barrows

[MiniEggs]

OK finally getting somewhere ! Thanks for your help !

Starting from one server where I could connect to the database but not see
any error info to another where I could see the error info but not connect
to the db it was pretty difficult to try any asp out !

Yes there was a mismatch between the name and mdf name. using the name from
the left most colum I can now connect to it

Now that I can see the error info I've found the problem.

I cannot pass my array to the execute statement I need to use the array
function to create the array with the appropriate number of elements

ie if I have say DIM aParamValue(3) I actaully end up with 4 elements which
will always be one too many elements

OK so I can just dim or re dim with one less element

I am aware there is an array() function that returns an array made of the
strings passed to it

although these can be variable or literals the actual bit between the
brackets is 'hard coded' so to speak

so to write a generic function that I can pass a query string to with ? and
the values in an array i've got to copy them into a new aray that is excatly
the correct size

or I guess instead of buildin the array like

aSQLParamValue(0) ="D%"
aSQLParamValue(1) ="R%"
etc

Just pass Array("D%","R%") into my function and never actually declare and
array to hold my params because it's almost always going to be the wrong
size most of the time if you sort of see what I mean

Just getting used to the VBScript quirks but think i'm finally starting to
get there

On the server 500 errors on the sever I do not have control over is there
anything else that should be set other than in IIS send errors to client and
in the browser untick show friendly errors ?

Thanks for all your help

"Bob Barrows" <reb0...@NOyahoo.SPAMcom> wrote in message

news:eTbTSpcc...@TK2MSFTNGP06.phx.gbl...

[Bob Barrows]

MiniEggs wrote:
> OK finally getting somewhere ! Thanks for your help !
>
> Starting from one server where I could connect to the database but
> not see any error info to another where I could see the error info
> but not connect to the db it was pretty difficult to try any asp out !
>
> Yes there was a mismatch between the name and mdf name. using the
> name from the left most colum I can now connect to it
>
> Now that I can see the error info I've found the problem.
>
> I cannot pass my array to the execute statement I need to use the
> array function to create the array with the appropriate number of
> elements
>
> ie if I have say DIM aParamValue(3) I actaully end up with 4 elements
> which will always be one too many elements

Yes. This is not restricted to vbscript. Arrays have zero-based indexes.

>
> On the server 500 errors on the sever I do not have control over is
> there anything else that should be set other than in IIS send errors
> to client and in the browser untick show friendly errors ?
>

No, the article I cited in my first reply is all the info I have. What
you can do is log the error someplace:

on error resume next
'statement whose error you wish to log
if err<>0 then
errmsg=err.description
'log the error, typically in a text file using filesystemobject
'rethrow the error
err.raise 1+vbObjectError,,errmsg
end if

Of course, you will need to get the server admin to create a folder on
the server and grant Modify permissions to all users of the web
application in order to enable the log file to be maintained.

--
HTH,
Bob Barrows

[MiniEggs]

Bob

Thanks for all your help. Things seem to be going as expected now.

The ISP had made a mistake on the send the error info to the client setting
so I can now finally see the errors

One more question. My input cleaning code looks like this ....

Function GetCleanUserValue(param)
Dim ReturnVal

ReturnVal=""

If IsEmpty(param) or param="" Then
ReturnVal=param
else
If IsNumeric(param) Then
ReturnVal=Clng(param)
Else
ReturnVal=CStr(param)
'escape single quotes
ReturnVal=(Replace(ReturnVal,"'","''"))
'escape double quotes
ReturnVal=(Replace(ReturnVal,(Chr(34)),(Chr(34) & Chr(34))))
'remove --
ReturnVal=(Replace(ReturnVal,"--",""))
'remove /*
ReturnVal=(Replace(ReturnVal,"/*",""))
'remove */
ReturnVal=(Replace(ReturnVal,"*/",""))
'remove xp_
ReturnVal=(Replace(ReturnVal,"xp_",""))
'remove ;
ReturnVal=(Replace(ReturnVal,";",""))
End If
End if

GetCleanUserValue=ReturnVal

End Function

It appears now that i'm using parameters passed to the query I no longer
need to "escape" the single quotes etc ... ie at the moment I am ending up
with two single quotes in the data rather than just the one I was expecting.
Is that correct ?

Thanks

"Bob Barrows" <reb0...@NOyahoo.SPAMcom> wrote in message

news:OhqoZhdc...@TK2MSFTNGP02.phx.gbl...

[Bob Barrows]

MiniEggs wrote:
> Bob
>
> Thanks for all your help. Things seem to be going as expected now.
>
> The ISP had made a mistake on the send the error info to the client
> setting so I can now finally see the errors
>
> One more question. My input cleaning code looks like this ....
>
> Function GetCleanUserValue(param)
> Dim ReturnVal
>

>

> GetCleanUserValue=ReturnVal
>
> End Function
>
> It appears now that i'm using parameters passed to the query I no
> longer need to "escape" the single quotes etc ... ie at the moment I
> am ending up with two single quotes in the data rather than just the
> one I was expecting. Is that correct ?
>

Exactly. One of the benefits of using parameters is the elmination of
the need to escape your string parameters. You can now concentrate on
preventing errors from invalid entries (alphas where numbers are
expected, numbers outside of the expected range, etc.) and, if you like,
detecting sql injection attempts if you are so inclined.

There is a school of thought that hackers can be discouraged by
"punishing" them when they make their attempts. For example, instead of
simply redirecting them to an error page, how about redirecting them to
a page that looks like the actual destination but takes several minutes
to load, perhaps displaying a never-ending progress bar. There's another
school of thought that such tactics will simply piss them off and make
them more determined to break in, but at least it will take longer for
them to do so (and perhaps prevent them from using their scripts),
giving your admin a chance to step in personally and take measures.

PS. You still have to worry about handling the data stored in the
database to prevent code injection. There is a tactic called "secondary
sql injection" that involves causing a string containing injected sql to
be stored in the database. The unwary programmer could cause that sql to
be executed if he unwittingly uses database values in dynamic sql.
Also, if the unwary programmer writes database values directly to
Response without HTMLEncoding it, he might find himself injecting script
blocks into the Response.

--
HTH,
Bob Barrows