INSERT INTO using HTML forms

Ian Griffiths · Oct 26, 2003

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:" & ""
response.write "Error Number=" & err.number & ""
response.write "Error Descr.=" & err.description & ""
response.write "Help Context=" & err.helpcontext & ""
response.write "Help Path=" & err.helppath & ""
response.write "Native Error=" & err.nativeerror & ""
response.write "Source=" & err.source & ""
response.write "SQLState=" & err.sqlstate & ""
else
Response.Write("Updated!")
end if
conn.Close
%>

<HR/>
<CENTER><H5>2003 Ian Griffiths</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.

Dan Brussee · Oct 26, 2003

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.

The Mighty Chaffinch · Oct 27, 2003

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

CJM · Oct 28, 2003

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

Survey details won't go through using php, ajax, Mysql	0	Oct 26, 2023
I Need Help with making a function that draws in a canvas using location data.	1	Dec 17, 2021
-2147217900:Syntax error in INSERT INTO statement.	1	Oct 2, 2006
Using Arrays to write data to SQL Server	2	Feb 1, 2007
Sending CDONTS HTML email issue	5	Mar 23, 2005
a newbie, questions about using asp variables to insert value into numeric column(bigint,int,money)	1	Nov 16, 2006
Insert data into MySQL from HTML Form using .psp script	0	Nov 8, 2008
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another	7	Dec 16, 2003

INSERT INTO using HTML forms

Ian Griffiths

Dan Brussee

The Mighty Chaffinch

CJM

Ask a Question

Similar Threads

Members online

Forum statistics

Latest Threads