Create TABLE using PHP, according to input

Question

I am about to create a table, but I want to declare it based on the user's input. thankyou for any response, all answers are appreciated, more power!

I am receiving this error (Error creating table: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''2020-2021' ( id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, firstname VARCHAR' at line 1)

here's the sample code I am doing.

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "mias";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$table = $_POST['usersinput'];

// sql to create table
$sql = "CREATE TABLE $table (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)";

if ($conn->query($sql) === TRUE) {
    echo "Table MyGuests created successfully";
} else {
    echo "Error creating table: " . $conn->error;
}

$conn->close();
?>

This can be extremely dangerous as it is wide open to SQL injection. Using user input to a SQL statement is not recommended - especially as you cannot even use a prepared statement due to the table name being the input. — Nigel Ren, Dec 10 '19 at 07:39
Also do you really need to create a new table? Would you be better off using 1 database table with some form of identifier to associate the data with a particular user/set of data? — Nigel Ren, Dec 10 '19 at 07:40
So, did you mean? I must ready my tables until it's time to use? — CipherGhoul, Dec 10 '19 at 07:40
Yeah, I also tried to think of that, I am creating an enrollment system, and go for the basis of the school year for filtering them, but is it too much data just for one table? — CipherGhoul, Dec 10 '19 at 07:42
My plan is to programmatically create a table every school year. to insert students in every table in every school year — CipherGhoul, Dec 10 '19 at 07:44
How many rows are you expecting? It should cater for at least 16TB of data. — Nigel Ren, Dec 10 '19 at 07:45
I expect more than that, try to imagine students are enrolling every year, so let's expect that 16tb is lack — CipherGhoul, Dec 10 '19 at 07:47
you cannot use `-` in a table name. An underscore would be acceptable though — Professor Abronsius, Dec 10 '19 at 07:48
I tried putting manually in PHPMyAdmin the 2020-2021 table, it was inserted. — CipherGhoul, Dec 10 '19 at 07:50
considering that the table name was intended to be years ( from-to ) you can certainly construct a much better table schema to suit your needs — Professor Abronsius, Dec 10 '19 at 07:50
You really think you are going to need more than 16tb of data for a list of pupils? That should include most of the population of the world. — Nigel Ren, Dec 10 '19 at 07:52
Anyway - just try putting backticks round the table name `CREATE TABLE \`$table\`(` — Nigel Ren, Dec 10 '19 at 07:53
hehe, THANKYOU so much for the answers, Super Appreciated, also to sir Nigel Ren :) — CipherGhoul, Dec 10 '19 at 07:54
I think in a years time you might look back and think - `"hmm, I wish I had created a different schema"` - afterall each pupil just needs the year of enrollment against their name — Professor Abronsius, Dec 10 '19 at 07:55
backticks are effective, thankyou sir Nigel Ren, I also tried thinking other schemas, such as putting the pupils inside one table, and filter them in 1 table, but I have encountered a problem on that situation, so I will try this method if it's really effective — CipherGhoul, Dec 10 '19 at 08:01

score 0 · Answer 1 · answered Dec 10 '19 at 07:44

Try this code by replacing your code. It will work. i have tried. Problem in your last line of your code.

CREATE TABLE $table(
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30),
email VARCHAR(50),
reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP)

score 0 · Answer 2 · answered Dec 10 '19 at 07:46

As Nigel has said in the comment, it's definitely a bad idea to allow user input to create a table.

How I would think about doing this would be to use relationships between the Table Guests and the Table or Booking you want them to be added to.

You would just need to create two tables, one for the Booking and one for the Guests then in the Guests table, have a Booking_ID field which would contain the ID of the bookings the user should be added to.

This way, when you want to look for Guests for a specific table, you would be able to do SELECT * FROM MyGuests WHERE booking_id=[the booking id] and this would return the guests for that table.

Thank you sir, Appreciated :) creating the table is not for the user, it was for the Admin. — CipherGhoul, Dec 10 '19 at 07:53

score 0 · Answer 3 · answered Dec 10 '19 at 07:51

Like other users stated there are several reasons (most importantly security) not to do that, but if you really want it you have to use concatenation for your string:

Option

$sql = "CREATE TABLE {$table}(id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, firstname VARCHAR(30) NOT NULL, lastname VARCHAR(30), email VARCHAR(50), reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP)";
Option

$sql = "CREATE TABLE" . $table . "(id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, firstname VARCHAR(30) NOT NULL, lastname VARCHAR(30), email VARCHAR(50), reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP)";

Create TABLE using PHP, according to input

3 Answers3