INSERT INTO using HTML forms

Discussion in 'ASP General' started by Ian Griffiths, Oct 26, 2003.

  1. I'm having issues witht the code I'm writing. I've dealt with SQL before,
    although only for extracting data, not adding it to the database. I've been
    intensively learning ASP/ADO over the past week or so. I have a HTML form
    that posts data to the following ASP file:

    <HTML>
    <HEAD>
    <TITLE>Sight Bites</TITLE>
    </HEAD>

    <BODY>

    <%
    set conn = Server.CreateObject("ADODB.Connection")
    conn.Provider="Microsoft.Jet.OLEDB.4.0"
    conn.Open(Server.Mappath("data/guest.mdb"))

    stmt = "INSERT INTO Guest (Name, Location, Date, Email, Website, Comment)"
    stmt = stmt & "VALUES ('" & Request.Form("Name") & "', '" &
    Request.Form("Location") & "', '" & Date & "', '"
    stmt = stmt & Request.Form("Email") & "', '" & Request.Form("Website") &
    "', '" & Request.Form("Comment") & "')"


    on error resume next
    conn.Execute stmt, recaffected
    if err<>0 then
    response.write "VBScript Errors Occured:" & "<P>"
    response.write "Error Number=" & err.number & "<P>"
    response.write "Error Descr.=" & err.description & "<P>"
    response.write "Help Context=" & err.helpcontext & "<P>"
    response.write "Help Path=" & err.helppath & "<P>"
    response.write "Native Error=" & err.nativeerror & "<P>"
    response.write "Source=" & err.source & "<P>"
    response.write "SQLState=" & err.sqlstate & "<P>"
    else
    Response.Write("Updated!")
    end if
    conn.Close
    %>

    <HR/>
    <CENTER><H5><I>2003 Ian Griffiths</I></H5></CENTER>
    </BODY>
    </HTML>

    I've run this using IIS, but I always seem to get a systax error in my
    INSERT statement, but I can't spot one. Anyone got any pointers?

    Cheers,

    Ian Griffiths.
    Ian Griffiths, Oct 26, 2003
    #1
    1. Advertising

  2. Ian Griffiths

    Dan Brussee Guest

    On Sun, 26 Oct 2003 13:09:41 +0000 (UTC), "Ian Griffiths"
    <> wrote:


    >
    > stmt = "INSERT INTO Guest (Name, Location, Date, Email, Website, Comment)"
    > stmt = stmt & "VALUES ('" & Request.Form("Name") & "', '" &
    >Request.Form("Location") & "', '" & Date & "', '"
    > stmt = stmt & Request.Form("Email") & "', '" & Request.Form("Website") &
    >"', '" & Request.Form("Comment") & "')"
    >
    >
    >
    >I've run this using IIS, but I always seem to get a systax error in my
    >INSERT statement, but I can't spot one. Anyone got any pointers?
    >


    Check a couple things...

    First off, use Response.Write stmt just before issuing the statement
    to SQL. This might show you more.

    Next look at the syntax for delimiters on dates using Access
    databases. It requires "#" marks for delimiters.

    Lastly, take a look at the comments. If they contain single quotes
    anywhere in them, this will make the statement fail. For any data that
    a user will type in, it is a good idea to "clean" that data by at
    least replacing single quotes with two single quotes. This escapes the
    single quote and puts it into the data value and does not use it for a
    delimiter. For example: If comment was

    I'm Thirsty

    then your stmt section would be...

    ....,'http://www.myweb.com','I'm Thirsty')

    The single quote in I'm throws everything off.
    Dan Brussee, Oct 26, 2003
    #2
    1. Advertising

  3. > stmt = "INSERT INTO Guest (Name, Location, Date, Email, Website,
    Comment)"
    > stmt = stmt & "VALUES ('" & Request.Form("Name") & "', '" &


    I imagine 'date' is a reserved word in Jet SQL, and possibly 'name' and
    some of the others too. In Jet SQL you can use a [...] syntax around
    table/column names to use reserved words, but these probably aren't good
    choices for column names anyway.

    MightyC



    ---
    Outgoing mail is certified Virus Free.
    Checked by AVG anti-virus system (http://www.grisoft.com).
    Version: 6.0.520 / Virus Database: 318 - Release Date: 18/09/03
    The Mighty Chaffinch, Oct 27, 2003
    #3
  4. Ian Griffiths

    CJM Guest

    > First off, use Response.Write stmt just before issuing the statement
    > to SQL. This might show you more.


    I would echo this. Output your SQL string, which you can then test in
    Access's Query Builder to test the validity of the SQL. Then you can work
    backwards to your ASP code.

    > Lastly, take a look at the comments. If they contain single quotes
    > anywhere in them, this will make the statement fail. For any data that
    > a user will type in, it is a good idea to "clean" that data by at
    > least replacing single quotes with two single quotes. This escapes the
    > single quote and puts it into the data value and does not use it for a
    > delimiter. For example: If comment was
    >
    > I'm Thirsty
    >
    > then your stmt section would be...
    >
    > ...,'http://www.myweb.com','I'm Thirsty')
    >
    > The single quote in I'm throws everything off.


    Rather than inserting to single quotes, you might conder just filtering them
    out.

    This improves the security of yout site, by reducing the risk of attack via
    SQL Injection:

    http://www.nextgenss.com/papers/advanced_sql_injection.pdf

    This article explains it much better than I ever could...

    hth

    Chris
    CJM, Oct 28, 2003
    #4
    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. Replies:
    3
    Views:
    12,156
    Beauregard T. Shagnasty
    Jan 10, 2006
  2. brianrpsgt1
    Replies:
    0
    Views:
    970
    brianrpsgt1
    Nov 8, 2008
  3. mcnewsxp

    insert html into html

    mcnewsxp, Mar 30, 2010, in forum: HTML
    Replies:
    24
    Views:
    4,410
    cipherd
    Jan 7, 2012
  4. Eric
    Replies:
    2
    Views:
    483
  5. Sergio del Amo
    Replies:
    4
    Views:
    233
Loading...

Share This Page