Patrick.net

Patrick, you're leaking SQL statements

By Dan8267   Follow   Fri, 26 Oct 2012, 4:34pm   1,437 views   23 comments
In Boca Raton FL 33433   Watch (1)   Share   Quote   Permalink   Like   Dislike (1)  

@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

Viewing Comments 1-23 of 23     Last »     See most liked comments

1. Patrick Follow Befriend (54) 5,188 threads 6,158 comments 46 male Menlo Park, CA

   1   4:43pm Fri 26 Oct 2012   Share   Quote   Permalink   Like   Dislike   Thanks for telling me! I was trying to reduce the total number of SQL calls and accidentally pushed test code to the server. Should have just been for a minute or two at most.

2. Dan8267 Follow Befriend (16) 764 threads 7,710 comments Boca Raton, FL Premium

   2   4:46pm Fri 26 Oct 2012   Share   Quote   Permalink   Like   Dislike (1)   Is the username "Bob';drop table threads" still available?

3. Patrick Follow Befriend (54) 5,188 threads 6,158 comments 46 male Menlo Park, CA

   3   4:48pm Fri 26 Oct 2012   Share   Quote   Permalink   Like   Dislike   Hopefully I've got things clean enough that that could actually be a username and wouldn't hurt anything. But just in case I think I'll re-check...

4. Dan8267 Follow Befriend (16) 764 threads 7,710 comments Boca Raton, FL Premium

   4   4:54pm Fri 26 Oct 2012   Share   Quote   Permalink   Like   Dislike   I just got this message No more than 5 links per comment allowed. and I only had http links, all to images. Explicitly putting in the img tag fixed it. Strange restriction. I'd rather there be a rule that you had to have at least 5 links every time you post, a sort of requirement for people to back up their assertions.

5. Patrick Follow Befriend (54) 5,188 threads 6,158 comments 46 male Menlo Park, CA

   5   5:04pm Fri 26 Oct 2012   Share   Quote   Permalink   Like   Dislike   Some spammers post comments with vast numbers of links, so I assumed a limit would be good. OK, it's now 10 links per comment. Anyway, explicitly putting in the img tag should not have fixed it. That sounds like a bug in itself.

6. Dan8267 Follow Befriend (16) 764 threads 7,710 comments Boca Raton, FL Premium

   6   5:10pm Fri 26 Oct 2012   Share   Quote   Permalink   Like   Dislike (1)   Are spammers registering accounts on patrick.net so they can post spam?

7. Patrick Follow Befriend (54) 5,188 threads 6,158 comments 46 male Menlo Park, CA

   7   5:16pm Fri 26 Oct 2012   Share   Quote   Permalink   Like   Dislike   Yes, vast numbers. They attempt a new registration a few times a minute. Fortunately, they have certain characteristics that let me detect them, such as typically being from Russia, Ukraine, or a few other countries, posting links immediately, and using certain keywords that are easy to block, like drug names or brand names.

8. Dan8267 Follow Befriend (16) 764 threads 7,710 comments Boca Raton, FL Premium

   8   5:30pm Fri 26 Oct 2012   Share   Quote   Permalink   Like   Dislike (1)   Ah, the problem is your not using captcha's when registering new users. You're just using email confirmation. I just registered user "test111" and didn't have to enter a captcha. Without such a human-verification challenge, people will write code to automatically register users. It's not worth their time to register a single account by hand. I'd recommend adding a ReCaptcha challenge for registering new users. Since a real user only needs to register once, this is hardly an inconvenience. But it kills the automated registering of accounts. Plus it helps digitize books.

9. Patrick Follow Befriend (54) 5,188 threads 6,158 comments 46 male Menlo Park, CA

   9   5:38pm Fri 26 Oct 2012   Share   Quote   Permalink   Like   Dislike (1)   Something about captcha's always bothered me. They seem unfriendly. Also, I've heard it's pretty easy to get around them by just making some porn site require the entry of a captcha, which is copies from the site you're trying to get into.

10. Dan8267 Follow Befriend (16) 764 threads 7,710 comments Boca Raton, FL Premium

    10   5:45pm Fri 26 Oct 2012   Share   Quote   Permalink   Like   Dislike (1)   Patrick says I've heard it's pretty easy to get around them by just making some porn site require the entry of a captcha, which is copies from the site you're trying to get into. lol. Crowdsourcing captcha's in exchange for porn. Priceless. Well, there is another way. You could make registration computationally intensive, say running a JavaScript function to factor two large primes. It would only take a few seconds on a browser, but automated registers won't want to execute the JavaScript and won't want to spend the time factoring the product.

11. Patrick Follow Befriend (54) 5,188 threads 6,158 comments 46 male Menlo Park, CA

    11   5:55pm Fri 26 Oct 2012   Share   Quote   Permalink   Like   Dislike   Tell me more. Do you have an example bit of code?

12. Dan8267 Follow Befriend (16) 764 threads 7,710 comments Boca Raton, FL Premium

    12   6:07pm Fri 26 Oct 2012   Share   Quote   Permalink   Like   Dislike (1)   http://arachnoid.com/prime_numbers/index.html Basically get a table of large primes from some math site, multiply two or more together to produce a very large number, and run the JavaScript from the above site (after removing the maximum value limitation) and it could take a few seconds to a few minutes of JavaScript execution to get the factors out. Use AJAX to send the result back to your server and compare with the original numbers you used in the product. If they match, accept the registration. Basically, it's like a captcha, but a machine can do it. No human intervention is required, but it's not worth spending computational time doing to register lots of accounts. At most, a human needs to keep a browser tab or window open for a few minutes while doing this verification. And since this only needs to be done once per legitimate user, it's not that inconvenient.

13. Dan8267 Follow Befriend (16) 764 threads 7,710 comments Boca Raton, FL Premium

    13   6:28pm Fri 26 Oct 2012   Share   Quote   Permalink   Like (1)   Dislike (1)   Speaking of spam...

14. Patrick Follow Befriend (54) 5,188 threads 6,158 comments 46 male Menlo Park, CA

    14   10:53am Sat 27 Oct 2012   Share   Quote   Permalink   Like   Dislike   Dan8267 says Basically, it's like a captcha, but a machine can do it. No human intervention is required, but it's not worth spending computational time doing to register lots of accounts. At most, a human needs to keep a browser tab or window open for a few minutes while doing this verification. And since this only needs to be done once per legitimate user, it's not that inconvenient. 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. If I could somehow easily charge users one cent to register, that would be perfect. The spammers won't pay even a cent, but most other people wouldn't care.

15. TechGromit Follow Befriend (6) 85 threads 969 comments 44 male Egg Harbor City, NJ

    15   7:20pm Sat 27 Oct 2012   Share   Quote   Permalink   Like   Dislike   I recommend SQL paste. That's how I fix my leaking SQL server issues.

16. Dan8267 Follow Befriend (16) 764 threads 7,710 comments Boca Raton, FL Premium

    16   7:28pm Sat 27 Oct 2012   Share   Quote   Permalink   Like   Dislike (1)   Patrick says 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 Follow Befriend (16) 764 threads 7,710 comments Boca Raton, FL Premium

    17   7:34pm Sat 27 Oct 2012   Share   Quote   Permalink   Like   Dislike (1)   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 Follow Befriend 3 threads 21 comments Hermosa Beach, CA

    18   7:26am Sun 28 Oct 2012   Share   Quote   Permalink   Like (1)   Dislike   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 Follow Befriend (54) 5,188 threads 6,158 comments 46 male Menlo Park, CA

    19   9:58am Sun 28 Oct 2012   Share   Quote   Permalink   Like   Dislike   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 Follow Befriend (16) 764 threads 7,710 comments Boca Raton, FL Premium

    20   12:52pm Sun 28 Oct 2012   Share   Quote   Permalink   Like   Dislike (1)   Patrick says 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 Follow Befriend 3 threads 21 comments Hermosa Beach, CA

    21   3:34pm Sun 28 Oct 2012   Share   Quote   Permalink   Like   Dislike   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 Follow Befriend (54) 5,188 threads 6,158 comments 46 male Menlo Park, CA

    22   5:56pm Sun 28 Oct 2012   Share   Quote   Permalink   Like   Dislike   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.

23. Melissa Follow Befriend 3 threads 21 comments Hermosa Beach, CA

    23   6:05pm Mon 29 Oct 2012   Share   Quote   Permalink   Like   Dislike   Patrick says So I'm pretty sure the spamming is not entirely automated. There must be humans working on it. Wow... That surprises me (obviously). I would have expected them to stick to things like Wordpress and other packaged systems, especially those not customized. Perhaps you have a spammer following. :-)