Asp Code help

[catfish696]

I'm working on a project and need help.

I am trying to add information to a database which is simple enough. But I am also checking the DB to make sure that the info isn't already in the DB. the code seem to work for the first time, but after i have added 1 record, it thinks everything else is a duplicate.
here is some of the code

Conn.CommandText = "Select Distinct Item1 from table1 Where Id = '" & Id "'"
set Duplicates = Conn.Execute

IF not Duplicates.EOF then
Sub Debug(Msg)
Msg = Replace(Msg,"'","\'")
Response.Write "<script language='JavaScript'>"
Response.Write "alert('"&Msg&"');"
Response.Write "history.back();"
Response.Write "</script>"
Response.End
End Sub
If Request("Duplicates") = "" Then Msg = "Duplicates Not Allowed" : Debug Msg 
END IF

I am truely an ASP newb. I am learning as i go, so please don't brow beat me too bad for my code.

[rdove]

try:

IF not Duplicates.EOF AND not Duplicates.BOF then

[catfish696]

Actually it was the select statement, it was too general, I fixed it. But now I’m having a problem with another page.

Mind trying to tackle it?

The referring page lists items in a table with check boxes next to them, you select the line items to delete and hit the button, and this is the delete page.

The problem I am having is that it will delete 1 record just fine, but if there are more than 1 records selected for deletion it goes straight to the deleted confirmation page, but doesn’t actually delete anything. Any suggestions?

here is the code

<%
Dim RS, MyConn, SQL

Set MyConn=Server.CreateObject("ADODB.Connection")
MyConn.Open "dsn=notreal"
SQL = "DELETE FROM table WHERE ITEM1 = '" & request.form("toDelete") & "'"
Set RS = MyConn.Execute(SQL)

MyConn.Close
Set MyConn = Nothing

%>

I have tried changing the "DELETE FROM..." to "DELETE * FROM..." and all I get is an SQL error

[rdove]

Here is a good example of how I would go about deleting items checked. This example assumes that the value is a NUMBER, but you could change it to accept strings too.

First I generate a checkbox name based on "i" which corresponds to a autonumber record in the database. I could just use the value of the record itself, but the code I copied and pasted from will never have a record out of the autonumber order.

Next I send a couple values in the querystring and post the data back to the page. Then I use a loop to check each checkbox that I created on the page. Notice I send a value called num back to the page. This tells me exactly how many checkboxes I generated.

I doubt this code does everything you need it to, but it will set you in the correct direction.

<%

Dim RS, MyConn, SQL

Set MyConn=Server.CreateObject("ADODB.Connection")
MyConn.Open "dsn=notreal"

if request("action") = "approve" then

 for i = 1 to request("num")

    if request.form("chk" & i) = "" & i then

      SQL = "DELETE FROM table WHERE ITEM1 = " & i
      MyConn.Execute(SQL)

    end if

  next

  response.redirect("page2.asp")

end if

%>


<form name="form1" method="post" action="page.asp?action=approve&num=<% response.write(rs.RecordCount) %>">
<% dim i
   i = 1
   While NOT rs.EOF 
%>
<input type="checkbox" name="chk<% response.write(i) %>" value="<% response.write (i) %>">

<% rs.MoveNext
   i = i + 1
   wend
   rs.close
   MyConn.close
   Set rs = Nothing
   Set MyConn = Nothing
%>

Hope this helps

[berklee]

Ok, I'm going to admit, I'm not even double-checking this for accuracy.... so your mileage may vary...

if you have a bunch of check boxes all given the same name, with different values...

<input type="checkbox" name="mycheckbox" value="1">
<input type="checkbox" name="mycheckbox" value="2">
<input type="checkbox" name="mycheckbox" value="3">

...and a bunch of them get checked, the form item for mycheckbox will come back as a comma-delimited string. From there, you can use VBScript's split() function to chop it up and perform a for-each on it.

Hope that helps.

[berklee]

What DBMS are you using?

If you're using something with stored procedure support, you may want to set up your transactions as procedures.

Having code in your page that says:

SQL = "DELETE FROM table WHERE ITEM1 = '" & request.form("toDelete") & "'"

could be potentially dangerous. If someone managed to figure out your table name, they could populate request.form("toDelete") with the following:

1;DROP TABLE table;

Which would delete item #1, and then dump the table. So very not good.

If you run your statement as a proc, rather than a text string, you can greatly reduce your risk of having your DB get hosed.

(sidenote/threadjack - this is why I haven't moved to MySQL yet - once the stored procedure support is there, I'll hit it. In the meantime, it's SAP DB for Open-Source database stuff. w00t.)

[berklee]

Oh! I almost forgot...

You can put some logic in a stored procedure to test for the existence of the record, and return something back to the ASP page to let you know what happened.

something like....

CREATE PROCEDURE dbo.up_MyTable_SmartDelete(@tableid integer)

AS

BEGIN

declare @result integer

IF EXISTS(SELECT tableid from MyTable where tableid = @tableid)
BEGIN
select @result = (SELECT tableid from MyTable where tableid = @tableid)
END

IF NOT EXISTS(SELECT tableid from MyTable where tableid = @tableid)
BEGIN
select @result = 0
END

return @result

END

When you try and delete something from this table using the above proc, you'll always get a value back as a one field, one row recordset. If the value is something other than 0, you know it's already in the table. So from there, you can just use a simple IF statement to give the user hell for trying to put the same stuff in there twice.

[rdove]

Having code in your page that says:

SQL = "DELETE FROM table WHERE ITEM1 = '" & request.form("toDelete") & "'"

could be potentially dangerous. If someone managed to figure out your table name, they could populate request.form("toDelete") with the following:

1;DROP TABLE table;

Which would delete item #1, and then dump the table. So very not good.

Not with a checkbox.....

[abc123]

Originally posted by rdove
Not with a checkbox.....

of course with a checkbox:

<input type="checkbox" name="doDelete" value="1 'go drop table table1 " />

assuming its an mssql db..

you should always *ALWAYS* replace any instance of a ' in your forms values...

[berklee]

Not with a checkbox.....

You're right, but a checkbox doesn't have to be how the information gets sent.

There's nothing indicating that the form which gets processed is coming from this server.

<html>
<body>
<form action="http://www.thisguysserver.com/hisprocessingpage.asp" method="post">

<his form field values, then... />
<input type="text" name="todelete" value="1;DROP TABLE table;
">
<input type="submit" value="go">

</form>
</body>
</html>

If I were to build that HTML on my desktop, open it in IE and then clicked the submit button, the form data gets submitted to his page. That means he's not just dealing with checkboxes anymore.

Some script kiddies were using a similar technique a while back with IBill's system to change payment values before submitting orders for various things, allowing them to get things at more than a modest discount.

You could do a check for the http referrer, but who honestly does that for every single page they work on? And really, that doesn't matter either - a five minute Python script can impersonate any browser from any IP. Wouldn't take much to screw with this guy's data.

Stored procs offer atomic application and user-level security, increase performance, and help to ensure that data only moves around the way you want it to.

PS sorry - I'm paranoid. But I hear it's a good thing at times.

[rdove]

I don't usually have these problems, but I see where you are coming from. Basically I have forgotten about them because I execute a procedure in each page that checks for all the characters that aren't supposed to be there such as ;'!#$ and so on.

Thanks for reminding me off this!

[berklee]

That's certainly a good thing....

But what about when you run statements on a mySQL database that require the contents of text form fields?

For the record, I'm not trying to be a pain. If you can help me understand a relatively secure way to do this, it might change my opinions on mySQL.

[abc123]

i posted it up there

replace all instances of <'> in your strings with <''>

i.e:

$one = "hello there's a cat in my hat";
#becomes...
$two = "hello there''s a cat in my hat";

then no malicious sql can run.

[sde]

berklee .. use the [code ] and [/code ] tags to make your code easier to read =)

or just hit the # button above the post box.

asp forum is funny, .. active but 1 thread =)

i guess it is so easy that no one needs any help. i wanna continue to learn it, but i'm so fluent in php .. it is a matter of convenience i guess. .. or laziness!

[berklee]

what about a simple select?

Select * from myTable where keyfield = 1

If that 1 is pulled from a page (say a drop-down list), you can build a page that makes a value:

<input type="hidden" name="keyfield" value="1;drop database test'">