QSqlQueryModel setQuery problem

[cyberboy]

Hi guys,

I'm stuck with a problem, I was so proud that the search function worked in my table view :)

This is the code


orderModel->setQuery("SELECT c.*, o.* FROM clients AS c, orders AS o WHERE o.cid = c.cid and o.currentStatus = 1 AND articleDescription LIKE '%blaat%'");


But it has to be dynamical sow I came up with this piece of code



QSqlQuery query;
query.prepare("SELECT c.*, o.* FROM clients AS c, orders AS o WHERE o.cid = c.cid and o.currentStatus = 1 AND :column LIKE '%:searchString%'");
query.bindValue(QString(":column"), QVariant(column));
query.bindValue(QString(":searchString"), QVariant(searchString));


//orderModel
orderModel->setQuery(query);


Before I wrote this code I did use a QString and the append function to generate the code. And that didn't work even.

I tested the query and it works well.

Does somebody know how to solve this problem?

[jacek]

It should be "... LIKE :searchString ..." and
query.bindValue(QString(":searchString"), QVariant( '%' + searchString + '%'));
But you can't use the placeholder for the column.

[cyberboy]

Thanks for the reply.

The new situation doesn't work.



QSqlQuery query;
query.prepare("SELECT c.*, o.* FROM clients AS c, orders AS o WHERE o.cid = c.cid and o.currentStatus = 1 AND lastName LIKE :searchString");
// query.bindValue(QString(":column"), QVariant(column));
query.bindValue(QString(":searchString"), QVariant('%' + searchString + '%'));


//orderModel
orderModel->setQuery(query);


That is the code I used in the new situation, I commented the line which binds the :column value and I removed the :column and replaced it by 'lastName'.

Does somebody knows what I'm doing wrong?

[ibergmark]

Just a thought:

Looking at the documentation for setQuery, it states that the query must be active, and must not be isForwardOnly().


void QSqlQueryModel::setQuery ( const QSqlQuery & query )
Resets the model and sets the data provider to be the given query. Note that the query must be active and must not be isForwardOnly().

Which database are you using?

/ Ingemar

[jacek]

Does somebody knows what I'm doing wrong?
Which database do you use?

What does lastError() return?

query.prepare(...);
query.bindValue( ... );
query.exec();
qDebug() << query.lastError();

[pdoria]

I might be somewhat out of focus but...
Why not doing it the simplest way?



char * sql = new char [#ofNeededBytes];
QSqlQuery myQuery(myDBconn);
sprintf( sql, "SELECT col1, col2 FROM myTable WHERE someCol=%s", mySearchValue );
myQuery.exec();


Also, I've noticed that you're issuing your SELECT statements with myTable.* ...
This, as explained in the docs, is always bad policy... You should always specify columns ;)

HTH,
Pedro Doria Meunier

[jacek]

Why not doing it the simplest way?
Because the simpliest way is susceptible to SQL injection attack?

[pdoria]

True enough Jacek...

Unless I'm totally out of my mind that wouldn't be an issue when both the app and the db server are running on the same machine (e.g. a socket server that only listens to some devices input, parses their data and communicates with the db server in the localhost)

Pedro.

[jacek]

that wouldn't be an issue when both the app and the db server are running on the same machine (e.g. a socket server that only listens to some devices input, parses their data and communicates with the db server in the localhost)
SQL injection doesn't happen because of networked connection, but poorly written application, which allows users to modify the query by entering non-standard data.

Let's consider your example: SELECT col1, col2 FROM myTable WHERE someCol=%s
What would happen if %s was changed to "xxx OR 1" or "xxx UNION SELECT login, password FROM users"?


P.S. Please don't use sprintf() in C++ code.

[cyberboy]

Sorry for my late response, I was at school all day long.


Which database are you using?

I'm using a SQLite database.


SQL injection doesn't happen because of networked connection, but poorly written application, which allows users to modify the query by entering non-standard data.

Let's consider your example: SELECT col1, col2 FROM myTable WHERE someCol=%s
What would happen if %s was changed to "xxx OR 1" or "xxx UNION SELECT login, password FROM users"?

Is there a way of preventing that malicious characters get in your sql query? Like mysql_escape_string in php?


Looking at the documentation for setQuery, it states that the query must be active, and must not be isForwardOnly().

How can I get my query in active mode?


What does lastError() return?

And lastError() doesn't return any thing, just an empty message box ( yeah, I'm using message boxes because somehow qDebug won't work)

[pdoria]

SQL injection doesn't happen because of networked connection, but poorly written application, which allows users to modify the query by entering non-standard data.

Let's consider your example: SELECT col1, col2 FROM myTable WHERE someCol=%s
What would happen if %s was changed to "xxx OR 1" or "xxx UNION SELECT login, password FROM users"?


P.S. Please don't use sprintf() in C++ code.

Even in the case where the server just sits in a rack of an ISP datacenter and the only users allowed in are the server's administrators?!

Btw: Why is it "evil" to use sprintf in C++ ? Please elaborate as I find this very useful (at least my former PHP head thinks so ... :o )

Regards,
Pedro.

[jacek]

and about the sql injections, is there a way of preventing that malicious characters get in your sql query. Like mysql_escape_string in php?
Using bindValue() should be enough.


And lastError() doesn't return any thing, just an empty message box ( yeah, I'm using message boxes because somehow qDebug won't work)
If the error type is 0, then there was no error in the query.

[cyberboy]

What I also discovered was that the sorting function in the table view doesn't work. Even when I had the sortingEnabled set to true.

Are those problems related to each other somehow?

Both cases have the same issue, it seems like they can't handle the data inside the tableview, whether it's refreshing the data or sorting the data.

Before I wrote the code to search the table view I wrote this to update the table view after new data was inserted into the database.


void mainWindow::updateTables(){

//set queries
this->setQueryCritics(orderModel, 1);
this->setQueryCritics(finishModel, 2);
this->setQueryCritics(trashModel, 3);
this->setQueryCritics(clientModel, 4);

//load in interface
this->interface->orderTable->setModel(orderModel);
this->interface->finishTable->setModel(finishModel);
this->interface->trashTable->setModel(trashModel);
this->interface->clientTable->setModel(clientModel);

}



void mainWindow::setQueryCritics(QSqlQueryModel *model, int type)
{



switch(type){
case 1 :
//orderModel
model->setQuery("SELECT c.*, o.* FROM clients AS c, orders AS o WHERE o.cid = c.cid and o.currentStatus = 1 ");
break;
case 2 :
//finishModel
model->setQuery("SELECT c.*, o.* FROM clients AS c, orders AS o WHERE o.cid = c.cid and o.currentStatus = 2 ");
break;
case 3 :
//trashModel
model->setQuery("SELECT c.*, o.* FROM clients AS c, orders AS o WHERE o.cid = c.cid and o.currentStatus = 3 ");
break;
case 4 :
//clientModel
model->setQuery("SELECT * FROM clients");
break;
}

}


And that one does work!

[jacek]

What I also discovered was that the sorting function in the table view doesn't work. Even when I had the sortingEnabled set to true.

Are those problems related to each other somehow?
I'm not sure what does "the sorting function in the table view doesn't work" exactly mean in your case. I thought you can't see any data in the table.

Do you invoke exec() on your query objects?

[jacek]

Even in the case where the server just sits in a rack of an ISP datacenter and the only users allowed in are the server's administrators?!
It doesn't matter where the server is, but who enters the data.

If you do something like this in your code:
QSqlQuery q( "SELECT whever FROM " + lineEdit->text() );person who is using your application will be able to do anything with your database regardless how well guarded your server is.


Btw: Why is it "evil" to use sprintf in C++ ? Please elaborate as I find this very useful (at least my former PHP head thinks so ... :o )
Consider this example:
char buf[100];
sprintf( buf, "abc %s def", str );
What is the maximum allowed length for str? What will happen if str is longer? Why should you care about it, if QString can do that for you?

Now consider this:
// user fills str1 with data
...
char buf[ SOME_LENGTH ];
sprintf( buf, str1, str2 );
This is so evil that I won't even say a word about it.

Note that "C strings" are Evil too.