Newbee Database Query Question

[WillBoss]

Hi

 

I am new to php and programming in general and was wondering could someone help me with a question I have?  Below is a bit of code I adapted from looking through the internet.  I am using it to reterieve user information from a database.  What I want to know is how can I also add a 'Delete' function for each user that the list displays?  I know its probably easy but its wrecking my head!! ???

 

               

<?php
include "../config.php";

mysql_connect($server, $db_user, $db_pass) or die (mysql_error());
$result = mysql_db_query($database, "select * from $table order by id desc") or die (mysql_error());

if (mysql_num_rows($result)) {
   echo "Registered Members:<ul>";
   while ($qry = mysql_fetch_array($result)) {
      echo "<li>$qry[username] | <a href='$qry[website]' target='_blank'>$qry[website]</a></li>";
   }
}
?>

 

Thanks for any help in advance

 

Regards

Will

[PC Nerd]

what do you meaen by belete function..... as in to delete the use or their website or wat, and where?

 

[WillBoss]

Hi

 

I want to be to delete a user is I wish from the list.  Eg:

 

Michael Mouse | http://disney.com | Delete

Minnie Mouse | http://disney.com | Delete

 

Will

[Unholy Prayer]

Just use a query to display the users in a list and then add a link to the word "Delete" so it would look sort of like this:

echo "$username | $website | <a href='delete.php?username=$username'>Delete</a>";

 

This would be delete.php:

<?php

$username = $_GET['username'];

$delete = mysql_query("DELETE  FROM table WHERE username = $username") or die("Error: ".mysql_error());

echo "The user has been deleted";

?>

[WillBoss]

Thanks very much Unholy , I'll try that

 

Regards

Will

[WillBoss]

Bingo, that did the trick Unholy.  Had to do a little tweaking but I got it to work.  Thanks a mill!

[Michael Lasky]

Some things to be aware of.  Google  "SQL Injection" and be aware of the risks.  Also, make sure any delete or update statements you send to the db have a WHERE clause.  Otherwise it'll delete or update the whole table.

 

SQL Injection vulnerabilities may not be too important for your current project (especially if you're doing it to learn php), but it's good to be aware of it and develop good practices to defend against it while you're still learning.

[WillBoss]

Where can I read more about Sql Injection. It doesnt sound good!

[cmgmyr]

if you use this:

 

$output = mysql_real_escape_string($input);

 

It will clean up most of that sql injection

[WillBoss]

What does $output = mysql_real_escape_string($input); mean exactly?

 

Will

[cmgmyr]

Well, what it does pretty much is take out anything that might "destroy" your initial query. You will have to read up on sql injections for a better understanding.

 

Take a look at: http://www.unixwiz.net/techtips/sql-injection.html

[WillBoss]

Thanks for that!

[Michael Lasky]

Also, remember that the php functions, like mysql_real_escape_string, help... but it's no excuse to not read up and learn all you can about it.  It's always better to over-sanitize data than to rely only on the php functions. 

[per1os]

It's always better to over-sanitize data than to rely only on the php functions. 

 

That is not necessarily true. It is better to know what you are getting yourself into than to "over sanitize".

 

PHP provides a wonderful function called get_magic_quotes_gpc

http://us2.php.net/manual/en/function.get-magic-quotes-gpc.php

 

What this does is tell you if your server is setup to automatically escape data from a form, if it is than you are free and clear on the injection. A good method to sanitize data would be something like this:

 

<?php
function clean($val) {
    return get_magic_quotes_gpc()?$val:mysql_real_escape_string($val);
}

foreach ($_POST as $key => $val) {
       $_POST[$key] = clean($val);
}
?>

 

That way if the data is already escaped it does not double escape it which makes for cleaner data and you know your data will be escaped properly.

 

EDIT: Modified the quote, it is always good to learn about how and why you are doing something. Just the over-sanitizing part is what can get you into trouble =)

[Michael Lasky]

Also, remember that the php functions, like mysql_real_escape_string, help... but it's no excuse to not read up and learn all you can about it.  It's always better to over-sanitize data than to rely only on the php functions. 

 

That is not necessarily true. It is better to know what you are getting yourself into than to "over sanitize".

 

PHP provides a wonderful function called get_magic_quotes_gpc

http://us2.php.net/manual/en/function.get-magic-quotes-gpc.php

 

What this does is tell you if your server is setup to automatically escape data from a form, if it is than you are free and clear on the injection. A good method to sanitize data would be something like this:

 

That way if the data is already escaped it does not double escape it which makes for cleaner data and you know your data will be escaped properly.

 

While I agree, I do think it's good to know how to sanitize data and what get_magic_quotes_gpc is doing before one should rely on it.  Though I usually sanitize data myself, maybe I'm just over paranoid.