Create mysql table from php variable

Question

I'm attempting to create a mysql table based on a variable from php but it fails without any explanation.

$name = mysql_real_escape_string($_POST['name']);
mysql_query("CREATE TABLE `".$name."` ( name VARCHAR(30), type VARCHAR(30), style VARCHAR(30))");

When I set $name = test; it then works but not with the variable fetching attached to it.

I've looked at this link among others and they all say it should work but from what I'm seeing it doesn't.

Update:

I've also tried the code below but that takes the page offline.

$variable=$_POST['name'];
mysqli_connect("localhost", "devices", "a") or die(mysql_error()); 
mysqli_select_db("devices") or die(mysqli_connect_error()); 
mysqli_query("CREATE TABLE $variable ( computer text, mac text, windows text)");

I'm running PHP version 5.5.36

1. [Stop using mysql_ functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) as they've been removed from PHP. 2. Are you sure your user in MySQL has permissions to do a `CREATE` statement? — Machavity, Oct 13 '16 at 18:28
There is no explanation because you have no code to show one. Look into the mysql_error function. Also you will be blasted with folks pointing out you use MySQL which is outdated and not MySQLI or PDO — Duane Lortie, Oct 13 '16 at 18:30
The user has permissions to do a create statement. What would you suggest I use instead of mysql functions? — Number1, Oct 13 '16 at 18:31
@Number1 [The PHP Manual has suggestions](http://php.net/manual/en/function.mysql-query.php) — Machavity, Oct 13 '16 at 18:33
@DuaneLortie I believe the reason I can't use mysqli is that I'm running php 5.5.36 — Number1, Oct 13 '16 at 18:56
Taking arbitrary user data and putting it in your query is a [SQL injection hole](http://bobby-tables.com/). You *need* to test this `$variable` value to ensure it's not hostile, and is a valid table name. — tadman, Oct 13 '16 at 20:50
Yeah, well, it's habits like that which have a way of showing up in production code where that's not the case. Be careful. — tadman, Oct 17 '16 at 06:43

Lucas Desouza · Answer 1 · 2016-10-13T19:18:59.430

1

You can use htmlspecialchars($_POST['name'],ENT_QUOTES); to clean the name.

Also you should be using PDO or MySQLi since MySQL is out dated and not supported anymore.

If you want to see the error use this:

mysql_query("CREATE TABLE `".$name."` ( name VARCHAR(30), type VARCHAR(30), style VARCHAR(30))");

echo mysql_errno() . ": " . mysql_error() . "\n";

Update:

MySQLi is available in PHP 5

if you plan on using MySQLi:

$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

if ($mysqli->connect_errno) {
    echo("Connect failed: %s\n", $mysqli->connect_error);
    exit();
}

$variable=$_POST['name'];

if ($mysqli->query("CREATE TABLE $variable ( computer text, mac text, windows text)") === TRUE) {
    echo("Table successfully created.\n");
}

edited Oct 13 '16 at 19:18

answered Oct 13 '16 at 18:44

Lucas Desouza

156
8

1

Why use `mysqli` over PDO? PDO is better in almost every conceivable way. Also you don't want to take in an arbitrary `$_POST` variable and then jam it in your query. That's super risky. – tadman Oct 13 '16 at 20:48
@Lucas That doesn't seem to work. From what I can tell it has something todo with how the $variable is being added to the mysqli->query call. When I take out the $ and make it a standard word so to speak it works... – Number1 Oct 14 '16 at 01:01
What error do you get back? if you dont get an error, build the query in a separate variable `$sql = "CREATE TABLE $variable ( computer text, mac text, windows text)";` then echo it out. `echo $sql;` This will let us see what is being run the post it here for me to see. Without an error or at least the query its anyone's guess what is wrong here. – Lucas Desouza Oct 14 '16 at 16:38
@LucasDesouza When using this code`$sql = "CREATE TABLE ".$name." ( computer text, mac text, windows text)"; echo $sql;`, I get an output of `CREATE TABLE ( computer text, mac text, windows text)` When I comment out this line `echo("Connect failed: %s\n", $mysqli->connect_error);` the colon from the line farther down shows up and nothing else. With the line enabled I get nothing. – Number1 Oct 17 '16 at 18:14

score 0 · Accepted Answer · edited May 23 '17 at 12:33

I don't exactly understand 100% why but it all comes down to the line of code below.

$variable=$_POST['name'];

When I switched from TextWrangler over to NetBeans to help with syntax checking, I found I was getting the error Warning “Do not Access Superglobal $_POST Array Directly”

Whereby I changed over to using filtering and not directly accessing the $_POST array which solved the problem.

$variable=filter_input(INPUT_GET, 'name');

On another note, I did swap over to MySQLi as MySQL is outdated. Thanks Lucas Desouza.

Create mysql table from php variable

2 Answers2