Creating a mysql database with "." in the name (using php)

Question

I've been having some trouble recently with trying to automate new database creations with a php script. Basically, the script takes the new login username and creates a database (and then insert some tables and data later on, which is also done via a php script). I used to have to manually create the database, but now need to make it automated.

The issue is that I used to be able to just create a new database using the phpadmin "new database" function from the web GUI and put in names like "test1.siteA", "userb.siteB".

However, now that I've tried to do the same via php script, it keeps giving me the "You have an error in your syntax..." from my last "echo".

Main parameters are:

$name = $user->username;
$servernm  = 'localhost';
$usnm = 'user';
$pasd  = 'user';
$dbname  = $name;
$dbname .= '.site';

I've found that the error would disappear once I remove the .site part from the code (it still exist even if I combine the $dbname into 1 line).

According to some articles that I've found online, it seems that MySQL doesn't allow special characters like "." to be included in the database name.

It just seems very weird to me that the ".site" can be added manually through phpMyadmin while the php/mysqli script doesn't allow this.

The full script is as follows (I'm sure it can be heavily improved, so any suggestions regarding that are also welcome):

<?php
define("_VALID_PHP", true);
require_once(APPPATH. "/libraries/init.php");
include (BASEPATH . "/database/DB_temp.php");
$row = $user->getUserData();

$name = $user->username;
$servernm  = 'localhost';
$usnm = 'user';
$pasd  = 'user';
$dbname  = $name;
$dbname .= '.site';


// Create connection
$conn = mysqli_connect($servernm, $usnm, $pasd);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

// Check if DB exist 
$sql = "SELECT count(SCHEMA_NAME) FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = '$dbname'";

$check = mysqli_query($conn,$sql)
or die("Connection failed: " . mysqli_connect_error()); 

while($row = mysqli_fetch_array($check,MYSQLI_NUM))
       {
        $dbval = $row[0];
    }   

if ($dbval == "0") 
{
    $createsql = "CREATE DATABASE '$dbname' ";
}
if ($dbval == "1") 
{
    $createsql = "SELECT count(SCHEMA_NAME) FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = '$dbname'";
}
if (mysqli_query($conn, $createsql)) {
Echo "Completed. DBVAL= " .$dbval ;
}
else
{
echo "Error creating database: " . mysqli_error($conn); 
}
?>

PHP version: 5.6.18 phpmyadmin: 4.5.4.1 Ubuntu 14.04

Apologies if I've made some posting errors on here. Do let me know about them and I'll try to correct it as much as I can. Any help is greatly appreciated!

If you don't care for yourself, think of other devs who will have to support your code with such a weird naming. — Your Common Sense, Jul 05 '16 at 16:09
@aynber Thank you! That pretty much explains the situation. I didn't realise that before. Will close the question now. — Nax.S, Jul 05 '16 at 16:24
@YourCommonSense Would be a great bit of advice if you also suggest on how I should be changing that. I'm not sure whether I'll be able to change the whole structure as I'm only working on a part of it, but if it really needs changing then I'll definitely try to. — Nax.S, Jul 05 '16 at 16:25

score 5 · Answer 1 · answered Jul 05 '16 at 16:09

5

. is a meta character in SQL, use to separate db/table/field names:

SELECT foo.bar.baz FROM sometable
         ^---------- database 'foo'
            ^------- table 'bar'
                ^--- field 'baz'

You should NOT be using metacharacters in any identifiers. It just leads to pain later on, and having to do stuff like:

SELECT `foo.bar`.baz.qux FROM ...
       ^^^^^^^^^--------- database 'foo.bar'
                  ^------ table 'baz'
                      ^-- field 'qux'

So you can use backticks if you absolutely have to, but you shouldn't be doing this in the first place.

answered Jul 05 '16 at 16:09

Marc B

356,200
43
426
500

Thank you. I'll try that out. However, with regards to not recommending to use ".", what would be the appropriate way to separate the 2nd part of the database names (in my case, by site?) – Nax.S Jul 05 '16 at 16:13
Assuming DB `foo`, table `bar`, and field `baz`, the syntax is actually like this: `SELECT baz FROM foo.bar` (used to select items from a database other than the current one) or `SELECT bar.baz FROM foo.bar` (used to disambiguate tables with identical column names.) Regardless, your diagnosis and solution are correct. – miken32 Jul 05 '16 at 16:21
@Nax.S: use an `_` underscore or something. Here's what mysql allows: http://dev.mysql.com/doc/refman/5.7/en/identifiers.html – Marc B Jul 05 '16 at 16:25

score 1 · Answer 2 · answered Jul 05 '16 at 16:09

1

try wrapping the database name with back ticks.

$dbname .= '`.site`';

answered Jul 05 '16 at 16:09

Adam Erstelle

2,454
3
21
35

Creating a mysql database with "." in the name (using php)

2 Answers2