SQL delete query

[mdb]

Hey guys,
So I am running an sql query and having a while loop return all the information for me in an html table. What I would like to do is also make a button on each row of the table that when clicked is able to delete that row from the sql. Does anyone have a good tutorial on how this can be accomplished?

Basically the while loop grabs this information from the sql table:

Code:

$row['name'] $row['ip'] $row['ban']

---

Then I would like to make another query like this:

Code:

$delete = mysql_query("DELETE FROM ".$table." WHERE ".$row['name']." AND ".$row['ip']." AND ".$row['ban']." LIMIT 1");

---

And place a button beside each row in the html table that deletes that row. Hopefully that makes sense haha. Is this possible? How would I go about doing it?

Cheers!

[ZiNgA BuRgA]

Instead of dumping the entire thought process I would have to tackle this issue, I think it may be more beneficial to you if you would perhaps share some thoughts on how you'd approach the problem, and perhaps what parts you're struggling with.

Take note that this requires two seperate routines - one to display the results to the user, and the other to handle the delete request from the user.  You cannot combine this while loop and a delete query since there is no point the user can intercept this.

[mdb]

Okay so I found a tutorial this morning and worked on it a little. Basically, following the tutorial, I placed an html link beside each row in the while loop that looks like this:

Code:

<a href="delete_ban.php?table={$table}&name={$row['name']}&ip={$row['ip']}&ban={$row['ban']}&banadded={$banadded}" onclick="return confirm('Are you sure you want to delete this ban from the database?');"><img src="/images/no_greyscale.png" alt="Delete" title="Delete" onmouseover="this.src='/images/no.png';" onmouseout="this.src='/images/no_greyscale.png';" /></a>

---

Then inside delete_ban.php I grab the information from the url by doing this:

Code:

1 2 3 4 5 6 7

$name = $_GET['name']; $ip = $_GET['ip']; $ban = $_GET['ban']; $banadded = $_GET['banadded'] - 5487365; $banadded = $banadded / 2; $delete = mysql_query("DELETE FROM $table WHERE name='$name' AND AND ip='$ip' AND ban='$ban' AND banadded='$banadded' LIMIT 1");

---

Everything works based on that. However there are a few things I am questioning:

1. Is it safe to pass information like that to a url without sanitizing it a bit?
Would using $name = htmlspecialchars($name) be a good idea to do, or would that at times make that variable mismmatch with what is inside the sql table? Thus resulting in the row not being successfully deleted.

2. How would I stop regular users from basically guessing the link and having a field day banning information from that sql? With that, I assume they could just delete a row on the table by following the link http://www.mywebsite.com/delete_ban.php?...http://www.mywebsite.com/delete_ban.php?name=name_1&ip=11.222.333.44&ban=cheating&banadded (if that row actually existed inside the sql table).
I tried adding a little security by having the banadded var (which inside the sql table is recorded as a unix time stamp) having a bit of calculations added to it, which the user obviously wouldn't know the formula I used. And then taken away on the delete_ban page so that it's correctly able to find it in the database. If that makes sense? But i'm not sure if that's a lame form of security or not, I would assume there's better measures I can take?

[ZiNgA BuRgA]

You want to pass along as little information as possible in parameters.
Why do you need to pass the table name, for example?
Your table should have an ID (and this should be set as the primary key) - you should need to pass all that other crap along, only the ID of the row should be required.

As for protecting against people directly visiting the URL, it depends on how you're actually authenticating users.  Ideally the delete script should check whether the current user has permissions to perform the action.

[mdb]

Hmm, for passing the table name as an ID, do you mean creating a variable for that?

In the script now where the delete button is shown, the user has the option to view a ban list from a few different tables using a switch:

Code:

1 2 3 4 5 6 7 8 9 10 11 12 13

switch($_GET['s']){ case '1' : $sname = 'Ban List 1'; $stable = 'ban_list_1'; break; case '2' : $sname = 'Ban List 2'; $stable = 'ban_list_2'; break; default: $sname = 'Ban List 1'; $stable = 'ban_list_1'; }

---

So basically with that code, I should just add in this?

Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

switch($_GET['s']){ case '1' : $sname = 'Ban List 1'; $stable = 'ban_list_1'; $id = '1'; break; case '2' : $sname = 'Ban List 2'; $stable = 'ban_list_2'; $id = '2'; break; default: $sname = 'Ban List 1'; $stable = 'ban_list_1'; $id = '1'; }

---

Also, how would I get ID's of each row? I was thinking maybe assigning them in the while loop like so:

Code:

1 2 3 4 5 6 7 8 9 10 11

$i = 0; $rowid = 0; while( ( $row = mysql_fetch_array( $result ) ) ) { $rowid = $rowid + $i; $row['name'] = htmlspecialchars( $row['name'] ); $maskip = MaskIP( $row['ip'] ); $row['ban'] = htmlspecialchars( $row['ban'] ); $row['ban_created'] = gmdate("d-M-h:ia", $row['ban_created'] + $zone); $i++; eval("\$ban_results .= \"".$templates->get("test_page_row")."\";"); }

---

Something like that? But then how would I use that data on the delete_ban.php without running that same while loop on it? Since that id isn't recorded into the sql table.

Or am I totally thinking of this in a wrong way? hahaha.

[ZiNgA BuRgA]

You may wish to look into reading a bit about database design.  In general, everything needs to have a primary key, and it's typically an autoincremented number (id).  Examples in MyBB: user ID (uid), thread ID (tid) etc.  Note that these IDs uniquely identify any user or thread object.

Your switch statement is better as it prevents the user from putting whatever they want in that query, although now you have voodoo numbers (eg, why 1 or 2?).

[devid456]

You can use WHERE clause with DELETE query to delete selected rows, otherwise all the records would be deleted.