Error in code. Where to start!!!

[morten444]

Hi. I am totally new to SQL and just got a task dumped on me, that I have not been able to resolve.


When attemting to subscrible to newsletter on my website i get the following error:

*****************************************************
Quote:
Microsoft VBScript compilation error '800a0400'

Expected statement

/subscribe.asp, line 55

& "'" & Request("mail_zimbabwe") & "', "_

******************************************************

I can tell you that the website only collect
-first name
-surename
-email
-password

Here is the code:
Quote:

*******************************************************
<!--#include file="inc/connect.asp"-->
<%

Conn.Open strDSN

Set rsConfig = Conn.Execute("SELECT * FROM dbo.tblConfigs "_
& " WHERE dbo.tblConfigs.ConfigID = 1")

Set rsMail = Conn.Execute("SELECT * FROM dbo.tblMails "_
& " WHERE dbo.tblMails.MailAddress = '" & Request("email") & "'")


Function smartText(dullText)
dullText = trim(dullText)
If Not isNull(dullText) Then
if inStr(dullText, "$uns") Then dullText = Replace(dullText, "$uns", "unsubscribe.asp?email=" & rsMail("MailAddress"))
if inStr(dullText, "$ret") Then dullText = Replace(dullText, "$ret", "retrieve.asp?email=" & rsMail("MailAddress"))
if inStr(dullText, "$pas") Then dullText = Replace(dullText, "$pas", "<strong>" & rsMail("MailPassword") & "</strong>")
if inStr(dullText, "$eml") Then dullText = Replace(dullText, "$eml", "<strong>" & rsMail("MailAddress") & "</strong>")
if inStr(dullText, "$fir") Then dullText = Replace(dullText, "$fir", "<strong>" & rsMail("MailFirstName") & "</strong>")
if inStr(dullText, "$las") Then dullText = Replace(dullText, "$las", "<strong>" & rsOrder("MailLastName") & "</strong>")
smartText = dulltext
end if
End Function

Function almostSmartText(dullText)
dullText = trim(dullText)
If Not isNull(dullText) Then
if inStr(dullText, "$uns") Then dullText = Replace(dullText, "$uns", "unsubscribe.asp?email=" & rsMail("MailAddress"))
if inStr(dullText, "$ret") Then dullText = Replace(dullText, "$ret", "retrieve.asp?email=" & rsMail("MailAddress"))
if inStr(dullText, "$pas") Then dullText = Replace(dullText, "$pas", rsMail("MailPassword"))
if inStr(dullText, "$eml") Then dullText = Replace(dullText, "$eml", rsMail("MailAddress"))
if inStr(dullText, "$fir") Then dullText = Replace(dullText, "$fir", rsMail("MailFirstName"))
if inStr(dullText, "$las") Then dullText = Replace(dullText, "$las", rsOrder("MailLastName"))
almostSmartText = dulltext
end if
End Function



if rsMail.EOF then
LayoutPage = 10

if Request("submit") = "Subscribe" then

LayoutPage = 11

conn.execute "INSERT tblMails VALUES ("_
& "'" & Request("email") & "', "_
& "'" & Request("first_name") & "', "_
& "'" & Request("last_name") & "', "_
& "'1', "_
' & "'0', "_
' & "'0', "_
& "'" & Request("mail_zimbabwe") & "', "_
& "'" & Request("mail_exhibitions") & "', "_
& "'" & Request("password") & "') "




Set rsMail = Conn.Execute("SELECT * FROM dbo.tblMails "_
& " WHERE dbo.tblMails.MailAddress = '" & Request("email") & "'")

Set rsMailText= Conn.Execute("SELECT * FROM dbo.tblLayouts "_
& " WHERE dbo.tblLayouts.LayoutID = 14")


Set mailer = Server.CreateObject("SoftArtisans.SMTPMail")
strVer = Mailer.Version
mailer.RemoteHost = "smtp.mvb.net"

mailer.FromName = "Friends Forever"
mailer.FromAddress = rsConfig("ConfigEmail")

mailer.AddRecipient Request("first_name") & " " & Request("last_name"), Request.Form("email")

mailer.Subject = almostSmartText(rsMailText("LayoutTitle"))
mailer.BodyText = almostSmartText(rsMailText("LayoutHeader")) & vbcrlf & vbcrlf _
& almostSmartText(rsMailText("LayoutSubheader")) & vbcrlf & vbcrlf _
& almostSmartText(rsMailText("LayoutText1")) & vbcrlf & vbcrlf _
& almostSmartText(rsMailText("LayoutText2")) & vbcrlf & vbcrlf _
& almostSmartText(rsMailText("LayoutText3"))

strErr1 = ""
If mailer.SendMail then
strErr1 = "Subscription confirmation mail sent."
Else
strErr1 = "Subscription confirmation mail failure: " & mailer.response
End If

set mailer = nothing

End if

Else
LayoutPage = 16
End if

Set rsLayout = Conn.Execute("SELECT * FROM dbo.tblLayouts "_
& " WHERE dbo.tblLayouts.LayoutID = " & LayoutPage & "")

%>


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "

http://www.w3.org/TR/xhtml1/DTD/xhtmll-transitional.dtd">

<html>
<head>
<title><%=rsLayout("LayoutTitle") %></title>
<!--#include file="inc/meta.asp"-->
<link rel="stylesheet" type="text/css" href="screen.css" />
</head>
<body>
<div id="paper">

<ul id="menu">
<li><a href="/">home</a></li>
<li><a href="news.asp">news</a></li>
<li><a href="browse.asp">browse</a></li>
<!-- <li><a href="search.asp">search</a></li>
--> <li><a href="about.asp">about</a></li>
<li><a href="buy.asp">how to buy?</a></li>
</ul>

<h1><img src="images/head.jpg" alt="Friends Forever" width="720" height="65"></h1>

<div class="box" id="leftcolumn">

<h2><%=smartText(rsLayout("LayoutHeader")) %></h2>
<h3><%=smartText(rsLayout("LayoutSubHeader")) %></h3>
<p class="descr"><%=smartText(rsLayout("LayoutText1")) %></p>
<p class="descr"><%=smartText(rsLayout("LayoutText2")) %></p>



<%
if LayoutPage = 10 Then
%>
<form method="post">
<dl>
<dt>
First Name:</dt>
<dd>
<input type="text" name="first_name" size="40" value="">
</dd>
<dt>Last Name:</dt>
<dd><input type="text" name="last_name" size="40" value=""></dd>
<dt>
E-mail:</dt>
<dd><input type="text" name="email" size="40" value="<%= Request("email") %>"> </dd>
<dt>Password:</dt>
<dd><input type="password" name="password" size="40" value=""></dd>
</dl>
<input type="submit" value="Subscribe" name="submit"><input type="reset" value="Reset" name="reset">
</form>

<%
End If
%>
<p class="descr"><%
Response.write(smartText(rsLayout("LayoutText3")))
%></p>

<p class="descr"><br><a href="./"><i>return to Friends Forever homepage...</i></a></p>

</div>

<div id="rightcolumn">


<!--#include file="inc/navig_sel.asp"-->
<!--#include file="inc/navig_disp.asp"-->


</div>

<!-- footer -->
<div class="box" id="footer">
<!--#include file="inc/footer.asp"-->
</div>
</div>
</body>
</html>
****************************************************


I have attached a picture showing the design of the table "tblMails"
Hopefully that can make sense to someone

Kind Regards
Morten

 

[MarkSweetland]

It's not the SQL per se, it's the script continuation characters that's out of whack:

Change :

conn.execute "INSERT tblMails VALUES ("_
& "'" & Request("email") & "', "_
& "'" & Request("first_name") & "', "_
& "'" & Request("last_name") & "', "_
& "'1', "_
[red]' & "'0', "_
' & "'0', "_[/red]
& "'" & Request("mail_zimbabwe") & "', "_
& "'" & Request("mail_exhibitions") & "', "_
& "'" & Request("password") & "') "

to

& "'1', " _
& "'0', " _
& "'0', " _


Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[morten44]

Hi I have corrected it so it now shows:
----------------------------------------------------
conn.execute "INSERT tblMails VALUES (" _
& "'" & Request("email") & "', " _
& "'" & Request("first_name") & "', " _
& "'" & Request("last_name") & "', " _
& "'1', " _
& "'0', " _
& "'0', " _
& "'" & Request("mail_zimbabwe") & "', " _
& "'" & Request("mail_exhibitions") & "', " _
& "'" & Request("password") & "') "

----------------------------------------------------

I now get the following error:
----------------------------------------------------------
0
Microsoft OLE DB Provider for SQL Server error '80040e14'

An explicit value for the identity column in table 'tblMails' can only be specified when a column list is used and IDENTITY_INSERT is ON.

/subscribe.asp, line 48
-----------------------------------------------------------
Line 48 is: conn.execute "INSERT tblMails VALUES (" _

I have read in some forums that it must be because the IDENTITY is not set to ON

I then executed:
Wrote "SET IDENTITY_INSERT dbo.tblMails ON"
It returned "command successfully"

But i still get the same fault.
I have read somewhere that maybe another table also have IDENTITY ON and I have to remove that.
Not sure how to check that in a easy way.
See the table I have attached to see if that helps seeing the fault.

Can it in anyway be an issue that IUSR (public) can not write to the tables. I think we had a previous webmaster that suffered sql injection on the page and then changed something with the premission. Can that have affected the "subscribe" form

Any idears?

Kind Regards
Morten

 

[jbenson001]

You have trying to insert too many values into the table. You are specifing 9 values. There are 9 cols, but it will think the first is going into the ID column, which, by the error you cannot do.

First, fix the Insert statement and test to get it working.
Second. Don't use inline SQL, write stored procedures with parameters to access your data.

 

[morten44]

Hi Again
Thanks for your advice

I changed it to:
conn.execute "INSERT tblMails VALUES (" _
& "'" & Request("email") & "', " _
& "'" & Request("first_name") & "', " _
& "'" & Request("last_name") & "', " _
& "'1', " _
& "'" & Request("mail_zimbabwe") & "', " _
& "'" & Request("mail_exhibitions") & "', " _
& "'" & Request("password") & "') "

Took away 2 lines and now its working


Now regarding your 2nd comment: Inline SQL and Store Procedures

I am sure this page is vonurable for sql injection as attached before. I guess using Store Procedures will protect the site.
I have tried to read about it and do no uderstand it very well. Guess I am trying to make a shortcut in the learning courve

Can you point me in the right direction. Can you easy spot the vonubility and do you have some advice on where to find an easy instruction on how to write a store procedure that I can use as my bible?

Thanks again

Kind Regards
Morten