createDb.sql
create database `:name` ;
I am trying this file in order to create a database.
$createSql = file_get_contents('createDb.sql');
$stmt = $db->prepare($createSql);
$stmt->bindValue(':name', $dbName);
$stmt->execute();
The problem is, I get extra quotes for the name of the database. For example if $dbName = trash, then the newly created database is named 'trash' (not with grave accent, but with quotes). Can I get rid of the quotes?
Wow, back up... rethink what you're doing here. Prepared statements only allow for the binding of values, not static parts of the query.
PDO, for example, probably does allow for you to use prepared statements with placeholders for the DB name, but that's because it's emulating the prepared statements, probably...
Even so, you're getting the DB name from a .sql file... why go through all that effort, and not simply run the .sql script on your DB server in the first place? Ah well, for future reference, here's a couple of notes (and at the bottom, an answer to your question):
In any case, using placeholders for those static parts of the query (SELECT, INSERT INTO the db name, ...) would seriously reduce the safety that prepared statements give you. This is because of the very nature of prepared statements.
Here's how they work (simplistic representation):
SELECT * from db.tbl WHERE field = ?
This string is sent to the DB, ready to be prepared. After the statement is prepared, you can then use the resulting resource and send the actual values to the DB server to be used with this statement:
<stmt> + 123
The values are sent over a different protocol, they are then properly escaped, and inserted into the pre-processed statement to guarantee correct escaping.
Now apply this to your case, assuming it'll even work:
CREATE DATABASE ?
Whatever value will be passed next, it shouldn't be escaped too much, because:
CREATE DATABASE 'foobar'
is not valid SQL, but then:
CREATE DATABASE IF NOT EXISTS foobar
Should be accepted, too, because that is valid SQL, and in some ways, it's more preferable than the simple create statement you're currently using.
At this point, we've not even discussed the options than can be specified (charset, collation, table specific options...) what about those?
Really, your best bet is to refactor, rethink and ask yourself if you even want to create databases dynamically.
9/10, or even 99.99999% of times, you're better of creating tables. Even then, you probably will not want to use variables for table names, not unless they're properly sanitized, at least.
But as an aside, if you want to carry on with what you're doing, and not have those quotes, you should do this:
$dbName = str_replace(' ', '_', strtolower(trim($dbName)));//remove spaces, trim
$dbName = preg_replace('/^\d*|[^a-z0-9]/','',$dbName);//remove leading digits and non alf-num chars
$db->query('CREATE DATABASE '.$dbName);
With the input 123foo% bar 3, this will create a database named foo_bar3.
A couple of interesting links:
