Permitting Special Characters in MySQL tablenames

[ArizonaJohn]

Hello,

I would like to allow users to add tables to my database with special characters in the table names. The characters I would like to allow include #, &, *, and +.

Below is my code which allows the users to create tables, and the name is the variable "$name". How can I allow them to use special characters for the table names?

Thanks

if (isset($_POST['name']) && !empty($_POST['name'])) {
mysql_connect("mysqlv10", "username", "password") or
die(mysql_error());
mysql_select_db("database") or die(mysql_error());
 
$table = mysql_real_escape_string($_POST['name']);
 
$query = "CREATE TABLE `$table` (id INT(11) NOT NULL auto_increment, site VARCHAR(150) NOT NULL, votes_up BIGINT(9) NOT NULL, votes_down BIGINT(9) NOT NULL, PRIMARY KEY(id), UNIQUE (site))";

[Atli]

Hi.

That query should work fine.
If you enclose the table name is back-ticks (`), you can safely put special characters into it.

For example:

mysql> CREATE TABLE `#&*+`(id serial primary key);
Query OK, 0 rows affected (0.03 sec)
 
mysql> INSERT INTO `#&*+` VALUES (),(),();
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
 
mysql> SELECT * FROM `#&*+`;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

There are some limitations to this tho.
See 8.2. Schema Object Names for details on what is allowed.