spam users in wp_users after wpsc upgrade

i read 3 pages and im still not sure what the best fix is.
hostgator is pressuring me to do something for some reason, and im not sure if i should roll back wpec, and if so, how far.

or if i should upgrade to newest and put the line in the functions.php

what are you guys doing in the meantime?

@tecvoid for me at the moment i am using the function Andres has posted on page 4 (with the only difference i had to use NOW()). I am also calling the function directly in functions.php (i just comment/uncomment the function in the evening). Somehow the wp cron doesn’t seem to work either

function fix_reset_wpsc_cron() {
	remove_action( 'wpsc_hourly_cron_task', '_wpsc_clear_customer_meta' );
	add_action( 'wpsc_hourly_cron_task', 'fix_wpsc_clear_customer_meta' );
}

So i just trigger/call fix_wpsc_clear_customer_meta directly. Have done this the last two days and the queries are reduce from ±8000 to 1000, and the anynomous users from 4000 to 400 (usermeta from 80000 to 8000).
Still far from perfect, but this project runs until end march, after that i am going to upgrade wpsc if a new one exists.

@ddt: If you want to avoid commenting and uncommenting the function every day, you could add something like this:

function fix_wpsc_clear_customer_meta() {
    if ( !get_option( 'fix_wpsc_clear_customer_meta_' . date( 'Ymd' ) ) ) {
        // The current body of the function goes here.
        add_option( 'fix_wpsc_clear_customer_meta_' . date( 'Ymd' ), true );
    }
}

That way the deleting process will be performed just once a day. You can even fine-tune the times it will run by using date() and the conditionals you need.

its too bad someone cant turn the fix into a plugin.
im going to try implementing that fix from page 4 and see what happens.

i tried adding the code to my functions.php and keep breaking the site.

ive tried changing the time code to now, ive tried including it inside the ending }
including it outside
including it outside with a closing }

im ok at inserting code but this is a headache considering that the same code doesnt work on different production servers.

// Remove users and meta data.
function fix_wpsc_clear_customer_meta() {
	global $wpdb;
	require_once( ABSPATH . 'wp-admin/includes/user.php' );
	$purge_count = 200;
	$sql = "
		SELECT user_id
		FROM {$wpdb->usermeta}
		WHERE
		meta_key = '_wpsc_last_active'
		AND meta_value < UNIX_TIMESTAMP() - " . WPSC_CUSTOMER_DATA_EXPIRATION . "
		LIMIT {$purge_count}
	";
	// Do this in batches of 200 to avoid memory issues when there are too many
	// anonymous users.
	@set_time_limit( 0 ); // no time limit
	do {
		$ids = $wpdb->get_col( $sql );
		$included_ids = array();
		foreach ( $ids as $id ) {
			$included_ids[$id] = $id;
		}
		$in = implode(',', $included_ids);
		$wpdb->query( "DELETE FROM $wpdb->users WHERE ID IN ($in)" );
		$wpdb->query("DELETE FROM $wpdb->usermeta WHERE user_id IN ($in)");
	} while ( count( $ids ) == $purge_count );
	// Update number of users.
	update_option( 'user_count', count_users()['total_users'] );
}

// Modify action hook for WP e-Commerce automated task.
function fix_reset_wpsc_cron() {
	remove_action( 'wpsc_hourly_cron_task', '_wpsc_clear_customer_meta' );
	add_action( 'wpsc_hourly_cron_task', 'fix_wpsc_clear_customer_meta' );
}

// Do reset.
add_action( 'wpsc_init', 'fix_reset_wpsc_cron' );

@ddt i hate to beg, but could you post once more the final code for a medium skill person you used? and touch on how you trigger the call/fix event?

one last question, im avoiding upgrading to version 3.8.13.3 from version 3.8.13.1, will i need to have the latest broken version to fix?

god what an awful mess

@tecvoid, what do you mean when you say the code is breaking your site? Are you seeing any errors? If you’re not, you may want to set the WP_DEBUG to true.

If you want to test the function directly, just run it by adding fix_wpsc_clear_customer_meta(); after the function declaration.

BTW, @ddt, I just figured out the cron is not working for you because the wpsc_hourly_cron_task action was already executed by the time functions.php loads (as the code of every plugin), so you need to include the fix inside a plugin or a must-use plugin. I find the later better, since must-use plugins load before common plugins, so you don’t have to care about the fix loading order. I said in my first post that the functions.php way should work, but I was wrong.

I’m currently doing this by creating a file named wp-content/mu-plugins/wpsc-fix.php and putting the code of the fix there.

i did what you said, i called my file anon-delete.php and im getting the folling error

Remove users and meta data. function fix_wpsc_clear_customer_meta() { global $wpdb; require_once( ABSPATH . ‘wp-admin/includes/user.php’ ); $purge_count = 200; $sql = ” SELECT user_id FROM {$wpdb->usermeta} WHERE meta_key = ‘_wpsc_last_active’ AND meta_value < UNIX_TIMESTAMP() – ” . WPSC_CUSTOMER_DATA_EXPIRATION . ” LIMIT {$purge_count} “; // Do this in batches of 200 to avoid memory issues when there are too many // anonymous users. @set_time_limit( 0 ); // no time limit do { $ids = $wpdb->get_col( $sql ); $included_ids = array(); foreach ( $ids as $id ) { $included_ids[$id] = $id; } $in = implode(‘,’, $included_ids); $wpdb->query( “DELETE FROM $wpdb->users WHERE ID IN ($in)” ); $wpdb->query(“DELETE FROM $wpdb->usermeta WHERE user_id IN ($in)”); } while ( count( $ids ) == $purge_count ); // Update number of users. update_option( ‘user_count’, count_users()[‘total_users’] ); } // Modify action hook for WP e-Commerce automated task. function fix_reset_wpsc_cron() { remove_action( ‘wpsc_hourly_cron_task’, ‘_wpsc_clear_customer_meta’ ); add_action( ‘wpsc_hourly_cron_task’, ‘fix_wpsc_clear_customer_meta’ ); } // Do reset. add_action( ‘wpsc_init’, ‘fix_reset_wpsc_cron’ );
Warning: session_start() [function.session-start]: Cannot send session cache limiter – headers already sent (output started at /home3/revenge1/public_html/a1decals.com/wp-content/mu-plugins/anon-delete.php:14) in /home3/revenge1/public_html/a1decals.com/wp-content/plugins/wp-e-commerce/wpsc-core/wpsc-constants.php on line 17

whats weird is this prints on the webpage then the content loads over it.

here is the code that doesnt seem to have any errors, but it doesnt seem to be removing any anon users, im not sure what to try next, i used the NOW () command also, im just loading pages on my site hoping this is supposed to be activating the deletion, im getting a better idea of whats going on i guess i just need the right code.

<?php
// Remove users and meta data.
function fix_wpsc_clear_customer_meta() {
global $wpdb;
require_once( ABSPATH . ‘wp-admin/includes/user.php’ );
$purge_count = 200;
$sql = “
SELECT user_id
FROM {$wpdb->usermeta}
WHERE
meta_key = ‘_wpsc_last_active’
AND meta_value < UNIX_TIMESTAMP() – ” . WPSC_CUSTOMER_DATA_EXPIRATION . “
LIMIT {$purge_count}
“;
// Do this in batches of 200 to avoid memory issues when there are too many
// anonymous users.
@set_time_limit( 0 ); // no time limit
do {
$ids = $wpdb->get_col( $sql );
$included_ids = array();
foreach ( $ids as $id ) {
$included_ids[$id] = $id;
}
$in = implode(‘,’, $included_ids);
$wpdb->query( “DELETE FROM $wpdb->users WHERE ID IN ($in)” );
$wpdb->query(“DELETE FROM $wpdb->usermeta WHERE user_id IN ($in)”);
} while ( count( $ids ) == $purge_count );
// Update number of users.
update_option( ‘user_count’, count_users() );
}

// Modify action hook for WP e-Commerce automated task.
function fix_reset_wpsc_cron() {
remove_action( ‘wpsc_hourly_cron_task’, ‘_wpsc_clear_customer_meta’ );
add_action( ‘wpsc_hourly_cron_task’, ‘fix_wpsc_clear_customer_meta’ );
}

// Do reset.
add_action( ‘wpsc_init’, ‘fix_reset_wpsc_cron’ );
?>

tecvoid, it’s best to test the script without running it through cron. If that code is in your functions.php, just add:

fix_wpsc_clear_customer_meta();

to the bottom to run it directly.

That function itself looks ill conceived though, unsetting the time limit and doing SQL queries in a while-loop is a bad idea and the require_once line isn’t needed at all. Deleting the extraneous users should be done in a single, non-looping SQL query instead.

Thanks for your response @litemotiv

i got the anon users deleted but im not quite sure when i got it to work.

i will recap my steps for anyone reading and is at a lower skill level

first went to the functions.php file and added the following to the end:

/** part of temp fix, can be deleted later */
define( ‘WPSC_CUSTOMER_DATA_EXPIRATION’, 3.5 * 3600 );

then i used cpanel file browser to create a folder inside /wp-content
called it mu-plugins
open that folder and create a file anon-delete.php
edited that blank file to contain this whole code with the <?php thats not mentioned

<?php
// Remove users and meta data.
function fix_wpsc_clear_customer_meta() {
global $wpdb;
require_once( ABSPATH . ‘wp-admin/includes/user.php’ );
$purge_count = 200;
$sql = “
SELECT user_id
FROM {$wpdb->usermeta}
WHERE
meta_key = ‘_wpsc_last_active’
AND meta_value < NOW() – ” . WPSC_CUSTOMER_DATA_EXPIRATION . “
LIMIT {$purge_count}
“;
// Do this in batches of 200 to avoid memory issues when there are too many
// anonymous users.
@set_time_limit( 0 ); // no time limit
do {
$ids = $wpdb->get_col( $sql );
$included_ids = array();
foreach ( $ids as $id ) {
$included_ids[$id] = $id;
}
$in = implode(‘,’, $included_ids);
$wpdb->query( “DELETE FROM $wpdb->users WHERE ID IN ($in)” );
$wpdb->query(“DELETE FROM $wpdb->usermeta WHERE user_id IN ($in)”);
} while ( count( $ids ) == $purge_count );
// Update number of users.
update_option( ‘user_count’, count_users() );
}

// Modify action hook for WP e-Commerce automated task.
function fix_reset_wpsc_cron() {
remove_action( ‘wpsc_hourly_cron_task’, ‘_wpsc_clear_customer_meta’ );
add_action( ‘wpsc_hourly_cron_task’, ‘fix_wpsc_clear_customer_meta’ );
}

// Do reset.
add_action( ‘wpsc_init’, ‘fix_reset_wpsc_cron’ );
?>

Here is where i got lost about when it kicked in because i was reloading the anon users page and the user count was not going down, so you might have to change the code. the changes i tried were:

NOW()
to
UNIX_TIMESTAMP()
and
UNIX_TIMESTAMP(NOW())

update_option( ‘user_count’, count_users() );
was
update_option( ‘user_count’, count_users()[‘total_users’] );

as @litemotive mentioned, i added

fix_wpsc_clear_customer_meta();

at the end so it looked like

// Do reset.
add_action( ‘wpsc_init’, ‘fix_reset_wpsc_cron’ );
fix_wpsc_clear_customer_meta();
?>

i was basicly changing the code and loading non-cached pages, so it will take more testing to see which code will work automaticall, but maybe this is a better explanantion for those currently working on the problem.

Here is the code that finally deletes my users.

<?php
// Remove users and meta data.
function fix_wpsc_clear_customer_meta() {
global $wpdb;
require_once( ABSPATH . ‘wp-admin/includes/user.php’ );
$purge_count = 200;
$sql = “
SELECT user_id
FROM {$wpdb->usermeta}
WHERE
meta_key = ‘_wpsc_last_active’
LIMIT {$purge_count}
“;
// Do this in batches of 200 to avoid memory issues when there are too many
// anonymous users.
@set_time_limit( 0 ); // no time limit
do {
$ids = $wpdb->get_col( $sql );
$included_ids = array();
foreach ( $ids as $id ) {
$included_ids[$id] = $id;
}
$in = implode(‘,’, $included_ids);
$wpdb->query( “DELETE FROM $wpdb->users WHERE ID IN ($in)” );
$wpdb->query(“DELETE FROM $wpdb->usermeta WHERE user_id IN ($in)”);
} while ( count( $ids ) == $purge_count );
// Update number of users.
update_option( ‘user_count’, count_users() );
}

// Modify action hook for WP e-Commerce automated task.
function fix_reset_wpsc_cron() {
remove_action( ‘wpsc_hourly_cron_task’, ‘_wpsc_clear_customer_meta’ );
add_action( ‘wpsc_hourly_cron_task’, ‘fix_wpsc_clear_customer_meta’ );
}

// Do reset.
add_action( ‘wpsc_init’, ‘fix_reset_wpsc_cron’ );
fix_wpsc_clear_customer_meta();
?>

i actually have to remove

AND meta_value < NOW() – ” . WPSC_CUSTOMER_DATA_EXPIRATION . “

if i dont remove that line, i dont think that it returns any values to delete.

im sorry if i kinda took over for a minute here, ill quiet down now that i have code i can run for just a couple seconds it seems to delete my anon users.

if anyone can point out why i should not let it run without that customer expiration line please let me know.

@tecvoid: It’s nice to see you got it working. I see as maybe problematic the fact that you’re deleting all the anonymous users. About that, keep in mind this comment from @Pye Brook in page 4:

If you don’t have any caching and you are not concerned about losing any session data you probably can directly delete the users and the meta created by WPEC without too many side effects. Without knowing everything in your setup I can’t say for sure.

So maybe you don’t want to delete all of them, but this is up to you and how you have your installation set up.

On the other hand, it looks like you’re having problems with MySQL time functions, so you may to replace this line:

AND meta_value < NOW() - " . WPSC_CUSTOMER_DATA_EXPIRATION . "

with this one:

AND meta_value < " . strtotime( 'now' ) . " - " . WPSC_CUSTOMER_DATA_EXPIRATION . "

Greetings!

3 Days ago I did an upgrade to my multisite. last night I discovered my 1000 new user accounts.

After not finding any registrations in my logs I figured it had to be some sort of virus. But I did notice ONE user account that looked google worthy “_wpsc_bot”. And that was what lead me to this thread. But it did cost me hours.

To the programmer…
See the big picture? Some rhetorical questions for you.. Why did you not comment in the meta data the source of the use account? Would it have killed you to just create a session table and use that instead of something that will actually show up on the front end and potentially affect hundreds of sites?

An observation…
We are using a multisite system. The plugin is only enabled on ONE site. The user accounts listed the source site in the metadata as a test.site that we had.. Not the actual source site. And not the site the plugin was enabled on. I find this odd and thought I would bring it to your attention.

My question…
I had created a script that backs up my site every hour and every night. So I was easily able to revert back to the old plugin BEFORE the user account creation was implemented. I am going to assume you have arrived at the obvious conclusion that this, although creative, was a very bad idea. And are going to move towards a session table(s) like other carts. How much longer till it is safe to upgrade and you have taken this fake user stuff out. And yes you will be posting here about it correct? Also, I paid for this plugin, you have my eMail address.. Why did you not send me an eMail to warn me about this???

~ Merlin

i am still having to delete the anonymous user data with php code, why is this thread practically dead?

does anyone have an eta on when they might update this? am i wrong, when i updated, the anonymous user link disappeared but the users are still stacking up