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

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

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

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>

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