Results 1 to 15 of 15

Thread: QSqlQueryModel setQuery problem

  1. #1
    Join Date
    Jan 2008
    Posts
    58
    Thanks
    3
    Qt products
    Qt4
    Platforms
    MacOS X

    Default QSqlQueryModel setQuery problem

    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
    Qt Code:
    1. 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%'");
    To copy to clipboard, switch view to plain text mode 

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

    Qt Code:
    1. QSqlQuery query;
    2. 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%'");
    3. query.bindValue(QString(":column"), QVariant(column));
    4. query.bindValue(QString(":searchString"), QVariant(searchString));
    5.  
    6.  
    7. //orderModel
    8. orderModel->setQuery(query);
    To copy to clipboard, switch view to plain text mode 

    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?
    Last edited by cyberboy; 10th March 2008 at 17:00. Reason: spelling error

  2. #2
    Join Date
    Jan 2006
    Location
    Warsaw, Poland
    Posts
    5,372
    Thanks
    28
    Thanked 976 Times in 912 Posts
    Qt products
    Qt3 Qt4
    Platforms
    Unix/X11 Windows

    Default Re: QSqlQueryModel setQuery problem

    It should be "... LIKE :searchString ..." and
    Qt Code:
    1. query.bindValue(QString(":searchString"), QVariant( '%' + searchString + '%'));
    To copy to clipboard, switch view to plain text mode 
    But you can't use the placeholder for the column.

  3. #3
    Join Date
    Jan 2008
    Posts
    58
    Thanks
    3
    Qt products
    Qt4
    Platforms
    MacOS X

    Default Re: QSqlQueryModel setQuery problem

    Thanks for the reply.

    The new situation doesn't work.

    Qt Code:
    1. QSqlQuery query;
    2. 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");
    3. // query.bindValue(QString(":column"), QVariant(column));
    4. query.bindValue(QString(":searchString"), QVariant('%' + searchString + '%'));
    5.  
    6.  
    7. //orderModel
    8. orderModel->setQuery(query);
    To copy to clipboard, switch view to plain text mode 

    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?

  4. #4
    Join Date
    Feb 2008
    Location
    South Korea
    Posts
    14
    Thanks
    3
    Qt products
    Qt4
    Platforms
    MacOS X Unix/X11 Windows

    Default Re: QSqlQueryModel setQuery problem

    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

  5. #5
    Join Date
    Jan 2006
    Location
    Warsaw, Poland
    Posts
    5,372
    Thanks
    28
    Thanked 976 Times in 912 Posts
    Qt products
    Qt3 Qt4
    Platforms
    Unix/X11 Windows

    Default Re: QSqlQueryModel setQuery problem

    Quote Originally Posted by cyberboy View Post
    Does somebody knows what I'm doing wrong?
    Which database do you use?

    What does lastError() return?
    Qt Code:
    1. query.prepare(...);
    2. query.bindValue( ... );
    3. query.exec();
    4. qDebug() << query.lastError();
    To copy to clipboard, switch view to plain text mode 

  6. #6
    Join Date
    Jan 2008
    Posts
    107
    Thanks
    36
    Thanked 2 Times in 2 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11

    Post Re: QSqlQueryModel setQuery problem

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

    Qt Code:
    1. char * sql = new char [#ofNeededBytes];
    2. QSqlQuery myQuery(myDBconn);
    3. sprintf( sql, "SELECT col1, col2 FROM myTable WHERE someCol=%s", mySearchValue );
    4. myQuery.exec();
    To copy to clipboard, switch view to plain text mode 

    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

  7. #7
    Join Date
    Jan 2006
    Location
    Warsaw, Poland
    Posts
    5,372
    Thanks
    28
    Thanked 976 Times in 912 Posts
    Qt products
    Qt3 Qt4
    Platforms
    Unix/X11 Windows

    Default Re: QSqlQueryModel setQuery problem

    Quote Originally Posted by pdoria View Post
    Why not doing it the simplest way?
    Because the simpliest way is susceptible to SQL injection attack?

  8. #8
    Join Date
    Jan 2008
    Posts
    107
    Thanks
    36
    Thanked 2 Times in 2 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11

    Default Re: QSqlQueryModel setQuery problem

    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.

  9. #9
    Join Date
    Jan 2006
    Location
    Warsaw, Poland
    Posts
    5,372
    Thanks
    28
    Thanked 976 Times in 912 Posts
    Qt products
    Qt3 Qt4
    Platforms
    Unix/X11 Windows

    Default Re: QSqlQueryModel setQuery problem

    Quote Originally Posted by pdoria View Post
    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.

  10. #10
    Join Date
    Jan 2008
    Posts
    58
    Thanks
    3
    Qt products
    Qt4
    Platforms
    MacOS X

    Default Re: QSqlQueryModel setQuery problem

    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)
    Last edited by cyberboy; 12th March 2008 at 14:51. Reason: reformatted to look better

  11. #11
    Join Date
    Jan 2008
    Posts
    107
    Thanks
    36
    Thanked 2 Times in 2 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11

    Default Re: QSqlQueryModel setQuery problem

    Quote Originally Posted by jacek View Post
    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 ... )

    Regards,
    Pedro.

  12. #12
    Join Date
    Jan 2006
    Location
    Warsaw, Poland
    Posts
    5,372
    Thanks
    28
    Thanked 976 Times in 912 Posts
    Qt products
    Qt3 Qt4
    Platforms
    Unix/X11 Windows

    Default Re: QSqlQueryModel setQuery problem

    Quote Originally Posted by cyberboy View Post
    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.

    Quote Originally Posted by cyberboy View Post
    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.

  13. #13
    Join Date
    Jan 2008
    Posts
    58
    Thanks
    3
    Qt products
    Qt4
    Platforms
    MacOS X

    Default Re: QSqlQueryModel setQuery problem

    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.

    Qt Code:
    1. void mainWindow::updateTables(){
    2.  
    3. //set queries
    4. this->setQueryCritics(orderModel, 1);
    5. this->setQueryCritics(finishModel, 2);
    6. this->setQueryCritics(trashModel, 3);
    7. this->setQueryCritics(clientModel, 4);
    8.  
    9. //load in interface
    10. this->interface->orderTable->setModel(orderModel);
    11. this->interface->finishTable->setModel(finishModel);
    12. this->interface->trashTable->setModel(trashModel);
    13. this->interface->clientTable->setModel(clientModel);
    14.  
    15. }
    To copy to clipboard, switch view to plain text mode 

    Qt Code:
    1. void mainWindow::setQueryCritics(QSqlQueryModel *model, int type)
    2. {
    3.  
    4.  
    5.  
    6. switch(type){
    7. case 1 :
    8. //orderModel
    9. model->setQuery("SELECT c.*, o.* FROM clients AS c, orders AS o WHERE o.cid = c.cid and o.currentStatus = 1 ");
    10. break;
    11. case 2 :
    12. //finishModel
    13. model->setQuery("SELECT c.*, o.* FROM clients AS c, orders AS o WHERE o.cid = c.cid and o.currentStatus = 2 ");
    14. break;
    15. case 3 :
    16. //trashModel
    17. model->setQuery("SELECT c.*, o.* FROM clients AS c, orders AS o WHERE o.cid = c.cid and o.currentStatus = 3 ");
    18. break;
    19. case 4 :
    20. //clientModel
    21. model->setQuery("SELECT * FROM clients");
    22. break;
    23. }
    24.  
    25. }
    To copy to clipboard, switch view to plain text mode 

    And that one does work!

  14. #14
    Join Date
    Jan 2006
    Location
    Warsaw, Poland
    Posts
    5,372
    Thanks
    28
    Thanked 976 Times in 912 Posts
    Qt products
    Qt3 Qt4
    Platforms
    Unix/X11 Windows

    Default Re: QSqlQueryModel setQuery problem

    Quote Originally Posted by cyberboy View Post
    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?

  15. #15
    Join Date
    Jan 2006
    Location
    Warsaw, Poland
    Posts
    5,372
    Thanks
    28
    Thanked 976 Times in 912 Posts
    Qt products
    Qt3 Qt4
    Platforms
    Unix/X11 Windows

    Default Re: QSqlQueryModel setQuery problem

    Quote Originally Posted by pdoria View Post
    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:
    Qt Code:
    1. QSqlQuery q( "SELECT whever FROM " + lineEdit->text() );
    To copy to clipboard, switch view to plain text mode 
    person who is using your application will be able to do anything with your database regardless how well guarded your server is.

    Quote Originally Posted by pdoria View Post
    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 ... )
    Consider this example:
    Qt Code:
    1. char buf[100];
    2. sprintf( buf, "abc %s def", str );
    To copy to clipboard, switch view to plain text mode 
    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:
    Qt Code:
    1. // user fills str1 with data
    2. ...
    3. char buf[ SOME_LENGTH ];
    4. sprintf( buf, str1, str2 );
    To copy to clipboard, switch view to plain text mode 
    This is so evil that I won't even say a word about it.

    Note that "C strings" are Evil too.

Similar Threads

  1. QSqlQueryModel write subclass
    By skuda in forum Qt Programming
    Replies: 6
    Last Post: 29th October 2007, 17:18
  2. Replies: 16
    Last Post: 7th March 2006, 16:57

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Digia, Qt and their respective logos are trademarks of Digia Plc in Finland and/or other countries worldwide.