0
0

Patrick, you're leaking SQL statements


               
2012 Oct 26, 9:34am   5,701 views  23 comments

by Dan8267   follow (4)  

@Patrick

Today the site started sending back the SQL the server is executing after posting replies to messages. Here's a snippet. Looks like your log file.

1 0.56290626525879 select self_ID, friend from relationships where other_ID = '8267'
2 0.59986114501953 select other_ID, friend, ignorr from relationships where self_ID = '8267'
3 0.86808204650879 insert into postviews (user_ID, post_ID, want_email) values (8267, 1217988, 0) on duplicate key update want_email=0
4 0.94485282897949 update comments set comment_date=now() where comment_ID=889784
5 0.97990036010742 update threads set latest_comment_excerpt='Dan8267 says CaptainShuddup says And I\'m sure there\'s sound scientific reasoning behind... \"There\'s also been studies showing that religious tendencies are genetic.\" If I bother to do the Google search and prove you wrong yet again, will you be man enough to' where post_ID=1217988

« First        Comments 16 - 23 of 23        Search these comments

16   Dan8267   @   2012 Oct 27, 12:28pm  


I'd prefer registration to be instant and as convenient as possible for human users, since merely having registration already discourages people a bit from commenting.

Well, there is one other way, but it does take away anonymity and requires a mobile phone with SMS messaging. The registering user gives his phone number and your server sends him a SMS with a short numeric code. The user then enters the code on a verification page.

Of course, that means users have to be willing to share their mobile number with you. Spammers won't do that, but not all humans will want to or be able to either. And they have to trust you to not reveal who they are as their mobile number effectively identifies them.

Online marketing companies love mobile phones because then they can attach a social security number and everything that goes with that to a user profile.

17   Dan8267   @   2012 Oct 27, 12:34pm  

Also, there is Askimet. It's a $5/month service that provides a Bayesian filter for forum comments based on all comments received from all their customers (around 20 million a day). You send them the text of a comment, and they reply "spam" or "not spam". There are libraries for various platforms including all the major ones.

18   Melissa   @   2012 Oct 28, 12:26am  

I've seen sites that ask very simple questions like "What is 6+3?" in an image to get proof of human. Don't know how easy that is to bypass. Seems quite simple and hard for a spammer to automatically figure out. After all, they are using code intended for the masses.

19   Patrick   @   2012 Oct 28, 2:58am  

The 6 + 3 test seems simple enough to do and reasonably hard to get around, especially if I generate it as an image.

But my latest attempt to simply filter by number of comments, country of origin, presence of links, and keywords is working pretty well lately. I hope it's not blocking any innocent people.

Anything else I should improve about the forum?

20   Dan8267   @   2012 Oct 28, 5:52am  


The 6 + 3 test seems simple enough to do and reasonably hard to get around, especially if I generate it as an image.

That's just a captcha. If spammers are crowsourcing captchas on porn sites, then the 6 + 3 test is no harder (no pun intended) to break.

21   Melissa   @   2012 Oct 28, 8:34am  

Dan8267 says

That's just a captcha.

But if it's a custom built "captcha," the spammers would have to customize code to deal with it. It's not a canned captcha for which they already have libraries to handle. I doubt Patrick has the following (sorry) to get spammers attention.

And it doesn't even have to be an image. It could be text. How would they know which field is the test? Registration asks lots of things. One could even be "leave this blank if you are a human."

22   Patrick   @   2012 Oct 28, 10:56am  

Melissa says

I doubt Patrick has the following (sorry) to get spammers attention.

Actually, they are paying close attention, and continuously modify their spam to try to get it through my filters.

For example, I added a feature where you can enter an email address so that the thread will get mailed to that address (and the address added as a user). Within one day they were entering valid email addresses, which then got registered as users. Then they would log in as those users and attempt to post spam.

So I'm pretty sure the spamming is not entirely automated. There must be humans working on it.

« First        Comments 16 - 23 of 23        Search these comments

Please register to comment:

api   best comments   contact   latest images   memes   one year ago   users   suggestions   gaiste