QSqlQuery::prepare fails on CREATE USER with bound values
-
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.
WhileQSqlQuery 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! -
Looks like it's not supported by MySQL/MariaDB: https://stackoverflow.com/questions/20647115/how-to-write-create-user-with-mysql-prepared-statement
-
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? AQSqlQuery 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...
-
@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 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.@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.
-
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.