Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Form To Database 2

Status
Not open for further replies.
evr72

evr72

MIS
Dec 8, 2009
265
US
Hello,

I have a form, it was working well, but not sure what happened when I click on submit I get
Code: 
Microsoft JET Database Engine (0x80040E14)
Syntax error in INSERT INTO statement.
/questions/add_to_database.asp, line 32

My questions are dynamic and look like this
Code: 
  <tr>
       <td><input name="category" type="text" value="<% = rs1Guestbook("category") %>" /></td>
       <td><input name="questions" type="text" value="<% = rs1Guestbook("questions") %>" /></td>
       <td><input name="questionid" type="text" value="<% = rs1Guestbook("questionid") %>" /></td>
       <td><input name="yesno" type="text" value="<% = rs1Guestbook("yesno") %>" /></td>
       <td><input name="answer" type="text" value="<% = rs1Guestbook("answer") %>" /></td>
       <td><input type="text" name="userid" value="<% = Response.Write(rs("userid"))%>" readonly="true" /></td>
     </tr>

my add_to_database.asp page looks like this

Code: 
<%@ Language="VBScript" %>
<% Option Explicit %> 
<html>
<head>
<title>Form to database</title>
</head>
<body> 
<%
'declare your variables
Dim answerquestionid, answeruserid, yesno, answer
Dim sConnString, connection, sSQL
'Receiving values from Form, assign the values entered to variables
answerquestionid = Request.Form("questionid")
answeruserid = Request.Form("userid")
yesno =Request.Form("yesno")
answer =Request.Form("answer")


'declare SQL statement that will query the database 
sSQL = "INSERT into answers1 (answerquestionid,answeruserid,yesno,answer) values ('" & _
answerquestionid & "', '" & answeruserid & "', '" & yesno & "', '" & answer & "')" 
'define the connection string, specify database
'driver and the location of database
sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _ 
"Data Source=" & Server.MapPath("qa.mdb") 
'create an ADO connection object 
Set connection = Server.CreateObject("ADODB.Connection")

'Open the connection to the database
connection.Open(sConnString)
'execute the SQL 
connection.execute(sSQL)
'Done. Close the connection object
connection.Close
Set connection = Nothing%>
<%
Response.Redirect "[URL unfurl="true"]http://mywebsite.com"[/URL]
%>



</body>
</html>

Line 32 is
Code: 
connection.execute(sSQL)
 
Sort by date Sort by votes
Do you have any apostrophes/single-quotes in your data? If so, you just stumbled upon the primary method of SQL Injection.

At a minimum, you should change this part of the code:

Code: 
sSQL = "INSERT into answers1 (answerquestionid,answeruserid,yesno,answer) values ('" & _
[!]Replace([/!]answerquestionid[!], "'", "''")[/!] & "', '" & [!]Replace([/!]answeruserid[!], "'", "''")[/!] & "', '" & [!]Replace([/!]yesno[!], "'", "''")[/!] & "', '" & [!]Replace([/!]answer[!], "'", "''")[/!] & "')"

Single-quotes are used to delimit your data within the SQL query. So, if your data contains a single quote, the query will parse as though that single-quote is the end of the string even though you may be using it as an apostrophe. To handle this situation, you simple double the single quotes. SQL will interpret 2 single quotes as 1 single quote embedded within the data instead of interpreting it as a string delimiter in your query.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
I think you are not sanitizing or cleaning your inputs.

Extra commas, quotes, and punctuation in unscreened inputs will mess your insert up.

At the very least you should emply a replace.

answer = replace(Request.Form("answer"),","," ")

When doing this, I typically replace a comma with a double tilde ~~ and then replace the ~~ with a comma when I write it out.
answer = replace(Request.Form("answer"),",","~~")

 
Upvote 0 Downvote
BigRed,

As far as I know, commas should not be a problem. The ONLY problem character is the single-quote. I haven't used Microsoft Access in a long time, so it's possible I could be mistaken, although I suspect I am not.

You do make a good point about sanitizing your inputs. I ALWAYS do this, even if I don't think I really need to. I always validate against the data type that I am expecting. For example, if I am expecting a number (integer/long), I always validate that it is an integer. To do this, I use the following function:

Code: 
Function IsInteger(Data)

  If Trim(Data) = "" Then
    IsInteger = False
  Else
    IsInteger = IsNumeric(Data & ".0e0")
  End If

End Function

This function returns a boolean to indicate if the parameter passed in is an integer. It will fail for text and numbers that are NOT integer. It will, however pass for negative integers. You could make it fail for negative integers easily enough by modifying this slightly, like this:

Code: 
Function Is[!]Positive[/!]Integer(Data)

  If Trim(Data) = "" Then
    IsInteger = False
  Else
    IsInteger = IsNumeric([!]"-" & [/!]Data & ".0e0")
  End If

End Function

I'm also fond of writing functions that return request variables (either post or get method), like this...

Code: 
Function GetEquipmentId
	
  cEquipmentId = Request("EquipmentId")
  If cEquipmentId = "" Then
    GetEquipmentId = 0
  ElseIf IsInteger(cEquipmentId) Then
    GetEquipmentId = cLng(cEquipmentId)
  Else
    GetEquipmentId = 0
  End If

End Function

Since I have several pages that expect EquipmentId passed in to it, I can reuse this function many times. I can confidently use this function to make sure that an integer is always returned from this function.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
gmmastros and BigRed1212,

I tried your suggestion but no luck still get the same error. Could it be becasue all the questions are dynamic, and are form a query in ms access and I am trying to insert the answerquestionid, answeruserid, yesno and question into the answers1 table?
 
Upvote 0 Downvote
Commas lept to my mind because I have a form where the users enters a large amount of text. In my learning zeal and efforts to be safe I initally zapped everything I could think of.

Function DeleteChars(str)
Set regex = New RegExp
regex.pattern="([\\{}':%()])"
regex.Global = True
DeleteChars = regex.Replace(str,"")
Set regex = Nothing
End Function

Zapping commas proved to offend the literary and compositional senses of the users (especially in long passages) and I had to go to my double tilde replace method.

I agree wholeheartedly with function stuff. I have several cleaning functions that I stuck in an include file. I just put the include file code in whatever I write as a matter of course even if I don't ever end up needing them).

I hadn't really thought of writing a function like getFormData that would both retrieve and clean in one motion but that is sure something to look at.
 
Upvote 0 Downvote
You are prepopulating your form inputs. Where is this data coming from? Have you closed that connection?
 
Upvote 0 Downvote
try modifying your add_to_database.asp page to this:

Code: 
<%@ Language="VBScript" %>
<% Option Explicit %>
<html>
<head>
<title>Form to database</title>
</head>
<body>
<%
'declare your variables
Dim answerquestionid, answeruserid, yesno, answer
Dim sConnString, connection, sSQL
'Receiving values from Form, assign the values entered to variables
answerquestionid = Request.Form("questionid")
answeruserid = Request.Form("userid")
yesno =Request.Form("yesno")
answer =Request.Form("answer")


'declare SQL statement that will query the database
sSQL = "INSERT into answers1 (answerquestionid,answeruserid,yesno,answer) values ('" & _
answerquestionid & "', '" & answeruserid & "', '" & yesno & "', '" & answer & "')"

[!]
Response.Write sSQL
Response.End[/!]

'define the connection string, specify database
'driver and the location of database
sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("qa.mdb")
'create an ADO connection object
Set connection = Server.CreateObject("ADODB.Connection")

'Open the connection to the database
connection.Open(sConnString)
'execute the SQL
connection.execute(sSQL)
'Done. Close the connection object
connection.Close
Set connection = Nothing%>
<%
Response.Redirect "[URL unfurl="true"]http://mywebsite.com"[/URL]
%>



</body>
</html>

Now, when you run the page, you should see the sSQL variable printed to the screen and NOT executed against the database. You should be able to copy/paste the output to an Access query window and run it. The error message you get from Access will probably be more descriptive than the error message you are currently getting. If you cannot determine what the problem is, copy/paste the code here.

Of course, once you determine what the problem is and resolve it, you will need to remove the 2 lines of code that I just suggested you add. Those 2 lines are for debugging purposes only.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
I think I am going to go a bit crazy.

I added the code, looks like it's some commas, I took both answersquestionid and answersuserid out, the error was the query must be an updatable query, so I changed permission, it processed, when I went to look at my answers table, no data. So I tried again. The erro that I get now is "Could not use ''; file already in use."

DB is closed, do not have any open documents, closed and re-openned the web browser, same thing. Took read and write permissions out, it works but when is time to process. Needs to be an updatable query
 
Upvote 0 Downvote
yes, I just got this to work, and I see what the problem is.

Code: 
    questionid	                 userid	                          
		
102, 103, 104, 105, 101	     45, 45, 45, 45, 45

as you can see all the questionid and userid's are going into one line.

not sure how to get them

Code: 
     questionid             userid
101                         45
102                         45
103                         45
104                         45
105                         45
 
Upvote 0 Downvote
Ah....

When you have multiple input boxes with the same name, and you post this to a new page, the values will show up the way you are seeing them.

Example.

Code: 
<input type="hidden" name="Blah" value="apple" />
<input type="hidden" name="Blah" value="Banana" />
<input type="hidden" name="Blah" value="Grape" />
<input type="hidden" name="Blah" value="orange" />

Later, when you do:

Response.Write Request("Blah")
you will get "apple,Banana,Grape,Orange".

You could do something like this...

Code: 
answerquestionid = [!]Split([/!]Request.Form("questionid")[!],",")[/!]
answeruserid = [!]Split([/!]Request.Form("userid")[!],",")[/!]
yesno =[!]Split([/!]Request.Form("yesno")[!],",")[/!]
answer =[!]Split([/!]Request.Form("answer")[!],",")[/!]

For i = lBound(answerquestionid) to ubound(answerquestionid)

  'declare SQL statement that will query the database
  sSQL = "INSERT into answers1 (answerquestionid,answeruserid,yesno,answer) values ('" & _
answerquestionid[!](i)[/!] & "', '" & answeruserid[!](i)[/!] & "', '" & yesno[!](i)[/!] & "', '" & answer[!](i)[/!] & "')"
  connection.execute(sSQL)
Next

The SPLIT function will return an array based on the string. The second parameter identifies the delimiter for the array elements (a comma in this case). You then loop through the array and build up the sql string for each element, and execute it. You'll need to modify your original code so that the connection object is created BEFORE the block of code I just showed up. Basically...

1. Create connection object
2. loop through the array, inserting in to DB for each iteration through the loop
3. close the db connection.

Make sense?


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Mr. gmmastros, that makes perfect sense. I will give it a try Thank you!!!
 
Upvote 0 Downvote
Mr. gmmastros,

Do I need to define "i"

Here is what I did

Code: 
<%
'declare your variables
Dim answerquestionid, answeruserid, yesno, answer
Dim sConnString, connection, sSQL
'Receiving values from Form, assign the values entered to variables
answerquestionid = Split(Request.Form("questionid"),",")
answeruserid = Split(Request.Form("userid"),",")


For i = lBound(answerquestionid) to ubound(answerquestionid)

  'declare SQL statement that will query the database
  sSQL = "INSERT into answers1 (answerquestionid,answeruserid) values ('" & _
answerquestionid(i) & "', '" & answeruserid(i) & "')"
  connection.execute(sSQL)
Next


'define the connection string, specify database
'driver and the location of database
sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("qa.mdb")
'create an ADO connection object
Set connection = Server.CreateObject("ADODB.Connection")

'Open the connection to the database
connection.Open(sConnString)
'execute the SQL
connection.execute(sSQL)
'Done. Close the connection object
connection.Close
Set connection = Nothing%>
<%
Response.Redirect "[URL unfurl="true"]http://mywebsite.com"[/URL]
%>

but I get the following error

Error Type:
Microsoft VBScript runtime (0x800A01F4)
Variable is undefined: 'i'
/questions/add_to_database1.asp, line 17
 
Upvote 0 Downvote
Yes. You need to define i, but you also need to open your database connection before this code.

Code: 
<%
'declare your variables
Dim answerquestionid, answeruserid, yesno, answer[!], i[/!]
Dim sConnString, connection, sSQL
'Receiving values from Form, assign the values entered to variables
answerquestionid = Split(Request.Form("questionid"),",")
answeruserid = Split(Request.Form("userid"),",")

[blue]'define the connection string, specify database
'driver and the location of database
sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("qa.mdb")
'create an ADO connection object
Set connection = Server.CreateObject("ADODB.Connection")

'Open the connection to the database
connection.Open(sConnString)
[/blue]

For i = lBound(answerquestionid) to ubound(answerquestionid)

  'declare SQL statement that will query the database
  sSQL = "INSERT into answers1 (answerquestionid,answeruserid) values ('" & _
answerquestionid(i) & "', '" & answeruserid(i) & "')"
  connection.execute(sSQL)
Next


'execute the SQL
' connection.execute(sSQL) [!]don't need this line anymore[/!]
'Done. Close the connection object
connection.Close
Set connection = Nothing%>
<%
Response.Redirect "[URL unfurl="true"]http://mywebsite.com"[/URL]
%>

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Mr. gmmastros...

I believe a huge THANK YOU IS IN ORDER!!!!!!!!!!!!!!!

I read your post late last night and have been reading a bunch about the "Split" function did not know it existed.
I really appreciate all the help and ponters from both
Mr. BigRed1212 and you.


Again Thank you!!!
 
Upvote 0 Downvote
You're welcome.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

penguinspeaks
  • Locked
  • Question
Parse a database field that is comma delimited and input the results into another table
Replies
0
Views
178
penguinspeaks
penguinspeaks
leifoet
  • Locked
  • Question
How to send HTML emails with ASP classic?
Replies
0
Views
252
leifoet
leifoet
penguinspeaks
  • Locked
  • Question
Return to current location on page after asp processes.
Replies
2
Views
203
penguinspeaks
penguinspeaks
bslintx2021
  • Locked
  • Question
How to configure IIS 10 server to allow Classic ASP to connect to MSSQL server database on a domain
Replies
1
Views
504
penguinspeaks
penguinspeaks
leifoet
  • Locked
  • Question
How to check if a datatable is empty ?
Replies
0
Views
193
leifoet
leifoet

Part and Inventory Search

Sponsor

Back
Top