How to create database dynamically on server?

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?

Schools should be represented by entries in a *table* not a whole new database for each one. — Quentin, Aug 21 '13 at 10:39
Ouch... create DB using form posted data??? What if I left the field open, or what if I'd posted _"users (someField VARCHAR(255)); --"_? Please google mysql injection, and note that the `mysql_*` extension is deprecated and will be removed — Elias Van Ootegem, Aug 21 '13 at 10:39
@zerkms - no, better to create new hardware node for every school. And, perhaps, new Internet :p — Alma Do, Aug 21 '13 at 10:39
You are using [an **obsolete** database API](http://stackoverflow.com/q/12859942/19068) and should use a [modern replacement](http://php.net/manual/en/mysqlinfo.api.choosing.php). You are also **vulnerable to [SQL injection attacks](http://bobby-tables.com/)** that a modern API would make it easier to [defend](http://stackoverflow.com/questions/60174/best-way-to-prevent-sql-injection-in-php) yourself from. — Quentin, Aug 21 '13 at 10:39
This definitely sounds like an XY Problem... http://www.perlmonks.org/?node_id=542341 — BenM, Aug 21 '13 at 10:40
@BenM: I think it's just more proof that the CTKI is the most error-prone and dangerous bit in software development... voted to close any answer will be insufficient to solve the aforementioned interface — Elias Van Ootegem, Aug 21 '13 at 10:42
test with root user, if problem solved it's a problem of access permissions. if not, try to read the error received by my sql on creating by mysql_error — Saeed, Aug 21 '13 at 10:48
@Quentin: Thank you for reply I have many tables like 'student_login', 'staff_login', 'attendance'... each of the new schools should have these — Adi, Aug 21 '13 at 10:48
No, they shouldn't. You should have one database. You should have one "user" table. That table should have a column which marks if a given user is a student or staff. It should have a column which is a foreign key on the schools table. — Quentin, Aug 21 '13 at 10:51
@Quentin Why shouldn't he create a new database per school? Don't assume you know how things should (or are required by law/policy) to run, give explanations and examples or ask for more information. — bendataclear, Aug 21 '13 at 11:02

Elias Van Ootegem · Accepted Answer · 2013-08-21T11:18:02.317

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

How to create database dynamically on server?

1 Answers1