asp and ms sql

[Eugene Anthony]

This is a table created in ms sql:

create table customer
(
CustomerID int IDENTITY,
FirstName varchar(25),
LastName varchar(25),
CompanyName varchar(25),
Phone int,
Email varchar(20),
Password varchar(20),
Address varchar(30),
Zip int,
StateID varchar(30),
City varchar(30),
CountryID varchar(30),
CustomerTypeID varchar(30),
Session varchar(30),
IP varchar(30),
LastUpdate Smalldatetime
)

This is the stored procedure created in ms sql:

Create Procedure usp_InsertCustomer
@FirstName varchar(25),
@LastName varchar(25),
@CompanyName varchar(25),
@Phone int,
@Email varchar(20),
@Password varchar(20),
@Address varchar(30),
@Zip int,
@StateID varchar(30),
@City varchar(30),
@CountryID varchar(30),
@CustomerTypeID int,
@IP varchar(20)
AS SET NOCOUNT ON

Declare @sessionID AS UNIQUEIDENTIFIER
Declare @session AS varchar(255)

set @sessionID = NEWID()
set @session = convert(varchar(255),@SessionID)

INSERT INTO Customer
FirstName,LastName,CompanyName,Phone,Email,Passwor d,Address,Zip,StateID,
City,CountryID,CustomerTypeID,Session,IP,LastUpdat e
VALUES(@FirstName,@LastName,@CompanyName,@Phone,@E mail,@Password,@Addres
s,@Zip,@StateID,@City,@CountryID,@CustomerTypeID,@ session,@IP,GETDATE())
In my RegistrationExec.asp I have the following code:

<!--#include file="database_Function.asp"-->
<!--#include file="string_Function.asp"-->
<!--#include file="validateField_Function.asp"-->
<%
firstname = formatforDb(getUserInput(Request.Form("textfield1" )))
lastname = formatforDb(getUserInput(Request.Form("textfield2" )))
companyname = formatforDb(getUserInput(Request.Form("textfield3" )))
phone = formatforDb(getUserInput(Request.Form("textfield4" )))
email = formatforDb(getUserInput(Request.Form("textfield5" )))
password = formatforDb(getUserInput(Request.Form("textfield6" )))
address = formatforDb(getUserInput(Request.Form("textfield7" )))
zip = formatforDb(getUserInput(Request.Form("textfield8" )))
state = formatforDb(getUserInput(Request.Form("select1")))
otherstate = formatforDb(getUserInput(Request.Form("textfield9" )))
city = formatforDb(getUserInput(Request.Form("textfield10 ")))
country = formatforDb(getUserInput(Request.Form("select2")))

if isLength(firstname) = false then
response.redirect "error_msg?msg=Please fill in the first name."
end if

if isLength(lastname) = false then
response.redirect "error_msg?msg=Please fill in the last name."
end if

if isLength(companyname) = false then
response.redirect "error_msg?msg=Please fill in the company name."
end if

if isLength(phone) = false then
response.redirect "error_msg?msg=Please fill in the phone number."
end if

if isLength(email) = false then
response.redirect "error_msg?msg=Please fill in the email address."
end if

if isLength(password) = false then
response.redirect "error_msg?msg=Please fill in the password."
end if

if isLength(address) = false then
response.redirect "error_msg?msg=Please fill in the address."
end if

if isLength(zip) = false then
response.redirect "error_msg?msg=Please fill in the zip code."
end if

if (isLength(state) = true AND isLength(otherstate) = true) OR
(isLength(state) = false AND isLength(otherstate) = false) then
response.redirect "error_msg?msg=Please fill in or select a state."
end if

if isLength(city) = false then
response.redirect "error_msg?msg=Please fill in the city."
end if

if isLength(country) = false then
response.redirect "error_msg?msg=Please select a country."
end if

if isEmail(email) = false then
response.redirect "error_msg?msg=You have entered an invalid email
address."
end if

if isZip(zip) = false then
response.redirect "error_msg?msg=You have entered an invalid zip code."
end if

if isPhone(phone) = false then
response.redirect "error_msg?msg=You have entered an invalid phone
number."
end if

Dim Temp

if isEmpty(state) then
Temp = otherstate
else
Temp = state
end if

mySQL = "EXECUTE usp_InsertCustomer @FirstName='" & firstname &
"',@LastName='" & lastname & "',@CompanyName='" & companyname &
"',@Phone='" & phone & "',@Email='" & email & "',@Password ='" &
password & "',@Address='" & address & "',@Zip='" & zip & "',@StateID='"
& "kl" & "',@City='" & city & "',@CountryID='" & country &
"',@CustomerTypeID=" & CInt(1) & ",@IP='" &
Request.ServerVariables("REMOTE_HOST") & "'"
call updateDB(mySQL, rs)

rs.close()
CloseDB()
%>

I am getting this error:

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E57)
String or binary data would be truncated.
/Mix/database_Function.asp, line 15
How do I solve this problem?.

Your help is kindly appreciated.

Regards

Eugene Anthony

*** Sent via Developersdex http://www.developersdex.com ***

[Jeff Cochran]

On Sun, 10 Jul 2005 06:50:16 -0700, Eugene Anthony
<so***********@yahoo.com> wrote:

I am getting this error:

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E57)
String or binary data would be truncated.
/Mix/database_Function.asp, line 15
How do I solve this problem?.

Try breaking the code down into the individual data pieces and running
your SQL query. It looks like one or more pieces of data entered are
longer than the field in the table allows. Once you figure out which
field, increase its size or reduce the lenght of input allowed.

Jeff

[Bob Barrows [MVP]]

Jeff has told you how to find your problem, but I have a couple things to
add.
You seem to have some good validation functions, but you need to revise them
(at least the isLength one) so that you can provide a maxlength argument,
either having the function return false if the data is too long to fit in te
field for which it is intended, or truncaqting the data to the max length if
that's how you wish to handle it. For example:

function isLength(byref pData, pMaxLength)
dim datalength
datalength=len(pData)
if datalength = 0 then
isLength = false
elseif datalength > pMaxlength then
' either:
' isLength = false
' or:
' pData=left(pData,pMaxLength)
' isLength = true
else
isLength = true
end if

More below:

Eugene Anthony wrote:

mySQL = "EXECUTE usp_InsertCustomer @FirstName='" & firstname &
"',@LastName='" & lastname & "',@CompanyName='" & companyname &
"',@Phone='" & phone & "',@Email='" & email & "',@Password ='" &
password & "',@Address='" & address & "',@Zip='" & zip &
"',@StateID='" & "kl" & "',@City='" & city & "',@CountryID='" &
country & "',@CustomerTypeID=" & CInt(1) & ",@IP='" &
Request.ServerVariables("REMOTE_HOST") & "'"
call updateDB(mySQL, rs)

Nothing to do with your problem, but ... why are you using a
resource-wasting recordset to execute a query that does not return records?
I suspect you are attempting to use a generic custom function to run all of
your queries, whether they return records or not, but, there are times when
functions can be too generic. This is one of them, especially if you care
about conserving your server's resources

Also, by using dynamic sql instead of parameters to execute your stored
procedure you are:
1. Undoing the performance benefits you gained from using the stored
procedure
2. Forcing yourself to do perform extra unnecessary processing to make sure
your string data will be accepted (the formatforDb() function)
3. Leaving yourself open to sql injection attack:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

It is much more efficient, as well as more secure, to use parameters to pass
values to a stored procedure. See:
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"

[Eugene Anthony]

This are functions which I am using to filter syntax that could lead to
sql injection:

<%

function getUserInput(input)

dim tempStr

tempStr = replace(input,"--","")
tempStr = replace(input,";","")
tempStr = replace(input,"SCRIPT","s-c-r-i-p-t",1,-1,1)
getUserInput = tempStr

end function

function formatforDb(input)

dim tempStr

tempStr = replace(input,"'","''")
tempStr = replace(input,"&"," and ")
formatforDb = tempStr

end function

%>
In regards to "dynamic sql instead of parameters" could you give me a
little more info so that I can do a research on it.

Regards

Eugene

*** Sent via Developersdex http://www.developersdex.com ***

[Eugene Anthony]

The connection is established as followed:

sub openDB()

objConn = Server.CreateObject("ADODB.Connection")
objConn.Open pConnectionString

end sub
sub updateDB(SQL,rs,filename)

openDb()
set rs = objConn.Execute(SQL)

end sub

Eugene Anthony

*** Sent via Developersdex http://www.developersdex.com ***

[Bob Barrows [MVP]]

Eugene Anthony wrote:

This are functions which I am using to filter syntax that could lead
to sql injection:

<%

function getUserInput(input)

dim tempStr

tempStr = replace(input,"--","")
tempStr = replace(input,";","")
tempStr = replace(input,"SCRIPT","s-c-r-i-p-t",1,-1,1)
This one does not seem to have anything to do with SQL Injection (SCRIPT is
not a SQL keyword).
getUserInput = tempStr

end function

function formatforDb(input)

dim tempStr

tempStr = replace(input,"'","''")
tempStr = replace(input,"&"," and ")
formatforDb = tempStr

end function

%>

Not good enough. See:
http://www.nextgenss.com/papers/adva..._injection.pdf
http://www.nextgenss.com/papers/more..._injection.pdf

And when you're finished filtering for the items mentioned in these papers,
then get ready to build filters for the next technique hackers find to
inject sql into the dynamic sql. Stop the madness! Plug the sql injection
hole once and for all - use parameters. SQL Injection depends on the use of
dynamic sql. No dynamic sql - no sql injection.

In regards to "dynamic sql instead of parameters" could you give me a
little more info so that I can do a research on it.

I already mentioned this:
http://tinyurl.com/jyy0

What more do you need? SQL Books Online has a little information about the
benefits of stored procedures. You should look for that,.

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"

[Bob Barrows [MVP]]

Eugene Anthony wrote:

The connection is established as followed:
I'm not sure why you are showing us this. Are you asking for
recommendations? If so, read on:

sub openDB()

objConn = Server.CreateObject("ADODB.Connection")
objConn.Open pConnectionString

This does not really tell me anything. Are you using ODBC or OLE DB? The
latter is preferable.
end sub
sub updateDB(SQL,rs,filename)
What is the purpose of filename? You don't seem to be using it ...

openDb()
set rs = objConn.Execute(SQL)

end sub

This is too generic. By using a recordset to execute a query that returns no
records, you are wasting system resources ... but I'm repeating myself. If
you insist on using dynamic sql to execute a stored procedure, this is all
that's required to execute a query that returns no records:

sub updatedb(SQL)
objConn.Execute SQL,,129

The 129 is the combination of two ExecuteOption constants:
adCmdText - 1
adExecuteNoRecords - 128

129 says you are passing a string containing a sql statement to be executed,
and the query will not return records so ADO does not have to waste time,cpu
and memory creating a recordset object that will not be used.

My preference would be:
'After validating the data (the getUserInput function will no longer be
' needed, unless you still want to avoid that SCRIPT fragment ...)

objConn.usp_InsertCustomer validatedtextfield1, ..., _
validatedSelect2
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"

[Eugene Anthony]

Is this the correct way. I did see it on the artical which you have
posted to me.
openDB()

objConn.usp_InsertCustomer
firstname,lastname,companyname,phone,email,passwor d,address,zip,"kl",cit
y,country,CInt(1),Request.ServerVariables("REMOTE_ HOST")

CloseDB()

Eugene Anthony

*** Sent via Developersdex http://www.developersdex.com ***

[Bob Barrows [MVP]]

Looks good to me. Much simpler isn't it? And it results in more efficient
execution at the SQL Server as well.

Bob Barrows
Eugene Anthony wrote:

Is this the correct way. I did see it on the artical which you have
posted to me.
openDB()

objConn.usp_InsertCustomer
firstname,lastname,companyname,phone,email,passwor d,address,zip,"kl",cit
y,country,CInt(1),Request.ServerVariables("REMOTE_ HOST")

CloseDB()

--
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"

[Eugene Anthony]

In regards to the validation I made the code much simpler:

<%
function isLength(input)

isLength = (len(input) > 0)

end function

function isEmail(input)

isEmail = (inStr(input,"@") > 0 and inStr(input,".") > 0 and
len(input) > 5)

end function

function isZip(input)

isZip = false

if len(input) = 5 then

isZip = (isNumeric(input))

end if

if len(input) = 10 then

z1 = left(input,5)
z2 = right(input,4)
z3 = mid(input,6,1)

isZip = (isNumeric(z1) and isNumeric(z2) and z3="-")

end if

end function

function isNumber(input)

isNumber = (isNumber(input))

end function

function isPhone(input)

Dim tempPh

tempPh = replace(input," ","")
tempPh = replace(input,"-","")
tempPh = replace(input,"-","")
tempPh = replace(input,"(","")
tempPh = replace(input,")","")

isPhone = (isNumeric(tempPh))

end function
%>

Regards

Eugene Anthony

*** Sent via Developersdex http://www.developersdex.com ***