OJS OCS OMP OHS

You are viewing the PKP Support Forum | PKP Home Wiki



Deleting Users

Are you responsible for making OJS work -- installing, upgrading, migrating or troubleshooting? Do you think you've found a bug? Post in this forum.

Moderators: jmacgreg, btbell, michael, bdgregg, barbarah, asmecher

Forum rules
What to do if you have a technical problem with OJS:

1. Search the forum. You can do this from the Advanced Search Page or from our Google Custom Search, which will search the entire PKP site. If you are encountering an error, we especially recommend searching the forum for said error.

2. Check the FAQ to see if your question or error has already been resolved.

3. Post a question, but please, only after trying the above two solutions. If it's a workflow or usability question you should probably post to the OJS Editorial Support and Discussion subforum; if you have a development question, try the OJS Development subforum.

Deleting Users

Postby keir » Wed Feb 19, 2014 9:12 pm

There is an infestation of spam users in our system that i want to delete.

You shouldn't delete them from users table because of hanging references elsewhere.

If someone can list the tables that may suffer from hanging references i can write some SQL that will do a clean delete
in all those tables.
I'll also add SQL code to remove review data inherited from the undesirables and repost for the use of everyone.
keir
 
Posts: 17
Joined: Sun Aug 12, 2012 9:43 pm

Re: Deleting Users

Postby asmecher » Wed Feb 19, 2014 10:18 pm

Hi keir,

Consider using tools/mergeUsers.php to bulk-delete accounts; it takes care of foreign keys etc.

Regards,
Alec Smecher
Public Knowledge Project Team
asmecher
 
Posts: 8347
Joined: Wed Aug 10, 2005 12:56 pm

Re: Deleting Users

Postby CFreire » Fri May 09, 2014 7:02 pm

Hi Alec

Below, my suggestion about delete web-bot or spam users.
I had almost a thousand records and MergeUser Tools does not seem to me the most appropriate way.

A simple php code: select users "no roles” (no problem with any real user) and delete them from the tables users, user_settings and user_interests.

Code: Select all
$sql = "SELECT user_id FROM users where not exists (select user_id from roles where users.user_id = roles.user_id)";
$result = mysql_query($sql,$con) or die(mysql_error());
$i=0; //only for echo
while($rs = mysql_fetch_array($result)) {
   
    $user = $rs['user_id’];
    mysql_query("DELETE FROM user_settings where user_id = $user”);
    mysql_query("DELETE FROM users where user_id = $user”);
mysql_query("DELETE FROM users_interests where user_id = $user”);
   
    echo '<p>['.$i.'] '.$user.' deleted</p>';
    $i++;
    }


Just in time: my “captcha”, now, is on. Better late than never... 8) :D

Regards,
Carlos
CFreire
 
Posts: 68
Joined: Sun Aug 12, 2007 7:39 pm

Re: Deleting Users

Postby newbies » Fri May 09, 2014 11:45 pm

Hi Carlos,

Thanks for the code, but how to run it on command line? I wrap your code between <?php and ?>, and save it as a file "deleteuser.php" and then invoke it use php deleteuser.php, nothing happened. Could you give more instructions?
newbies
 
Posts: 73
Joined: Sat Feb 23, 2013 9:15 pm

Re: Deleting Users

Postby CFreire » Sat May 10, 2014 10:52 am

Hi newbies, two questions:

- You have properly configured your database connection ($con) before run the code? Example:
Code: Select all
$con = mysql_connect(“my_mysql.connection”,"my_login”,"my_password") or die(mysql_error());
mysql_select_db(“my_db", $con) or die(mysql_error());

- If yes, try a small change in the delete command. If it works now, my advance apologies ... I changed the code to post: it works exactly as it is below.
Code: Select all
mysql_query("DELETE FROM user_settings where user_id = ".$rs['user_id']);
mysql_query("DELETE FROM users where user_id = ".$rs['user_id']);
mysql_query("DELETE FROM users_interests where user_id = ".$rs['user_id']);

If still nothing happens ... you don't have “no roles" records in your database. Try this to make sure:
Code: Select all
$con = mysql_connect(“my_mysql.connection”,"my_login”,"my_password") or die(mysql_error());
mysql_select_db(“my_db", $con) or die(mysql_error());

$sql = "SELECT user_id FROM users where not exists (select user_id from roles where users.user_id = roles.user_id) order by username";
$result = mysql_query($sql,$con) or die(mysql_error());
$total = mysql_num_rows($result);

echo $total;


Regards, Carlos
CFreire
 
Posts: 68
Joined: Sun Aug 12, 2007 7:39 pm

Re: Deleting Users

Postby newbies » Sat May 10, 2014 11:37 pm

Thank you! it worked like a charm.

here is the complete code, just in case others want to do the same

Code: Select all
<?php

$con = mysql_connect("localhost","your_username","your_password") or die(mysql_error());
mysql_select_db("your_db_name", $con) or die(mysql_error());


$sql = "SELECT user_id FROM users where not exists (select user_id from roles where users.user_id = roles.user_id) order by username";
$result = mysql_query($sql,$con) or die(mysql_error());
$total = mysql_num_rows($result);

echo $total;

$i=0; //only for echo
while($rs = mysql_fetch_array($result)) {

    $user = $rs['user_id'];
    mysql_query("DELETE FROM user_settings where user_id = $user");
    mysql_query("DELETE FROM users where user_id = $user");
    mysql_query("DELETE FROM users_interests where user_id = $user");

    echo '<p>['.$i.'] '.$user.' deleted</p>';
    $i++;
    }


?>
newbies
 
Posts: 73
Joined: Sat Feb 23, 2013 9:15 pm

Re: Deleting Users

Postby CFreire » Tue May 13, 2014 1:34 pm

Hi newbies:

Glad it worked fine.
But when I saw that I had over 5000 web-bots and only 800 have been eliminated with the code of "no rules” :shock:, decided to enhance the code a bit...
Now I can see more details of the records and deselect, if applicable, those that should not be deleted.
Applying this new code, sql for sql, could eliminate 99% of these intruders.
A bit of manual work, but... 8)

Code: Select all
$con = mysql_connect("localhost","your_username","your_password") or die(mysql_error());
mysql_select_db("your_db_name", $con) or die(mysql_error());

/* use one sql at a time
$sql = "SELECT users.user_id, username, first_name, email, phone, setting_value FROM users inner join user_settings on (users.user_id = user_settings.user_id) where not exists (select user_id from roles where users.user_id = roles.user_id)";
$sql = "SELECT users.user_id, username, middle_name, first_name, email FROM users inner join user_settings on (users.user_id = user_settings.user_id) where username=first_name and first_name=middle_name and middle_name=last_name";
$sql = "SELECT users.user_id, username, first_name, email, phone, setting_value FROM users inner join user_settings on (users.user_id = user_settings.user_id)  where first_name like '%aaa%' or username like '%aaa%'";
$sql = "SELECT users.user_id, username, first_name, email, phone, setting_value FROM users inner join user_settings on (users.user_id = user_settings.user_id) where first_name like '%zzz%' or username like '%zzz%'";
$sql = "SELECT username, first_name, users.user_id, email, phone, setting_value FROM users inner join user_settings on (users.user_id = user_settings.user_id) where phone = '123456'";
*/
//example with the first sql -> “no roles”; change only here!
$sql = "SELECT users.user_id, username, first_name, email, phone, setting_value FROM users inner join user_settings on (users.user_id = user_settings.user_id) where not exists (select user_id from roles where users.user_id = roles.user_id)”;
//
$result = mysql_query($sql,$con) or die(mysql_error());

if ($_POST['delete']){
   
   $user_del = $_POST['user_del'];
   
   for($z=0;$z<count($user_del);$z++){
   mysql_query("DELETE FROM user_settings where user_id = ".$user_del[$z]);
   mysql_query("DELETE FROM users where user_id = ".$user_del[$z]);
   mysql_query("DELETE FROM users_interests where user_id = ".$user_del[$z]);
   
   echo '<p>'.$user_del[$z].' deleted</p>';
   }   
}
?>

<form method="post">
<?php
 $i=0;
 while($rs = mysql_fetch_array($result)) {
    $user = $rs['user_id'];
    echo '<p><input type=checkbox name=user_del[] value='.$user.' checked>&nbsp;['.$i.'] ['.$user.'] ['.$rs['username'].': '.$rs['first_name'].' '.$rs['middle_name'].' '.$rs['last_name'].'] '.$rs['email'].' - '.$rs['phone'].' - '.$rs['setting_value'].'</p>';
    $i++;
    }
    ?>
   
 <p><input type="submit" name="delete" value="delete" /></p>
</form>


Regards,
Carlos
CFreire
 
Posts: 68
Joined: Sun Aug 12, 2007 7:39 pm


Return to OJS Technical Support

Who is online

Users browsing this forum: Yahoo [Bot] and 6 guests