Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 2.5.1, 2.5.2
    • Fix Version/s: 2.4.8, 2.5.4, 2.6.1
    • Component/s: Reports
    • Labels:
    • Database:
      Oracle
    • Testing Instructions:
      Hide

      This needs to be tested on all databases

      1. Go to Site administration / ► Reports / ► Spam cleaner
      2. Click on autodetect spam button, make sure no error is generated.
      Show
      This needs to be tested on all databases Go to Site administration / ► Reports / ► Spam cleaner Click on autodetect spam button, make sure no error is generated.
    • Affected Branches:
      MOODLE_25_STABLE
    • Fixed Branches:
      MOODLE_24_STABLE, MOODLE_25_STABLE, MOODLE_26_STABLE
    • Pull Master Branch:
      MDL-41316-master
    • Sprint:
      BACKEND Sprint 7
    • Story Points (Obsolete):
      3
    • Sprint:
      BACKEND Sprint 7

      Description

      I’m using Oracle and when I click on Site administration / ► Reports / ► Spam cleaner I get this error:
      ORA-00933: SQL command not properly ended

      SELECT u.*, p.summary FROM d_user AS u, d_post AS p WHERE ( LOWER(p.summary) LIKE LOWER(:o_sumpat0) ESCAPE '\' OR LOWER(p.summary) LIKE LOWER(:o_sumpat1) ESCAPE '\' OR LOWER(p.summary)
      LIKE LOWER(:o_sumpat2) ESCAPE '\' OR LOWER(p.summary) LIKE LOWER(:o_sumpat3) ESCAPE '\' OR LOWER(p.summary) LIKE LOWER(:o_sumpat4) ESCAPE '\' OR LOWER(p.summary)
      LIKE LOWER(:o_sumpat5) ESCAPE '\' OR LOWER(p.summary) LIKE LOWER(:o_sumpat6) ESCAPE '\' OR LOWER(p.summary)
      LIKE LOWER(:o_sumpat7) ESCAPE '\' OR LOWER(p.summary) LIKE LOWER(:o_sumpat8) ESCAPE '\' ) AND u.deleted = 0 AND u.id=p.userid AND u.id <> :o_userid

      I resolved deleting aliases in table names changing admin/tool/spamcleaner/index.php in this way:

      @@ -179,12 +179,12 @@ function search_spammers($keywords) {
      $conditions7 = '( '.implode(' OR ', $keywordfull7).' )';

      $sql = "SELECT * FROM

      {user} WHERE deleted = 0 AND id <> :userid AND $conditions"; // Exclude oneself

      $sql2 = "SELECT u.*, p.summary FROM {user}

      u,

      {post} AS p WHERE $conditions2 AND u.deleted = 0 AND u.id=p.userid AND u.id <> :userid";
      $sql3 = "SELECT u.*, p.subject as postsubject FROM {user} u, {post}

      AS p WHERE $conditions3 AND u.deleted = 0 AND u.id=p.userid AND u.id <> :userid";
      $sql4 = "SELECT u.*, c.content FROM

      {user} u, {comments} AS c WHERE $conditions4 AND u.deleted = 0 AND u.id=c.userid AND u.id <> :userid";
      $sql5 = "SELECT u.*, m.fullmessage FROM {user}

      u,

      {message}

      m WHERE $conditions5 AND u.deleted = 0 AND u.id=m.useridfrom AND u.id <> :userid";
      $sql6 = "SELECT u.*, fp.message FROM

      {user} u, {forum_posts} fp WHERE $conditions6 AND u.deleted = 0 AND u.id=fp.userid AND u.id <> :userid";
      $sql7 = "SELECT u.*, fp.subject FROM {user}

      u,

      {forum_posts}

      fp WHERE $conditions7 AND u.deleted = 0 AND u.id=fp.userid AND u.id <> :userid";

        Gliffy Diagrams

          Activity

          Hide
          ankit_frenz Ankit Agarwal added a comment -

          Thanks Sara for reporting this issue and suggesting a patch. For future, if you can provide git branches that will help speed things quite a lot.

          Oracle doesn't allow using 'AS' for table alias and it is clearly mentioned in our guidelines http://docs.moodle.org/dev/Database

          Pushing for review.

          Thanks

          Show
          ankit_frenz Ankit Agarwal added a comment - Thanks Sara for reporting this issue and suggesting a patch. For future, if you can provide git branches that will help speed things quite a lot. Oracle doesn't allow using 'AS' for table alias and it is clearly mentioned in our guidelines http://docs.moodle.org/dev/Database Pushing for review. Thanks
          Hide
          rwijaya Rossiani Wijaya added a comment -

          Hi Ankit,

          Patch looks great.

          [y] Syntax
          [y] Whitespace
          [y] Output
          [-] Language
          [y] Databases
          [y] Testing (instructions and automated tests)
          [-] Security
          [-] Documentation
          [y] Git
          [-] Third party code
          [y] Sanity check

          You might want to re-check if the keyword 'as' for $sql3 needs to be capitalized.

          Feel free to push it for integration.

          Show
          rwijaya Rossiani Wijaya added a comment - Hi Ankit, Patch looks great. [y] Syntax [y] Whitespace [y] Output [-] Language [y] Databases [y] Testing (instructions and automated tests) [-] Security [-] Documentation [y] Git [-] Third party code [y] Sanity check You might want to re-check if the keyword 'as' for $sql3 needs to be capitalized. Feel free to push it for integration.
          Hide
          ankit_frenz Ankit Agarwal added a comment -

          Hi Rosie,
          Thanks for the review. I didn't want to change the coding style of the sql. But since you commented I have corrected the indention.

          Pushing forward.
          Cheers

          Show
          ankit_frenz Ankit Agarwal added a comment - Hi Rosie, Thanks for the review. I didn't want to change the coding style of the sql. But since you commented I have corrected the indention. Pushing forward. Cheers
          Hide
          stronk7 Eloy Lafuente (stronk7) added a comment -

          The main moodle.git repository has just been updated with latest weekly modifications. You may wish to rebase your PULL branches to simplify history and avoid any possible merge conflicts. This would also make integrator's life easier next week.

          TIA and ciao

          Show
          stronk7 Eloy Lafuente (stronk7) added a comment - The main moodle.git repository has just been updated with latest weekly modifications. You may wish to rebase your PULL branches to simplify history and avoid any possible merge conflicts. This would also make integrator's life easier next week. TIA and ciao
          Hide
          samhemelryk Sam Hemelryk added a comment -

          Thanks Sara + Raj - this has been integrated now.

          Show
          samhemelryk Sam Hemelryk added a comment - Thanks Sara + Raj - this has been integrated now.
          Hide
          samhemelryk Sam Hemelryk added a comment -

          LOL sorry Ankit - no Raj here!

          Show
          samhemelryk Sam Hemelryk added a comment - LOL sorry Ankit - no Raj here!
          Hide
          salvetore Michael de Raadt added a comment -

          Test result: Success!

          Tested in 2.4, 2.5 and on- sync master.

          Tested with MySQL, MSSQL (SQLSVR and FreeTDS), PostgreSQL and Oracle.

          I was able to achieve negative and positive results without errors.

          Show
          salvetore Michael de Raadt added a comment - Test result: Success! Tested in 2.4, 2.5 and on- sync master. Tested with MySQL, MSSQL (SQLSVR and FreeTDS), PostgreSQL and Oracle. I was able to achieve negative and positive results without errors.
          Hide
          stronk7 Eloy Lafuente (stronk7) added a comment -

          ...
          But still, I thank you, for you made me stronger…

          Stronger as the beast that roar in the wild
          Stronger as the storm across the ocean
          Stronger as the diamond that won’t break
          Stronger enough to take all heart aches….

          I thank you my friend, for you made me stronger…

          ---- Juneah Landicho

          Closing as fixed. Ciao

          Show
          stronk7 Eloy Lafuente (stronk7) added a comment - ... But still, I thank you, for you made me stronger… Stronger as the beast that roar in the wild Stronger as the storm across the ocean Stronger as the diamond that won’t break Stronger enough to take all heart aches…. I thank you my friend, for you made me stronger… ---- Juneah Landicho Closing as fixed. Ciao

            People

            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:
                Fix Release Date:
                13/Jan/14

                Agile