You are not logged in.

#1 2010-07-18 5:11 pm

KeyDog
Member
From: Europe
Registered: 2010-07-18
Posts: 181
Website

PunBB 1.3 Extension Querying Your DB

The Beta version of Stopforumspam Extension for PunBB 1.3.x

Grez wrote:

Anyway... I was checking the function Slavok advised, and somehow it seems we've got working (little bit rewriten) extension now big_smile

Check it out (.zip)  smile

forget all my blabla below big_smile
========================
Now question is:

1. How do I know how many queries "my" forum has sent you, i.e. how would you suggest I log and verify that queries are correctly being sent?
2. How would you suggest the limit be coded (if at all) to limit to 5k queries? Or do you automatically enfore the 5k anyway?

If you see any problems with the above ext, let me know...

Last edited by KeyDog (2010-07-20 7:45 am)


Project URL Checker:
Database with several thousand website URLs spammers are promoting. Please feel free to contribute obvious spam related URLs to me: keydog@keydogbb.info
Further Plug-In users welcome; currently over 50k daily checks

Offline

#2 2010-07-18 5:31 pm

pedigree
uıɐbɐ ʎɐqǝ ɯoɹɟ pɹɐoqʎǝʞ ɐ buıʎnq ɹǝʌǝu ɯ,ı
From: New Zealand
Registered: 2008-04-16
Posts: 7,056

Re: PunBB 1.3 Extension Querying Your DB

You would have to keep a counter on your side to record the number of queries youve made.  We do enforce a 5000 queries per 24 hour period, starting from the first query.  When you hit the magic 5k, youll get a 503 header and a page this xml response

<response success="false">
      <error>rate limit exceeded</error>
</response>

Offline

#3 2010-07-19 12:59 pm

KeyDog
Member
From: Europe
Registered: 2010-07-18
Posts: 181
Website

Re: PunBB 1.3 Extension Querying Your DB

Hm. Seems it's not working.
If someone knowledgable can take a look I'd appreciate it (at the code in the extension that is).
An IP just registered which is in your db 200x...

Last edited by KeyDog (2010-07-19 1:00 pm)


Project URL Checker:
Database with several thousand website URLs spammers are promoting. Please feel free to contribute obvious spam related URLs to me: keydog@keydogbb.info
Further Plug-In users welcome; currently over 50k daily checks

Offline

#4 2010-07-19 2:08 pm

pedigree
uıɐbɐ ʎɐqǝ ɯoɹɟ pɹɐoqʎǝʞ ɐ buıʎnq ɹǝʌǝu ɯ,ı
From: New Zealand
Registered: 2008-04-16
Posts: 7,056

Re: PunBB 1.3 Extension Querying Your DB

What I would do

Change
$urlchecker = file_get_contents($url);
to
$urlchecker = @file_get_contents($url);

It does fail from time to time.  For your debugging, what does

var_dump($result);var_dump($urlchecker);

show after each remote file pull?

You might also consider caching your results as each query will generate another query.  The logic is easy

Offline

#5 2010-07-19 2:15 pm

KeyDog
Member
From: Europe
Registered: 2010-07-18
Posts: 181
Website

Re: PunBB 1.3 Extension Querying Your DB

pedigree wrote:

For your debugging, what does
var_dump($result);var_dump($urlchecker);
show after each remote file pull?

Thanks, how do I check that?

pedigree wrote:

You might also consider caching your results as each query will generate another query.  The logic is easy

Do you have an example of code that does that?


Project URL Checker:
Database with several thousand website URLs spammers are promoting. Please feel free to contribute obvious spam related URLs to me: keydog@keydogbb.info
Further Plug-In users welcome; currently over 50k daily checks

Offline

#6 2010-07-19 2:22 pm

pedigree
uıɐbɐ ʎɐqǝ ɯoɹɟ pɹɐoqʎǝʞ ɐ buıʎnq ɹǝʌǝu ɯ,ı
From: New Zealand
Registered: 2008-04-16
Posts: 7,056

Re: PunBB 1.3 Extension Querying Your DB

KeyDog wrote:
pedigree wrote:

For your debugging, what does
var_dump($result);var_dump($urlchecker);
show after each remote file pull?

Thanks, how do I check that?

pedigree wrote:

You might also consider caching your results as each query will generate another query.  The logic is easy

Do you have an example of code that does that?

You can download my vb mod vbstopforumspam from www.fizzleblood.net/vbStopForumSpam_v0.61.zip

Its not the most ideal caching as it has a broken logic to it.  If you make the primary key unique and sort by date desc limit 1, then you get the most recent result.  Ive fixed it in v0.62 but havent tested it properly.

I do this *a lot* for debugging my code

    $urlchecker = file_get_contents($url);
    $var_dump($urlchecker);exit;

and

    $result = curl_exec($curl);
    var_dump($result);exit;

Offline

#7 2010-07-19 2:44 pm

pedigree
uıɐbɐ ʎɐqǝ ɯoɹɟ pɹɐoqʎǝʞ ɐ buıʎnq ɹǝʌǝu ɯ,ı
From: New Zealand
Registered: 2008-04-16
Posts: 7,056

Re: PunBB 1.3 Extension Querying Your DB

Logic is based on the insertion of a record

table cachetable {
  record unique,
  expiry datetime
}

insert into cachetable (record, expiry) values ($data, date_add(now(), 15 minute) on duplicate key set record = $data, expiry = date_add(now(), 15 minutes)

then to check
delete * from cachetable where expiry < now();
select record from cachetable where record = [whatever];
if (row_count) = 0 {
   $data = get new data
   insert into table...
}

do something with $data...

You can base the expiry on different criteria.  cache blacklist hits for a day, misses for 15 minutes etc...

Offline

#8 2010-07-19 2:52 pm

KeyDog
Member
From: Europe
Registered: 2010-07-18
Posts: 181
Website

Re: PunBB 1.3 Extension Querying Your DB

Thanks very helpful,
I will try and get a dev over at punbb to give it the look over
to incorporate the caching...
I'll also point to that good mod you've made...


Project URL Checker:
Database with several thousand website URLs spammers are promoting. Please feel free to contribute obvious spam related URLs to me: keydog@keydogbb.info
Further Plug-In users welcome; currently over 50k daily checks

Offline

#9 2010-07-19 11:01 pm

KeyDog
Member
From: Europe
Registered: 2010-07-18
Posts: 181
Website

Re: PunBB 1.3 Extension Querying Your DB

Ok so now Grez (mainly) over at Punbb.informer.com has come up with this much improved extension. Download
If you could publish on your Downloads page would be cool. Maybe incl. one of these logos.

punbblogo.png

punbblogo2.png

Thx


Project URL Checker:
Database with several thousand website URLs spammers are promoting. Please feel free to contribute obvious spam related URLs to me: keydog@keydogbb.info
Further Plug-In users welcome; currently over 50k daily checks

Offline

#10 2010-07-19 11:13 pm

pedigree
uıɐbɐ ʎɐqǝ ɯoɹɟ pɹɐoqʎǝʞ ɐ buıʎnq ɹǝʌǝu ɯ,ı
From: New Zealand
Registered: 2008-04-16
Posts: 7,056

Re: PunBB 1.3 Extension Querying Your DB

Im not familiar with the internals of phpbb but I hope

$query = array(
'INSERT'   => 'ip, bot_visit, lastseen, frequency',
'INTO'     => 'stopforumspam',
  'VALUES'   => '\''.$ip.'\', NOW(), \''.$xml["response"]

handles duplicate primary key inserts/updates

Offline

#11 2010-07-19 11:18 pm

KeyDog
Member
From: Europe
Registered: 2010-07-18
Posts: 181
Website

Re: PunBB 1.3 Extension Querying Your DB

I'll inquire. PS: PunBB 1.3(like is running here, well 1.2! tongue) not phpbb. smile And give us fair warning if you notice anything abnormal... it's beta...

Last edited by KeyDog (2010-07-19 11:20 pm)


Project URL Checker:
Database with several thousand website URLs spammers are promoting. Please feel free to contribute obvious spam related URLs to me: keydog@keydogbb.info
Further Plug-In users welcome; currently over 50k daily checks

Offline

#12 2010-07-20 10:17 am

pedigree
uıɐbɐ ʎɐqǝ ɯoɹɟ pɹɐoqʎǝʞ ɐ buıʎnq ɹǝʌǝu ɯ,ı
From: New Zealand
Registered: 2008-04-16
Posts: 7,056

Re: PunBB 1.3 Extension Querying Your DB

Force of habit in my typing smile The new site, to upgrade this one, has fluxbb 1.4 as its forum now.  Ill add this mod to the new version of the downloads page as I want to have this site running asap, just need to find the time where four projects arent pulling on each limb at the same time.

Offline

#13 2010-07-20 10:55 am

KeyDog
Member
From: Europe
Registered: 2010-07-18
Posts: 181
Website

Re: PunBB 1.3 Extension Querying Your DB

No worries, (saw in your mod for vb that you code after drinking wine sometimes tongue)

- if you are using the forked fluxbb 1.4 (without ext system!) you won't be able to use the extension (not mod) that we've created... 1.4 is a 1.2 punbb *plus* and 1.3 is different approach to mods, i.e. one-click install ext system

- checked back with ext devs at punbb and they agree the caching won't be a problem...


Project URL Checker:
Database with several thousand website URLs spammers are promoting. Please feel free to contribute obvious spam related URLs to me: keydog@keydogbb.info
Further Plug-In users welcome; currently over 50k daily checks

Offline

Board footer

Powered by FluxBB

Close
Close