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

Question

I am writing some software right now. Reader's digest version: users select a package, enter their name, email, and desired subdomain. Subdomain is then checked to see if someone already registered it, and if it is only alphanumeric. I had all of this working using OO mysqli, but I have decided to make the move to PDO.

Exact wording of the error:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '->subdomain' at line 1

When I get to instantiating my Admin object, all is well. However, when I call the createAccount() function, all hell breaks loose. The stack trace is all over the place, and I can barely figure out when to begin troubleshooting this. I have checked the other answers here, and they all seem too localized, so here's the code that produces it, then all of the methods that contain errors. Here we go....

First, the code that produces the 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 the subdomain

private function checkSubdomain() {
    $this->data->query('SELECT * FROM clients WHERE subdomain = :subdomain');
    $this->data->bind(':subdomain', $this->subdomain);
    $this->data->execute();

    return ($this->data->rowCount() == 0) && (ctype_alnum($this->subdomain));
}

PDO class execute, bind, and 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 is rampant through my code, so I think its only in the PDO class, but my Session class worked just fine. Also, my queries worked perfectly using only mysqli, so I am confident in my syntax. Any help is greatly appreciated.

By request, the create account function:

public function createAccount($firstname, $lastname, $subdomain, $package)
{
    $this->firstname = $firstname;
    $this->lastname  = $lastname;
    $this->subdomain = $subdomain;
    $this->package   = $package;
    //does the subdomain exist, or is it invalid?
    if(!$this->checkSubdomain())
        throw new Exception('This domain is not available. It can only contain letters and numbers and must not already be taken.');

    //now comes the table creation, provided everything is in order
    $this->setup();
}

Here's the setup function that is called by createAccount (sans all of the table structures):

private function setup() {
    $isError = false;
    $queries = array(
        //all of these are CREATE statements, removed for security reasons
        );

    //need a database that matches with the 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 as $query) {
        $this->data->query($query);
        if(!$this->data->execute()) { //we hit some sort of error, time to 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 is where I insert the user into the admin table using PDO, removed for security
        $this->data->execute();
    }

    $this->data->close();

}

Answer 1

I've hit this error, but with a slightly different cause. You gotta leave spaces in the SELECT statement 'SELECT '.$userfield.' FROM '.$usertable. ' WHERE 1' works great, but 'SELECT'.$userfield.'FROM'.$usertable.'WHERE 1' fails miserably.

    $stmt = $dbh->query(
    'SELECT ' . $userfield . ' FROM ' . $usertable . ' WHERE 1 '
    );
    $results = $stmt->fetchAll(PDO::FETCH_ASSOC);

info offered in case anybody hits this entry with a 42000 failure code lookup.

Answer 2

This is what is causing the error:

$this->data->query('CREATE TABLE $this->subdomain');
$this->data->bind(':subdomain', $this->subdomain);

As Michael Berkowski and andrewsi noted in comments, you cannot bind value to :subdomain placeholder since it is not noted as that in query and even if it is PDO placeholders can only be used for values not database, table or column names.

If you want to have that kind of SQL queries dynamically created you need to enclose database, table or column names in backtick quotes (in case your columns and names contain SQL reserved keywords that may break the query) and escape values that are placed, but you cannot use MySQLi for that if already using PDO.

Since PDO does not come with real_escape_string() method which would do just that, and in practice it is not needed to escape values like that (unless you really have columns named like Ye'name which is totally stupid IMHO), so simple filter using preg_match() or preg_replace() is good enough:

if (preg_match('/^[\w_]+$/i', $this->subdomain)) {
    // note the ` (backtick), and using " (double quotes):
    $this->data->query("CREATE TABLE `{$this->subdomain}`"); 
} else {
    // throw exception or error, do not continue with 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 is used for arrays and object property access and can be used around regular variables.
Escaping $ in double quotes is done by \$ otherwise it will assume a variable call.