Using mysqli prepared statements on schema queries

[diafol]

While extending a mysqli class, I ran into an issue with binding parameters. I've read that db names and table names should not be used as parameters, but while I was pondering this statement...

SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = '$dbname'

I thought, as the db name is a string in this instance, a prepared query should work. Shouldn't it?

This works...

    $stmt = $mysqli->query("SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = '$dbname'");
    print_r( $stmt ); //shows [num_rows] = 1 if dbname exists

So if anybody could enlighten me as to why this doesn't work, I'd appreciate it...

    $stmt = $mysqli->prepare("SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = ?");
    $stmt->$mysqli->bind_param("s", $dbname);
    $stmt->$mysqli->execute();
    print_r( $stmt ); //shows [num_rows] = 0 even if dbname exists

I should point our that the above code has been modified for clarity and that these exist inside methods with $this instead of $mysqli, but I don't think that has a bearing. This is really cooking my noodle :)

Method example:

public function dbExists($dbname)
{
    $stmt = $this->query("SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = '$dbname'");
    return $stmt->num_rows; 
}

(but I don't like it due to unescaped input).

[pritaeas]

Interesting. Do you have this with PDO too? A shame I can't test this here. Have to wait till I get home. Do you get an error, or just no output?

[cereal]

Hi!

It seems to work fine form me, but this:

$stmt->$mysqli->bind_param("s", $dbname);
$stmt->$mysqli->execute();

should be:

$stmt->bind_param("s", $dbname);
$stmt->execute();

My example:

$stmt = $mysqli->prepare("SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = ?");
$stmt->bind_param("s", $dbname);
$stmt->execute();

print_r($stmt);
$result = $stmt->get_result();

while($r = $result->fetch_array(MYSQLI_BOTH))
{
    echo $r[0];
}

And outputs:

mysqli_stmt Object
(
    [affected_rows] => -1
    [insert_id] => 0
    [num_rows] => 0
    [param_count] => 1
    [field_count] => 1
    [errno] => 0
    [error] => 
    [sqlstate] => 00000
    [id] => 1
)

And the dbname.

Consider that if you're using mysqli_stmt::get_result(), this is available only if you using MySQLi through the MySQL Native Driver.

[diafol]

Oh cereal - you beauty. What a berk! But the ? still causing me probs. Will play more...

public function dbExists($dbname)
{
    $stmt = $this->prepare("SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = ?");
    $stmt->bind_param('s', $dbname);
    $stmt->execute();
    print_r($stmt);
    //return $stmt->num_rows;   
}

Current code above still gives 0 rows

[diafol]

@P

Do you have this with PDO too? A shame I can't test this here. Have to wait till I get home. Do you get an error, or just no output?

Haven't tried with PDO as I'm extending mysqli. No error, just output num_rows = 0 - expecting num_rows = 1

[cereal]

Maybe I got it, add $stmt->store_result(); before num_rows. Looking better at my previous test I was getting your same output, now is:

mysqli_stmt Object
(
    [affected_rows] => 1
    [insert_id] => 0
    [num_rows] => 1
    [param_count] => 1
    [field_count] => 1
    [errno] => 0
    [error] => 
    [sqlstate] => 00000
    [id] => 1
)

[diafol]

For the record, I've now implemented this to avoid dirty calls:

public function dbExists($dbname)
{
    $stmt = $this->query("SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA");
    $dbRawArray = $stmt->fetch_all(MYSQLI_NUM);
    $dbArray = array_map("array_shift", $dbRawArray);
    return (in_array($dbname, $dbArray)) ? true : false;
}

Seems like a bad fudge though. I really don't want to start using mysqli_real_escape_string, that would be so wrong.

[diafol]

Drat - sorry cereal, we must have posted at the same time and as mine was after yours, I didn't get the 'new post flag' on the listings. I shall go back and have a look. I ended up with the real_escape_string - felt like a fail :)

[diafol]

OK...

public function dbExists($dbname)
{
    $stmt = $this->prepare("SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = ? LIMIT 1");
    $stmt->bind_param('s',$dbname);
    $stmt->execute();
    $stmt->store_result();
    //print_r($stmt);
    return $stmt->num_rows;
}

Works like a charm. Nice one cereal. I'd never had got that on my own.