INSERT INTO using HTML forms


I

Ian Griffiths

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.
 
Ad

Advertisements

D

Dan Brussee

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.
 
T

The Mighty Chaffinch

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
 
Ad

Advertisements

C

CJM

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
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top