How can I create a Table whose name is based on a form input?

Question

I'm trying to build a widget for a client which would allow them to create a new table without logging into the phpMyAdmin, but I'm failing.

I can't figure out how to take some text that the client would input in page 1 and use it to create the table name in page 2.

Help?

---------------------------------------------------------
PAGE 1
---------------------------------------------------------

<html>
<h2>Create Table</h2> 
</br>
<form action="/create_reg.php" method="post">
Table Name:<input type="text" name="title" />
</br>

<input type="submit" value="Create Table" />

</form>
</html>

---------------------------------------------------------
PAGE 2
---------------------------------------------------------

<?php

$con = mysql_connect("localhost","database","password");
if (!$con)
 {
 die('Could not connect: ' . mysql_error());
  }

//CREATE TABLE
mysql_select_db("database", $con);

$sql = "CREATE TABLE ???????
(
line1 varchar(19),
line2 varchar(19)
)";

if (!mysql_query($sql,$con))
 {
  die('Error: ' . mysql_error());
  }
echo "You have successfully created the table.";

mysql_query($sql,$con);

mysql_close($con);

?>

MichaelH · Accepted Answer · 2012-03-04T04:01:04.473

0

Add This:

if(isset($_POST['title'])==FALSE){ die('No Title Found.'); }
$name = mysql_real_escape_string($_POST['title']);
$sql = "CREATE TABLE $name 
(
    line1 varchar(19),
    line2 varchar(19)
)";

edited Mar 04 '12 at 04:01

answered Mar 04 '12 at 02:26

MichaelH

1,600
3
14
20

Giant SQL injection vulnerability! – Conrad Shultz Mar 04 '12 at 02:27
@ConradShultz I fixed it! Thats what I get for posting something with 2 hours sleep haha. I'm so used to prepared procedures. – MichaelH Mar 04 '12 at 04:01
Just make sure to check if table with that name exists or not. – Nazar Mar 04 '12 at 18:49
The `mysql_real_escape_string` is pointless there. It's an SQL identifier, not used as string. If the incoming table name was `comments; DELETE FROM USERS; --` it wouldn't magically be escaped or enclsoed in quotes. The only sensible approach is to create a whitelist, or a restrictive regex for sanitization. @user1247567 – mario Mar 04 '12 at 21:15
possible duplicate of [How to prevent SQL injection with dynamic tablenames?](http://stackoverflow.com/questions/5811834/how-to-prevent-sql-injection-with-dynamic-tablenames) – mario Mar 04 '12 at 21:17
Personally I don't believe that their is ever any need to create a dynamic table name. – MichaelH Mar 05 '12 at 02:28
@MichaelH I'm building a widget that will allow the client, within the password-protected backend of the site to create an 'event'(table) and spit back a page that for event participants to register. I'm pretty much done now, but I'd love to hear ideas. – user1247567 Mar 05 '12 at 22:56
@mario Thanks for the thoughts on SQL injection. Although the widget will be in the back end of the website, it's always worth the extra measure of protection. – user1247567 Mar 05 '12 at 22:57
Well in that case, I would have two tables.. one is events and two is participants.. They would look like this: Events { ID, EventName, EventOwner } Participants { EventID, UserID }. If that's not clear, I would be happy to clarify.. – MichaelH Mar 06 '12 at 08:56

score 0 · Answer 2 · answered Mar 04 '12 at 02:26

0

$sql = "CREATE TABLE " . $_POST['title'] . "

Of course this is very unsafe, make sure to extract the title value to a variable and check if it's a valid table name.

answered Mar 04 '12 at 02:26

Wesley

693
4
9

Thanks, do you know a good post about checking a table name? – user1247567 Mar 04 '12 at 04:26
I would just strip all special characters and whitespaces, make it lowercase and make sure the name is 5-15 characters long. – Wesley Mar 04 '12 at 04:35

score 0 · Answer 3 · answered Mar 04 '12 at 02:26

0

The table name would now be in $_POST['title']

So your query should be:

$sql = "CREATE TABLE " . mysql_real_escape_string($_POST['title']) . "
(
line1 varchar(19),
line2 varchar(19)
)";

Letting your user create tables is generally not considered a good idea as they could possibly make a mess of it. If it's the same fields every time it would be better to just store them as rows in a single table.

answered Mar 04 '12 at 02:26

Cfreak

19,191
6
49
60

possible duplicate of [How to prevent SQL injection with dynamic tablenames?](http://stackoverflow.com/questions/5811834/how-to-prevent-sql-injection-with-dynamic-tablenames) – mario Mar 04 '12 at 21:18

How can I create a Table whose name is based on a form input?

3 Answers3