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. SQL error when using parameters
Forum Updated to NodeBB v4.3 + New Features

SQL error when using parameters

Scheduled Pinned Locked Moved Unsolved General and Desktop
14 Posts 3 Posters 647 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.
  • SPlattenS Offline
    SPlattenS Offline
    SPlatten
    wrote on last edited by SPlatten
    #1

    In my application I process the JSON:

    {"command":"query"
        ,"sql":"INSERT INTO test.tblTest (`dtWhen`,`tmWhen`) VALUES (?,?);"
       ,"args":["current_date()","current_time()"]}
    

    Here is the code that interprets this JSON:

    QString strCmd = citCmd.value().toString();
    if ( strCmd.compare(clsModSQL::mscszCmdQuery) != 0 ) {
        robjResponse.insert(clsModSQL::mscszError  ,"Invalid command!");
        return false;
    }
    QJsonObject::const_iterator citSQL = robjCmd.find(clsModSQL::mscszSQL);
    bool blnNoSQL = true;
    QString strSQL;
    
    if ( citSQL != robjCmd.end() ) {
        strSQL = citSQL->toString();
    
        if ( strSQL.trimmed().isEmpty() != true ) {
            blnNoSQL = false;
        }
    }
    if ( blnNoSQL == true ) {
    //No SQL supplied, cannot proceed!
        robjResponse.insert(clsModSQL::mscszError, "No SQL supplied!");
        return false;
    }
    QJsonArray aryArgs;
    QSqlQuery query;
    query.prepare(strSQL);
    
    if ( citArgs != robjCmd.end() ) {
        QJsonArray aryArgs = citArgs->toArray();
        for( int i=0; i<aryArgs.count(); i++ )  {
            QVariant varArg(aryArgs.at(i).toVariant());
            query.addBindValue(varArg);
        }
    }
    query.exec();
    //Any errors
    QSqlError err = query.lastError();
    if ( err.type() != QSqlError::NoError ) {
        QString strErrType("[" + QString(err.type()) + "]");
        robjResponse.insert(clsModSQL::mscszError, strErrType + err.text());
        return false;
    }
    

    Where citCmd is of type QJsonObject::const_iterator that has found the command in the JSON and has been verified as found.

    I've single stepped to the error checking at the bottom of the above code and it results in the following error:

    "[]Incorrect date value: 'current_date()' for column `test`.`tbltest`.`dtWhen` at row 1 QMYSQL3: Una"
    

    What have I done wrong because if I take the SQL and the parameters and put into MySQLWorkbench it works ?

    The fields I'm trying to insert into:

    Column:dtWhen, Type:date, Default Value:NULL, Nullable:YES
    Column:tmWhen, Type:time, Default Value:NULL,Nullable:YES

    If I change the SQL to:

    "INSERT INTO test.tblTest (`dtWhen`,`tmWhen`) VALUES (current_date(),current_time());"
    

    So the arguments are not dropped in to replace the ? it works perfectly, but I need the ability to pass in the parameters, suggestions and help very much appreciated.

    Kind Regards,
    Sy

    KroMignonK JonBJ 2 Replies Last reply
    0
    • SPlattenS SPlatten

      In my application I process the JSON:

      {"command":"query"
          ,"sql":"INSERT INTO test.tblTest (`dtWhen`,`tmWhen`) VALUES (?,?);"
         ,"args":["current_date()","current_time()"]}
      

      Here is the code that interprets this JSON:

      QString strCmd = citCmd.value().toString();
      if ( strCmd.compare(clsModSQL::mscszCmdQuery) != 0 ) {
          robjResponse.insert(clsModSQL::mscszError  ,"Invalid command!");
          return false;
      }
      QJsonObject::const_iterator citSQL = robjCmd.find(clsModSQL::mscszSQL);
      bool blnNoSQL = true;
      QString strSQL;
      
      if ( citSQL != robjCmd.end() ) {
          strSQL = citSQL->toString();
      
          if ( strSQL.trimmed().isEmpty() != true ) {
              blnNoSQL = false;
          }
      }
      if ( blnNoSQL == true ) {
      //No SQL supplied, cannot proceed!
          robjResponse.insert(clsModSQL::mscszError, "No SQL supplied!");
          return false;
      }
      QJsonArray aryArgs;
      QSqlQuery query;
      query.prepare(strSQL);
      
      if ( citArgs != robjCmd.end() ) {
          QJsonArray aryArgs = citArgs->toArray();
          for( int i=0; i<aryArgs.count(); i++ )  {
              QVariant varArg(aryArgs.at(i).toVariant());
              query.addBindValue(varArg);
          }
      }
      query.exec();
      //Any errors
      QSqlError err = query.lastError();
      if ( err.type() != QSqlError::NoError ) {
          QString strErrType("[" + QString(err.type()) + "]");
          robjResponse.insert(clsModSQL::mscszError, strErrType + err.text());
          return false;
      }
      

      Where citCmd is of type QJsonObject::const_iterator that has found the command in the JSON and has been verified as found.

      I've single stepped to the error checking at the bottom of the above code and it results in the following error:

      "[]Incorrect date value: 'current_date()' for column `test`.`tbltest`.`dtWhen` at row 1 QMYSQL3: Una"
      

      What have I done wrong because if I take the SQL and the parameters and put into MySQLWorkbench it works ?

      The fields I'm trying to insert into:

      Column:dtWhen, Type:date, Default Value:NULL, Nullable:YES
      Column:tmWhen, Type:time, Default Value:NULL,Nullable:YES

      If I change the SQL to:

      "INSERT INTO test.tblTest (`dtWhen`,`tmWhen`) VALUES (current_date(),current_time());"
      

      So the arguments are not dropped in to replace the ? it works perfectly, but I need the ability to pass in the parameters, suggestions and help very much appreciated.

      KroMignonK Offline
      KroMignonK Offline
      KroMignon
      wrote on last edited by KroMignon
      #2

      @SPlatten said in SQL error when using parameters:

      So the arguments are not dropped in to replace the ? it works perfectly, but I need the ability to pass in the parameters, suggestions and help very much appreciated.

      I think your analysis if wrong and your implementation also ;)
      What you want is:

      INSERT INTO test.tblTest (`dtWhen`,`tmWhen`) VALUES (current_date(),current_time());
      

      And what you get is, I guess:

      INSERT INTO test.tblTest (`dtWhen`,`tmWhen`) VALUES ("current_date()","current_time()");
      

      With QSqlQuery::addBindValue(), the parameter will be converted to an SQL compatible string. In your case the QVariant holds a string, so it will convert to SQL string (with escape sequences and so on).

      I am not sure, but I think using query.addBindValue(varArg, QSql::In | QSql::Binatry); should work.

      It is an old maxim of mine that when you have excluded the impossible, whatever remains, however improbable, must be the truth. (Sherlock Holmes)

      SPlattenS 1 Reply Last reply
      2
      • SPlattenS SPlatten

        In my application I process the JSON:

        {"command":"query"
            ,"sql":"INSERT INTO test.tblTest (`dtWhen`,`tmWhen`) VALUES (?,?);"
           ,"args":["current_date()","current_time()"]}
        

        Here is the code that interprets this JSON:

        QString strCmd = citCmd.value().toString();
        if ( strCmd.compare(clsModSQL::mscszCmdQuery) != 0 ) {
            robjResponse.insert(clsModSQL::mscszError  ,"Invalid command!");
            return false;
        }
        QJsonObject::const_iterator citSQL = robjCmd.find(clsModSQL::mscszSQL);
        bool blnNoSQL = true;
        QString strSQL;
        
        if ( citSQL != robjCmd.end() ) {
            strSQL = citSQL->toString();
        
            if ( strSQL.trimmed().isEmpty() != true ) {
                blnNoSQL = false;
            }
        }
        if ( blnNoSQL == true ) {
        //No SQL supplied, cannot proceed!
            robjResponse.insert(clsModSQL::mscszError, "No SQL supplied!");
            return false;
        }
        QJsonArray aryArgs;
        QSqlQuery query;
        query.prepare(strSQL);
        
        if ( citArgs != robjCmd.end() ) {
            QJsonArray aryArgs = citArgs->toArray();
            for( int i=0; i<aryArgs.count(); i++ )  {
                QVariant varArg(aryArgs.at(i).toVariant());
                query.addBindValue(varArg);
            }
        }
        query.exec();
        //Any errors
        QSqlError err = query.lastError();
        if ( err.type() != QSqlError::NoError ) {
            QString strErrType("[" + QString(err.type()) + "]");
            robjResponse.insert(clsModSQL::mscszError, strErrType + err.text());
            return false;
        }
        

        Where citCmd is of type QJsonObject::const_iterator that has found the command in the JSON and has been verified as found.

        I've single stepped to the error checking at the bottom of the above code and it results in the following error:

        "[]Incorrect date value: 'current_date()' for column `test`.`tbltest`.`dtWhen` at row 1 QMYSQL3: Una"
        

        What have I done wrong because if I take the SQL and the parameters and put into MySQLWorkbench it works ?

        The fields I'm trying to insert into:

        Column:dtWhen, Type:date, Default Value:NULL, Nullable:YES
        Column:tmWhen, Type:time, Default Value:NULL,Nullable:YES

        If I change the SQL to:

        "INSERT INTO test.tblTest (`dtWhen`,`tmWhen`) VALUES (current_date(),current_time());"
        

        So the arguments are not dropped in to replace the ? it works perfectly, but I need the ability to pass in the parameters, suggestions and help very much appreciated.

        JonBJ Online
        JonBJ Online
        JonB
        wrote on last edited by JonB
        #3

        @SPlatten
        I was about to type in a similar analysis to @KroMignon, who has explained it admirably!

        In general, you are going to have find a generic way to pass something which is a SQL expression to evaluate (current_date(), current_time()) as a bound parameter. This could potentially happen all over the place in the text of the commands you read in. You will need to distinguish literal numbers from literal strings from SQL functions/expressions etc. when deciding what to construct from the queries.

        Let's hope his query.addBindValue(varArg, QSql::In | QSql::Binary) works. Though why QSql::Binary ("to indicate that the data being transferred is raw binary data.") will treat a string (current_date()) as something to pass as-is for later evaluation I do not know.

        1 Reply Last reply
        0
        • KroMignonK KroMignon

          @SPlatten said in SQL error when using parameters:

          So the arguments are not dropped in to replace the ? it works perfectly, but I need the ability to pass in the parameters, suggestions and help very much appreciated.

          I think your analysis if wrong and your implementation also ;)
          What you want is:

          INSERT INTO test.tblTest (`dtWhen`,`tmWhen`) VALUES (current_date(),current_time());
          

          And what you get is, I guess:

          INSERT INTO test.tblTest (`dtWhen`,`tmWhen`) VALUES ("current_date()","current_time()");
          

          With QSqlQuery::addBindValue(), the parameter will be converted to an SQL compatible string. In your case the QVariant holds a string, so it will convert to SQL string (with escape sequences and so on).

          I am not sure, but I think using query.addBindValue(varArg, QSql::In | QSql::Binatry); should work.

          SPlattenS Offline
          SPlattenS Offline
          SPlatten
          wrote on last edited by SPlatten
          #4

          @KroMignon , the issue I have is that originally I did exactly that, however it will not compile like that because current_date() and current_time() only have meaning when executed by the SQL server, they mean nothing in JSON and cannot be evaluated, so they become undefined. The second parameter of addBindValue defaults to QSql::In.

          Kind Regards,
          Sy

          JonBJ 1 Reply Last reply
          0
          • SPlattenS SPlatten

            @KroMignon , the issue I have is that originally I did exactly that, however it will not compile like that because current_date() and current_time() only have meaning when executed by the SQL server, they mean nothing in JSON and cannot be evaluated, so they become undefined. The second parameter of addBindValue defaults to QSql::In.

            JonBJ Online
            JonBJ Online
            JonB
            wrote on last edited by JonB
            #5

            @SPlatten
            If indeed it does not work with QSql::In | QSql::Binary then you (seem to) have no other choices for the parameter which will work from Qt client. That would mean it "cannot be done via a bound parameter", and you would need to handle such cases via direct interpolation into the query string instead of via a bound parameter. I did a look around and was unable to find any examples/questions where a bound value from Qt is set to an expression to be evaluated server-side, so we do not know whether it is supported. Unless @KroMignon has reason to believe it should be for the proposal he offers?

            SPlattenS 1 Reply Last reply
            0
            • JonBJ JonB

              @SPlatten
              If indeed it does not work with QSql::In | QSql::Binary then you (seem to) have no other choices for the parameter which will work from Qt client. That would mean it "cannot be done via a bound parameter", and you would need to handle such cases via direct interpolation into the query string instead of via a bound parameter. I did a look around and was unable to find any examples/questions where a bound value from Qt is set to an expression to be evaluated server-side, so we do not know whether it is supported. Unless @KroMignon has reason to believe it should be for the proposal he offers?

              SPlattenS Offline
              SPlattenS Offline
              SPlatten
              wrote on last edited by
              #6

              @JonB , Just tried using QSql::In | QSql::Binary combination as the second parameter and removed the quotes around the arguments, it still doesn't like it.

              It works as in my original post I drop in the arguments inline without quotes.

              Kind Regards,
              Sy

              JonBJ 1 Reply Last reply
              0
              • SPlattenS SPlatten

                @JonB , Just tried using QSql::In | QSql::Binary combination as the second parameter and removed the quotes around the arguments, it still doesn't like it.

                It works as in my original post I drop in the arguments inline without quotes.

                JonBJ Online
                JonBJ Online
                JonB
                wrote on last edited by
                #7

                @SPlatten
                It won't work "without the quotes", assuming you mean something like

                query.addBindValue(current_date(), QSql::In | QSql::Binary)
                

                as that won't compile. @KroMignon must intend you to try:

                query.addBindValue("current_date()", QSql::In | QSql::Binary)
                

                and hope that will cause the driver to pass across plain current_date() for evaluation at server-side. Which as I've said, unless @KroMignon knows better, I would doubt does that: more likely, it does something about passing the bytes of "current_date()" in some binary representation.

                It works as in my original post I drop in the arguments inline without quotes.

                Indeed. That's why I said, unless you can find a way to make it work with a bound parameter, you will have to recognise a case like this (e.g. parameter is neither a quoted string nor a number) and replace the ? directly in-line instead of trying to use any placeholder parameter.

                SPlattenS 1 Reply Last reply
                0
                • JonBJ JonB

                  @SPlatten
                  It won't work "without the quotes", assuming you mean something like

                  query.addBindValue(current_date(), QSql::In | QSql::Binary)
                  

                  as that won't compile. @KroMignon must intend you to try:

                  query.addBindValue("current_date()", QSql::In | QSql::Binary)
                  

                  and hope that will cause the driver to pass across plain current_date() for evaluation at server-side. Which as I've said, unless @KroMignon knows better, I would doubt does that: more likely, it does something about passing the bytes of "current_date()" in some binary representation.

                  It works as in my original post I drop in the arguments inline without quotes.

                  Indeed. That's why I said, unless you can find a way to make it work with a bound parameter, you will have to recognise a case like this (e.g. parameter is neither a quoted string nor a number) and replace the ? directly in-line instead of trying to use any placeholder parameter.

                  SPlattenS Offline
                  SPlattenS Offline
                  SPlatten
                  wrote on last edited by SPlatten
                  #8

                  @JonB , No, thats not what I meant. If I write this:

                  INSERT INTO test.tblTest (`dtWhen`,`tmWhen`) VALUES (current_date(),current_time());
                  

                  It works fine, if I try:

                  INSERT INTO test.tblTest (`dtWhen`,`tmWhen`) VALUES (?, ?);
                  

                  And try:

                  query.addBindValue("current_date()", QSql::In | QSql::Binary);
                  query.addBindValue("current_time()", QSql::In | QSql::Binary);
                  

                  It does not work. Correction, it may work when its hard coded like that, but that isn't how I have it, the SQL and arguments are passed in via JSON. If I put the SQL functions into the JSON without quotes then the JSON fails.

                  NVM, I have chosen to treat this as a special case and if SQL functions are required then I will just document it that they have to be embedded into the query.

                  Kind Regards,
                  Sy

                  JonBJ 2 Replies Last reply
                  0
                  • SPlattenS SPlatten

                    @JonB , No, thats not what I meant. If I write this:

                    INSERT INTO test.tblTest (`dtWhen`,`tmWhen`) VALUES (current_date(),current_time());
                    

                    It works fine, if I try:

                    INSERT INTO test.tblTest (`dtWhen`,`tmWhen`) VALUES (?, ?);
                    

                    And try:

                    query.addBindValue("current_date()", QSql::In | QSql::Binary);
                    query.addBindValue("current_time()", QSql::In | QSql::Binary);
                    

                    It does not work. Correction, it may work when its hard coded like that, but that isn't how I have it, the SQL and arguments are passed in via JSON. If I put the SQL functions into the JSON without quotes then the JSON fails.

                    NVM, I have chosen to treat this as a special case and if SQL functions are required then I will just document it that they have to be embedded into the query.

                    JonBJ Online
                    JonBJ Online
                    JonB
                    wrote on last edited by JonB
                    #9

                    @SPlatten
                    You say "No, thats not what I meant.", but I do understand that is what you meant!

                    I am suggesting: if indeed @KroMignon's

                    query.addBindValue("current_date()", QSql::In | QSql::Binary);
                    

                    does not work --- and I was questionable that it would, as I don't think that is what QSql::Binary is for/does, but he may know better that I --- that means there is nothing left for the variable type/QSql::ParamType for you to try from Qt which would work for what you need to do here.

                    If that is true you must go for the literal, in-line-interpolated

                    INSERT INTO test.tblTest (`dtWhen`,`tmWhen`) VALUES (current_date(),current_time());
                    

                    in the cases where a parameter is something like neither a literal string not a number, it cannot be done via bindValue().

                    As I said, I have had a look around. Although it's not easy to Google for, there simply does not seem to be any question about how to bind a value which is a SQL expression instead of a literal. So I can find neither a solution nor even someone who wants to do as you want to do (although it's quite reasonable) via a bound value.

                    This has nothing to do with your JSON. Of course anything you put there must be legitimate JSON, there is no point in "If I put the SQL functions into the JSON without quotes then the JSON fails".

                    NVM, I have chosen to treat this as a special case and if SQL functions are required then I will just document it that they have to be embedded into the query.

                    Yep, that's OK. Or, as I said, you could recognise that a parameter which is neither a quoted string nor a number (or similar) is to be interpolated directly into the query string. Though beware of injection attacks!

                    1 Reply Last reply
                    0
                    • SPlattenS SPlatten

                      @JonB , No, thats not what I meant. If I write this:

                      INSERT INTO test.tblTest (`dtWhen`,`tmWhen`) VALUES (current_date(),current_time());
                      

                      It works fine, if I try:

                      INSERT INTO test.tblTest (`dtWhen`,`tmWhen`) VALUES (?, ?);
                      

                      And try:

                      query.addBindValue("current_date()", QSql::In | QSql::Binary);
                      query.addBindValue("current_time()", QSql::In | QSql::Binary);
                      

                      It does not work. Correction, it may work when its hard coded like that, but that isn't how I have it, the SQL and arguments are passed in via JSON. If I put the SQL functions into the JSON without quotes then the JSON fails.

                      NVM, I have chosen to treat this as a special case and if SQL functions are required then I will just document it that they have to be embedded into the query.

                      JonBJ Online
                      JonBJ Online
                      JonB
                      wrote on last edited by JonB
                      #10

                      @SPlatten
                      On a related but different note, how have you decided that the user should specify a literal string parameter in your JSON file?

                      Say the query is

                      SELECT * from names_table WHERE name = ?
                      

                      and the user wants to look for Simon. In your JSON, do you require the user to write

                      "args":["Simon"]
                      

                      or

                      "args":["'Simon'"]
                      

                      ? I hope it is the latter, but do you actually require the former?

                      SPlattenS 1 Reply Last reply
                      0
                      • JonBJ JonB

                        @SPlatten
                        On a related but different note, how have you decided that the user should specify a literal string parameter in your JSON file?

                        Say the query is

                        SELECT * from names_table WHERE name = ?
                        

                        and the user wants to look for Simon. In your JSON, do you require the user to write

                        "args":["Simon"]
                        

                        or

                        "args":["'Simon'"]
                        

                        ? I hope it is the latter, but do you actually require the former?

                        SPlattenS Offline
                        SPlattenS Offline
                        SPlatten
                        wrote on last edited by
                        #11

                        @JonB , I've used ? and argument binding before and it works very nicely, the single quotes are not required, passing the argument as:

                        "args":["Simon"]
                        

                        Will work just fine.

                        Kind Regards,
                        Sy

                        JonBJ 1 Reply Last reply
                        0
                        • SPlattenS SPlatten

                          @JonB , I've used ? and argument binding before and it works very nicely, the single quotes are not required, passing the argument as:

                          "args":["Simon"]
                          

                          Will work just fine.

                          JonBJ Online
                          JonBJ Online
                          JonB
                          wrote on last edited by JonB
                          #12

                          @SPlatten
                          Then I think you may run into problems constructing queries because you cannot distinguish, say, what is to be a string parameter from a numeric parameter. You have (presumably) made it impossible to pass an argument as a numeric, everything will go as a string. That is not what you would do in code when constructing a query which has a numeric parameter, e.g you would write

                          query.addBindValue(10);
                          

                          rather than

                          query.addBindValue("10");
                          

                          I leave you to contemplate the consequences.

                          Actually, this depends again on what you require from the user. Would they specify in your JSON a number for an argument via

                          "args":["10"]
                          

                          or

                          "args":[10]
                          

                          ?

                          SPlattenS 1 Reply Last reply
                          0
                          • JonBJ JonB

                            @SPlatten
                            Then I think you may run into problems constructing queries because you cannot distinguish, say, what is to be a string parameter from a numeric parameter. You have (presumably) made it impossible to pass an argument as a numeric, everything will go as a string. That is not what you would do in code when constructing a query which has a numeric parameter, e.g you would write

                            query.addBindValue(10);
                            

                            rather than

                            query.addBindValue("10");
                            

                            I leave you to contemplate the consequences.

                            Actually, this depends again on what you require from the user. Would they specify in your JSON a number for an argument via

                            "args":["10"]
                            

                            or

                            "args":[10]
                            

                            ?

                            SPlattenS Offline
                            SPlattenS Offline
                            SPlatten
                            wrote on last edited by
                            #13

                            @JonB , actually I've played around with this in the past the routines are quite smart in the way they handle parameters, which is why its better and safer to use ? and use parameters rather than embedding the parameter values inline. The code handles the interpretation of the type and adds quotes to the query automatically, of course you won't see this but putting quotes on the data is not required.

                            Kind Regards,
                            Sy

                            JonBJ 1 Reply Last reply
                            0
                            • SPlattenS SPlatten

                              @JonB , actually I've played around with this in the past the routines are quite smart in the way they handle parameters, which is why its better and safer to use ? and use parameters rather than embedding the parameter values inline. The code handles the interpretation of the type and adds quotes to the query automatically, of course you won't see this but putting quotes on the data is not required.

                              JonBJ Online
                              JonBJ Online
                              JonB
                              wrote on last edited by JonB
                              #14

                              @SPlatten
                              Yes, I realize that about parameters. They will respect the type of the C++ variable/JSON variant type you have read the parameters in as. That's why JSON "args":["10"] will produce a different C++/SQL type from "args":[10].

                              Anyway, sounds like you have this under control.

                              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