如何在服务器上动态创建数据库？

Question

I am working on a project in which when user enters information and clicks 'Add School', a new database having the name of school should be created on server.

I tried something like this-

<?php
    $con = mysql_connect('localhost', 'root', '') or die('Connection failed');
    $query = "CREATE DATABASE `".$_POST['school_name']."`"; //'school_name' is fetched from form

    if(mysql_query($query, $con))
      echo "Database created successfully";
    else
      echo "Database creation failed";
?>

The code works fine on XAMPP server on local machine. But when I upload it to the server, database creation always fails.

Note: I granted all rights to user on server (but there is no option in the rights list like 'CREATE DATABASE')

Is there any way to do this?

Answer 1

Well, to be perfectly honest, I voted to close, because the way you're going about this is just a recipe for disaster. You can't go ahead and create a new database for every school. What you really shouldn't be doing is use a deprecated extension. What you're asking is similar too: "Hey, I've put convicted paedophiles to work in a nursery, but there are complaints... How shoudl I handle them?". The only right answer is to not do what you're doing.

For a kick-off: You're assuming people will nicely put in a valid DB name in the form... They could exploit the form and enter something like:

tbl`(
   id INT AUTO_INCREMENT PRIMARY KEY,
   field_name1 VARCHAR(255) NOT NULL PRIMARY KEY, -- normal fields
   field_name2 INTEGER(11) NOT NULL PRIMARY KEY,
   field_name3 VARCHAR(255) NOT NULL DEFAULT '',
   inserted TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   updated TIMESTAMP NOT NULL DEFAULT 0
                          ON UPDATE CURRENT_TIMESTAMP
)ENGINE = INNODB
 CHARACTER SET utf8 COLLATE utf8_general_ci; --

Which is valid SQL, but rather messes up your query. This assumes the user knows about injection, though. Even if they don't, what makes you think that any school name is a valid DB name? Suppose a school like "Institute of Numbskulls" or, a non-fictional one: "M.I.T". Of course that won't work as a DB name!

What you should do is have a simple table, called schools, that looks like this:

+----------+----------+
|    id    |   name   |
+----------+----------+
|    12    |  foobar  |
|    15    |  M.I.T.  |
+----------+----------+

Now every school has its own unique id, in all your other tables, like student login, add a field school_id:

//tbl student_login
+--------------------+--------------------+--------------------+--------------------+
|         id         |      school_id     |       login        |        passw       |
+--------------------+--------------------+--------------------+--------------------+
|         1          |          12        |       Bobby        |         hash       |
|         2          |          15        |       Bobby        |         hash       |
+--------------------+--------------------+--------------------+--------------------+

Now every record of every student is linked to the correct school. What's more, you can now use all the goodies a DB offers, like foreign keys, stored procedures, views and what have you... Make the tables relational!

If you want to get all student logins for foobar students: Easy:

SELECT students.login FROM schools
    LEFT JOIN studend_login AS students
    ON schools.id = studends.school_id
WHERE schools.name = 'foobar';

You can create a view per school, so that you don't have to keep on writing the same join over and over, if you really want...
If a school should happen to close/not require your services any longer, simply delete it from the schools table, and thanks to some cleverly placed foreign keys you can set your tables so, that all records in the other tables that link back to that school are deleted, or updated (soft-delete), too.

A quick google for "Relational design mysql", brought me here, I haven't read through it, but the diagrams do show what relational design is about: more tables, containing less data, but with proper indexes and keys