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. Return boolean ( if value exist in column ) in QSqlQuery
Forum Updated to NodeBB v4.3 + New Features

Return boolean ( if value exist in column ) in QSqlQuery

Scheduled Pinned Locked Moved Solved General and Desktop
16 Posts 5 Posters 7.3k 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.
  • TravT Offline
    TravT Offline
    Trav
    wrote on last edited by VRonin
    #1

    I am working on a project that integrates itself with a MAMP MYSQL Database. The issue I am experiencing is how to search the table and column compare the value inputed by the user and if the email exist essentially its login information like so : email: email@address password: pass This is what i have so far:

         qDebug()<<usernameField<<passwordField;
    
          db = QSqlDatabase::addDatabase("QMYSQL"); // could take second argument but we leave it deafult name
          db.setHostName( "127.0.0.1" ); // localhost
          db.setPort(8889);
          db.setDatabaseName( "Trainer" );
          db.setUserName( "user" );
          db.setPassword( "root" );
    
          if( !db.open())
          {
            qDebug() << db.lastError();
    
          } else {
        qDebug( "Connected!");
    
        QSqlQuery qry(db);
    
    qry.prepare("SELECT * FROM 'known_users'' WHERE 'email_address' VALUES (':email') "); // compare in DB return boolean
    qry.bindValue(":email",usernameField); 
    
    
    /* TODO: check if user email exist and returns value based on users inputed email address
     Code below demonstrates what I'm trying to accomplish 
    */
    
    if(userNameField == True){
    qDebug("You are now signed in!");
    
    } else if( userNameField == False)
    qDebug("sorry you will need to create an account first!");
    

    Top part works fine in context of connecting to the database but I'm stuck with the SQL command and most examples I find are in PHP and do not work or are dead questions with no real solid response. Can someone please help me understand how to return a boolean value based if users email address is in the MYSQL database ? thanks

    jsulmJ Taz742T 2 Replies Last reply
    0
    • TravT Trav

      I am working on a project that integrates itself with a MAMP MYSQL Database. The issue I am experiencing is how to search the table and column compare the value inputed by the user and if the email exist essentially its login information like so : email: email@address password: pass This is what i have so far:

           qDebug()<<usernameField<<passwordField;
      
            db = QSqlDatabase::addDatabase("QMYSQL"); // could take second argument but we leave it deafult name
            db.setHostName( "127.0.0.1" ); // localhost
            db.setPort(8889);
            db.setDatabaseName( "Trainer" );
            db.setUserName( "user" );
            db.setPassword( "root" );
      
            if( !db.open())
            {
              qDebug() << db.lastError();
      
            } else {
          qDebug( "Connected!");
      
          QSqlQuery qry(db);
      
      qry.prepare("SELECT * FROM 'known_users'' WHERE 'email_address' VALUES (':email') "); // compare in DB return boolean
      qry.bindValue(":email",usernameField); 
      
      
      /* TODO: check if user email exist and returns value based on users inputed email address
       Code below demonstrates what I'm trying to accomplish 
      */
      
      if(userNameField == True){
      qDebug("You are now signed in!");
      
      } else if( userNameField == False)
      qDebug("sorry you will need to create an account first!");
      

      Top part works fine in context of connecting to the database but I'm stuck with the SQL command and most examples I find are in PHP and do not work or are dead questions with no real solid response. Can someone please help me understand how to return a boolean value based if users email address is in the MYSQL database ? thanks

      jsulmJ Offline
      jsulmJ Offline
      jsulm
      Lifetime Qt Champion
      wrote on last edited by
      #2

      @Trav

      SELECT * FROM 'known_users'' WHERE 'email_address' VALUES (':email') 
      

      this is wrong.
      It should be:

      SELECT * FROM known_users WHERE email_address = ':email'
      

      https://forum.qt.io/topic/113070/qt-code-of-conduct

      the_T 1 Reply Last reply
      0
      • jsulmJ jsulm

        @Trav

        SELECT * FROM 'known_users'' WHERE 'email_address' VALUES (':email') 
        

        this is wrong.
        It should be:

        SELECT * FROM known_users WHERE email_address = ':email'
        
        the_T Offline
        the_T Offline
        the_
        wrote on last edited by
        #3

        @jsulm said in Return boolean ( if value exist in column ) in QSqlQuery:

        @Trav

        SELECT * FROM 'known_users'' WHERE 'email_address' VALUES (':email') 
        

        this is wrong.
        It should be:

        SELECT * FROM known_users WHERE email_address = ':email'
        

        Doesn't this looks for all rows that have :email as mail address value?
        Without the single quotes :email is a parameter

        qery.prepare("SELECT * FROM known_users WHERE email_address =:email");
        

        -- No support in PM --

        1 Reply Last reply
        1
        • VRoninV Offline
          VRoninV Offline
          VRonin
          wrote on last edited by VRonin
          #4

          The query should be:
          qry.prepare("SELECT * FROM known_users WHERE email_address = :email ");

          the part in the TODO is

          userNameField =false;
          if(qry.exec())
          userNameField =qry.next();
          

          This does not check the password though, only the email

          "La mort n'est rien, mais vivre vaincu et sans gloire, c'est mourir tous les jours"
          ~Napoleon Bonaparte

          On a crusade to banish setIndexWidget() from the holy land of Qt

          TravT 1 Reply Last reply
          2
          • TravT Trav

            I am working on a project that integrates itself with a MAMP MYSQL Database. The issue I am experiencing is how to search the table and column compare the value inputed by the user and if the email exist essentially its login information like so : email: email@address password: pass This is what i have so far:

                 qDebug()<<usernameField<<passwordField;
            
                  db = QSqlDatabase::addDatabase("QMYSQL"); // could take second argument but we leave it deafult name
                  db.setHostName( "127.0.0.1" ); // localhost
                  db.setPort(8889);
                  db.setDatabaseName( "Trainer" );
                  db.setUserName( "user" );
                  db.setPassword( "root" );
            
                  if( !db.open())
                  {
                    qDebug() << db.lastError();
            
                  } else {
                qDebug( "Connected!");
            
                QSqlQuery qry(db);
            
            qry.prepare("SELECT * FROM 'known_users'' WHERE 'email_address' VALUES (':email') "); // compare in DB return boolean
            qry.bindValue(":email",usernameField); 
            
            
            /* TODO: check if user email exist and returns value based on users inputed email address
             Code below demonstrates what I'm trying to accomplish 
            */
            
            if(userNameField == True){
            qDebug("You are now signed in!");
            
            } else if( userNameField == False)
            qDebug("sorry you will need to create an account first!");
            

            Top part works fine in context of connecting to the database but I'm stuck with the SQL command and most examples I find are in PHP and do not work or are dead questions with no real solid response. Can someone please help me understand how to return a boolean value based if users email address is in the MYSQL database ? thanks

            Taz742T Offline
            Taz742T Offline
            Taz742
            wrote on last edited by Taz742
            #5

            @Trav
            Hi.

            bool UserLoginDlg::User_Searched(QString user, QString pass){
                    QSqlQuery query;
            
                    query.prepare("select * from users where USERNAME ='"+user+"' and PASSWORD = '"+pass+"'");
            
                    query.exec();
            
                    while(query.next()){
                            return true;
                    }
            
                    return false;
            }
            
            void UserLoginDlg::on_Log_In_clicked()
            {
                if(User_Searched(ui->USERNAME->text(),ui->PASSWORD->text())){
                    User Searched :))
                }
                else
                QMessageBox::information(this,"WARNING","Username Or Password Is Wrong");
            }
            

            ui->USERNAME->text(),ui->PASSWORD->text(), USERNAME AND PASSWORD Is Linedit Text

            Do what you want.

            Taz742T VRoninV 2 Replies Last reply
            -2
            • Taz742T Taz742

              @Trav
              Hi.

              bool UserLoginDlg::User_Searched(QString user, QString pass){
                      QSqlQuery query;
              
                      query.prepare("select * from users where USERNAME ='"+user+"' and PASSWORD = '"+pass+"'");
              
                      query.exec();
              
                      while(query.next()){
                              return true;
                      }
              
                      return false;
              }
              
              void UserLoginDlg::on_Log_In_clicked()
              {
                  if(User_Searched(ui->USERNAME->text(),ui->PASSWORD->text())){
                      User Searched :))
                  }
                  else
                  QMessageBox::information(this,"WARNING","Username Or Password Is Wrong");
              }
              

              ui->USERNAME->text(),ui->PASSWORD->text(), USERNAME AND PASSWORD Is Linedit Text

              Taz742T Offline
              Taz742T Offline
              Taz742
              wrote on last edited by Taz742
              #6
                  QSqlQuery query;
                  query.prepare("INSERT INTO employee (id, name, salary) "
                                "VALUES (:id, :name, :salary)");
                  query.bindValue(":id", 1001);
                  query.bindValue(":name", "Thad Beaumont");
                  query.bindValue(":salary", 65000);
                  query.exec();
              

              And Read This:
              http://doc.qt.io/qt-5/qsqlquery.html

              Do what you want.

              the_T 1 Reply Last reply
              2
              • Taz742T Taz742

                @Trav
                Hi.

                bool UserLoginDlg::User_Searched(QString user, QString pass){
                        QSqlQuery query;
                
                        query.prepare("select * from users where USERNAME ='"+user+"' and PASSWORD = '"+pass+"'");
                
                        query.exec();
                
                        while(query.next()){
                                return true;
                        }
                
                        return false;
                }
                
                void UserLoginDlg::on_Log_In_clicked()
                {
                    if(User_Searched(ui->USERNAME->text(),ui->PASSWORD->text())){
                        User Searched :))
                    }
                    else
                    QMessageBox::information(this,"WARNING","Username Or Password Is Wrong");
                }
                

                ui->USERNAME->text(),ui->PASSWORD->text(), USERNAME AND PASSWORD Is Linedit Text

                VRoninV Offline
                VRoninV Offline
                VRonin
                wrote on last edited by VRonin
                #7

                @Taz742
                do you want to get hacked? 'cause that's how you get hacked

                See for an easy reference on what's wrong with your code:
                https://www.w3schools.com/sql/sql_injection.asp
                https://www.youtube.com/watch?v=8ZtInClXe1Q

                "La mort n'est rien, mais vivre vaincu et sans gloire, c'est mourir tous les jours"
                ~Napoleon Bonaparte

                On a crusade to banish setIndexWidget() from the holy land of Qt

                Taz742T 1 Reply Last reply
                3
                • Taz742T Taz742
                      QSqlQuery query;
                      query.prepare("INSERT INTO employee (id, name, salary) "
                                    "VALUES (:id, :name, :salary)");
                      query.bindValue(":id", 1001);
                      query.bindValue(":name", "Thad Beaumont");
                      query.bindValue(":salary", 65000);
                      query.exec();
                  

                  And Read This:
                  http://doc.qt.io/qt-5/qsqlquery.html

                  the_T Offline
                  the_T Offline
                  the_
                  wrote on last edited by the_
                  #8

                  @Taz742

                  To add

                  http://doc.qt.io/qt-5/qsqlquery.html#approaches-to-binding-values !!!!

                  ps:
                  why is everyone ignoring this section of the qsqlquery docs?!?!?!

                  -- No support in PM --

                  1 Reply Last reply
                  0
                  • VRoninV VRonin

                    @Taz742
                    do you want to get hacked? 'cause that's how you get hacked

                    See for an easy reference on what's wrong with your code:
                    https://www.w3schools.com/sql/sql_injection.asp
                    https://www.youtube.com/watch?v=8ZtInClXe1Q

                    Taz742T Offline
                    Taz742T Offline
                    Taz742
                    wrote on last edited by Taz742
                    #9

                    @VRonin
                    This was just an example.I know about it.
                    @the_
                    If you read what this man wrote query, your question is wrong.

                    Do what you want.

                    VRoninV 1 Reply Last reply
                    0
                    • Taz742T Taz742

                      @VRonin
                      This was just an example.I know about it.
                      @the_
                      If you read what this man wrote query, your question is wrong.

                      VRoninV Offline
                      VRoninV Offline
                      VRonin
                      wrote on last edited by VRonin
                      #10

                      @Taz742 said in Return boolean ( if value exist in column ) in QSqlQuery:

                      This was just an example

                      Sorry mate but an "example" that can get you fired and/or sued it's a hell of a bad example

                      "La mort n'est rien, mais vivre vaincu et sans gloire, c'est mourir tous les jours"
                      ~Napoleon Bonaparte

                      On a crusade to banish setIndexWidget() from the holy land of Qt

                      Taz742T 1 Reply Last reply
                      2
                      • VRoninV VRonin

                        @Taz742 said in Return boolean ( if value exist in column ) in QSqlQuery:

                        This was just an example

                        Sorry mate but an "example" that can get you fired and/or sued it's a hell of a bad example

                        Taz742T Offline
                        Taz742T Offline
                        Taz742
                        wrote on last edited by Taz742
                        #11

                        @VRonin
                        You are right.

                        Do what you want.

                        1 Reply Last reply
                        0
                        • VRoninV VRonin

                          The query should be:
                          qry.prepare("SELECT * FROM known_users WHERE email_address = :email ");

                          the part in the TODO is

                          userNameField =false;
                          if(qry.exec())
                          userNameField =qry.next();
                          

                          This does not check the password though, only the email

                          TravT Offline
                          TravT Offline
                          Trav
                          wrote on last edited by
                          #12

                          @VRonin said in Return boolean ( if value exist in column ) in QSqlQuery:

                          qry.prepare("SELECT * FROM known_users WHERE email_address = :email ");

                          Thanks, I found your reply the most helpful just from the clear response. The if statement can you please explain this a little more? the issue I guess I'm having is the if statement logic the components..
                          So we are declaring the userNameField = false and then entering a IF statement which is executing what exactly? and the final line "userNameField =qry.next();" we are moving to next bool? Im just a bit confused by this and want to understand this correctly so I can understand the logic thoroughly and have my hah moment , thank you.

                          1 Reply Last reply
                          0
                          • VRoninV Offline
                            VRoninV Offline
                            VRonin
                            wrote on last edited by VRonin
                            #13
                            • if(qry.exec()) just checks that the query was executed i.e. we don't have syntax errors in our query and the SQL server is responding
                            • userNameField =qry.next(); so if the database contains the email passed as a parameter it will return at least one row, if it does not then it will return 0 records. qry.next() moves to the next record (the first in this case) and returns true if the record exists. What i'm doing here is asking "does the query returns at least 1 record?" to do this I try accessing the first record if it's unsuccessful it means that the query returned no rows and hence the user does not exist

                            P.S.
                            Can I ask what type is usernameField?

                            "La mort n'est rien, mais vivre vaincu et sans gloire, c'est mourir tous les jours"
                            ~Napoleon Bonaparte

                            On a crusade to banish setIndexWidget() from the holy land of Qt

                            TravT 1 Reply Last reply
                            1
                            • VRoninV VRonin
                              • if(qry.exec()) just checks that the query was executed i.e. we don't have syntax errors in our query and the SQL server is responding
                              • userNameField =qry.next(); so if the database contains the email passed as a parameter it will return at least one row, if it does not then it will return 0 records. qry.next() moves to the next record (the first in this case) and returns true if the record exists. What i'm doing here is asking "does the query returns at least 1 record?" to do this I try accessing the first record if it's unsuccessful it means that the query returned no rows and hence the user does not exist

                              P.S.
                              Can I ask what type is usernameField?

                              TravT Offline
                              TravT Offline
                              Trav
                              wrote on last edited by
                              #14

                              @VRonin Thanks for clarifying that ! I understand and can apply this to future applications and sure... Just a QString at the moment which is entered into a UI field then passed to the function. I will take more secure practices in the future in context of the application though will defiantly want the password hashed etc. If you have any suggestions in context of something like this ( I think you understand what I'm doing here ) I'd be glad to hear them as this is my first time doing a project of this sort with communication to a Database

                              VRoninV 1 Reply Last reply
                              0
                              • TravT Trav

                                @VRonin Thanks for clarifying that ! I understand and can apply this to future applications and sure... Just a QString at the moment which is entered into a UI field then passed to the function. I will take more secure practices in the future in context of the application though will defiantly want the password hashed etc. If you have any suggestions in context of something like this ( I think you understand what I'm doing here ) I'd be glad to hear them as this is my first time doing a project of this sort with communication to a Database

                                VRoninV Offline
                                VRoninV Offline
                                VRonin
                                wrote on last edited by VRonin
                                #15

                                @Trav said in Return boolean ( if value exist in column ) in QSqlQuery:

                                Just a QString at the moment which is entered into a UI field then passed to the function.

                                then you need to declare another boolean instead of re-using that string

                                bool userNameFieldFound =false;
                                if(qry.exec())
                                userNameFieldFound =qry.next();
                                

                                I will take more secure practices in the future in context of the application though will defiantly want the password hashed etc. If you have any suggestions in context of something like this ( I think you understand what I'm doing here ) I'd be glad to hear them

                                I suggest Argon2 but QCryptographicHash with QCryptographicHash::Sha3_512 algorithm should be ok as long as you always remember to salt it

                                "La mort n'est rien, mais vivre vaincu et sans gloire, c'est mourir tous les jours"
                                ~Napoleon Bonaparte

                                On a crusade to banish setIndexWidget() from the holy land of Qt

                                TravT 1 Reply Last reply
                                3
                                • VRoninV VRonin

                                  @Trav said in Return boolean ( if value exist in column ) in QSqlQuery:

                                  Just a QString at the moment which is entered into a UI field then passed to the function.

                                  then you need to declare another boolean instead of re-using that string

                                  bool userNameFieldFound =false;
                                  if(qry.exec())
                                  userNameFieldFound =qry.next();
                                  

                                  I will take more secure practices in the future in context of the application though will defiantly want the password hashed etc. If you have any suggestions in context of something like this ( I think you understand what I'm doing here ) I'd be glad to hear them

                                  I suggest Argon2 but QCryptographicHash with QCryptographicHash::Sha3_512 algorithm should be ok as long as you always remember to salt it

                                  TravT Offline
                                  TravT Offline
                                  Trav
                                  wrote on last edited by
                                  #16

                                  @VRonin Great, thanks for providing that. I was able to get my DB to check if the userNameField exist of not by using the logic you provided, I will implement what you just shared regarding the declaration as well as the security, Cheers!

                                  1 Reply Last reply
                                  0

                                  • Login

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