Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 7 of 7 total
Thread Dataset Query no longer working?
Sat, Feb 22 2014 9:14 PMPermanent Link

Glenn Mc

Library Concepts

Something worked for me in EWB 102b2 that probably shouldn't have, but it seems not to work in v103b4.

Using server EWB 103b4. Dataset: "SearchTitle1" uses a Query as follows:
  SELECT * FROM Titles {WHEREline=""}
  Note: WHEREline is not a Dataset field. It is a holder for text to be added onto the SQL query statement.

WHEREline is returned from a function and might look like:
     WHEREline := ' WHERE (UPPER(Title) LIKE ''%FROGGY%'') ORDER BY AlphaTitle' ;

     SearchTitle1.Params.Clear ;
     SearchTitle1.Params.Add ('WHEREline=' + WHEREline) ;
     Database.Load (SearchTitle1);

The BeforeLoad trigger does not fire. The error message is a "Dataset load response error."

My question is, should it still work in v103b4 or did I unknowingly mess up my code somewhere?

Thanks.
Sun, Feb 23 2014 4:30 AMPermanent Link

Uli Becker

Glenn,

you should rewrite your query like this:

SELECT * FROM Titles where title like {title=''} order by AlphaTitle

and setting the param like this, so that the param contains just one
token. Don't pass complete conditions as a parameter.

SearchTitle1.Params.Clear ;
SearchTitle1.Params.Add ('title=''%' + placeholder + '%''');
Database.Load (SearchTitle1);

Not tested though (especially the numbers of quotes Smile)

Uli
Mon, Feb 24 2014 4:31 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Glenn,

<< Something worked for me in EWB 102b2 that probably shouldn't have, but it
seems not to work in v103b4.

Using server EWB 103b4. Dataset: "SearchTitle1" uses a Query as follows:
  SELECT * FROM Titles {WHEREline=""}
  Note: WHEREline is not a Dataset field. It is a holder for text to be
added onto the SQL query statement.  >>

This is a hacker's dream, because it allows them to use SQL injection to add
DELETE FROM, etc. to your internal SQL.  This is why 1.03 sanitizes the
parameters now so that they can only be valid single-token constants.  This
is mentioned in the release notes:

http://www.elevatesoft.com/download?action=info&category=ewb&type=ewb&majorversion=1&version=1.03

"Web Server

The Elevate Web Builder Web Server now properly sanitizes all parameter
values before including them in queries in the DataSet Manager"

However, it's mentioned under improvements/enhancements, and probably should
have been mentioned as a breaking change.

The workaround is to do as Uli indicates.

Tim Young
Elevate Software
www.elevatesoft.com


Mon, Feb 24 2014 6:01 PMPermanent Link

Glenn Mc

Library Concepts

Thank you both.

Uli, even your brief example showed me coding possibilities I had not considered. Thanks!

And Tim, thank you for making EWB so solid. I should have studied the Release notes more closely and tried to relate them to what I had done. You were fine in not calling it a 1.03 breaking change. Nobody in their right mind should have tried what I did!-)
Mon, Mar 10 2014 7:57 PMPermanent Link

Glenn Mc

Library Concepts

Now I have a follow-up question.

In DBISAM I can do a multiple-word search as follows:
   SELECT title,author,publisher FROM ItemTable WHERE
            UPPER(title) LIKE '' + param1 + ''
            AND UPPER(title) LIKE '' + param2 + ''

(I probably have the quotes all wrong, but I can sort those out later.)

In EWB the structure of a multiple-word search could be:
   SELECT title,author,publisher FROM ItemTable WHERE
            UPPER(title) LIKE {title=''}
            AND UPPER(title) LIKE  {title=''}

  SearchTitle1.Params.Clear ;
  SearchTitle1.Params.Add ('title=' + ''%FROGGY%'' ) ; // param1
  SearchTitle1.Params.Add ('title=' + ''%BOOK%'' ) ;      // param2
  Database.Load (SearchTitle1);

Of course, this does not work in EWB. So is there a way to send two (or more) different parameters for the same field?

Thanks.
Tue, Mar 11 2014 9:17 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Glenn,

<< Of course, this does not work in EWB. So is there a way to send two (or
more) different parameters for the same field?  >>

Yes, just name them uniquely, like this:

   SELECT title,author,publisher FROM ItemTable WHERE
            UPPER(title) LIKE {title1=''}
            AND UPPER(title) LIKE  {title2=''}

  SearchTitle1.Params.Clear ;
  SearchTitle1.Params.Add ('title1=' + ''%FROGGY%'' ) ; // param1
  SearchTitle1.Params.Add ('title2=' + ''%BOOK%'' ) ;      // param2
  Database.Load (SearchTitle1);

Tim Young
Elevate Software
www.elevatesoft.com
Tue, Mar 11 2014 12:20 PMPermanent Link

Glenn Mc

Library Concepts

THANK YOU .... that was easy.
Image