Prepared statement for adding tables to MySQL (PHP)

Question

This code works and adds tables to my database. My question is how do I protect it with prepared statements.

 require "conn.php";
    $MyServer =($_POST["username"]);
        $sql = ("CREATE TABLE $MyServer (
            id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
            username VARCHAR(30) NOT NULL
            )");
        if($conn->query($sql) === TRUE){
            echo "Table created successfully";
        }

I am using MySQLi. I tryed this and it isn't adding the table.

$MyServer =($_POST["username"]);
    if (!preg_match('^/[A-Za-z][A-Za-z0-9]{0,7}$/', $MyServer)) {
       throw new Exception ('username unsuitable for use as a table name');
    }
    $sql = ("CREATE TABLE `$MyServer` (
        id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
        username VARCHAR(30) NOT NULL
        )");
    if($conn->query($sql) === TRUE){
        echo "Table created successfully";
    } else {
        echo "Table is not created successfully ";
    }

protect what ? what you wanna protect ?you mean how you can avoid sql injections and all — Jok3r, Aug 05 '17 at 14:02
You are allowing users to create tables based on a `$POST` variable? Probably not the best plan. You'll want to sanitize / clean up the table name at least. — random_user_name, Aug 05 '17 at 14:03
Possible duplicate of [Can PHP PDO Statements accept the table or column name as parameter?](https://stackoverflow.com/questions/182287/can-php-pdo-statements-accept-the-table-or-column-name-as-parameter) — random_user_name, Aug 05 '17 at 14:04
@EgorCherkashin is the username already in another database table? — MinistryOfChaps, Aug 05 '17 at 14:05
why would you want to allow users to create tables in the database? Sounds like bad planning to me ~ what if `$_POST['username']='fred; drop database;';`? — Professor Abronsius, Aug 05 '17 at 14:05
I still cannot understand why you would think that having multiple tables ( per user ) is better than a correctly structured database schema — Professor Abronsius, Aug 05 '17 at 14:08
I want users to be able to save other users username and I need to be able to go through each of those users. — Egor Cherkashin, Aug 05 '17 at 14:10
Allowing people to create tables like that is a **horrible** idea. Even if there's a lot of information per user, they should all be stored in tables you create. That is much more secure, clean and structured. How much data is needed per user? — Qirel, Aug 05 '17 at 14:23
@EgorCherkashin The only way the selected answer will really work is if your usernames only have alphabetic and numeral characters only. Make sure that is the case with your usernames. — MinistryOfChaps, Aug 05 '17 at 14:27

score 1 · Accepted Answer · answered Aug 05 '17 at 14:14

I guess you're thinking about how to avoid SQL injection into your query via that $MyServer variable in your sample program.

You cannot use a parameterized value to name a table (or a database, or a column) in SQL. You must do the variable substitution shown in your program.

You can use php to sanitize your $MyServer variable before you use it for subsitution though.

For example: How to check, if a php string contains only english letters and digits?

You could do this, or something like it. This requires the variable to start with a letter, then contain up to seven more characters that are letters or digits. If the variable doesn't match it throws an exception.

if (!preg_match('^/[A-Za-z][A-Za-z0-9]{0,7}$/', $MyServer)) {
     throw new Exception ('username unsuitable for use as a table name');
}

score 0 · Answer 2 · answered Aug 05 '17 at 14:14

Although it is questionable whether it is a good plan to allow users to create tables, to answer your question:

First of all, make sure your variable doesn't contain any strange character. Although MySQL allows (a subset of) unicode characters, you probably only want normal letters and numbers:

if (
    !preg_match('/^[a-z0-9]+$/i', $MyServer)
    || preg_match(/^[0-9]+$/, $MyServer) // Identifiers may begin with a digit but unless quoted may not consist solely of digits.
    || strlen($MyServer) > 64 // Limit of table-name length
) {
    // Insert your own error handling
    die('Not allowed');
}

Second, to make sure SQL treat it as a identifier, quote it in backticks

$sql = "CREATE TABLE `$MyServer` (...etc..."

CREATE TABLE index (...etc... will raise an error in MySQL because index is a keyword

CREATE TABLE `index` (...etc...

wont, because it is marked as an identifier.

MinistryOfChaps · Answer 3 · 2017-08-05T14:24:36.873

Honestly, you are really opening yourself up to SQL injection here by getting the data from $_POST.

Your method is a definite no go unless your usernames are already stored in your database and do not have special characters that will lead to SQL injection (such as quotations).

EDIT: I see two answers above that compliment what I have said, you could use one of those two answers (O. Jones is my preferred one).

If you want your code to be a bit more in line with PDO procedure (binding PDO values to avoid SQL injection), why not create one table with columns (username, saved_username) and you can interact with the information in that table effectively with PDO statements.

For example, all you will have to do to insert data would be:

$query = 'INSERT INTO table (username, saved_username) VALUES (:username, :username_to_save)';
$stmt->bindParam(':username', $_POST['username']);
$stmt->bindParam(':username_to_save', $_POST['username_to_save']);
$stmt->execute()

And to select the data:

$query = 'SELECT * FROM table WHERE username = :username';
$stmt->bindParam(':username', $_POST['username']);
$stmt->execute()
$users_saved_usernames = $stmt->fetchAll();

Prepared statement for adding tables to MySQL (PHP)

3 Answers3