QSqlQuery::prepare fails on CREATE USER with bound values

[stryga42]

Not sure if this is an SQL / DB problem or Qt - maybe you have a hint for me anyway!
Working on 10.3.37-MariaDB-0ubuntu0.20.04.1 with Qt 5.15.2.
While

QSqlQuery q(db);
q.exec("CREATE USER 'funny'@'%' IDENTIFIED BY 'GreatPwd!1!' ;")

works as expected and creates the user, the attempt to use a bound value for the password

QSqlQuery q(db);
q.prepare("CREATE USER 'funny'@'%' IDENTIFIED BY :passwd ;")
q.bindValue(":passwd", "GreatPwd!1!");

always fails at the prepare step with

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '?' at line 1 QMYSQL3: Unable to prepare statement

The question mark mentioned in the error message is somehow strange - there is no question mark in my query. A

q.lastQuery();

gives (as expected)

CREATE USER 'funny'@'%' IDENTIFIED BY :passwd ;

Of course I make sure that there is no user funny in the DB, it is definitely not a duplicate-user problem.
Could it be that value binding is available in DML statements only? If so, how could I block SQL injection through weird passwords?
Thank you folks!

[Christian Ehrlicher]

Looks like it's not supported by MySQL/MariaDB: https://stackoverflow.com/questions/20647115/how-to-write-create-user-with-mysql-prepared-statement

[stryga42]

Got it - thank you for the mySQL support!
So I have an SQL script which works fine: (tested in DBeaver)

SET @pass := '''; DROP TABLE x; --'; 
PREPARE stmt FROM CONCAT("CREATE USER 'foo'@'%' IDENTIFIED BY ", QUOTE(@pass));
EXECUTE stmt;

This creates a valid user with the (arguably strange) password '; DROP TABLE x; --
Is there a way to code this in Qt? A

QSqlQuery q(db);
QString qu="SET @pass := '''); DROP TABLE x; --'; CONCAT(\"CREATE USER 'foo'@'%' IDENTIFIED BY \", QUOTE(@pass));";
q.prepare(qu);

does NOT work since the SET is "on the wrong side" of the PREPARE...

[ChrisW67]

@stryga42 said in QSqlQuery::prepare fails on CREATE USER with bound values:

The question mark mentioned in the error message is somehow strange - there is no question mark in my query.

The Qt database plugin has translated your named parameter from :passwd to the ? placeholder accepted by MariaDB. The error message comes directly from MariaDB which can only see the ?.

To quote the docs:

The markers are legal only in certain places in SQL statements. For example, they are allowed in the VALUES() list of an INSERT statement (to specify column values for a row), or in a comparison with a column in a WHERE clause to specify a comparison value.

Your CREATE USER is considered DDL not DML.

Depending on the MariaDB version you may be able to reform this into an INSERT on mysql.user or mysql.global_priv. Not sure if this direct approach is still permitted.

[stryga42]

@ChrisW67 About the ?: Understood, the SQL error messages refers to the "translated" query while QSqlQuery::lastQuery() returns the original query string, before database specific translations occurred. About DML vs. DDL: I was not sure if value binding works for DDL - but yes, it seemingly does work.

[stryga42]

Although not really solving the issued, I created a workaround using a stored procedure at the server.

DROP PROCEDURE IF EXISTS testdb.CreaUser;
DELIMITER //
CREATE PROCEDURE testdb.CreaUser(IN username TEXT, IN passwd TEXT)
BEGIN
PREPARE stmt FROM CONCAT("CREATE USER ", QUOTE(username), "@'%' IDENTIFIED BY ", QUOTE(passwd));
EXECUTE stmt ;
END;
//
DELIMITER ;

Having the procedure in place I can call it from Qt with bound values:

QSqlQuery q(db);
QString qu="CALL CreaUser ( :username , :passwd );";
if(q.prepare(qu))
	qDebug()<<"query prepared";
else
{
	qDebug()<<"q.prepare failed "<<q.lastError().text();
	return;
}
q.bindValue(":username", "foo");
q.bindValue(":passwd", "; DROP TABLE x; --'");
if(q.exec())
	qDebug()<<"query executed";
else
{
	qDebug()<<"q.exec failed "<<q.lastError().text()<<q.lastQuery();
	return;
}
db.close();

and get the user created without any loopholes for SQL injection.