SQL has no results after using bind

Question

SQL has no results after using bind

Navigation

#1 by (5 votes)

2

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());
        }
    }

It was not displaying any errors and no results ... I refined 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 not leave without bind to avoid SQL injection .

php mysql sql pdo

asked by anonymous 04.04.2015 / 15:59

1 answer

Hide URL data and change the view "clear" written text in input A and rewrite it in input B using jquery

score 5 · Accepted Answer

This will not work at all. binding works to replace data that should be used in the query. It does not work to replace any part of the query text. It is not a substitute for strings that magically solves code injection problems. He can do this precisely because his function is limited, he knows how to deal with the data that will come from unreliable sources to place in very specific places of the query which are exactly where he should have data .

So there's not much of a solution other than going the second way. If what comes with $name and $collation are not reliable, your code will have to give adequate treatment so that an injection does not occur in this case. This check will have to be made manually. It's a good exercise. And we're here to help with specific questions you have in the middle of the process.

You would have to check the content individually within what is expected. You have 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 check also if it is within a list of acceptable words. That is, you have to clean up 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 it does not solve the problem by itself. The PDO does not have one and I do not know if it can be used with mysqli . There is a PDO::quote() but I do not know if it is equivalent.

I'm critical of using the PDO, I think it brings more problems than solutions in most cases. And in cases that brings a reasonable solution that is to abstract several databases - which is rarely needed and actually used - still has problems. Unfortunately its use is spread as if it were magic solution and perfect for all problems. And usually those who do not understand all the implications of their use end up buying cat by hare.

A starting point to study more about the subject of SQL injection . Specific PHP documentation .