up vote 1 down vote favorite

I'm trying to create a table whose name is the value of what is stored inside the variable $name. I have tried numerous different methods but none seem to work for me. Here is the code I am using currently:

 mysql_connect("localhost", "peltdyou_admin", "123456") or die(mysql_error()); 
 mysql_select_db("peltdyou_orders") or die(mysql_error()); 
 mysql_query("CREATE TABLE '" .$_POST['name']. "' ( name VARCHAR(30), age INT, car VARCHAR(30))");

I know it is something to do with '" .$_POST['name']. "' but I can't work out what. I have tried '$name' in its place which gets it's value from further up in the code.

Any help would be great!

share|improve this question
1  
Have you tried outputting the SQL you're generating, so you can be sure it contains what you think it contains? – andrewsi Jun 12 '12 at 19:48
3  
Never use POST data directly in any mySQL queries. – Hidde Jun 12 '12 at 19:48
1  
To my knowledge, MySQL does not require quotes around the table name. Have you tried removing the single quotes? – watcher Jun 12 '12 at 19:48
@Hidde, or GET, or COOKIE, or any data that cannot be trusted. – Brad Jun 12 '12 at 19:49
Check privileges for your sql user – scriptin Jun 12 '12 at 19:49
show 3 more comments

4 Answers

up vote 3 down vote accepted

Use backticks around table name, not quotes. And escape the input! Also, while this works on localhost, make sure that the user running on your production server has the privilege to CREATE tables (usually it's not, AFAIK, on shared hostings of course).

A word of warning: are you really sure you want to create a table on a user input?? how many tables are you going to create in this way? Can't you just redesign the whole thing so that you insert values instead?

$name = mysql_real_escape_string($_POST['name']);
mysql_query("CREATE TABLE `".$name."` ( name VARCHAR(30), age INT, car VARCHAR(30))");
share|improve this answer
+1 for not knowing about the backticks ;) – Gerep Jun 12 '12 at 19:55
Perfect! Works like a charm, thank you. I will accept the answer in 4 minutes – user1449737 Jun 12 '12 at 19:56
The tables will be created by an administrator when adding new clients to the database. So it won't be spammed – user1449737 Jun 12 '12 at 20:03
I'd suggest adding a client in a "client" table, as a new row, not creating a new table... – Damien Pirsy Jun 12 '12 at 20:08
up vote 2 down vote

Put it in another variable and it will work, there's a conflict with the "'" character in the POST variable and in the mysql_query.

<?php
mysql_connect("localhost", "peltdyou_admin", "123456") or die(mysql_error()); 
mysql_select_db("peltdyou_orders") or die(mysql_error()); 
$name = mysql_real_escape_string($_POST['name']);
mysql_query("CREATE TABLE '$name' ( name VARCHAR(30), age INT, car VARCHAR(30))");
?>

I posted this code to help you in your code but you should not use the mysql_* functions you should use the mysqli_* functions. You can read more about them here: http://php.net/manual/en/book.mysqli.php

share|improve this answer
Do prepared statements work with identifiers? I thought they worked only for binding values – Damien Pirsy Jun 12 '12 at 19:56
You are right they don't! If you look at php.net/manual/en/mysqli.prepare.php it tell you :P I will edit that out. That was a mistake sorry!!! – Frank_Hemsworth Jun 12 '12 at 20:02
up vote 1 down vote 
 mysql_connect("localhost", "peltdyou_admin", "123456") or die(mysql_error()); 
 mysql_select_db("peltdyou_orders") or die(mysql_error()); 

 //prevent injection:
 $name = mysql_real_escape_string($name);

 $query = <<<SQL
CREATE TABLE `{$name}` (name VARCHAR(30), age INT, car VARCHAR(30));
SQL; 

if ( mysql_query($query) ) {
 //success
} else {
 //error
}
share|improve this answer
up vote 1 down vote

You should really be using PDO or MySQLi instead of mysql_* functions. mysql_* functions are in the process of being deprecated and they are full of security holes.

With that said you don't need to quote your table name and instead should use nothing or backticks.

share|improve this answer
How do you bind a variable to an identifier using PDO or mysqli? – Damien Pirsy Jun 12 '12 at 20:00
Your query would look like CREATE TABLE :name (name VARCHAR(30), age INT, car VARCHAR(30) and then $stmt->bindParam(":name", $_POST['name']); For a full example you will want to find a good PDO tutorial that will give you all the basics. – Cody Covey Jun 12 '12 at 20:29
Are you really sure? The docs say otherwise. ALso, read this: stackoverflow.com/questions/182287/… – Damien Pirsy Jun 12 '12 at 20:39
Oh yeah you are right identifiers cannot be done with bindParam. I am unsure why you down voted my answer however since the answer makes no mention of binding parameters... – Cody Covey Jun 12 '12 at 21:05
You're right, I misread your answer :). I made a small edit so I could revert my downvote, again sorry! – Damien Pirsy Jun 12 '12 at 21:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.