asp and ms sql

Discussion in 'ASP General' started by Eugene Anthony, Jul 10, 2005.

  1. 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,Password,Address,Zip,StateID,
    City,CountryID,CustomerTypeID,Session,IP,LastUpdate
    VALUES(@FirstName,@LastName,@CompanyName,@Phone,@Email,@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 ***
     
    Eugene Anthony, Jul 10, 2005
    #1
    1. Advertising

  2. Eugene Anthony

    Jeff Cochran Guest

    On Sun, 10 Jul 2005 06:50:16 -0700, Eugene Anthony
    <> 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
     
    Jeff Cochran, Jul 10, 2005
    #2
    1. Advertising

  3. 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"
     
    Bob Barrows [MVP], Jul 10, 2005
    #3
  4. 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, Jul 10, 2005
    #4
  5. 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 ***
     
    Eugene Anthony, Jul 10, 2005
    #5
  6. 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/advanced_sql_injection.pdf
    http://www.nextgenss.com/papers/more_advanced_sql_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], Jul 10, 2005
    #6
  7. 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"
     
    Bob Barrows [MVP], Jul 10, 2005
    #7
  8. 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,password,address,zip,"kl",cit
    y,country,CInt(1),Request.ServerVariables("REMOTE_HOST")

    CloseDB()

    Eugene Anthony

    *** Sent via Developersdex http://www.developersdex.com ***
     
    Eugene Anthony, Jul 10, 2005
    #8
  9. 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,password,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"
     
    Bob Barrows [MVP], Jul 10, 2005
    #9
  10. 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 ***
     
    Eugene Anthony, Jul 11, 2005
    #10
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. ecoolone
    Replies:
    0
    Views:
    766
    ecoolone
    Jan 3, 2008
  2. Ed Garcia
    Replies:
    4
    Views:
    190
    Guinness Mann
    Aug 7, 2003
  3. Belinda
    Replies:
    4
    Views:
    366
    Bob Barrows [MVP]
    Jun 11, 2004
  4. weiwei

    asp and sql statement in sql server db

    weiwei, Sep 22, 2004, in forum: ASP General
    Replies:
    3
    Views:
    207
    Jeff Cochran
    Sep 22, 2004
  5. Replies:
    10
    Views:
    278
    Bob Barrows [MVP]
    Jan 6, 2005
Loading...

Share This Page