Create SQL Table in PHP with variables and text

Question

I'm trying to create a SQL table in PHP using the following code:

$sql = "CREATE TABLE `$DBName`.`$login`_clients  (
  ClientID int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`ClientID`),
  AgentClients varchar(15),
  ClientTotal int
)";

The botton script runs fine and saves the database as my $login query. I wanted to save the table as $login_clients however. Ex. $login="Fred", then the table would be named Fred_clients. I have tried a few different methods of combining variables with text but can't get the format down. Any suggestions?

$sql = "CREATE TABLE `$DBName`.`$login`_clients  (
  ClientID int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`ClientID`),
  AgentClients varchar(15),
  ClientTotal int
)";

Don't you think it's bad practice to separate data in such manner? — Jovan Perovic, Oct 29 '12 at 20:25
@jperovic Clearly not. How about explaining to the OP why it might be a bad practice? — Michael Berkowski, Oct 29 '12 at 20:26
It might be just paranoia inside me but I would never grant a `CREATE` to the front-end without pretty god reason. Instead I would look up table portioning if data separation is indeed needed... — Jovan Perovic, Oct 29 '12 at 20:33

score 4 · Accepted Answer · answered Oct 29 '12 at 20:25

4

You just have your back tick in the wrong place, it should go after _clients. The problem you likely ran into was that the PHP interpreter then thought your variable was called $login_clients instead of $login, which can be solved by wrapping the variable in curly braces {}.

$sql = "CREATE TABLE `{$DBName}`.`{$login}_clients`  (
  ClientID int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`ClientID`),
  AgentClients varchar(15),
  ClientTotal int
)";

answered Oct 29 '12 at 20:25

doublesharp

26,888
6
52
73

2

You'll want to be super mega careful that `$login` does not contain anything harmful here. – tadman Oct 29 '12 at 20:28
1

Also note the proper use of encapsulation in the answer. Encapsulation is very important. – Yitzhak Oct 29 '12 at 20:28
@doublesharp thanks for your help, this clears up my questions on braces! Once I can choose an answer, this will be it. :) – Blaine Hurtado Oct 29 '12 at 20:30
@BlaineHurtado no problem - take note of what @tadman posted as well, you want to make sure that your `$login` variable is what you expect it to be, if you are accepting input from a form for example this could be used for a SQL injection attack. – doublesharp Oct 29 '12 at 20:33
@doublesharp This page requires a valid session cookie through a previous login. Other than sanitizing values and checking for input validations/existing logins, would you recommend other extra securities? Once again, everyone's help is greatly appreciated! – Blaine Hurtado Oct 29 '12 at 20:39

score 0 · Answer 2 · answered Oct 29 '12 at 20:28

0

This should work

$sql = "CREATE TABLE `$DBName`.`" . $login . "_clients`  (
  ClientID int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`ClientID`),
  AgentClients varchar(15),
  ClientTotal int
)";

answered Oct 29 '12 at 20:28

Adi

49
2

Create SQL Table in PHP with variables and text

2 Answers2