PHP / MySQL: Create Database from User Input

Question

I would like to create a database based on user input if that database doesn't exist. Problem is, I do not understand how to check whether the database exists or not.

Also another question is I wonder if the following code would work:

if (isset($_POST['companyName'])) {
   $companyName = $_POST['companyName'];
}

$query = "
    CREATE DATABASE 'companyName';
       USE 'companyName';
    CREATE TABLE users (
           ID int NOT NULL AUTO_INCREMENT,
           FirstName varchar(255),
           LastName varchar(255),
           user text,
           Password varchar(255),
           Email varchar(255),
           PRIMARY KEY (ID)
   );
";

$result = mysqli_query($conn, $query);

Because basically I typed the whole SQL code in and just query it, would that create any problem?

I'm not really experienced in PHP and MySQL so thank you for paying attention and answer my question in advance!

The real question here is 'Why?'. insert Ryan Reynolds meme here. But seriously talking, what is the scheme of your system why you want to create a database through user's input? — Logan Wayne
– Logan Wayne, Commented Jul 19, 2016 at 7:48
In terms of security, it's probably best to have a database already created and then simply add in the user data, even if it's the first record. Nothing is gained from a user being able to enter data into a web form and then indirectly running SQL code. — James Gould
– James Gould, Commented Jul 19, 2016 at 7:49
how to check : mysql_select_db('your_db_name') or die ('DB NOT FOUND'); — J. Zend
– J. Zend, Commented Jul 19, 2016 at 7:52
I need to have have unique database for each company, and in each company contain its user data. And in the database contain tables for the company. It's a two step sign up form, like they create a company or find their company, then sign up as a user within the company. I'm not sure if that makes sense, tell me if it's not, I will try to explain better, but is there a better way to do that? — Ronald Ng
– Ronald Ng, Commented Jul 19, 2016 at 7:56

Quynh Nguyen · Accepted Answer · 2016-07-19 07:56:54Z

You can try this

<?php
$servername = 'localhost';
$username = 'root';
$password = 'xxxxx';

$conn = new mysqli($servername, $username, $password);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
// Create database
$sql = "CREATE DATABASE myDB";
if ($conn->query($sql) === TRUE) {
    $conn =  mysqli_connect($servername, $username, $password,'myDB');
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    } 
    $query = "CREATE TABLE users
          (
            ID int NOT NULL AUTO_INCREMENT,
            FirstName varchar(255),
            LastName varchar(255),
            user text,
            Password varchar(255),
            Email varchar(255),
            PRIMARY KEY (ID)
          )";
    if ($conn->query($query) === TRUE) {
        echo "Table users created successfully";
    } else {
        echo "Error creating table: " . $conn->error;
    }
} else {
    echo "Error creating database: " . $conn->error;
}

$conn->close();
?>

My result

But I'm not recommend user can create new database in your sql server.

5eeker · Accepted Answer · 2016-07-19 07:52:30Z

0

You should first filter out the user inputs before putting to use in mysql queries. Use htmlspecialchars(), stripslashes() functions.

Before creating a database you should check if it exists. You can do it by using : CREATE DATABASE IF NOT EXISTS yourdb;

It is not advisable to create db and tables based on user inputs, but in case you have no other option, make sure to filter the user inputs.

answered Jul 19, 2016 at 7:52

5eeker

1,0371 gold badge9 silver badges31 bronze badges

2 Comments

CD001 Over a year ago

That's what prepared statements with bound parameters are for - as a general rule don't interpolate variables into SQL strings.

Your Common Sense Over a year ago

What on the Earth htmlspecialchars() and stripslashes() have to do with mysql ?

Collectives™ on Stack Overflow

PHP / MySQL: Create Database from User Input

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related