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?

Answer 1

1. Intro

I understand that this is a really old post (2013) and I am commenting on 2024, but I am going to write a reply anyway, for anyone new looking to do something similar to this.

I also want to mention that people here are recommending not to do what the OP is wanting, for the wrong reasons. From what I understand, you want each school to have its data separated. And that's not bad to try and implement.

---

2. When & Why you would need this

You might want to do something like this, for privacy reasons, or perhaps, due to limitations opposed by your hosting provider. For example, with Hostinger, you can have unlimited databases with 100 GB total storage for their premium plan, but each Database is capped at 3GB each. So if one school (in this case), has a lot of data, you would want to create individual databases for each school so that the limit is not reached/exceeded.

---

3. Main issue with this approach (Spoiler Alert: It's NOT because of SQL Injection)

The main issue I find with this, is not SQL Injection or anything of the sort, as you can easily prevent that with PHP. There are methods you can wrap your data with, to secure your fields, you can even create an API to handle this for you and perform validation checks before running the query.

I would say the main issue is with database management after a database has been created. Even if a customer created a new database by adding these details in, they would not be able to have secure access to the database without some sort of account, user, database name and password. And exposing those details about the database is a recipe for disaster.

---

You can't just create a database and call it a day! You need to ensure it is secure and protected from outside resources.

---

4. My recommendation for a solution

I believe the best scenario for something like this, would be to create 2 products; (1) super admin dashboard, and (2a) admin dashboard or (2b) customer dashboard.

4.1. How would this look like

The super admin dashboard would be responsible for managing all created databases per school. It would have its own database, to store other database's details (encrypted for extra security).

Users, instead of filling in the database name on a form, would instead provide you with details about their school (e.g. name, address, etc). Based on those details you would use your super admin dashboard to create and manage that database.

You would then also need an API that can be called from the customer dashboard, with an authentication token, and if the token is valid, then you would authorize the user to access data from the database to their customer dashboard.

The super admin dashboard, would also need to be hosted somewhere secure, with a sign-in and 2FA (if possible). It could even be hosted on your own machine (as you are the only one that will be accessing it), and the database could also live in your machine, or can be hosted and exposed to your Provider's IP Address.

4.2. Additional things you could implement

You can even go further and implement some sort of DNS management on your super admin dashboard, so that this can become a proper SaaS product where your users purchase the service, and you provide them with a domain, a new database, and an admin dashboard. All managed from your super admin dashboard.

---

5. My personal opinion

I believe this would be the best approach for this scenario, as I agree with the statement that "users should not be creating their own database" to use your product.

I hope this helped.

Answer 2

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