Cannot create a database schema with PDO

Question

I'm unable to create a MySQL database schema and assign user privileges via PDO, I can't find anything wrong with my code:

    $db = $this->pdo->prepare( 'CREATE SCHEMA :dbName;
                            GRANT ALL ON :dbName.* TO :dbUser@`localhost` IDENTIFIED BY :dbPassword' );
    $db->bindParam( ':dbName', $dbName, PDO::PARAM_STR );
    $db->bindParam( ':dbUser', $dbUser, PDO::PARAM_STR );
    $db->bindParam( ':dbPassword', $dbPassword, PDO::PARAM_STR );
    $db->execute();
    var_export($db->errorInfo());

returns: '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 \'\'test\'; GRANT ALL ON \'test\'.* TO \'test\' at line 1'

@PeeHaa埽 The query I'm using is valid and tested in console. — user2443876, Jun 01 '13 at 19:21
If multiple queries work you are probably running with emulated prepared statements. — PeeHaa, Jun 01 '13 at 19:35

score 1 · Accepted Answer · edited Jun 01 '13 at 19:21

1

Parameters (such as :dbName) take scalar inputs such as strings and numbers. You can't use them for column names or table names; those must be entered into the query explicitly. That is, CREATE SCHEMA 'test' will fail, but CREATE SCHEMA test is what you need.
PDO cannot run more than one query in a single statement (on purpose). Split this into separate queries.

edited Jun 01 '13 at 19:21

Shoe

74,840
36
166
272

answered Jun 01 '13 at 19:21

Explosion Pills

188,624
52
326
405

Thanks for the response, I really wanted to avoid exec-like queries. PDO actually does accept multiple queries in a single prepare statement, tested. – user2443876 Jun 01 '13 at 19:22

Cannot create a database schema with PDO

1 Answers1