Now that I'm getting a bit of a handle on EWB other issues pop into mind.
Many of our tables are very large so we typically populate non-data-aware
controls with some subset of the data as defined by other parameters, such
as starting and ending dates, for example. So it seems to me that when I
write our own DBISAM-JSON conversion functions we are going to also want to
pass along other parameters (value-pairs), otherwise we'll be sending
enormous amounts of JSON data to the client. In other words, we are going to
want to constrain the BuildRows function on the server side.

Right?

Jerry

Jerry,

This is how i'm looking at this :

1. Generically i will want server side paging for data so as to limit
the amount of data transferred.
The TDataSet.LoadRows even has an append as 2nd parameter so some this
is even built in by Tim to do requests based on for example user
scrolling to end or such.
you'd want to just include some additional parameters that would
indicate start and end index/record position as well as possibly max
number of rows to return.
(e.g. request.Params.Add('maxrows=100;
request.Params.Add('startrow=201');
).

2. I like to treat the web service API as not being same as tables yet
use the tables as a easy data exchange mechanism - i'm planning to take
some other runtime data and send it across as rows even though they
never started out as dbisam table.

How you control this is very much up to you - one way would be to
include additional parameters in the actual URL (same as above Params.Add).

The other would be to use a REST like architecture where the URL itself
defines what you want and and your back-end will include business logic
rather than having to include it on client side (e.g.
"http://myserver/users" could return users table while
"http://myserver/users/active" would return list of active users but
only server would know how that is determined).

I will be using both simultaneously as they both have value - the
"/users/active" makes sense on server side but paging option
"startrow=201" makes sense on client side.

If your data is all in DB and you want very generic solution then have
the URLs map to sql statements (that can also be stored in DB) and
params act as additional filter conditions for WHERE clause or such -
once you write the basic engine for this then all you need to do after
is update the SQL statements table as you add more features.

Raul





On 7/22/2012 5:46 PM, Jerry Clancy wrote:
> Now that I'm getting a bit of a handle on EWB other issues pop into
> mind. Many of our tables are very large so we typically populate
> non-data-aware controls with some subset of the data as defined by other
> parameters, such as starting and ending dates, for example. So it seems
> to me that when I write our own DBISAM-JSON conversion functions we are
> going to also want to pass along other parameters (value-pairs),
> otherwise we'll be sending enormous amounts of JSON data to the client.
> In other words, we are going to want to constrain the BuildRows function
> on the server side.
>
> Right?
>
> Jerry
>

Jerry,

> In other words, we are going to want to constrain the BuildRows function
> on the server side.

How about executing a query instead of a opening a table?

By using an additional param in EWB I pass the sql-string to the server:

procedure THauptForm.ExecuteQuery(DataSet: TDataSet; QueryString: string);
var
   TempRequest: TServerRequest;
begin
   Cursor := crProgress;
   TempRequest:=RequestQueue.GetNewRequest;
   TempRequest.URL:='http://localhost:8080';
   TempRequest.Params.Add('sql=' + QueryString);
   TempRequest.Data:=DataSet;
   TempRequest.ResponseContent.LineSeparator:=#13+#10;
   TempRequest.OnComplete:=RequestComplete;
   RequestQueue.AddRequest(TempRequest);
end;

On the server side e.g.:

function TForm1.EDBWebServer1Resource(Sender: TObject; Thread:
TEDBHTTPServerThread; const HostName, ResourceName: AnsiString): Boolean;
var
  TempStrings: TStrings;
begin

....

with Thread do
  begin
    ...
      TempStrings := TStringList.Create;
      try
        if RequestParams.Values['sql'] <> '' then
        begin
          try
            with MyQuery do
            begin
              close;
              sql.Text := RequestParams.Values['sql'];
              Open;
            end;
            TempStrings.Add(BuildRows(MyQuery));
            SendCustomContent(TempStrings.Text, 'application/json;
charset=ISO-8859-1', '');
          except
            on E: Exception do
              SendError(HTTP_BAD_REQUEST, E.Message);
          end;
        end;
      finally
        FreeAndNil(TempStrings);
      end;
    end;
  end;
 ...
end;

Regards Uli

Uli,

<< By using an additional param in EWB I pass the sql-string to the server:
>>

I would strongly advise against doing this - it is too easy for someone to
inject malicious SQL or simply send over malicious SQL to your web server.
It's much better to simply have the back-end accept a few URL parameters
that specify the filtering constraints, and then build the SQL from those
using parameterized statements.

Tim Young
Elevate Software
www.elevatesoft.com

Uli,

"Uli Becker" <johnmuller54@googlemail.com> wrote in message
news:071FF8A9-E1AD-4587-B0CF-BB791B66AC61@news.elevatesoft.com...
> Jerry,
>
>> In other words, we are going to want to constrain the BuildRows function
>> on the server side.
>
> How about executing a query instead of a opening a table?
>
> By using an additional param in EWB I pass the sql-string to the server:
>....


Thanks, but I'm not an SQL guy, one of the reasons I haven't moved to EDB.
Only want to take on a few new things at once. Very over the "new frontiers"
guy I was when young. Too many arrows and lessons learned the hard way.

I'm actually comfortable with the procedural code, most of which I can just
copy to EWB, I believe, with just minor component adjustments. Thanks for
the suggestion and code, however.

Cheers,

Jerry

Tim,>

> I would strongly advise against doing this - it is too easy for someone
> to inject malicious SQL or simply send over malicious SQL to your web
> server. It's much better to simply have the back-end accept a few URL
> parameters that specify the filtering constraints, and then build the
> SQL from those using parameterized statements.

Good point - I didn't think about that during "playing" with EWB. Of
course you are right!

Uli

"Tim Young [Elevate Software]" wrote:

<<I would strongly advise against doing this - it is too easy for someone to
inject malicious SQL or simply send over malicious SQL to your web server.
It's much better to simply have the back-end accept a few URL parameters
that specify the filtering constraints, and then build the SQL from those
using parameterized statements.>>

Wouldn't it be sufficient to encrypt QueryString with a reversible algorithm such as:

function EnDeCrypt(const Value : String) : String;
var
 CharIndex : integer;
begin
 Result := Value;
 for CharIndex := 1 to Length(Value) do
   Result[CharIndex] := chr(not(ord(Value[CharIndex])));
end;

or would this be a naive approach ?

Alex

There are 2 issues at play here :

1. As Tim mentioned allowing SQL to be supplied from client side in
general is something to avoid or you will be exposing yourself to all
kinds of issues.

2. This is javascript and all the code will be in the source form in the
browser anyways so if SQL is part of your app then i can just look it
from there and also figuring out encryption is pretty easy since i have
access to source.

I'm sure it's possible to come up with some method of doing this
reasonably securely (download sql from server or use user password as
encryption key) but in long term its better to design your API and
communication not to be vulnerable in the first place

Raul





On 7/23/2012 5:24 PM, alexza wrote:
> "Tim Young [Elevate Software]" wrote:
>
> <<I would strongly advise against doing this - it is too easy for someone to
> inject malicious SQL or simply send over malicious SQL to your web server.
> It's much better to simply have the back-end accept a few URL parameters
> that specify the filtering constraints, and then build the SQL from those
> using parameterized statements.>>
>
> Wouldn't it be sufficient to encrypt QueryString with a reversible algorithm such as:
>
> function EnDeCrypt(const Value : String) : String;
> var
>    CharIndex : integer;
> begin
>    Result := Value;
>    for CharIndex := 1 to Length(Value) do
>      Result[CharIndex] := chr(not(ord(Value[CharIndex])));
> end;
>
> or would this be a naive approach ?
>
> Alex
>

Thanks a lot Raul,

I guessed being naive...thanks for making me understand better.

Alex

<<I would strongly advise against doing this - it is too easy for someone to
inject malicious SQL or simply send over malicious SQL to your web server.
It's much better to simply have the back-end accept a few URL parameters
that specify the filtering constraints, and then build the SQL from those
using parameterized statements.>>

This would allow anyone that sees the url and parameters still to be able to get to your data. It's not as bad as the actual select or update statements but it's still not very secure.

I am thinking that by really obfuscating the js code so that it can't be read, and encrypting the data that's in the url would be a reasonable way to stop anyone from getting to the data.

I know there aren't many php people here, but I wonder how I could reverse the encryption that Alexza was suggesting in php so I could read the encrypted data that's being passed.

Big Al