Executing SQL Prod ... and any good debuggers? 1

[Autosys]

Hi there ..

A bit new to ASP o sorry for the experts out there who are reading this.

I'm having trouble executing a sql 2000 proc through my asp/vbscript code. (Snippet below) .. Any ideas?

I find it a bit hard to find out where I'm going wrong as I'm doing it in notepad ... are there any good (& free if possible) asp debuggers that you know of?

Perhaps something that can step through the code line by line or at least return me an error?

Your help would be much appreciated thx.

strSQL1 = "exec sp_register '" & strUsername & "','" & strPassword & "','" & strName & "','" & strSurname & "','" & strAge & "','" & strSuburb & "','" & strStreet & "','" & strPostcode & "','" & strPhone & "','" & strEmail

strConnection = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=myID;Password=myPassword;Initial Catalog=MYDB;Data Source=MyServer"

Set conn = Server.CreateObject("ADODB.Connection")

conn.open strConnection
conn.execute(strSQL1)

 

[gmmastros]

There's a couple things that may help you.

First. If you get an error, it might be nice to get the error message.

Second. It's often helpful to see the query that is being built, because that will often times help you see the problem.

As such, I would recommend this...

strSQL1 = "exec sp_register '" & strUsername & "','" & strPassword & "','" & strName & "','" & strSurname & "','" & strAge & "','" & strSuburb & "','" & strStreet & "','" & strPostcode & "','" & strPhone & "','" & strEmail

strConnection = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=myID;Password=myPassword;Initial Catalog=MYDB;Data Source=MyServer"

Set conn = Server.CreateObject("ADODB.Connection")

conn.open strConnection
conn.execute(strSQL1)
[blue]
If Err.Number <> 0 Then
   Response.Write(Err.Description & "<br />" & strSQL1)
End If
[/blue]

Now, when you run this, you will get an error. If it's not obvious what the problem is, then you can copy/paste the query to SQL Server 2000's query analyzer and run it there.

In this case, you are missing the last single-quote.

[tt][blue]strSQL1 = "exec sp_register '" & strUsername & "','" & strPassword & "','" & strName & "','" & strSurname & "','" & strAge & "','" & strSuburb & "','" & strStreet & "','" & strPostcode & "','" & strPhone & "','" & strEmail[!] & "'"[/!][/blue][/tt]

Now, having said all that, I encourage you to do a google search on [google]SQL Injection[/google]. Read the first couple articles to truly understand this problem. Please don't think "Since I've never heard of this, it can't be that big of a problem". That would be the wrong attitude to take.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Autosys]

Thanks so much for your detailed explanation gmmastros .. I'm going to look into all of your suggestions, and of course the obvious missing quote one.

I'm familiar with sql injection, but not an expert in preventing it which is something i need to look into in order to make sure that people won't be able to drop tables etc and do nasty things to my site!

thanks again for you quick response.!

 

[Autosys]

further to my previous post ...

added the missing quote ... then it still failed (but that was obviously a prob good spot!) ..

then added the error trapping bit and ouput my sql command as you suggested ... pasted it into sql query and it turned out i tried to put varchar data into an int column.

fantastic advice ... some of it is coming back to me thanks a lot!

S

 

[gmmastros]

With ASP, it's important to validate the inputs before sending data to SQL Server (or any database).

One of my favorite functions is...

Function IsInteger(Data)
	
  If Trim(Data) = "" Then
    IsInteger = False
  Else
    IsInteger = IsNumeric(Data & ".0e0")
  End If

End Function

I put that function in an include file. Then, I check each value before I even think about calling the database.

Ex:

[tt][blue]
If Not IsInteger(strAge) Then
Response.Write("Age must be a number.")
Else
' Other code to use the age.
End If
[/blue][/tt]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Autosys]

next on my list ... checking each field on the form for strings not allowed .. sure i'll find a place for your int checking thanks mate ..!