IDENTITY_INSERT is ON issue [Archive]

View Full Version : IDENTITY_INSERT is ON issue

morten44

01-08-2009, 11:40 PM

Hi.
I have been asked to help a friend with a asp problem he has with subscribing to his newsletter.
When filling in the online formular and press "subscribe" it returns the following error:

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

I then open "subscribe.asp to have a look at line 48 and it reads:

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

As I read this it can not place the info into the sql table for some reason. Is that right.

The story behind this is that the website suffered a sql injection. A previous programmer did not know how to close the holes, so I think he user "SQL Server Enteprise" manager to limite the rights the webuser had to isert info into the tables.
Can this be the problem?
Is the messages I get an indication that the webuser who is trying to subscribe does not have access to store info in SQL table.

I have tried to give rights to user on the table "tblMails" using SQL Server Enteprise but probably not done that right

I am very new to this, so any hint/help would be appresiated

Kind Regards
Morten

Spudhead

01-09-2009, 10:04 AM

A good idea when you get an error message you don't understand is to stick a chunk of it into Google. (http://www.google.co.uk/search?q=can+only+be+specified+when+a+column+list+is+used+and+IDENTITY_INSERT+is+ON). But yeah, the error is because you're trying to insert something into a field that the database is expecting to manage itself.

The SQL injection issue has nothing to do with permissions; again, Google is your friend (http://www.google.co.uk/search?q=ASP+SQL+injection) here. You need to make sure that your user input doesn't contain any unsafe characters (http://xkcd.com/327/) (like single quotes) before you try to put it into a database.

morten44

01-09-2009, 10:25 AM

Hi
Thanks for your answer.
Yes I tried google and i did not understand if i had to write a statement like
SET IDENTITY_INSERT dbo.tblmail ON ?

I did not do it yet as there are 200 sql websites on the server and i am afraid of doing something that can affect the others..

The reason why I mentioned sql injection is that the prevois administrator was not ablt fix it, so as a temp fix i think he created a user and added premissions so he cound not write to any fileds. since then "subscribe" has not worked and thats why I thought that I might have a premission fault and not a code fault.

Does that make sence?

Regards
Morten

morten44

01-09-2009, 11:45 AM

I now tried the following;
1.Opened SQL Server Enteprise Manager
2.Opened Query
3. Wrote "SET IDENTITY_INSERT dbo.tblMails ON"
4. Executed
It says Command completed successfully

I then try my subscribe form again and it still says:
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

Same as before

Any idears?

Regards Morten

Spudhead

01-09-2009, 05:16 PM

Find out which field in your database is set as the identity field and take that field out of your insert statement.

morten44

01-10-2009, 01:00 AM

Hi
Thanks for your reply. The message seem to have changed now.

When attemting to subscrible i get the following error:

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:

<%

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>

<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="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">




</div>


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

</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

SouthwaterDave

01-10-2009, 12:06 PM

I have spotted 3 things:
The underscore character used to continue a statement on the next line must be preceded with a space.
A T-SQL INSERT statement must name the columns if you are not inserting all the columns in a table. It would look something like this: INSERT (columns) VALUES (values) where columns is a comma seperated list of column names.
I hope you are validating your data input. Imagine what would happen if a user entered an email address of '; DELETE FROM dbo.tblMails!

morten44

01-11-2009, 06:48 PM

Hi. Thanks for your reply.

I have now corrected the following:
--------------------------------------------
conn.execute "INSERT tblMails VALUES (" _
& "'" & Request("email") & "', " _
& "'" & Request("first_name") & "', " _
& "'" & Request("last_name") & "', " _
' & "'1', " _
' & "'0', " _
' & "'0', " _
' & "'" & Request("mail_zimbabwe") & "', " _
' & "'" & Request("mail_exhibitions") & "', " _
& "'" & Request("password") & "') "
--------------------------------------------
As you see i have commented out 5 fileds now that we do not request to be filled in. As you write in point 2 I have to name the columns as I am not inserting all of them.
Can you give me a concrete example on what you mean?
For examble EMAIL has to go to a column called MAILADDRESS. How to I write that line? If i get help to this I guess I can manage the rest :)

Kind Regards
Morten

SouthwaterDave

01-12-2009, 08:29 AM

INSERT INTO tblMails (MailAddress, MailFirstName, MailLastName)
VALUES ('fred@bloggs.com', 'Fred', 'Bloggs')

I have just spotted something else too. If you continue statements over lines using the underscore character, I think (sorry, I haven't actually used ASP for a few years: I've moved onto ASP.NET but that forum is a bit quiet) that the lines are interpreted as only one line and everything after the first apostrophe is treated as a comment, so you would lose the end of the statement.

morten44

01-12-2009, 10:38 AM

Hi again.
Very interesting.
With my minimal knowledge that sounds logical :)
Thanks will remove them when the server comes up again.

One last question if you dont mind.
Thanks for the code:
------------------------------------------------------------------------
e INSERT INTO tblMails (MailAddress, MailFirstName, MailLastName)
VALUES ('fred@bloggs.com', 'Fred', 'Bloggs')
------------------------------------------------------------------------

The VALUES
As not know in the script as users are to enter it, i guess that I have to use a kind of Place holder. Would that just be like:
------------------------------------------------------------------------
VALUES (' ', ' ', ' ')
------------------------------------------------------------------------

I will the get cracking with this when my server is back online a little later today

Thanks again for your help

regards
Morten

SouthwaterDave

01-12-2009, 11:15 AM

The placeholders you have coded will put a single space in each column. This is OK but there are other options:
Put a zero-length string in each column: VALUES ('', '', '')
Don't put anything in these columns at all (and leave them with NULL values).
For character values I would recommend option 1 and make sure you define these columns as NOT NULL. This saves space in the database.

Don't apply this to dates though. A value of NULL clearly indicates no value supplied but an arbitrary value of 01/01/1900 (for instance) is not so clear.

morten44

01-15-2009, 11:39 AM

Hi Again
Thanks for your help in this.

It has now been resolved with your help

Thanks again for your time

Regards
Morten