2

I have a script using PHP and MySQLi with prepared statements. The purpose is to create a new user on a MySQL server, however preparing the statement fails with no further information as to why.

$query = 'CREATE USER ?@`10.1.1.%` IDENTIFIED BY ?;';

if ($stmt = $newdb->prepare($query)) {
$stmt->bind_param('ss', $db_username, $db_password);

    if ($stmt->execute()) {
    // Database user created successfully
    } else {
    die(errorJSON('db', 'create', 22));
}

$stmt->close();
} else {
    die(errorJSON('db', 'create', 3));
}

Any ideas why perparing this statement would fail?

Thank You.

MPelletier
  • 16,256
  • 15
  • 86
  • 137
Jamescun
  • 673
  • 5
  • 8
  • please include error from mysql -- http://php.net/manual/en/mysqli.error.php – ajreal Aug 31 '11 at 14:02
  • Hi ! Can you give us a little more information : where is this script failing ? What is the error type (php or mysql) ? What do you get when you echo $stmt->error after a failure ? – Benjamin Dubois Aug 31 '11 at 14:05
  • 2
    I am having exactly the same problem, preparing that statement fails, i don't know, why: mysql> PREPARE stmt1 FROM 'CREATE USER ? IDENTIFIED BY ?'; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? IDENTIFIED BY ?' at line 1 – Erbureth Oct 18 '11 at 20:44

1 Answers1

1

You should print $stmt->error (and optionally $stmt->errno) when testing the success/failure of your queries. For instance:

if ($stmt->execute()) {
        // Database user created successfully
    } else {
        errorJSON('db', 'create', 22)
        die($stmt->error);
    }

The above is quite a bad example - likely I've messed up the logic of your error reporting itself - but that is how you would retrieve the error and print/log it.

pb149
  • 2,298
  • 1
  • 22
  • 30