Updating sql server through classic asp and vbscript

Question

Im trying to update details of a single customer and I'm having problems updating with the new user input. I can see the changes being passed but its not updating the sql. Here is the code -

    'Update' 
    updateC = request.QueryString("action")
    if updateC = "update" then

        Id = request.QueryString("Id")
        Name = request.QueryString("Name")
        Address = request.QueryString("Address") 
        Suburb = request.QueryString("Suburb") 
        Postcode = request.QueryString("Postcode")
        Age = request.QueryString("Age")
        Email = request.QueryString("Email")

    end if


    %>
    <form method="get" action="CreateCustomer.asp">
    Name:&nbsp;&nbsp;&nbsp;&nbsp; <input type="text" value="<%=Name %>" name="Name"><br/>
    Address:&nbsp; <input type="text" value="<%=Address %>" name="Address"><br/>
    Suburb:&nbsp;&nbsp;&nbsp; <input type="Suburb" value="<%=Suburb %>"  name="Suburb"><br/>
    Postcode: <input type="text" value="<%=Postcode %>"  name="Postcode"><br/>
    Age:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <input type="text" value="<%=Age %>"  name="Age"><br/>
    Email:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <input type="text" value="<%=Email %>"  name="Email"><br/><br/>
    <% if updateC = "update" then%>
        <input type="hidden" value="update" name="updateButton">
        <input type="submit" value="Update Customer">            
    <% else %>
        <input type="hidden" value="insert" name="insert">
        <input type="submit" value="New Customer">
    <% end if %>

    </form>

    <%       


    'Assign Variables'
    insertCheck = request.QueryString("insert")
    updCheck = request.QueryString("updateButton")
    if insertCheck = "insert" or updCheck = "update" then

        ID = request.QueryString("Id")
        Name = request.QueryString("Name")
        Address = request.QueryString("Address")
        Suburb = request.QueryString("Suburb")
        Postcode = request.QueryString("Postcode")
        Age = request.QueryString("Age")
        Email = request.QueryString("Email")

    end if

'update customer'
    updButton = request.QueryString("updateButton")
    if updButton = "update" and name<>"" then
        updateCustomer()            
    end if


     'Update customer sub procedure'
  sub updateCustomer()

        Dim uSQL, objCon

        Set objCon = CreateObject("ADODB.Connection")
        objCon.Open "Provider=SQLOLEDB.1;Password=xxxx;Persist Security Info=True;User ID=xxxx;Initial Catalog=Customer;Data Source=PC"

        uSQL = "UPDATE Customer SET Name = " & "'" & Name & "'" & " Where ID = " & "'" & Id & "'"
        objCon.Execute(uSQL)

        uSQL = "UPDATE Customer SET Address = " & "'" &  Address & "'" & " Where ID = " & "'" & Id & "'"
        objCon.Execute(uSQL)

        uSQL = "UPDATE Customer SET Suburb = " & "'" &  Suburb & "'" & " Where ID = " & "'" & Id & "'"
        objCon.Execute(uSQL)

        uSQL = "UPDATE Customer SET Postcode = " & "'" &  Postcode & "'" & " Where ID = " & "'" & Id & "'"
        objCon.Execute(uSQL)

        uSQL = "UPDATE Customer SET Age = " & "'" &  Age & "'" & " Where ID = " & "'" & Id & "'"
        objCon.Execute(uSQL)

        uSQL = "UPDATE Customer SET Email = " & "'" &  Email & "'" & " Where ID = " & "'" & Id & "'"  
        objCon.Execute(uSQL)

        objCon.Close

  end sub

The code above is from createcustomer.asp and the code below is from table.asp

        <td><Center><a href="CreateCustomer.asp?action=update&Id=<%= objRS("Id") %>&Name=<%= objRS("Name") %>&Address=<%= objRS("Address") %>&suburb=<%= objRS("Suburb") %>&postcode=<%= objRS("Postcode") %>&age=<%= objRS("Age") %>&email=<%= objRS("Email") %>">
        <input type="submit" value="Update"></a></Center></td>

You should at least sanitize your input by escaping the '-character in the raw querystring values to break of possible injection scripts! — mzwaal, May 26 '11 at 06:05

score 1 · Accepted Answer · edited May 23 '17 at 11:55

Change

<% if updateC = "update" then%>
    <input type="hidden" value="update" name="updateButton">
    <input type="submit" value="Update Customer">            
<% else %>
    <input type="hidden" value="insert" name="insert">
    <input type="submit" value="New Customer">
<% end if %>

to

<% if updateC = "update" then%>
    <input type="hidden" value="<%=id%>" name="id">
    <input type="hidden" value="update" name="updateButton">
    <input type="submit" value="Update Customer">            
<% else %>
    <input type="hidden" value="insert" name="insert">
    <input type="submit" value="New Customer">
<% end if %>

Because in your current code you do not pass the id of the customer so the update method does not know who to update.

As others have stated though there is room for a lot of improvement, like

avoid SQL Injection attack by sanitizing your input or using parameterized queries.
Update the record in one go instead of an update for each field.
Re-use your declared variable instead of reading the queryString whenever you need something (you already have most values in variables)

thanks great ideas that i will be looking into to improve my code — kurupt_89, May 27 '11 at 23:48

score 0 · Answer 2 · answered May 26 '11 at 10:35

0

Change

updateC = request.QueryString("action")

to

updateC = request.QueryString("updateButton")

answered May 26 '11 at 10:35

stealthyninja

10,343
11
51
59

unfortunatly that wont work cause i have another .asp page that sends data to this page which i would then edit and update ive updated the code above – kurupt_89 May 26 '11 at 10:43

Updating sql server through classic asp and vbscript

2 Answers2