inseting data in a mysql database with prepared query



  • Hello everybody , i am unable to send data to a table by using prepared query when the user write the apostrophi symbol in his data ('). That is my code :

    QSqlQuery req;
        req.prepare("insert into fournisseur (nom,ville,matriculefour,info_supp) values( :nom, :ville, :matricule , :info_supp)");
        req.bindValue(":nom", four->getNom());
        req.bindValue(":ville", four->getVille());
        req.bindValue(":matricule", matricule);
        req.bindValue(":info_supp",four->getInfoSupp());
    
        //requete fini
        if(!req.exec(count)){
            err.status = false;
            err.code = 125;
            err.msg = req.lastError().text() +" [ "+req.lastQuery()+"] ";
            return err;
        }else{
            err.status = true;
            err.code = 200;
            err.msg = "Ajout du fournisseur reussit";
            return err;
        }
    

    i am using Qt 5.3.0 on windows 8.1; the result i have is that:

    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'bh')' at line 1 QMYSQL: Unable to execute query [ INSERT INTO fournisseur(nom,ville,matriculefour,info_supp)values(' hfn' ,'hucm' ,'25hhuc', 'huio'bh')]
    

    in this case the user as type huio'bh and it has to be insert into the column info_supp which is the last column



  • @adonisQt97

    Just a quick question?
    What exactly is count in your req.exec statement and what is stored in count?

    As far as I read the doc correctly QSqlQuery::exec(QString &query) executes the sql statement stored in the string query.


  • Qt Champions 2016

    @adonisQt97 said:
    Hi

    • 'huio'bh'
      This is not valid data. If you allow user to use single quotes, you
      must escape it by using an extra one.
      As far as I know :)
      Maybe you can use QString::Replace to do it easy on save.


  • @the_ yes i have see this error later but when i modify the code a execute the good query i have another error

    Using unsupported buffer type: 6741409 (parameter: 1) QMYSQL3: Unable to bind value [ insert into fournisseur (nom,ville,matriculefour,info_supp) values( ?,?,? ,?) ] 
    


  • @adonisQt97 said:

    Using unsupported buffer type:
    when i check the features of the QMYSQL3 diver like this :

    qDebug() << appBD.driver()->hasFeature(QSqlDriver::PositionalPlaceholders);
    qDebug() << appBD.driver()->hasFeature(QSqlDriver::PreparedQueries);
    qDebug() << appBD.driver()->hasFeature(QSqlDriver::NamedPlaceholders);
    

    i obtaint

    false
    false
    false
    

    do anybody have another solution?


  • Lifetime Qt Champion

    Hi,

    You can build the query by hand using e.g. QString::args.
    For example:

    QString("insert into fournisseur (nom) values(%1)").arg(four->nom())
    

    or

    "insert into fournisseur (nom) values(" + four->nom() + ")"
    

Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.