SQL queries like UPDATE...SET with multiple quoted variables are parsed incorrectly (e.g. in Pre Wash)

[jwcatau]

When using multiple sets of quotes, such as in the pre wash SQL, the SQL parser removes from the first quote to the third quote, breaking the query and/or potentially resulting in unexpected queries being executed.

Example:

cleaner_custom_sql_pre | sql:

UPDATE mdl_oauth2_issuer SET clientid = '', clientsecret = '' WHERE id = 1;

When executed, it fails with an error like:

UPDATE mdl_oauth2_issuer SET clientid = '', clientsecret = '' WHERE id = 1;
Database transaction aborted automatically in /var/www/site/local/datacleaner/cli/clean.php
"Default exception handler: Error writing to database Debug: ERROR:  unterminated quoted string at or near ""' WHERE id = 1"""
LINE 2: UPDATE mdl_oauth2_issuer SET clientid = $1' WHERE id = 1 
                                                  ^
'"UPDATE mdl_oauth2_issuer SET clientid = $1' WHERE id = 1 
[array (
"  0 => '\', clientsecret = '," 
)]
Error code: dmlwriteexception
* line 497 of /lib/dml/moodle_database.php: dml_write_exception thrown
* line 293 of /lib/dml/moodle_read_slave_trait.php: call to moodle_database->query_end()
* line 341 of /lib/dml/pgsql_native_moodle_database.php: call to pgsql_native_moodle_database->read_slave_query_end()
* line 846 of /lib/dml/pgsql_native_moodle_database.php: call to pgsql_native_moodle_database->query_end()
* line 387 of /local/datacleaner/classes/clean.php: call to pgsql_native_moodle_database->execute()
* line 43 of /local/datacleaner/cleaner/custom_sql_pre/classes/clean.php: call to local_datacleaner\clean::execute_sql()
* line 160 of /local/datacleaner/cli/clean.php: call to cleaner_custom_sql_pre\clean::execute()
!!! Error writing to database !!!

WORKAROUND: Split the query into multiple UPDATEs, e.g.:

UPDATE mdl_oauth2_issuer SET clientid = '' WHERE id = 1;
UPDATE mdl_oauth2_issuer SET clientsecret = '' WHERE id = 1;