Database & table naming bug in MySQL driver of Moodle

Database & table naming bug in MySQL driver of Moodle

by Konrad Lorinczi -
Number of replies: 1
Using Moodle 2.2.17.
Currently doing a Moodle upgrade from 1.9.19 => 2.2.17 => 2.7 => 3.2, and I'm in the middle of upgrading & app development.

I get error, when using the following code (table doesn't exist or error in your SQL syntax):
$DB->delete_records($table);
It likely happens because I use the www.mymoodle.com as database name, and _mytable as table name.
Using dot in database name is non-conventional, but it is still valid name if it is quoted well using double quotes or backticks.


The problem is, that in /lib/dml/mysqli_native_moodle_database.php the emulate_bound_params() is called, which escapes & adds quote around the the strings:

                $param = $this->mysqli->real_escape_string($param);
                $return .= "'$param'";

This adds single quotes around the strings, which is not enough for non-conventional names.
Double quotes or backticks would be required in this case.


Current single quote solution results the following SQL code, which doesn't work for non-conventional database & table names (i.e: which has dot in the name):

TRUNCATE TABLE 'www.mymoodle.com'.'_mytable'

Which will point to non-existent database, as the db name is invalid, unless it is not double quoted.


The following SQL code works successfully in MySQL client. This would be the awaited result this for non-conventional database & table naming:

TRUNCATE TABLE `www.mymoodle.com`.`_mytable`


Could be possible to add support for dots in database & table names of MySQL driver?


I checked the Moodle git repo, and seems the code has the same bug in the latest code in master branch (current latest commit is weekly on-sync release 3.4dev).


Average of ratings: -
In reply to Konrad Lorinczi

Re: Database & table naming bug in MySQL driver of Moodle

by Konrad Lorinczi -
Quoting the MySQL docs:
https://dev.mysql.com/doc/refman/5.7/en/identifiers.html
An identifier may be quoted or unquoted. If an identifier contains

special characters or is a reserved word, you must quote it whenever you refer to it. (Exception: A reserved word that follows a period in a qualified name must be an identifier, so it need not be quoted.)
Reserved words are listed at Section 9.3, “Keywords and Reserved Words”. Identifiers are converted to Unicode internally. They may contain these characters:

  • Permitted characters in unquoted identifiers:

    • ASCII: [0-9,a-z,A-Z$_] (basic Latin letters, digits 0-9, dollar, underscore)

    • Extended: U+0080 .. U+FFFF

  • Permitted characters in quoted identifiers include the full Unicode Basic Multilingual Plane (BMP), except U+0000:

    • ASCII: U+0001 .. U+007F

    • Extended: U+0080 .. U+FFFF

  • ASCII NUL (U+0000) and supplementary characters (U+10000 and higher) are not permitted in quoted or unquoted identifiers.

  • Identifiers may begin with a digit but unless quoted may not consist solely of digits.

  • Database, table, and column names cannot end with space characters.

The identifier quote character is the backtick (`): 

mysql> SELECT * FROM `select` WHERE `select`.id > 100;

If the ANSI_QUOTES SQL mode is enabled, it is also permissible to quote identifiers within double quotation marks: 

mysql> CREATE TABLE "test" (col INT);
ERROR 1064: You have an error in your SQL syntax...
mysql> SET sql_mode='ANSI_QUOTES';
mysql> CREATE TABLE "test" (col INT);
Query OK, 0 rows affected (0.00 sec)

The ANSI_QUOTES mode causes the server to interpret double-quoted strings as identifiers. Consequently, when this mode is enabled, string literals must be enclosed within single quotation marks. They cannot be enclosed within double quotation marks. The server SQL mode is controlled as described in Section 5.1.8, “Server SQL Modes”.


Average of ratings: -