QSqlQuery bindValue doesn't work with DROP TABLE

emorymiles

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'?

D0IT

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).

emorymiles

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) );@

ChrisW67

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.