php - SQLSTATE[42000]: Syntax error or access violation: 1064 -

i writing software right now. reader's digest version: users select package, enter name, email, , desired subdomain. subdomain checked see if registered it, , if alphanumeric. had of working using oo mysqli, have decided make move pdo.

exact wording of error:

sqlstate[42000]: syntax error or access violation: 1064 have error in sql syntax; check manual corresponds mysql server version right syntax use near '->subdomain' @ line 1

when instantiating admin object, well. however, when call createaccount() function, hell breaks loose. stack trace on place, , can barely figure out when begin troubleshooting this. have checked other answers here, , seem localized, here's code produces it, of methods contain errors. here go....

first, code produces error:

include 'classes/admin.class.php'; $admin = new admin('test@test.com');  try {     $admin->createaccount('john', 'smith', 'test', 'pro'); } catch(exception $e) {     echo '<pre />';     echo $e->getmessage();     die(print_r($e->gettrace())); } 

admin class constructor

public function __construct($email) {         $this->email = $email;          include 'database.class.php';         include 'passwordhash.php';          define("db_host", "localhost");         define("db_user", "root");         define("db_pass", "");         define("db_name", "ems");          $this->data = new database;         $this->hasher = new passwordhash(8, false);     } 

inside admin class,checking subdomain

private function checksubdomain() {     $this->data->query('select * clients subdomain = :subdomain');     $this->data->bind(':subdomain', $this->subdomain);     $this->data->execute();      return ($this->data->rowcount() == 0) && (ctype_alnum($this->subdomain)); } 

pdo class execute, bind, , query

public function execute() {     return $this->stmt->execute(); }  public function query($query) {     $this->stmt = $this->dbh->prepare($query); }  public function bind($param, $value, $type = null) {     if(is_null($type)) {         switch(true) {             case is_int($value):                 $type = pdo::param_int;                 break;             case is_bool($value):                 $type = pdo::param_bool;                 break;             case is_null($value):                 $type = pdo::param_null;             default:                 $type = pdo::param_str;         }     }     $this->stmt->bindvalue($param, $value, $type); } 

this error rampant through code, think in pdo class, session class worked fine. also, queries worked using mysqli, confident in syntax. appreciated.

by request, create account function:

public function createaccount($firstname, $lastname, $subdomain, $package) {     $this->firstname = $firstname;     $this->lastname  = $lastname;     $this->subdomain = $subdomain;     $this->package   = $package;     //does subdomain exist, or invalid?     if(!$this->checksubdomain())         throw new exception('this domain not available. can contain letters , numbers , must not taken.');      //now comes table creation, provided in order     $this->setup(); } 

here's setup function called createaccount (sans of table structures):

private function setup() {     $iserror = false;     $queries = array(         //all of these create statements, removed security reasons         );      //need database matches client subdomain     $this->data->query('create table $this->subdomain');     $this->data->bind(':subdomain', $this->subdomain);      //pdo secured...execute     $this->data->execute();      $this->data->begintransaction();     foreach($queries $query) {         $this->data->query($query);         if(!$this->data->execute()) { //we hit sort of error, time gtfo of here             $iserror = true;             $this->data->canceltransaction();             //die('error with: ' . $query);             break;         }     }      if(!$iserror) {         $this->data->endtransaction();         mkdir('a/' . $this->subdomain, 0755);         $this->generatepass();         //this insert user admin table using pdo, removed security         $this->data->execute();     }      $this->data->close();  } 

this causing error:

$this->data->query('create table $this->subdomain'); $this->data->bind(':subdomain', $this->subdomain); 

as michael berkowski , andrewsi noted in comments, cannot bind value :subdomain placeholder since not noted in query , if pdo placeholders can used values not database, table or column names.

if want have kind of sql queries dynamically created need enclose database, table or column names in backtick quotes (in case columns , names contain sql reserved keywords may break query) , escape values placed, cannot use mysqli if using pdo.

since pdo not come real_escape_string() method that, , in practice not needed escape values (unless have columns named ye'name totally stupid imho), simple filter using preg_match() or preg_replace() enough:

if (preg_match('/^[\w_]+$/i', $this->subdomain)) {     // note ` (backtick), , using " (double quotes):     $this->data->query("create table `{$this->subdomain}`");  } else {     // throw exception or error, not continue creating table } 

just few examples of using ' (single quote - apostrophe) against " (double quotes) strings in php:

$a = 1; $b = 2; echo '$a + $b'; // outputs: $a + $b echo "$a + $b"; // outputs: 1 + 2 $c = array(5, 10); echo '\$c[0] = {$c[0]}'; // outputs: \$c[0] = {$c[0]} echo "\$c[0] = {$c[0]}"; // outputs: $c[0] = 5 

the {} inside double quotes string used arrays , object property access , can used around regular variables.
escaping $ in double quotes done \$ otherwise assume variable call.