Wt::Dbo::Session date query format.

Added by Aleksander Fular over 10 years ago

Hello,

I've been trying to wrap the call to "find" method on the Wt::Dbo::Session object, with my own non-templated one so I can test it freely.

So instead of using standards approach and that is:

dbSession_.find<db::models::BlogPost>("where date >= ? "
      "and date < ? "
      "and (state = ? or author_id = ?) "
      "and title_as_url = ?").bind(Wt::WDateTime(pathParser.getLowerTimeConstraint())).bind(
      Wt::WDateTime(pathParser.getUpperTimeConstraint())).bind(db::models::BlogPost::kPublished).bind(
      dbSession_.getLoggedInUserId()).bind(pathParser.getTitle());

I would like to use it like this:

 dbSession_.fetchPosts(
      Wt::WString("where date >= {1} "
      "and date < {2} "
      "and (state = {3} or author_id = {4}) "
      "and title_as_url = {5}").arg(Wt::WDateTime(pathParser.getLowerTimeConstraint()).toString("yyyy-MM-dd")).arg(
      Wt::WDateTime(pathParser.getUpperTimeConstraint()).toString("yyyy-MM-dd")).arg(db::models::BlogPost::kPublished).arg(
      dbSession_.getLoggedInUserId()).arg(pathParser.getTitle()));

This way i can have one virtual function getting const Wt:WString& as parameter, and freely mock it in the tests.

Posts DbSessionImp::fetchPosts(const Wt::WString& condition) {
    return find<models::BlogPost>(condition.toUTF8());
}

However it seems that I cannot get the format of the date right i get:

"Wt: fatal error: Sqlite3: select ""id"", ""version"", ""state"", ""date"", ""title"", ""title_as_url"", ""brief_src"", ""brief_html"", ""body_src"", ""body_html"", ""author_id"" from ""blog_posts"" where date >= 2014-10-14-00:00:00 and date < 2014-10-15-00:00:00 and (state = 0 or author_id = -1) and title_as_url = blogpost2: near "":00"": syntax error"

Could you point me to the correct format of WDateTime as String passed to query? I tried to search in the code but with no success.

Thanks

Replies (4)

RE: Wt::Dbo::Session date query format. - Added by Aleksander Fular over 10 years ago

It turned out that all I was missing was quotation around the arguments:

posts = dbSession.fetchPosts(
    Wt::WString("where date >= \"{1}\" "
    "and date < \"{2}\" "
    "and (state = \"{3}\" or author_id = \"{4}\") "
    "and title_as_url = \"{5}\" ").arg(Wt::WDateTime(pathParser.getLowerTimeConstraint()).toString("yyyy-MM-dd hh:mm:ss")).arg(
    Wt::WDateTime(pathParser.getUpperTimeConstraint()).toString("yyyy-MM-dd hh:mm:ss")).arg(db::models::BlogPost::kPublished).arg(
    dbSession.getLoggedInUserId()).arg(pathParser.getTitle()));

RE: Wt::Dbo::Session date query format. - Added by Koen Deforche over 10 years ago

Hey,

Careful though as this is an SQL Injection attack vector.

Koen

RE: Wt::Dbo::Session date query format. - Added by Aleksander Fular over 10 years ago

Hey Koen,

Internally it just invokes find method:

Posts DbSessionImp::fetchPosts(const Wt::WString& condition) {
    return find<models::BlogPost>(condition.toUTF8());
}

Is there a slot for sql injection then?

Or find method from dbsession protects before that?

RE: Wt::Dbo::Session date query format. - Added by Koen Deforche over 10 years ago

Yes, this is vulnerable.

As soon as you use string concatenation to mix SQL with strings (which may be under control of the user), then you have a SQL Injection attack vector.

The bind() functions allow you to separate data from the SQL query itself.

Koen

(1-4/4)

Project

General

Profile

Wt