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

问题描述：

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?

我正在开展一个项目，当用户输入信息并点击“添加学校”时，一个新的数据库学校名称应该在服务器上创建。 p>

我尝试过这样的事情 - p>

 ＆lt;？php 
 $ con = mysql_connect  （'localhost'，'root'，''）或死（'连接失败'）; 
 $ query =“CREATE DATABASE`”。$ _ POST ['school_name']。“`”;  //'school_name'从表单中获取
 
如果（mysql_query（$ query，$ con））
 echo“数据库创建成功”; 
否则
 echo“数据库创建失败”; 
？＆gt;  
  code>  pre> 
 
 代码在本地计算机上的XAMPP服务器上正常工作。 但是当我将它上传到服务器时，数据库创建总是失败。 p> 
 
 
注意：我在服务器上授予了用户的所有权限（但是权限列表中没有选项，如'CREATE DATABASE'  ） p> 
 
 
有没有办法做到这一点？ p> 
  div>

答

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

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

相关推荐