Ticket creation fails with captchas enabled and PostgreSQL

[eseifert@…]

Creating a new ticket with captcha usage enabled always leads to an internal server error. New tickets can be created again when disabling captcha usage.

System configuration: SpamFilter 0.4.7dev-r10711, Trac 0.12.2, PostgreSQL 8.4.7 backend, Python 2.7.1

Python backtrace:

Traceback (most recent call last):
  File "/usr/lib64/python2.7/site-packages/trac/web/main.py", line 511, in _dispatch_request
    dispatcher.dispatch(req)
  File "/usr/lib64/python2.7/site-packages/trac/web/main.py", line 237, in dispatch
    resp = chosen_handler.process_request(req)
  File "/usr/lib64/python2.7/site-packages/trac/ticket/web_ui.py", line 170, in process_request
    return self._process_newticket_request(req)
  File "/usr/lib64/python2.7/site-packages/trac/ticket/web_ui.py", line 396, in _process_newticket_request
    valid = self._validate_ticket(req, ticket)
  File "/usr/lib64/python2.7/site-packages/trac/ticket/web_ui.py", line 1182, in _validate_ticket
    for field, message in manipulator.validate_ticket(req, ticket):
  File "build/bdist.linux-x86_64/egg/tracspamfilter/adapters.py", line 77, in validate_ticket
    FilterSystem(self.env).test(req, author, changes, ip)
  File "build/bdist.linux-x86_64/egg/tracspamfilter/api.py", line 191, in test
    'potential spam %s' % msg)
  File "build/bdist.linux-x86_64/egg/tracspamfilter/captcha/api.py", line 72, in reject_content
    self._cleanup()
  File "build/bdist.linux-x86_64/egg/tracspamfilter/captcha/api.py", line 180, in _cleanup
    + "name == 'captcha_reject_time'))", (tc,t))
  File "/usr/lib64/python2.7/site-packages/trac/db/util.py", line 65, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
ProgrammingError: ERROR: operator does not exist: text < integer
LINE 1: ...aptcha%' AND (name != 'captcha_verified' OR value < 13083447...

HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts.

[Dirk Stöcker]

Could you try this patch? Does it help?

--- tracspamfilter/captcha/api.py       (Revision 10711)
+++ tracspamfilter/captcha/api.py       (Arbeitskopie)
@@ -172,10 +172,10 @@
         db = self.env.get_db_cnx()
         cursor = db.cursor() 
         cursor.execute("DELETE FROM session_attribute WHERE name LIKE " \
-            + "'captcha%' AND (name != 'captcha_verified' OR value < %s) " \
+            + "'captcha%' AND (name != 'captcha_verified' OR CAST(value AS INT) < %s) " \
             + "AND ("\
             + "sid IN (SELECT sid FROM session_attribute WHERE name " \
-            + "== 'captcha_reject_time' AND value < %s) "\
+            + "== 'captcha_reject_time' AND CAST(value AS INT) < %s) "\
             + "OR sid NOT IN (SELECT sid FROM session_attribute WHERE " \
             + "name == 'captcha_reject_time'))", (tc,t))
         db.commit()

[eseifert@…]

I just tried the patch but, sorry, this leads to a new PostgreSQL error:

ERROR:  operator does not exist: text == unknown at character 184
HINT:  No operator matches the given name and argument type(s). You may need to add explicit type casts.
STATEMENT:  DELETE FROM session_attribute WHERE name LIKE 'captcha%' AND (name != 'captcha_verified' OR CAST(value AS INT) < 1308386421) AND (sid IN (SELECT sid FROM session_attribute WHERE name == 'captcha_reject_time' AND CAST(value AS INT) < 1308472701) OR sid NOT IN (SELECT sid FROM session_attribute WHERE name == 'captcha_reject_time'))

The mentioned character 184 is the comparison operator in name == 'captcha_reject_time'.

[Dirk Stöcker]

Actually this is totally strange. name is text and 'captcha_reject_time' should also be text.

I have sqlite and it works fine. Can you try to manual test and fix the corresponding select statement to the delete?

SELECT * FROM session_attribute WHERE name LIKE 'captcha%' AND (name != 'captcha_verified' OR CAST(value AS INT) < 1308386421) AND (sid IN (SELECT sid FROM session_attribute WHERE name == 'captcha_reject_time' AND CAST(value AS INT) < 1308472701) OR sid NOT IN (SELECT sid FROM session_attribute WHERE name == 'captcha_reject_time'))

Maybe == is not accepted, but only a single =? Does this help?

[eseifert@…]

Thanks! Yes, replacing == by = in the SQL statement solved the SQL error. Here's the patch, including your previous changes:

--- tracspamfilter/captcha/api.py       (revision 10711)
+++ tracspamfilter/captcha/api.py       (working copy)
@@ -172,10 +172,10 @@
         db = self.env.get_db_cnx()
         cursor = db.cursor() 
         cursor.execute("DELETE FROM session_attribute WHERE name LIKE " \
-            + "'captcha%' AND (name != 'captcha_verified' OR value < %s) " \
+            + "'captcha%' AND (name != 'captcha_verified' OR CAST(value AS INT) < %s) " \
             + "AND ("\
             + "sid IN (SELECT sid FROM session_attribute WHERE name " \
-            + "== 'captcha_reject_time' AND value < %s) "\
+            + "= 'captcha_reject_time' AND CAST(value AS INT) < %s) "\
             + "OR sid NOT IN (SELECT sid FROM session_attribute WHERE " \
-            + "name == 'captcha_reject_time'))", (tc,t))
+            + "name = 'captcha_reject_time'))", (tc,t))
         db.commit()

Now, the only problem left for me is, I don't see any captcha in the ticket form. That's probably another ticket, but I'll look into that myself first.

[Remy Blank]

Replying to dstoecker:

Could you try this patch? Does it help?

You should use the cast()​ method of the database connection for casting, as it's backend-specific (and use a lowercase int as the type).

[Dirk Stöcker]

Replying to rblank:

You should use the cast()​ method of the database connection for casting, as it's backend-specific (and use a lowercase int as the type).

Can you supply a patch? I was happy to get this working at all and don't really know what you are talking about.

[Dirk Stöcker]

Replying to eseifert@…:

Now, the only problem left for me is, I don't see any captcha in the ticket form. That's probably another ticket, but I'll look into that myself first.

You wont see a captcha unless you enter SPAM. Spamfilter only uses captcha when required points have not been reached. With a proper trained filter entering the name of the blue pills usually leads to proper test results :-)

[Remy Blank]

Replying to dstoecker:

I was happy to get this working at all and don't really know what you are talking about.

See for example how it's done for ticket reports​.

[Dirk Stöcker]

In r10724.

[Dirk Stöcker]

Fixed in r10725.