Removing spam users from wordpress with bbforum using SQL

I have a wordpress blog, actually i have a lot of them, which uses the bb forum plugin as well as the main site.
This particular forum attracts a lot of spam signups, this can bog down the database once it gets to the point of over 11k or more, and its a security risk (and pain in the butt). I have implemented several plugins to stop sign ups but they still eventually find their way around things.

So today i decided that i really wanted to be rid of all the users that had not commented or posted on the forum. I had previously found plugins that did this for wordpress itself, but they do not work with the bb forums. Often they check if a person has commented on the wordpress, but not if they have posted on the forum.

Anyway i figured it would be a simple mysql query, so hunted about the forum and made the following up

DELETE FROM wp_users
WHERE wp_users.id NOT
IN (
SELECT user_id
FROM wp_comments
)
AND wp_users.id NOT
IN (
SELECT poster_id
FROM bb_posts
)
AND wp_users.id NOT
IN (
SELECT post_author
FROM wp_posts
)

Then of course you need to hunt out and remove all the other meta data, i used something like this
DELETE FROM wp_usermeta
WHERE wp_usermeta.user_id NOT
IN (
SELECT id FROM wp_users
)

It probably pays to check before deleting, and swap the first “DELETE FROM” for a “SELECT * FROM” to view the data.
It also probably pays to delete the user from wp_usermeta by id and other places (will update later)
Hope this helps somebody, let me know of any other places the data needs to be removed from if you know more.

ps. my PC crashed hard whilst writing this, i lost the exact MySQL query i used so redid this from scratch – let me know if i missed any bits or you get any errors.

Finding a Flash video in chrome – Saving files open but deleted

Flash movies are online, but sometimes i want to save some for offline usage so i can watch them later or offline (laptops) etc.
Often there is no way to save/download these files, but they are not held in the tmp dir or cache as they used to be due to changes in flash. In Linux this is easy to overcome using the following methods. This also works for any file that’s been deleted but is still running in memory.

First, you need to find what or where the file is. For flash try

velofille@apple:~$ sudo lsof |grep Flash
npviewer.  8774  velofille   16u      REG                9,1 253092771     396153 /tmp/FlashXXDfgiS7 (deleted)
velofille@apple:~$ 

Bingo, i found my file! Of course the file /tmp/FlashXXDfgiS7 is deleted, so i cant just copy it like the good old days. Now i can copy that file from the proc filesystem using the following

cat /proc/8774/fd/16 > movie.flv

Now to breakdown where i got those parms from. The 8774 is the pid and 2nd number on the line, the 16 was from the ’16u’ which is the file descriptor . So when you do your lsof you can change those according to what you have open.
To find files open from a particular app, use ps to find the pid, then use

lsof -p 8774

Output is something like this

chrome  26572 velofille  mem    REG                9,1  1285536   1836431 /usr/lib/x86_64-linux-gnu/libX11.so.6.3.0
chrome  26572 velofille  mem    REG                9,1   141088   3146088 /lib/x86_64-linux-gnu/ld-2.13.so
chrome  26572 velofille  DEL    REG               0,16           34257353 /dev/shm/.com.google.chrome.i96CPN
chrome  26572 velofille  DEL    REG               0,16           34257687 /dev/shm/.com.google.chrome.W9M2O5
chrome  26572 velofille  mem    REG                9,1   377352   4587538 /opt/google/chrome/libppGoogleNaClPluginChrome.so
chrome  26572 velofille    0r   CHR                1,3      0t0      5778 /dev/null
chrome  26572 velofille    1u   REG               0,20   953173   5767170 /home/velofille/.xsession-errors
chrome  26572 velofille    2u   REG               0,20   953173   5767170 /home/velofille/.xsession-errors
chrome  26572 velofille    3u   REG               0,20   953173   5767170 /home/velofille/.xsession-errors
chrome  26572 velofille    4r  FIFO                0,8      0t0  34257284 pipe
chrome  26572 velofille    5u  unix 0xffff88002c61f740      0t0  34257940 socket
chrome  26572 velofille    6u  sock                0,7      0t0  34257281 can't identify protocol
chrome  26572 velofille    7u   CHR                1,9      0t0      5783 /dev/urandom

The only ones you can take like this are the ones with a file descriptor to play with.

Good luck, an have fun!

Who wants an Android tablet? Wholesale order

A few weeks ago i asked if anyone was interested in an android tablet if I could do a wholesale order and get them cheap. I got a fairly overwhelming response, so i looked into it more.
I have decided on the Zenithink ZT-180 Android to import, and i have worked out the prices to be approximately $350 each. This takes into account, purchase, shipping, GST, and hopefully any other hidden costs.

zt-180zt-180

Specs:
Operation System Android 2.1 (upgradeable to 2.2)
LCD display 10.2″ TFT display,1024*600 Resistive screen (sorry, nobody is making capacitive yet)
CPU ARM11 1Ghz (This means it will probably never run flash/shockwave! – still better than an iPad!)
Memory DDR 256MB
Internet support WIFI:USB port
USB for web camera
I/O ports DC in Power
Earphone
RJ-45
TF
1*USB 2.0 Host port
1*USB 2.0 OTG port
Touch screed Resistive
Audio Built in stereo speakers 1w*2
Built in mono recorder
LED indicators Power; charging
Key-press Power on /off 1 key
Volume 2key
ESC 1 key
Max battery time Wi-Fi on :5 hours
Wi-Fi off:7 hours
Power adapter 220V Input 9V DC Output ,2A
Office software Word viewer
Excel Viewer
PowerPoint Viewer
PDF Viewer
Image Viewer
EDIT Tools Microsoft WordPad
Microsoft Spread Excel
Network Correspondence QQ,MSN,Skype
Internet Internet Explorer
Audio MP3,WAV,WMA,AAC,AAC+,WMA record.
Video 1080P MP4,1080P AVI(XVID,H.263,DIVX),
1080P MKV,1080P RMVB, RM
Photo JPEG,GIF
Game Support all Android Editions,Games
Language Multi-Language

There is NO 3G or GPS, however there is an optional extra for this available online.
When ordering I am also ordering a leather case for them (included in the $350) which has a keyboard built in.

cover and keyboard

cover closed

If you are interested in one of these for approximately $350, please read on.

IMPORTANT!

I will be buying them online in a large portion from an unknown company, whilst I will do everything in my power to make sure i dont get ripped off (its my money too!) there is a slim chance that this may happen. The money is in escrow so I’m fairly sure we’re ok on this front (they don’t get my money until i get the goods)

Payment must be made up front as I do not have the funds to cover everyone elses items!

There is no profit made anywhere in this for me. The price may vary depending on currency conversions at the time and any other unknown fees incurred. If its under the $350, then you will get any leftover. If its more, then I’ll let you know (though I can’t imagine it will be much if it is).

Ideally I need a minimum order of 15 of these before its viable, I can still do it with less, however the buying power is lost somewhat.

Yes you can search around and see other prices on ebay etc, you will find there are a few people that list these at around $220-$300USD each without the leather wallet/cover thing, however i suspect that having the leather cover is probably something most people will want (we can opt for or not either way).

Most android tablets from china are cloned, which leaves the Market unusable, this is fixable, I’ll do it myself and upgrade it to 2.2 if necessary before passing them onto you. You are welcome to take it as it is and do that yourself if you want 🙂

So, you still want in?

Email me: liz at velofille dot com