0

I am creating one login validation page for my classic asp site(vbscript). as I want prevent my page from SQL Injection, I used parametrized queries in my page but I am unable to retrieve value after writing if Not Recordset.EOF line. value is not passing. please help me to solve this issue. my code is below;

<%
Dim Objrs, objConn, objCmd, str

Set objConn = Server.CreateObject("ADODB.Connection")
Set objCmd  = Server.CreateObject("ADODB.Command")
Set Objrs   = Server.CreateObject("ADODB.Recordset")

objConn.open MM_connDUdirectory_STRING '(already created)

Set objCmd.ActiveConnection = objConn

str = "SELECT * FROM admin WHERE Ausr=? AND Apwd=?"

objCmd.CommandText = str
objCmd.CommandType = adCmdText

dim objParam1, objParam2
Set objParam1 = objCmd.CreateParameter("param1", adVarChar, adParamInput, len(StrUserName), "")
objCmd.Parameters.Append objParam1
objCmd.Parameters("param1") = StrUserName

Set objParam2 = objCmd.CreateParameter("param2", adVarChar, adParamInput, len(StrPassword), "")
objCmd.Parameters.Append objParam2
objCmd.Parameters("param2") = StrPassword
set objRS = objCmd.execute


'if objRS.EOF <> True and objRS.BOF <> True then
'if Objrs("Ausr") = objCmd.Parameters("param1") then
'response.Write(Objrs("Ausr"))
'response.Write should show username but its showing blank
'end if
'end if

'Do While Not objRS.EOF
'if Objrs("Ausr") = objCmd.Parameters("param1") then
'response.Write(Objrs("Ausr"))
'end if
'objRS.MoveNext 
'Loop 

If Not objRS.EOF Then
response.write("Granted access to the user:" & StrUserName)
end if
%>

I tried with If..End If as above but its showing same problem, the recordset(objrs) parametrized method is not executing. its show me blank page. code should check if user exist or not. Response.Write("Granted access to the user:" & StrUserName) should show me strusername value but its not showing and page is blank. please help me workout where I'm going wrong?

user692942
  • 16,398
  • 7
  • 76
  • 175
lokesh purohit
  • 179
  • 2
  • 2
  • 16
  • The code is fine, are you sure that the query is right? `SELECT * FROM admin WHERE Ausr=? AND Apwd=?` and that the table `admin` contains the relevant data? – user692942 Aug 02 '14 at 10:13
  • @Lankymart, yes query is ok and have relevant data too. if i pass value without creating parameter then its works fine. but with parametrized query its not passing value after eof command line. – lokesh purohit Aug 02 '14 at 11:34
  • Adjusted my answer based on your [comment](http://stackoverflow.com/questions/25079229/value-is-not-showing-under-recordset-eof-using-parameterized-query-in-vbscript-f?noredirect=1#comment39049096_25094632). – user692942 Aug 02 '14 at 12:43
  • your problem was solved? – Rafael Aug 14 '14 at 13:09

3 Answers3

0

From i can see with you current code,you have 2 problems:

  1. You have this condition if objRS.EOF <> True and objRS.BOF <> True then with this you are excluding the first and the last record from printing. Don't know why this is needed, but because you are not iterating over all the elements in your recordset (aka Rows). you will never see any record printed.

  2. To overcome the problem #1 you need to enclose all the code in loop (for,while,do while) and use objRS.MoveNext() function in your recordset object to read all the records obtained in your Query.

this is all the problems that i can see with this limited context. I hope this helps.

More Information: Recordset Object Properties, Methods, and Events - MSDN

EDIT: Seeing the edit from OP in the code, and the goal that i think he want to achieve i suggest this code instead:

'Do While Not objRS.EOF'
   'if Objrs("Ausr") = objCmd.Parameters("param1") then'
      'response.Write(Objrs("Ausr"))'
   'end if'
   'objRS.MoveNext' 
'Loop'


If Not objRS.EOF Then
    response.write("Granted access to the user:" & StrUserName)
End if

I'm assuming that you want to check if a single user its logged in.

Rafael
  • 3,081
  • 6
  • 32
  • 53
0

Debug; Check if you are passing the values to the parameters. Print out the values and see.

Response.write "StrUserName ="& StrUserName &"<br/>"
Response.write "StrPassword ="& StrPassword &"<br/>"
set objRS = objCmd.execute

Also, try passing in the values during creation of the parameter:

Set objParam1 = objCmd.CreateParameter("param1", adVarChar, adParamInput, len(StrUserName), StrUserName)
Flakes
  • 2,422
  • 8
  • 28
  • 32
  • it retrieving value if i pass even after objcmd.execute command line. – lokesh purohit Aug 02 '14 at 12:03
  • okay. Just wanted to check. Can you pass in the values during creation of the parameter, as shown ? – Flakes Aug 02 '14 at 12:05
  • it retrieving value if i pass even after objcmd.execute command line and i m able to get value what i pass in creating parameter (like:-objCmd.Parameters("param2")). but i m not able to retrieve value under EOF command. – lokesh purohit Aug 02 '14 at 12:10
  • what is the data type of the fields in the db ? – Flakes Aug 02 '14 at 12:15
  • And which db ? MySQl or Sql Server ? – Flakes Aug 02 '14 at 12:19
  • as backend, i m using MSSQL2005 – lokesh purohit Aug 02 '14 at 12:37
  • datatype in db is varchar(50) for username and varchar(50) for password – lokesh purohit Aug 02 '14 at 12:38
  • @lokeshpurohit How long are the values you are passing in?, personally I tend to set the size parameter to the max size of the field (`50` in this case) in the database rather then trying to set it dynamically. – user692942 Aug 02 '14 at 12:40
  • i think parameter size is not an issue, even i tried max size of field(50) also what i have in db – lokesh purohit Aug 02 '14 at 12:44
  • @lokeshpurohit I write this kind of code everyday and can't see anything wrong with the samples provided, which in my opinion points to the database being the problem. Is it possible you have two databases and the connection string is pointing at the wrong one? – user692942 Aug 02 '14 at 12:45
  • 1
    @Lankymart, yes problem has been solved. the problem was with encrypted password value. now its working find. i really appreciate all of your support and guidance. i wish to thank this site too. – lokesh purohit Aug 02 '14 at 13:06
  • @lokeshpurohit Please add this comment to your post or post as an answer and accept to close. – Flakes Aug 02 '14 at 14:05
  • 1
    @lokeshpurohit This is what I'm talking about, you make no mention of encryption when describing the problem. Just mentioning that would have saved myself and others a lot of pointless work trying to help you. – user692942 Aug 02 '14 at 14:17
0

Actually after looking closer at your code there a few issues

Didn't notice it at first but looks as though your not setting the values correctly, there are two ways to do it;

Specify them during the CreateParameter() method

Call .Parameters.Append(.CreateParameter("param1", adVarChar, adParamInput, 50, StrUserName)
Call .Parameters.Append(.CreateParameter("param2", adVarChar, adParamInput, 50, StrPassword)

Specify after creation of the parameters

Call .Parameters.Append(.CreateParameter("param1", adVarChar, adParamInput, 50)
Call .Parameters.Append(.CreateParameter("param2", adVarChar, adParamInput, 50)

.Parameters("param1").Value = StrUserName
.Parameters("param2").Value = StrPassword

That present your setting the parameter object to a string, which won't give the expected result.

Give this a try;

<%
Dim objRS, objCmd, str

Set objCmd  = Server.CreateObject("ADODB.Command")
Set Objrs   = Server.CreateObject("ADODB.Recordset")

str = "SELECT * FROM admin WHERE Ausr=? AND Apwd=?"

With objCmd
  'No need to create ADODB.Connection as the ADODB.Command will do it 
  'for you if you pass the Connection string.
  .ActiveConnection = MM_connDUdirectory_STRING
  .CommandText = str
  .CommandType = adCmdText

  'Don't pass blank values, just specify the name, data type, 
  'direction and length.
  Call .Parameters.Append(.CreateParameter("param1", adVarChar, adParamInput, 50)
  Call .Parameters.Append(.CreateParameter("param2", adVarChar, adParamInput, 50)

  'If setting values after the CreateParameter() don't use blank strings in
  'the CreateParameter() call.
  .Parameters("param1").Value = StrUserName
  .Parameters("param2").Value = StrPassword
  Set objRS = .Execute()

  If Not objRS.EOF Then
    Call Response.Write("Granted access to the user:" & StrUserName)
  End If
End With
Set objCmd = Nothing
%>

Useful Links

Community
  • 1
  • 1
user692942
  • 16,398
  • 7
  • 76
  • 175