asp insert

Discussion in 'ASP General' started by Eugene Anthony, Jun 12, 2006.

  1. The code bellow functions well when I attemp to delete a record. But
    when I attemp to insert a record I am getting the following error
    -2147217900:Syntax error in INSERT INTO statement. How do I solve the
    problem?.

    <%
    Sub GetData (sql)
    on error resume next
    set conn = server.CreateObject("ADODB.Connection")
    conn.connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data
    Source=" & Server.MapPath("/db/upload/stelladb.mdb") & ";"
    conn.open
    conn.execute(sql)
    set conn = nothing
    if Err.number <> 0 then
    Response.Write(Err.number & ":" & Err.Description & "<br>")
    end if
    on Error goto 0
    End Sub

    If request.queryString("Action") = 1 then
    GetData "DELETE * FROM Account WHERE ID=" &
    Request.QueryString("ID")
    End if

    If request.queryString("Action") = 2 then
    username = request.form("username")
    password = request.form("password")
    GetData "INSERT INTO Account(Username,Password) VALUES(" & "'" &
    username & "','" & password & "')"
    End if
    %>

    Eugene Anthony

    *** Sent via Developersdex http://www.developersdex.com ***
     
    Eugene Anthony, Jun 12, 2006
    #1
    1. Advertising

  2. Eugene Anthony

    Jeff Guest

    "Eugene Anthony" <> wrote in message
    news:...
    > The code bellow functions well when I attemp to delete a record. But
    > when I attemp to insert a record I am getting the following error
    > -2147217900:Syntax error in INSERT INTO statement. How do I solve the
    > problem?.
    >
    > <%
    > Sub GetData (sql)
    > on error resume next
    > set conn = server.CreateObject("ADODB.Connection")
    > conn.connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data
    > Source=" & Server.MapPath("/db/upload/stelladb.mdb") & ";"
    > conn.open
    > conn.execute(sql)
    > set conn = nothing
    > if Err.number <> 0 then
    > Response.Write(Err.number & ":" & Err.Description & "<br>")
    > end if
    > on Error goto 0
    > End Sub
    >
    > If request.queryString("Action") = 1 then
    > GetData "DELETE * FROM Account WHERE ID=" &
    > Request.QueryString("ID")
    > End if
    >
    > If request.queryString("Action") = 2 then
    > username = request.form("username")
    > password = request.form("password")
    > GetData "INSERT INTO Account(Username,Password) VALUES(" & "'" &
    > username & "','" & password & "')"
    > End if


    VALUES(" & "'" &
    > username & "','" & password & "')"

    what is the " & " for??
    i would write it VALUES('" & username & "', '" & password & "')

    but i am sure that others may have an even better way to do it.


    > %>
    >
    > Eugene Anthony
    >
    > *** Sent via Developersdex http://www.developersdex.com ***
     
    Jeff, Jun 12, 2006
    #2
    1. Advertising

  3. Eugene Anthony wrote:
    > The code bellow functions well when I attemp to delete a record. But
    > when I attemp to insert a record I am getting the following error
    > -2147217900:Syntax error in INSERT INTO statement. How do I solve the
    > problem?.
    >


    The first step is to tell us the datatypes of the fields involved in the
    query (Text? Number? Date/Time?).

    The second step is to show us the sql statement ... and no, showing us
    the vbscript code that is supposed to generate the sql statement is not
    the same as showing us the resulting sql statement. You need to assign
    the results of your concatenation to a variable and response.write the
    variable:

    sql="INSERT INTO Account(Username,Password) VALUES(" & _
    "'" & username & "','" & password & "')"
    'comment out the following two lines when finished debugging
    '************************
    response.write sql
    response.end
    '************************
    GetData sql

    I suspect your problem is due to the use of a reserved keyword,
    "password" as the name of your field. You really should change that
    name, but, if you can't, you will need to remember to bracket it
    [password] when using it in your queries:

    sql="INSERT INTO Account(Username,[Password]) VALUES(" & _
    "'" & username & "','" & password & "')"


    Further points to consider:
    Your use of dynamic sql is leaving you vulnerable to hackers using sql
    injection:
    http://mvp.unixwiz.net/techtips/sql-injection.html
    http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

    See here for a better, more secure way to execute your queries by using
    parameter markers:
    http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

    Personally, I prefer using stored procedures, or saved parameter queries
    as
    they are known in Access:

    Access:
    http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=

    http://groups.google.com/groups?hl=...=1&selm=


    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows [MVP], Jun 12, 2006
    #3
  4. Will this solve the problem?

    <%
    function getUserInput(input,stringLength)
    dim tempStr

    tempStr = left(trim(input),stringLength)

    ' replace due to DB hack threats
    tempStr = replace(tempStr,"--","")
    tempStr = replace(tempStr,";","")
    tempStr = replace(tempStr,"SCRIPT","s-c-r-i-p-t",1,-1,1)

    getUserInput = tempStr
    end function

    function formatForDb(input)
    dim tempStr
    tempStr=input
    ' replace to avoid DB errors
    tempStr = replace(tempStr,"'","''")
    tempStr = replace(tempStr,"&"," and ")
    formatForDb = tempStr
    end function
    %>

    Eugene Anthony

    *** Sent via Developersdex http://www.developersdex.com ***
     
    Eugene Anthony, Jun 13, 2006
    #4
  5. Eugene Anthony

    Mike Brind Guest

    Eugene Anthony wrote:
    > Will this solve the problem?
    >
    > <%
    > function getUserInput(input,stringLength)
    > dim tempStr
    >
    > tempStr = left(trim(input),stringLength)
    >
    > ' replace due to DB hack threats
    > tempStr = replace(tempStr,"--","")
    > tempStr = replace(tempStr,";","")
    > tempStr = replace(tempStr,"SCRIPT","s-c-r-i-p-t",1,-1,1)
    >
    > getUserInput = tempStr
    > end function
    >
    > function formatForDb(input)
    > dim tempStr
    > tempStr=input
    > ' replace to avoid DB errors
    > tempStr = replace(tempStr,"'","''")
    > tempStr = replace(tempStr,"&"," and ")
    > formatForDb = tempStr
    > end function
    > %>
    >


    Solve which problem? The use of a reserved word as a field name? No.
    The only thing you can do is as Bob suggested: bracket it [Password],
    change it or use a saved parameter query. Access will automatically
    bracket the field name for you if you use the last option.

    I strongly recommend the saved parameter query option. Spend an hour
    or so trying it out, and it will save you tons of time in the future.
    You will wonder why you ever did database inserts, updates etc any
    other way, and you won't need either of the functions above.

    --
    Mike Brind
     
    Mike Brind, Jun 13, 2006
    #5
  6. Eugene Anthony, Jun 13, 2006
    #6
  7. Eugene Anthony wrote:
    > solve the vulnerable to hackers using sql injection.
    >

    Please quote some of the message to which you are replying.

    For the answer to your question, see
    http://www.nextgenss.com/papers/advanced_sql_injection.pdf
    and
    http://www.nextgenss.com/papers/more_advanced_sql_injection.pdf

    particularly the part that talks about defenses.

    Escaping quotes and replacing comment marks as you are doing only fixes
    part of the problem. Experienced hackers can defeat this defense. The
    best defense is to:
    1. Use server-side validation to _reject_ input that is known to be
    bad - don't try to massage it so it becomes "good" input. Raise an
    error, but be careful not to give the hacker too much information. If
    you detect a definite injection attack, just return a message such as
    "Bad input" or something else that is unrevealing. For other validation
    failures that may be honest mistakes, return a more helpful error
    message. But ... never reveal information about your database or website
    structure in these error messages. One of the ways hackers work is to
    cause errors that reveal information. You must trap all errors, logging
    them and returning unrevealing messages.
    2. For injection attempts that you miss in step 1 (and you WILL miss
    some), use parameters instead of concatenation. Direct SQL Injection is
    impossible when data is passed by parameter (although secondary
    injection is still possible if you get careless)

    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows [MVP], Jun 13, 2006
    #7
  8. Pass by parameter, can u show me an example that suites my codes.

    Eugene Anthony

    *** Sent via Developersdex http://www.developersdex.com ***
     
    Eugene Anthony, Jun 13, 2006
    #8
  9. Eugene Anthony wrote:
    > Pass by parameter, can u show me an example that suites my codes.
    >

    <sigh>
    Why aren't you quoting?
    I guess you are talking about this code:

    %>
    Sub GetData (sql)
    on error resume next
    set conn = server.CreateObject("ADODB.Connection")
    conn.connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data
    Source=" & Server.MapPath("/db/upload/stelladb.mdb") & ";"
    conn.open
    conn.execute(sql)
    set conn = nothing
    if Err.number <> 0 then
    Response.Write(Err.number & ":" & Err.Description & "<br>")
    end if
    on Error goto 0
    End Sub

    If request.queryString("Action") = 1 then
    GetData "DELETE * FROM Account WHERE ID=" &
    Request.QueryString("ID")
    End if

    If request.queryString("Action") = 2 then
    username = request.form("username")
    password = request.form("password")
    GetData "INSERT INTO Account(Username,Password) VALUES(" & "'" &
    username & "','" & password & "')"
    End if
    %>

    OK, first off, why are you calling this sub "GetData"? It does not
    return any data. Let's start by renaming it "RunQueryString"

    Sub RunQueryString(pSQL, parms)
    on error resume next
    dim cn,cmd
    set conn = server.CreateObject("ADODB.Connection")
    conn.open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & Server.MapPath("/db/upload/stelladb.mdb")
    set cmd=createobject("adodb.command")
    With cmd
    .CommandText=pSQL
    .commandType= 1 'adCmdText
    set .ActiveConnection=conn
    err.clear
    .Execute ,parms,128
    if err<>0 then
    Response.Write Err.number & ":" & _
    Err.Description & "<br>"
    end if
    on Error goto 0
    End With
    End Sub
    dim sql, arParms, id
    If request.queryString("Action") = 1 then
    id=Request.QueryString("ID")
    'First validate id
    'Then, if valid, do this
    arParms=Array(clng(id))
    sql="DELETE * FROM Account WHERE ID=?"
    RunQueryString sql, arParms
    End if

    If request.queryString("Action") = 2 then
    username = request.form("username")
    password = request.form("password")
    'First validate these inputs, then
    arParms=Array(username,password)
    sql="INSERT INTO Account(Username,[Password])" & _
    "VALUES(?,?)"
    RunQueryString sql, arParms
    End if

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows [MVP], Jun 13, 2006
    #9
  10. The code you have given works well, however after inserting the record
    and deleting the record no changes are shown to the records displayed
    using this code:

    <html>
    <head>
    <title></title>
    </head>
    <body>
    <%
    Dim conn, rs
    Dim currentPage, rowCount, i
    currentPage = Trim(Request("CurrentPage"))
    if currentPage = "" then currentPage = 1 end if
    set conn = server.CreateObject("ADODB.Connection")
    conn.connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
    & Server.MapPath("/db/upload/stelladb.mdb") & ";"
    conn.open
    set rs = server.CreateObject("ADODB.Recordset")
    rs.CursorType = 3
    rs.PageSize = 1
    rs.Open "SELECT Username, Password FROM Account",conn
    rs.AbsolutePage = cInt(currentPage)
    rowCount = 0

    while not rs.EOF and rowCount < rs.PageSize
    response.write rs("Username") & "<BR>"
    rowCount = rowCount + 1
    rs.movenext
    wend
    %>

    <% If CInt(currentPage) > 1 Then %>
    <A HREF="paging.asp?currentPage=<%=currentPage-1%>">Prior</A>
    <% End If %>

    <% If CInt(currentPage) < CInt(rs.PageCount) Then %>
    <A HREF="paging.asp?currentPage=<%=currentPage+1%>">Next</A>
    <% End If %>

    </body>
    </html>

    It seems that I have to refresh the webpage by pressing the refresh
    button on my internet explorer to see the results.

    Eugene Anthony

    *** Sent via Developersdex http://www.developersdex.com ***
     
    Eugene Anthony, Jun 13, 2006
    #10
  11. now it works fine, I simple had to close the connection as shown bellow:

    <%
    Sub RunQueryString (pSQL,parms)

    conn.close
    Set conn = nothing

    End Sub

    Eugene Anthony

    *** Sent via Developersdex http://www.developersdex.com ***
     
    Eugene Anthony, Jun 14, 2006
    #11
  12. Eugene Anthony

    Mike Brind Guest

    Bob Barrows [MVP] wrote:
    > Eugene Anthony wrote:
    > > Pass by parameter, can u show me an example that suites my codes.
    > >

    > <sigh>
    > Why aren't you quoting?



    I think it's a limitation of using developersdex for posting to the
    group.

    Eugene, have a look at
    http://groups.google.com/group/microsoft.public.inetserver.asp.general,
    and see if you can use that for posting?

    --
    Mike Brind
     
    Mike Brind, Jun 14, 2006
    #12
    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. cannontrodder
    Replies:
    1
    Views:
    763
    cannontrodder
    Jul 25, 2006
  2. Replies:
    1
    Views:
    477
  3. Replies:
    1
    Views:
    1,870
    Albert Hopkins
    Dec 6, 2008
  4. eagle

    Insert & Insert/Repeat buttons in a detailsview

    eagle, Jul 12, 2007, in forum: ASP .Net Datagrid Control
    Replies:
    0
    Views:
    1,087
    eagle
    Jul 12, 2007
  5. Feege
    Replies:
    0
    Views:
    502
    Feege
    Dec 20, 2005
Loading...

Share This Page