SHOW DATABASES query noltworking

Website URL

(http://the-vikings.rf.gd/historical_results.php)

Error Message

No error message.

Other Information

I have had to use some trickery to overcome this problem. The full query was as follows:

$database_search = 'viking_challenge%';

if ($dbc = mysqli_connect (DBHOST, DBUSER, DBPW))
{
	$stmt = $dbc->prepare("SHOW databases LIKE ?"); //generate query to gather the 
                                            list of databases held in MySQL

        $stmt->bind_param("s", $database_search);
        
        $stmt->execute(); // execute the prepared query
        
        $stmt->store_result(); // transfer the result set from the prepared statement
        
        $stmt->bind_result($databases[0]);
}
	
if ($stmt->affected_rows === 0)  //generate an alarm if no result
{
        echo "DB Error, could not list databases\n";
        echo 'MySQL Error: ' . mysqli_error();
        echo "rows";
        exit;
}

The DATABASE sql stuffs is not allowed on free hosting.You can only connect to one of your databases,instead of connecting to the server first and then use your dbs.
Don’t know whether this works on shared premium but will definitely work on VPS/Dedicated.

3 Likes

I think SHOW DATABASES is indeed blocked, you’re not supposed to list the databases and just configure the database you want to connect to.

I recommend to add some error checking to your code. Right now, if mysqli_connect returns false, the if statement will be skipped, but the code will likely crash afterwards because $stmt is not defined, but you won’t know why the connection failed. Additionally, $stmt->execute() also returns true or false depending on whether the query succeeded or not. If it didn’t, you can get the error message from $stmt->error.

Finally, even if the query does work, searching for viking_challenge% will yield no results, because all database names are prefixed with your account username. So if your account username is if0_12345678, the database names will look like if0_12345678_viking_challenge%.

On premium hosting, SHOW DATABASES does work, but the things about error handling and username prefixes also applies to premium hosting. It’s common to prefix database names with account usernames on shared servers to prevent naming conflicts.

9 Likes