Return boolean ( if value exist in column ) in QSqlQuery
-
wrote on 16 Mar 2017, 04:55 last edited by VRonin
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
-
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
SELECT * FROM 'known_users'' WHERE 'email_address' VALUES (':email')
this is wrong.
It should be:SELECT * FROM known_users WHERE email_address = ':email'
-
SELECT * FROM 'known_users'' WHERE 'email_address' VALUES (':email')
this is wrong.
It should be:SELECT * FROM known_users WHERE email_address = ':email'
wrote on 16 Mar 2017, 05:41 last edited by@jsulm said in Return boolean ( if value exist in column ) in QSqlQuery:
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 parameterqery.prepare("SELECT * FROM known_users WHERE email_address =:email");
-
wrote on 16 Mar 2017, 09:05 last edited by VRonin
The query should be:
qry.prepare("SELECT * FROM known_users WHERE email_address = :email ");
the part in the
TODO
isuserNameField =false; if(qry.exec()) userNameField =qry.next();
This does not check the password though, only the email
-
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
wrote on 16 Mar 2017, 10:59 last edited by 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
-
@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
wrote on 16 Mar 2017, 11:05 last edited by Taz742QSqlQuery 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 -
@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
wrote on 16 Mar 2017, 11:10 last edited by VRoninSee 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 -
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.htmlwrote on 16 Mar 2017, 11:15 last edited by the_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?!?!?! -
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 -
wrote on 16 Mar 2017, 11:44 last edited by 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
-
@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
-
The query should be:
qry.prepare("SELECT * FROM known_users WHERE email_address = :email ");
the part in the
TODO
isuserNameField =false; if(qry.exec()) userNameField =qry.next();
This does not check the password though, only the email
wrote on 16 Mar 2017, 21:13 last edited by@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. -
wrote on 16 Mar 2017, 22:49 last edited by 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 respondinguserNameField =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? -
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 respondinguserNameField =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?wrote on 17 Mar 2017, 03:43 last edited by@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
-
@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
wrote on 17 Mar 2017, 08:44 last edited by 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 -
@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 itwrote on 17 Mar 2017, 21:24 last edited by@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/16