PHP sql delete row 1

[jasc2k]

Hi there,
Firstly apologies as I know this is not technically the right forum but I am using PHP? Anyway I hope you can help.

Basically I have a table for internal user mail that has many rows including UserTo and mail_id. The function below is to delete a message my only problem being because I pass the mail_id through the URL it allows people to delete others mail and I am trying to patch it with the following:

	function delete($msgid, $box){
		global $database, $session;

			$xxx = $database->query("SELECT UserTo FROM ".TBL_MAIL_INBOX." WHERE mail_id = '$msgid'");
			$row = mysql_fetch_array($xxx); 
			$s = $row["UserTo"];
			
			if($session->username == $s){
				$q = "DELETE FROM ".TBL_MAIL_INBOX." WHERE UserTo = '$session->username' AND mail_id = '$msgid' LIMIT 1";
			} else {
				echo "This is not your mail!";
			}
}

I have tried using just the delete statement with the where and clause with some bugs. Can anyone suggest any better method to check the UserTo matches the current username AND the mail_id is correct before deleting or is this my best bet?

Any ideas,
Thanks

 

[jpadie]

just the delete query should do it. however it does not cover cc and bcc events.

it sounds more like you have a fundamental architectural design flaw in your set up. surely each user should have their own table?

 

[ingresman]

surely each user should have their own table?

I wouuld say that each user gets a unique ID rather than a table, if you have 1000 users you need 1000 table, not the way to go I would think.
In the OP's original problem, I'd stop passing this kind of field in the query string, get it in the POST body or a session.

 

[jpadie]

a POST parameter won't stop misuse.

and I was thinking along the lines of separate tables so that a rebuild could be easily managed.

however best of all would be to use an imap server to handle email. some imap servers will use db storage if you want them to. that takes all the hassle out of things and allows people to access their mail over a normal imap client interface.

if you are going down the shared table route then you need to make sure that the same email body is stored for each bcc and cc and to addressee. so as ingresman intimates, you need to make sure that a user unique ID is attributed to each row that identifies the mailbox user. this ID should never be passed over POST/GET but always be inferred from session attributes.

 

[jasc2k]

thank you for your suggestions..
I would like to think that my site will handle 1000s of users so a table each would get messy
Can you suggest how I would go about generating a random unique id?
Would I generate this upon user registration and then refer back to that?

 

[jpadie]

why does it need to be random? surely you already have a unique ID for each user?

and if you are really going to have 1000s of users then why reinvent the wheel? use imap as your message store.

for those readers that want a globally unique ID ...

$id = sha1(uniqid(mt_rand(), true));

 

[jasc2k]

each username is unique and they get a random userid generated each time they login and stored in the sql database. Is this wrong?

Its not technically an email system more downscaled as a user to user message system. I will look into imap.

Thanks

 

[jpadie]

then use the username as the foreign key in the email table. but as a separate column.

it's not wrong, no. but optimising would result in an integer being the primary key in the user table

 

[jasc2k]

i see - so an integer would prove faster.

Thanks very much for your input you have helped me out loads