For each item in Request.Form

[Drew]

I need to iterate through a submitted form, inserting data on each pass. In
the past, I have always used form elements that were named with numbers at
the end, like this,

name1 relationship1
name2 relationship2
name3 relationship3

With the above formatting, I could write a For... Next loop to insert all
the data into my database,

For i = 1 to 3
If Request.Form("name" & i) <> "" Then
sql = INSERT INTO Table (Name, Relationship)
sql = sql & " VALUES"
sql = sql & "('" Request.Form("name" & i) "',"
sql = sql & "('" Request.Form("relationship" & i) "')"

'Execute SQL
End If
Next

Now I am working on a new loop, but do not want to go back and change the
way the submitting page works. I saw on while researching that I can use a
For each item in Request.Form, but tried this and it throws off my SQL
statement. It adds all of the request.form values to the sql statement,
which then fails.

How can I make this loop?

Thanks,
Drew

 

[Bob Barrows [MVP]]

I need to iterate through a submitted form, inserting data on each
pass. In the past, I have always used form elements that were named
with numbers at the end, like this,

name1 relationship1
name2 relationship2
name3 relationship3

With the above formatting, I could write a For... Next loop to insert
all the data into my database,

For i = 1 to 3
If Request.Form("name" & i) <> "" Then
sql = INSERT INTO Table (Name, Relationship)
sql = sql & " VALUES"
sql = sql & "('" Request.Form("name" & i) "',"
sql = sql & "('" Request.Form("relationship" & i) "')"

'Execute SQL
End If
Next

Now I am working on a new loop, but do not want to go back and change
the way the submitting page works. I saw on while researching that I
can use a For each item in Request.Form, but tried this and it throws
off my SQL statement. It adds all of the request.form values to the
sql statement, which then fails.

Response.Write the statement to see why it fails. We cannot debug a sql
statement without seeing what it is.

 

[Drew]

The SQL Statement will look like this, if there are 3 textboxes for name and
relationship,

INSERT INTO Table (Name,Relationship) VALUES ('Jack, John, Diane','Brother,
Brother, Sister')

I need it to look like this,

INSERT INTO Table (Name,Relationship) VALUES ('Jack','Brother')
INSERT INTO Table (Name,Relationship) VALUES ('John','Brother')
INSERT INTO Table (Name,Relationship) VALUES ('Diane','Sister')

Thanks,
Drew

 

[Bob Barrows [MVP]]

The SQL Statement will look like this, if there are 3 textboxes for
name and relationship,

INSERT INTO Table (Name,Relationship) VALUES ('Jack, John,
Diane','Brother, Brother, Sister')

So this is the failing statement resulting from your code?
Let's see the code that generated this.

 

[Drew]

If Request.Form("Submitted") = "Yes" Then
'Start Loop
For Each item In Request.Form
'Run Insert Code
sql="INSERT INTO People (Name,Relationship)"
sql=sql & " VALUES "
sql=sql & "('" & Request.Form("Name") & "',"
sql=sql & "'" & Request.Form("Relationship") & "')"

'execute the insert to SQL Server
Set MM_editCmd = Server.CreateObject("ADODB.Command")
MM_editCmd.ActiveConnection = MM_lcnn_STRING
MM_editCmd.CommandText = sql
Response.Write(sql)
MM_editCmd.Execute
MM_editCmd.ActiveConnection.Close
Next
End If

Thanks,
Drew

 

[Evertjan.]

Drew wrote on 24 jan 2006 in microsoft.public.inetserver.asp.general:

For Each item In Request.Form
'Run Insert Code
sql="INSERT INTO People (Name,Relationship)"
sql=sql & " VALUES "
sql=sql & "('" & Request.Form("Name") & "',"
sql=sql & "'" & Request.Form("Relationship") & "')"

I don't think this will work.

Do you expect multiple
Request.Form("Name") and Request.Form("Relationship") groups
that will listen to "Each item In Request.Form" ???

 

[Drew]

Yep... as I say, I have never used the "For each item in Request.Form", I
thought it would iterate through each form element, instead it just comma
delimits them and that is it. I guess I will have to build an array in the
For... Next loop and insert the array values?

Thanks,
Drew

 

[Bob Barrows [MVP]]

If Request.Form("Submitted") = "Yes" Then
'Start Loop
For Each item In Request.Form
'Run Insert Code
sql="INSERT INTO People (Name,Relationship)"
sql=sql & " VALUES "
sql=sql & "('" & Request.Form("Name") & "',"
sql=sql & "'" & Request.Form("Relationship") & "')"

To see what is going on here, do a
Response.Write Request.Form("Name")
See? the form submission process concatenates the values from all
like-minded fields into a comma-delimited string.

You need to revert to the technique of differentiating the field names with
"row" numbers. You can still use "for each":

dim sql,sName,sRelationship, arParms
sql="INSERT INTO People (Name,Relationship) Values(?,?)"
dim cn

'Always use an explicit connection object - implicit connections
'can impair performance by disabling session pooling

Set cn = Server.CreateObject("ADODB.Connection")
cn.open MM_lcnn_STRING
Set MM_editCmd = Server.CreateObject("ADODB.Command")
Set MM_editCmd.ActiveConnection = cn
MM_editCmd.CommandText = sql
MM_editCmd.commandtype = 1 'adCmdText
For Each item In Request.Form
If left(item,4) = "Name" then
rownumber= mid(item,5)
sName=request.form(item)
sRelationship=request.form("Relationship" & rownumber)
arParms=Array(sName,sRelationship)
MM_editCmd.Execute ,arParms, 128 'adExecuteNoRecords
end if
Next
cn.close: set cn=nothing

 

[Bob Barrows [MVP]]

If you could be sure the field values were always in the proper order (I am
not sure this is a valid assumption), you could do this:

dim arNames, arRelationships, i
arNames = Split(Request.Form("Name"),",")
arRelationships = Split(Request.Form("Relationship"),",")

dim sql,sName,sRelationship, arParms
sql="INSERT INTO People (Name,Relationship) Values(?,?)"
dim cn

'Always use an explicit connection object - implicit connections
'can impair performance by disabling session pooling

Set cn = Server.CreateObject("ADODB.Connection")
cn.open MM_lcnn_STRING
Set MM_editCmd = Server.CreateObject("ADODB.Command")
Set MM_editCmd.ActiveConnection = cn
MM_editCmd.CommandText = sql
MM_editCmd.commandtype = 1 'adCmdText
for i = 0 to ubound(arNames)
arParms= Array(arNames(i), arRelationships(i))
MM_editCmd.Execute ,arParms, 128 'adExecuteNoRecords
Next
cn.close: set cn=nothing

 

[AnthonyWJones]

What heppens if the persons name happens to be O'Brian ?

Or worse yet what happens if the user enters something like:-
',''); Delete People; Other SQL code that does malicious things; --

?? Ouch.

It's called SQL Injection.

Yep... as I say, I have never used the "For each item in Request.Form", I
thought it would iterate through each form element, instead it just comma
delimits them and that is it. I guess I will have to build an array in the
For... Next loop and insert the array values?

Thanks,
Drew

 

[Evertjan.]

Drew wrote on 24 jan 2006 in microsoft.public.inetserver.asp.general:
[please do not toppost on usenet]

Yep... as I say, I have never used the "For each item in
Request.Form", I thought it would iterate through each form element,
instead it just comma delimits them and that is it. I guess I will
have to build an array in the For... Next loop and insert the array
values?

No, no array.

Just have individual numbered names for the form values,
the below works for any number of entries:

<input name='Name1'> <input name='Relationship1'><br>
<input name='Name2'> <input name='Relationship2'><br>
<input name='Name3'> <input name='Relationship3'><br>
.................


===================================
debug = true
' debug = false
n=1
while Request.Form("Name" & n) <> ""
sql = "INSERT INTO People (Name,Relationship) VALUES "&_
"('" & Request.Form("Name" & n) & "','" &_
Request.Form("Relationship" & n) & "')"
if debug then
response.write sql & "<br>"
else
CONN.execute(sql)
end if
n=n+1
wend
if debug then response.end
.....

====================================

not tested.

btw: You will have to do proper validation against hacker injection,
if this is an open internet job!!!!

 

[Larry Bud]

If Request.Form("Submitted") = "Yes" Then
'Start Loop
For Each item In Request.Form
'Run Insert Code
sql="INSERT INTO People (Name,Relationship)"
sql=sql & " VALUES "
sql=sql & "('" & Request.Form("Name") & "',"
sql=sql & "'" & Request.Form("Relationship") & "')"

'execute the insert to SQL Server
Set MM_editCmd = Server.CreateObject("ADODB.Command")
MM_editCmd.ActiveConnection = MM_lcnn_STRING
MM_editCmd.CommandText = sql
Response.Write(sql)
MM_editCmd.Execute
MM_editCmd.ActiveConnection.Close
Next
End If

I would rather use an ado.recordset object, which will handle the
possible SQL injection attacks.

For example, let's say each form name you want to save into the
database begins with name_ and relationship_

so
name_1, relationship_1
name_2, relationship_2

etc.

for each item in request.form
if left(item,5)="name_" then
itemcount=right(item,len(item)-5)
rs.addnew
rs("name")=request.form(item)
rs("relationship")=request.form("relationship_" & itemcount)
rs.update
end if
next

 

[Drew]

Thanks for all the replies. I finally got this fixed by just renaming the
form elements to name1, name2, name3, relationship1, etc... SQL injection is
not one of my worries, since these apps reside on an intranet. If someone
does do a injection attack, we will know who it was, take administrative
action and restore our data from the tapes...

It would be better to prevent them in the first place, but then how do you
take administrative action? We want to get any employee who is trying to
damage our network/database out of this organization.

Thanks,
Drew

 

[Bob Barrows [MVP]]

Thanks for all the replies. I finally got this fixed by just
renaming the form elements to name1, name2, name3, relationship1,
etc... SQL injection is not one of my worries, since these apps
reside on an intranet.

A majority of hacks are done by disgruntled employees

If someone does do a injection attack, we
will know who it was, take administrative action and restore our data
from the tapes...
It would be better to prevent them in the first place, but then how
do you take administrative action?

Validate the data in server-side code before using it, logging the
suspicious data and the user's name.

SAL Injection is not the only reason to avoid dynamic sql. It's a powerful
reason, but not the only one. Performance and code maintainability also
enter into the equation.

 

[Bob Barrows [MVP]]

I would rather use an ado.recordset object, which will handle the
possible SQL injection attacks.

But that adds its own overhead. Cursors are very inefficient when it comes
to maintaining data. It is much better to use DML, and SQL Injection is
easily prevented by the use of parameters as I illustrated in my two posts.

Bob Barrows