Moodle
  1. Moodle
  2. MDL-22189

moodle produces warnings with postgresql >= 8.3 (ESTWARNING: nonstandard use of \' in a string literal at character)

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Duplicate
    • Affects Version/s: 1.9.8
    • Fix Version/s: None
    • Component/s: Database SQL/XMLDB
    • Labels:
      None
    • Database:
      PostgreSQL
    • Affected Branches:
      MOODLE_19_STABLE
    • Rank:
      5959

      Description

      Hi,

      I just upgrade to postgres 8.3. There are lots of warnings like:
      "ESTWARNING: nonstandard use of \' in a string literal at character"

      I guess that was discussed here before:
      http://moodle.org/mod/forum/discuss.php?d=132739

      Trevor Johnson wrote:
      "For Moodle 1.9.5 on PostgreSQL 8.4 you need to set "standard_conforming_strings" to off, which is the default for PostgreSQL 8.4.
      And then to turn the event log warnings off, set escape_string_warning to off, the default is on."

        Activity

        Hide
        Eloy Lafuente (stronk7) added a comment - - edited

        Hi Dennis,

        yes, problem is that until 8.4 PGSQL have allowed both backslash () and single quote (') to be used as escape chars. While the later is the SQL-standard one, we have been using the former since the beginning of Moodle (mainly to share all the DB code with MySQL without any transformation).

        Main problem is that right now, in the middle of the 1.9.x series I think it's too much risky to change that behavior, as far as it involves changing the way addslashes() work, review how 1.9.x underlying DB driver is performing with that escape char and review potential uses of custom escaping here and there, so I'm not sure if to perform that change is a good idea right now. Instead, configuring the server seems "safer".

        If you take a look to the configure_dbconnection() function (lib/dmllib.php), you' ll see that, for mssql and oracle, both using the single quote as escape char, we are doing this:

        /// Enable sybase quotes, so addslashes and stripslashes will use "'"
            ini_set('magic_quotes_sybase', '1');
        /// NOTE: Not 100% useful because GPC has been addslashed with the setting off
        ///       so IT'S MANDATORY TO CHANGE THIS UNDER php.ini or .htaccess for this DB
        ///       or to turn off magic_quotes to allow Moodle to do it properly
        

        So, perhaps, if you want to try it... you could add that code in the 'postgres7' section, and also set magic_quotes = off and play with it. If we get positive reports from some POSTGRES servers running it ok and without problems... we could add it upstream.

        Finally, note that under Moodle 2.0 we have changed the whole DB layer, and we are using proper prepared statements, so that escaping won't be a problem anymore.

        Hope this helps, ciao

        Show
        Eloy Lafuente (stronk7) added a comment - - edited Hi Dennis, yes, problem is that until 8.4 PGSQL have allowed both backslash () and single quote (') to be used as escape chars. While the later is the SQL-standard one, we have been using the former since the beginning of Moodle (mainly to share all the DB code with MySQL without any transformation). Main problem is that right now, in the middle of the 1.9.x series I think it's too much risky to change that behavior, as far as it involves changing the way addslashes() work, review how 1.9.x underlying DB driver is performing with that escape char and review potential uses of custom escaping here and there, so I'm not sure if to perform that change is a good idea right now. Instead, configuring the server seems "safer". If you take a look to the configure_dbconnection() function (lib/dmllib.php), you' ll see that, for mssql and oracle, both using the single quote as escape char, we are doing this: /// Enable sybase quotes, so addslashes and stripslashes will use "'" ini_set('magic_quotes_sybase', '1'); /// NOTE: Not 100% useful because GPC has been addslashed with the setting off /// so IT'S MANDATORY TO CHANGE THIS UNDER php.ini or .htaccess for this DB /// or to turn off magic_quotes to allow Moodle to do it properly So, perhaps, if you want to try it... you could add that code in the 'postgres7' section, and also set magic_quotes = off and play with it. If we get positive reports from some POSTGRES servers running it ok and without problems... we could add it upstream. Finally, note that under Moodle 2.0 we have changed the whole DB layer, and we are using proper prepared statements, so that escaping won't be a problem anymore. Hope this helps, ciao
        Hide
        Eric Merrill added a comment -

        After upgrading to postgres 8.4 (from 8.1), we were just getting too many messages flooding the log. I added that line to the postgres7 section, and it has been working fine - no issues to report so far.

        I'll report back when we go live across out 20,000 user install, but I think this would be a pretty minor change to make.

        thanks
        -Eric

        Show
        Eric Merrill added a comment - After upgrading to postgres 8.4 (from 8.1), we were just getting too many messages flooding the log. I added that line to the postgres7 section, and it has been working fine - no issues to report so far. I'll report back when we go live across out 20,000 user install, but I think this would be a pretty minor change to make. thanks -Eric
        Hide
        David Binney added a comment -

        Hey Guys,

        I was having the same problem so I just added the line and it seems to have solved it. I am just wondering though if i am deploy this change to production which is on the older version without any problem?

        <code>
        Index: ../../lib/dmllib.php
        ===================================================================
        — ../../lib/dmllib.php (revision 642)
        +++ ../../lib/dmllib.php (working copy)
        @@ -2327,6 +2327,7 @@
        break;
        case 'postgres7':
        $db->Execute("SET NAMES 'utf8'");
        + ini_set('magic_quotes_sybase', '1');
        break;
        case 'mssql':
        case 'mssql_n':
        <code>

        Show
        David Binney added a comment - Hey Guys, I was having the same problem so I just added the line and it seems to have solved it. I am just wondering though if i am deploy this change to production which is on the older version without any problem? <code> Index: ../../lib/dmllib.php =================================================================== — ../../lib/dmllib.php (revision 642) +++ ../../lib/dmllib.php (working copy) @@ -2327,6 +2327,7 @@ break; case 'postgres7': $db->Execute("SET NAMES 'utf8'"); + ini_set('magic_quotes_sybase', '1'); break; case 'mssql': case 'mssql_n': <code>
        Hide
        Petr Škoda added a comment -

        Hello, this was already fixed in 2.x versions, in 1.9 you need to change postgresql configuration to get rid of this warning, thanks for the report. Petr

        Show
        Petr Škoda added a comment - Hello, this was already fixed in 2.x versions, in 1.9 you need to change postgresql configuration to get rid of this warning, thanks for the report. Petr

          People

          • Votes:
            1 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: