Gossamer Forum
Home : Products : Gossamer Links : Version 1.x :

Delete Records (admin.cgi) -> Weird, but working!

Quote Reply
Delete Records (admin.cgi) -> Weird, but working!
Okay...I have looked through a bunch of documents at http://www.mysql.com and I didn't think that the following syntax within the sub delete_records routine in the admin.cgi script would work, but it does!

Code:

if ($db->{'db_table'} eq 'Users') {
$db->do ("UPDATE Banners SET UserID = '103' WHERE UserID = $id");
$db->do ("DELETE FROM Bookmarks WHERE UserID = $id");
$db->do ("DELETE FROM Editor_Reviews WHERE UserID = $id");
$db->do ("UPDATE Links SET UserID = '103' WHERE UserID = $id");
$db->do ("DELETE FROM User_Reviews WHERE UserID = $id");
# undef $Links::DBSQL::DBH;
$db->do ("DELETE FROM anthrotech4.Applied WHERE (EmployerID = $id) OR (UserID = $id)");
$db->do ("DELETE FROM anthrotech4.Company WHERE UserID = $id");
$db->do ("DELETE FROM anthrotech4.CoverLetters WHERE CoverLetterID = $id");
$db->do ("DELETE FROM anthrotech4.Jobs WHERE UserID = $id");
$db->do ("DELETE FROM anthrotech4.Resume WHERE ResumeID = $id");
}


I guess my confusion is using db.table type of syntax.

My questions are:

1) Are there any security loopholes in the above codes?
2) Are there any CPU/Memory issues that I should be aware of, in terms of improving data processing?

Anyway...I am glad that this work, since it maintains referential integrity of tables....but I am confused of why adding the db. (db = database, in my case, anthrotech4. works)....

Regards,

Eliot Lee
Quote Reply
Re: Delete Records (admin.cgi) -> Weird, but working! In reply to
Hi Eliot,

the database.tablename syntax is the correct standard SQL syntax to referr to a table.

The database connection used in linksSQL doesn't really connect you exlusively to the given database - it connects you to your mysql-user account. (the provided database name only sets the default database, in order to use the tablenames directly - without db.)

With this connection you can access any database you have the granted rights.

1. I do not see any securitiy problems as long as nobody has access to the database connection string.

2. The only way of speeding up the querrys is to index the colums searched on. (But indexing colums is increasing the insert time of a record)

regards, alexander

Quote Reply
Re: Delete Records (admin.cgi) -> Weird, but working! In reply to
Thanks, Alex!!!

I guess I overlooked the appropriate MySQL documents!

Yea...in terms of the LINKS SQL connection, I had to add some codes in the sub connect routine to allow connections to multiple databases...this, however, did not work when I tried deleting related records across different tables and databases, so I added the db.table reference and it worked!

The weird thing is that in front-end scripts like my delete user account feature, I've had to use the undef... codes and they worked...as you can see, I remmed out those codes in the sub delete_records routine since they didn't work...

Thanks for the input about security...I have the admin script password protected both via sub authentication via LINKS SQL and .htaccess...double protection doesn't hurt.

I also have secondary indexes on all tables posted above...so, that has helped in terms of data processing. I just wanted to double check to see if there wasn't any better codes that I could use.

thanks!

Regards,

Eliot Lee