CREATE USER fails with Mysqli prepared statements

Question

I'm trying to create a simple PHP script to create and assign attributes to an SQL user. The query works fine if no parameters are used, but fails otherwise.

Works fine

$conn = new mysqli('127.0.0.1', 'root', 'pass');
$sql = "CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password'";
$stmt = $conn->prepare($sql);
$stmt->execute();

Both username and password are ?

$conn = new mysqli('127.0.0.1', 'root', 'pass');
$sql = "CREATE USER '?'@'localhost' IDENTIFIED BY '?'";
$stmt = $conn->prepare($sql);
$uname = "carn";
$pass = "pass";
$stmt->bind_param('ss', $uname, $pass);
$stmt->execute();

Both username and password are ?

$conn = new mysqli('127.0.0.1', 'root', 'pass');
$sql = 'CREATE USER "?"@"localhost" IDENTIFIED BY "?"';
$stmt = $conn->prepare($sql);
$uname = "carn";
$pass = "pass";
$stmt->bind_param('ss', $uname, $pass);
$stmt->execute();

Error in syntax, check documentation

$conn = new mysqli('127.0.0.1', 'root', 'pass');
$sql = "CREATE USER ?@'localhost' IDENTIFIED BY ?";
$stmt = $conn->prepare($sql);
$uname = "carn";
$pass = "pass";
$stmt->bind_param('ss', $uname, $pass);
$stmt->execute();

Error in syntax, check documentation

$conn = new mysqli('127.0.0.1', 'root', 'pass');
$sql = "CREATE USER `?`@'localhost' IDENTIFIED BY `?`";
$stmt = $conn->prepare($sql);
$uname = "carn";
$pass = "pass";
$stmt->bind_param('ss', $uname, $pass);
$stmt->execute();

I'm not sure where to go from here. This might be an issue with the way I'm using quotes I saw two related questions here on SO but none of them answered properly.

@Fred-ii- I said it might be an issue, not that I'm sure of it. I tried all combinations I could think of, for all other queries I never wrap `?` with anything and it works fine, here however `bind_param()` fails. — Cârnăciov, Mar 02 '16 at 14:03
*"Might"* ? - More like "is". Placeholders cannot have quotes and/or ticks, far as I remember. — Funk Forty Niner, Mar 02 '16 at 14:04
DDL statements like `CREATE TABLE` do not work with parameters. `CREATE USER` is likely the same. — JimmyB, Mar 02 '16 at 14:05

Your Common Sense · Accepted Answer · 2016-03-02T14:13:23.687

1

With prepared statement you can use only limited subset of queries. Create user not included.

You can try INSERT into mysql.user table, something like

INSERT INTO `mysql`.`user` (Host,User,Password)VALUES(?,?,password(?));

Besides, with users created dynamically you are doing something wrong

edited Mar 02 '16 at 14:13

answered Mar 02 '16 at 14:04

Your Common Sense

156,878
40
214
345

So it's impossible to do it like this? If all names and passwords are generated by me and not user input, should I just go for no params? – Cârnăciov Mar 02 '16 at 14:06
Decided to go with no parameterization since there is no user input, username is generated by appending numeric id to a word, and password just random alpha characters. – Cârnăciov Mar 02 '16 at 14:19

score 1 · Answer 2 · answered Mar 02 '16 at 14:05

1

Don't put single quotes around the ?, bind_param() will take care of it.

However, there is another issue that you can't use placeholders for CREATE USER statements in MySQL.

See How to write "create user ?" with MySQL prepared statement for a way of doing it.

answered Mar 02 '16 at 14:05

neuhaus

3,886
1
10
27

Thanks, see the 4th example, that's how I usually write them, I just tried all combinations of quotation I could think of before posting the question. – Cârnăciov Mar 02 '16 at 14:21

CREATE USER fails with Mysqli prepared statements

2 Answers2

Linked