SQL has no results after using bind

Question

I made this code to create Databases:

public function addDatabase($name, $collation) {   
        try {
            $sql = "CREATE DATABASE `:name` COLLATE `:collation`;";

            // Prepare the query to execute
            $query = $this->db->prepare($sql);

            $parameters = array(':name' => $name, ':collation' => $collation);

            // Execute the query
            $query->execute($parameters);
        } catch (PDOException $e) {
            die("DB ERROR: ". $e->getMessage());
        }
    }

He wasn’t displaying any errors and no results... I redid the code without bind

public function addDatabase($name, $collation) {   
    try {
        $sql = "CREATE DATABASE `$name` COLLATE `$collation`;";

        // Prepare the query to execute
        $query = $this->db->prepare($sql);

        // Execute the query
        $query->execute();
    } catch (PDOException $e) {
        die("DB ERROR: ". $e->getMessage());
    }
}

And without bind worked. But I can’t leave without bind to avoid SQL Injection.

Answer 1

This is really not going to work. The Binding works to replace data that should be used in the query. It does not work to replace any part of the query text. He’s not a substitute for strings that magically solve code injection problems. He can do this precisely because his function is limited, he knows how to handle the data that will come from unreliable sources to place in very specific locations of query which are just where you should be dice.

Then there is not much solution but to go the second way. If what comes from $name and $collation your code will have to give proper treatment so that an injection does not occur in this case. This check will have to be done manually. It’s a good exercise. And we’re here to help you with specific questions you have in the middle of the process.

You would have to check the content individually within what is expected. It has to see, for example, if you have only one word in $name or the same in $collation, but in this case it would be good to also check whether it is within a list of acceptable words. That is, has to clean possible "dirt" that may come or refuse what comes if there is presence of "dirt".

The library that does not use PDO has the function mysqli_real_escape_string() that helps in this - but does not solve the problem by itself. PDO does not have one and I do not know if it can be used mysqli. There is the PDO::quote() but I don’t know if she’s the equivalent.

I’m critical of using PDO, I think it brings more problems than solutions in most cases. And in the cases that bring a reasonable solution that is to abstract several databases - which is rarely necessary and actually used - still keep having problems. Unfortunately its use is disseminated as if it were magic solution and perfect for all problems. And usually who does not understand all the implications of its use ends up buying cat by hare.

A starting point to study more on the subject of SQL injection. Documentation specific to PHP.