QSqlQuery bindValue doesn't work with DROP TABLE



  • I'm using Qt5, sqlite on windows... and I'm having trouble using QSqlQuery::bindValue with "DROP TABLE" command.

    The error I keep seeing says "Parameter count mismatch" which doesn't make sense. Am I missing a small detail somewhere?

    @ // clear anything in the DB.
    QStringList list << "DataItem" << "DataBlock";

    foreach( QString qs, list )
    {
    QSqlQuery query;
    query.prepare( "DROP TABLE :tableName" );
    query.bindValue( ":tableName", qs );
    bOk = query.exec();
    if( !bOk )
    qDebug() << "ERROR:" << query.executedQuery() << "-" << query.lastError().text();
    }@

    Listing the tables before and after the above code produces:
    Tables
    "sqlite_sequence"
    "DataBlock"
    "DataItem"

    ERROR: "DROP TABLE ?" - " Parameter count mismatch"
    ERROR: "DROP TABLE ?" - " Parameter count mismatch"

    Tables
    "sqlite_sequence"
    "DataBlock"
    "DataItem"

    If I write a QSqlQuery specifically for each table, it works (drops tables, no errors) - as in the following code:

    @ QSqlQuery query;
    query.prepare( "DROP TABLE DataItem" );
    bOk = query.exec();
    if( !bOk )
    qDebug() << query.lastError().text();

    query.prepare( "DROP TABLE DataBlock" );
    bOk = query.exec();
    if( !bOk )
    qDebug() << query.lastError().text();@

    I have used QSqlQuery & bindValue with 'SELECT' and 'INSERT' statements without any problems. Is there a reason why it doesn't work with 'DROP TABLE'?



  • AFAIK dynamic table name binding is not supported by any database. That's why your query doesn't work. Binding values is meant for the actual value(s) (WHERE field = :bindValue).



  • Thanks. I figured out a solution. Looks like a simple QString replacement is all that is needed.

    @ QSqlQuery query;
    query.prepare( tr("DROP TABLE %1").arg(qs) );@



  • If the value of qs can come from a user be careful of invalid or malicious input. For example with MySql if a user supplies qs = "a, b, c, d" then four tables can be dropped where only one was intended.


Log in to reply
 

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