Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Get Qt Extensions
  • Unsolved
Collapse
Brand Logo
  1. Home
  2. Qt Development
  3. General and Desktop
  4. QSqlQuery::prepare fails on CREATE USER with bound values
Forum Updated to NodeBB v4.3 + New Features

QSqlQuery::prepare fails on CREATE USER with bound values

Scheduled Pinned Locked Moved Solved General and Desktop
6 Posts 3 Posters 367 Views 1 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • S Offline
    S Offline
    stryga42
    wrote on last edited by
    #1

    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!

    1 Reply Last reply
    0
    • Christian EhrlicherC Offline
      Christian EhrlicherC Offline
      Christian Ehrlicher
      Lifetime Qt Champion
      wrote on last edited by
      #2

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

      Qt Online Installer direct download: https://download.qt.io/official_releases/online_installers/
      Visit the Qt Academy at https://academy.qt.io/catalog

      1 Reply Last reply
      3
      • S Offline
        S Offline
        stryga42
        wrote on last edited by
        #3

        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...

        1 Reply Last reply
        0
        • C Offline
          C Offline
          ChrisW67
          wrote on last edited by
          #4

          @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.

          S 1 Reply Last reply
          0
          • C 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.

            S Offline
            S Offline
            stryga42
            wrote on last edited by
            #5

            @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.

            1 Reply Last reply
            0
            • S Offline
              S Offline
              stryga42
              wrote on last edited by
              #6

              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.

              1 Reply Last reply
              1

              • Login

              • Login or register to search.
              • First post
                Last post
              0
              • Categories
              • Recent
              • Tags
              • Popular
              • Users
              • Groups
              • Search
              • Get Qt Extensions
              • Unsolved