Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 10 of 10 total
Thread Lots of EWB Experience BUT Minimal DB - Need Advice Please
Sun, Nov 23 2014 5:32 AMPermanent Link

Mark Brooks

Slikware

Avatar

All,

I've been using EWB for some time now and have completed a number of very challenging projects. It's an awesome tool and I can't wait to start playing with EWB 2.

During this time, however, I've been using EWB to provide a front-end to our pre-existing secure cloud platform. This in itself publishes a REST API. The platform is essentially MS SQL server with a REST API layer created using MS .NET. So, I've had to create a set of EWB wrapper class for the API primitives and parse the JSON accordingly.

Moving forward, we have visibility of some new projects which won't need the secure platform backend and this is where my experience fails me. I imagine that for these projects I could use EDB but this is not something that I've ever done before.

Assuming that our web sever is Windows, running IIS, does it work like this:

- Install EDB on the server
- Hook it up to IIS (somehow?)
- Use EWB to talk directly to it?

Can anybody who's been here before (including even Tim) point me in the right direction please? Please feel free to treat me like a baby (within reason ....).

Many thanks
Mark
Sun, Nov 23 2014 6:10 AMPermanent Link

Walter Matte

Tactical Business Corporation

Mark:

I know you already understand most of what I will write so forgive me in advance.  

IIS Web Server: There is no link or direct interaction between EDB and IIS.    

Only the Web Server distributed with EWB or Web Servers written by EWB users (me included) have that built-in Web Server - Database interaction.


A Web App generated from EWB cannot talk directly to any database.  The JS created by EWB has been downloaded and is executing within in the Client Browser.  The EWB App then makes requests back to the Web Server.

On the Web Server side you could use PHP web pages to respond an EWB Web App and these PHP web pages on the web server would do the interaction with the database.   (I don't do this but others have and there is example I believe distributed with EWB.)


I'm sure others will have more to add.

Walter
Sun, Nov 23 2014 6:53 AMPermanent Link

Mark Brooks

Slikware

Avatar

Walter Matte wrote:

>>I know you already understand most of what I will write so forgive me in advance.

No problem. I honestly know incredibly little about the DB side of things.  

>> IIS Web Server: There is no link or direct interaction between EDB and IIS.    
>> Only the Web Server distributed with EWB or Web Servers written by EWB users (me included) have that built-in >> Web Server - Database interaction.

Understood. Is the EWB web server a reasonable choice to get started with? I'm thinking perhaps 10 - 20 concurrent users maximum? Does it "connect" to EDB pretty much seamlessly?

>> A Web App generated from EWB cannot talk directly to any database.  The JS created by EWB has been  >>downloaded and is executing within in the Client Browser.  The EWB App then makes requests back to the Web >>Server.

Yup. I think I get this. So if you write your own web server rather than using the EWB provided one then you need to provide a REST interface per the EWB spec if you want the automatic DB functionality?

>> On the Web Server side you could use PHP web pages to respond an EWB Web App and these PHP web >> pages on the web server would do the interaction with the database.   (I don't do this but others have and there is >> example I believe distributed with EWB.)

Never touch PHP so would likely try to avoid this day now.

>> I'm sure others will have more to add.

Much appreciated Walter. I'm getting there already. Cheers.
Sun, Nov 23 2014 12:45 PMPermanent Link

Raul

Globestar Systems

Team Elevate Team Elevate

On 11/23/2014 5:32 AM, Mark Brooks wrote:
> Assuming that our web sever is Windows, running IIS, does it work like this:
> - Install EDB on the server

Generally yes but not the actual EDB install (that you get from
elevatesoft).

You need to decide how the data will be accessed - it  basically boils
down to whether you will run the
(1) EDB server on this server
(2) Access data directly using file system.

Either way you still need another prices of the puzzle that allows the
web server to access the data so see next point.

> - Hook it up to IIS (somehow?)

To continue from previous point easiest option is to run the EWB Web
Server (EWB WS) that can use either option above (you can configure EDB
in local or remote mode) and since it already talks EWB JSON for
datasets you'd be all done.

Note that you could run EWB web server alongside IIS if you need IIS for
something else (EWB WS must be on different port). You can also load the
EWB app from IIS and have it use EWB WS just for data access (for
example if you want to use ISS security and such - though note that you
still need to open EWB WS to internet access).

This will be by far the simplest solution if you wish to get started
quickly and not have to worry about the back-end web service side.

Another option is to write an ISAPI DLL plugin in delphi for example so
that it can access EDB data directly and now you would have a pure IIS
solution (though now you'd need to write the plugin part still).

EDB does support visual studio so i would assume (though i have not done
it myself) that one can write ASP (or whatever it is is now called) IIS
web app is Visual Studio, deploy it on IIS and again have a pure IIS
solution (you would need VS and EDB DAC edition for this).

Yet another is to use one of the other frameworks like RemObjects or
MorMot or even delphi datasnap to write the backend (most of these can
generate a stand-alone web server or can create a ISAPI DLL i believe).


> - Use EWB to talk directly to it?
> Can anybody who's been here before (including even Tim) point me in the right direction please? Please feel free to treat me like a baby (within reason ....).

In general you have few components at play:
1. Web server to host the EWB app so user can access it by URL
2. EWB app running in the end-user browser once user loads it
3. EWB apps can only use HTTP queries to access back end services
(TServerRequest in EWB) so you will need a server side "web services"
that you can talk to in order to get/update data
4. The back end web service is only part that needs to know how to
access databases so this is where the data access layer is.

Few notes:
a. the web server in point 1 and 4 can be same but does not have to be -
you can host the EWB app from any web server (it's just html/js/css
files) but the server in point 4 needs to actually understand talk the
EWB specific web services (think of your REST API in current solution).
This is the reason you can run EWB WS for example alongside IIS so one
serves EWB app files and other actual DB data

b. EWB WS does all of this (including DB access) "automagically" already
so using EWB WS is the quiskest way to get started (either as the only
web server or one for data access)

c. Note that in either case you can mix-and-match things also - for
example use the EWB JSON dataset logic to access data but also publish
some custom REST APIs alongside to do other things. For example in our
case we use a mix - our server has a REST style APIs for authentication
but then once you're authenticated you can use EWB dataset functions.
Similarly for some other operations we have REST APIs EWB app can call
that effectively result in multiple changes on backend DB this way we're
moving the business logic to server and EWB app just deals with business
logic primitives.

Raul
Sun, Nov 23 2014 12:47 PMPermanent Link

Walter Matte

Tactical Business Corporation

Mark

>>Understood. Is the EWB web server a reasonable choice to get started with? I'm thinking perhaps 10 - 20 >>concurrent users maximum? Does it "connect" to EDB pretty much seamlessly?

Yes I am going to say basically - yes - but I have not tried it.  Since I started using EWB when it was first release it did not have the web server until a short while down the road, by then I had my own.  I use RealThinClient (RTC)components for the web server - and build the Web Server by extending the already provided Web Server with RTC.

>>Yup. I think I get this. So if you write your own web server rather than using the EWB provided one then you >>need to provide a REST interface per the EWB spec if you want the automatic DB functionality?

I have Posted into this forum more of the JSON code to do the REST database interface.  If wasn't more than a couple hundred lines of code and I learned a lot.

>> Never touch PHP so would likely try to avoid this day now.

Me either.


One advantage with my web server is I have modularized the DB part.  I have a DBISAM, and EDB and MSSQL version (which uses UniDac component set).

RTC is VERY reliable and stable.  30 concurrent users will not even tax it.

Walter
Mon, Nov 24 2014 4:31 AMPermanent Link

Matthew Jones

Mark Brooks wrote:

> This in itself publishes a REST API.

I'll stick in my 2 pence worth - and focus on that sentence. To me,
most applications you write for public consumption, should have a
defined interface - an API. If you are doing some basic internal use
application then the EWB server is fine, with the database stuff
running in the browser. But there are massive security implications if
you provide a general purpose interface to your database where anyone
can run any query they want against it. EWB does have authentication,
and you should certainly apply that and make sure it is used, but if
you leave a hole, then you are vulnerable. "DROP TABLE *;" and oops.
We've shown how easy it is to debug in the browser, so it wouldn't be
hard to change the SQL for the query to that. (Harder if you compress,
but not impossible. Indeed, that's why my deployed code is always
through FinalBuilder to ensure that I can't accidentally release an
un-compressed version.)

So back to my happy place, I like to have an API defined. This could be
REST, or any other web interface that the browser supports (http/s is
easiest). For me, the key is to define an interface of the actions that
are to be done (create account, lookup something with a parameter,
delete an invoice, whatever), and then create API functions to do that.
Then, the EWB application calls that interface and it is the server
that does the work of making that into queries, and to validate that
the current user is allowed to do that action. All nicely parameterised
for security.

The implementation can be in any language. It also means that the API
is accessible by other tools too. You could have an iOS native app that
uses it, or a desktop app. Or if EWB turns out to be not what is wanted
you could switch to Angular or other trendy and low level thing
</snark>.

So, that's my thinking. Me, I use the RemObjects SDK which makes the
interface definitions very easy - it is a GUI tool and it templates all
the Delphi server code and the browser code. (I have a tool to zap the
interface to make it EWB compatible, I think I published it.) RO SDK is
good if you are going to stick with it. I've used it for over ten years
for all sorts of projects and I find it lovely. A colleague used it for
one project about 6 years ago and didn't use it for much more and finds
maintaining that project a pain. But it is a powerful leg up. (I wrote
more on the benefit at
http://matthew-jones.com/why-do-i-use-remobjects/ )

--

Matthew Jones
Mon, Nov 24 2014 7:25 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Matthew,

<< I'll stick in my 2 pence worth - and focus on that sentence. To me, most
applications you write for public consumption, should have a defined
interface - an API. If you are doing some basic internal use application
then the EWB server is fine, with the database stuff running in the browser.
But there are massive security implications if you provide a general purpose
interface to your database where anyone can run any query they want against
it. EWB does have authentication, and you should certainly apply that and
make sure it is used, but if you leave a hole, then you are vulnerable.
"DROP TABLE *;" and oops. We've shown how easy it is to debug in the
browser, so it wouldn't be hard to change the SQL for the query to that.
(Harder if you compress, but not impossible. Indeed, that's why my deployed
code is always through FinalBuilder to ensure that I can't accidentally
release an un-compressed  version.) >>

Just to clarify some things here:

1) EWB does not send any SQL over the wire.  An EWB client application has
no idea it is even accessing a database.  It just simply asks the web server
for some JSON, and if the web server understands the request, it sends it
back.  The data could be coming from an Excel spreadsheet, for all EWB
knows.  The same holds true for transactions sent back to the EWB Web Server
(or any web server).  IOW, EWB already uses an API for data access.  It's
just simply not a specific REST API tailored to a specific application.

2) You most certainly cannot send *any* DDL to the EWB Web Server at all,
let alone any DML statements.  I can't stress this enough. Smile

3) All parameters that you pass for dataset requests are either put into
parameterized queries, or in the case of DBISAM, are sanitized using a
parser prior to inclusion in the query on the EWB Web Server end.  DBISAM
cannot use parameterized queries because it doesn't support parameter data
type discovery, whereas ElevateDB and other databases do.  And, ElevateDB
doesn't support multiple statements separated by semicolons, so it would be
impossible to use SQL injection even it it wasn't using parameterized
queries.

------------

The authentication of dataset operations can be done via the simple
authentication that we provide (X-EWBUser and X-EWBPassword request
headers), or you can do something more complex via an EWB Web Server module
that performs custom authentication, and then satisfies the dataset requests
via the TDatabaseAdapter and TDataSetAdapter components attached to
TDataSet-descendants.  No matter how you do it, you're going to want to use
an SSL connection for any dataset operations, since that negates the
necessity of performing hashing, etc. on the client side.

As for your compression recommendation:  that is something that everyone
should definitely heed.  The compression will help prevent (but not
completely prevent) someone from casually snooping in on how your
application works in the browser's debugger.  At the very least, it will
make even dedicated snooping very, very hard on the snooper.

Tim Young
Elevate Software
www.elevatesoft.com
Mon, Nov 24 2014 7:29 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Mark,

<< Understood. Is the EWB web server a reasonable choice to get started
with? I'm thinking perhaps 10 - 20 concurrent users maximum? >>

Yes, that will work.

<< Does it "connect" to EDB pretty much seamlessly? >>

Yes, if you want to use the default simple authentication, which relies on
the security constructs (users/roles/permissions) in ElevateDB to
authenticate a user's access level.  If you want to use something more
complicated, then you can switch to creating your own custom dataset module:

http://www.elevatesoft.com/manual?action=viewtopic&id=ewb1mod&product=rsdelphi&version=XE&topic=Custom_DataSet_Modules

and add a layer of authentication on top of it.

<< Yup. I think I get this. So if you write your own web server rather than
using the EWB provided one then you need to provide a REST interface per the
EWB spec if you want the automatic DB functionality? >>

Correct.  Here is the spec:

http://www.elevatesoft.com/manual?action=viewtopic&id=ewb1&topic=JSON_Reference

<< Never touch PHP so would likely try to avoid this day now. >>

Smile

If you have any other questions, please let me know.

Tim Young
Elevate Software
www.elevatesoft.com
Mon, Nov 24 2014 7:45 AMPermanent Link

Matthew Jones

Tim Young [Elevate Software] wrote:

> Just to clarify some things here:
>
> 1) EWB does not send any SQL over the wire.  An EWB client
> application has no idea it is even accessing a database.  It just

Tim, Thank you for enlightening me. As usual when I think I find a
problem, you've already thought it through and solved it. I must have a
play with the built in stuff - it might fit some of our internal needs
with this information.

--

Matthew Jones
Mon, Nov 24 2014 11:46 AMPermanent Link

Mark Brooks

Slikware

Avatar

Walter, Raul, Tim and Matthew,

Thanks for taking the time to assist guys. It's really appreciated.

I'm now in reading / disseminating / understanding mode.

But, in the words of Arnold, I'll be back....................

Mark
Image