PHP PDO Prepared Statement doesn't work

Question

<?php
function CreateDatabase($username, $database)
{
global $con;
if($con) {
    $createdatabase = $con -> prepare("
                                              CREATE DATABASE ?");
    $createdatabase -> execute(array($database));
    $createdatabase = $con -> prepare("
                                        GRANT ALL 
                                        ON 
                                            ?.* 
                                        TO  
                                            ?@'localhost'");
    $createdatabase -> execute(array($database, $username));
    $createdatabase = $con -> prepare("
                                        FLUSH PRIVILEGES");     
    $createdatabase -> execute();
    return true;
}
return false;

}

For some reason this code won't run the way it should. I quadrupled checked that the strings $username and $database are correctly passed on.

Can you see any errors here? Any hints, tips on how to resolve this? Just started learning PDO today, so if I'm doing it wrong or do it in a bad practice, please tell me.

I'm creating a database management system for my clients, where they can create a user if it doesn't already exist, then create a database and look over the info. If anyone is interested in helping out finishing it, I would be most grateful!

you're simply assuming all of those commands are going to work. general rule for prepared statements: placeholders can only represent values, never field/table names. Always check return values for failures. NEVER assume success. Always assume failure and treat success as a pleasant surprise. — Marc B, Dec 02 '13 at 20:10
That's weird, because it works in all my other prepared statements :/ EDIT: I was mistaken, it does NOT work in my other prepared statements. How should I proceed with this task if not with a prepared statement. Security is vital. — Eirik Utvik, Dec 02 '13 at 20:11
@EirikUtvik Not all databases use prepared queries in the same way. Some drivers only emulate them, meaning varied compatibility. — Brad, Dec 02 '13 at 20:13
How would I proceed with doing this task? It has to be secured against SQL injections, as the strings comes straight out of a form. That's why I tried prepared statements first. — Eirik Utvik, Dec 02 '13 at 20:18
Any reason you want to create databases dynamically? Most likely your idea of using a database is wrong. — Your Common Sense, Dec 02 '13 at 20:23
Well, as said above, I'm creating a database management system where clients can create databases themselves after they've purchased them. Say they have 1 database available. To keep a lot of unused databases from piling up, I want the customer to create it himself when needed, and be able to drop it. I don't know how else I would go about it if not dynamically. — Eirik Utvik, Dec 02 '13 at 20:33
It's for a game hosting company, so we give 1 free with every server, but they can buy multiple databases afterwards as well — Eirik Utvik, Dec 03 '13 at 10:57

Bill Karwin · Answer 1 · 2013-12-03T17:20:27.310

As other comments have noted, parameters are only for dynamic values, not identifiers for database/table/column names. Think of it this way: if you could imagine in place of the parameter a single quoted string literal, quoted data literal, or numeric literal, then it's an okay place for a parameter.

If you want to avoid SQL injection for database names, then filter the user's input according to a rule. For example, tell them that their database names must contain only alphanumeric characters and underscore. Then force that rule by stripping out any characters not in that set:

$database = preg_replace('/[^\w]/', '', $_POST['database']);

Also make sure they cannot drop other people's databases, or system databases!

$system_dbs = ['mysql', 'information_schema', 'performance_schema'];
if (array_search($database, $system_dbs)) {
  trigger_error("You can't do that.", E_USER_ERROR);
}

The database names are like this "Firstname_Lastname_1" or whatever number they're at. I've made it so they can't drop any databases except their own. They only have permission on their own database. It checks their name up against the database, and compares them. I was thinking to only use an admin account to create the database, and ask for password everytime they want to use it. — Eirik Utvik, Dec 03 '13 at 11:05

PHP PDO Prepared Statement doesn't work

1 Answers1