db tool - flashPash beta released (MX component + PHP class)

[si]

(sorry if this is the wrong section, i'm not sure if it should be here or MX comps)

greetings!

The beta-release of flashPash has just been sent to those who expressed an interest in checking it out and testing it. If after reading this, you want to have a look, email me - si@doof.organd let me know what db you run, as a couple more beta-testers on different databases would be good.

flashPash is a free open source flash MX component and PHP class, it's job is to allow easier sending, receiving and storage of data on a database on a web server.  It does this by sending XML over HTTP.   It was built as I couldn't find any flash MX tools that do this.

It uses the PHP ADOdb database abstraction class, which supports over a dozen databases and is used in many applications.

A screen-shot of the demo / debug application is here:
http://www.frontierisp.net.au/~si/flashPash.jpg

The shockwave of this demo can be downloaded here:
http://www.frontierisp.net.au/~si/flashPash.demo.swf

NB: I have not attached the server script to this yet, so you will get errors. Server-side demo will only happen after the beta-testing period, i've linked the shockwave file here to help elaborate the text below:

flashPash works by building an XML document in the client, sending this document to the server, which converts it into an SQL query, executes the query against the database, then builds a result XML document which is sent back to the client. After this XML document is received and parsed, an appropriate action takes place, depending upon the method called and data source type involved.

The MX client has 5 possible states and a property reflecting what is occuring in real-time, these states are:

Sleeping == doing nothing
Working == building an XML object to send to the server
Waiting == XML object is (or has) been sent, waiting for server
Loading == receiving XML object from server
Updating == acting upon result from server

There are 6 methods available in *both* the client and server:

- doSelect : Generates a SQL SELECT query from the dataSource (ie. firefly dataset) This dataSource is populated with data upon successful execution of method.
- doInsert : Generates a SQL INSERT query based upon current record data and the SQLcondition property (if set by programmer).
- doUpdate : Generates a SQL UPDATE query based upon current record data and SQLcondition.
- doDelete : Generates a SQL DELETE using current records key field(s) and SQLcondition.
- doResolve : Generates multiple INSERT / UPDATE / DELETE queries based upon the contents of the firefly dataset delta packet(s). Unfortunately, this is currently not working. The problems should be resolved shortly after the next release of firefly.
- doMeta : Generates database meta-data, parameters passed to method determine whether it lists all available db's, all tables in a db, or all meta-data from a SQL select query. If data source is a firefly dataset, flashPash will also build the necessary attributes to set the map source (ie. field structure) automatically upon completion of query.
- doSQL : Freeform or generic SQL query. Any results are returned to client as XML in a similar way MS SQL generates XML data. You can also use this for database maintenance or checking record counts, etc...

The PHP server class also has a dumpXML method to send results to client, so it is possible to build queries on the server in the same way you do on the client, then you can decide if you want to send to the result back to the client or not.

Each client method has separate 'before', 'after' and 'error' events, these events pass parameters which contain information about the event. I'm not sure if it's overkill to have 21 events when you can just have 3 and pass the method's SQLtype as a parameter of the event. For the moment you can do it both ways. This is shown in the demo code.

The other two client (MX component) events are:

- onDebug : Fired if debug property is true and something has happened that might be worth checking out (nb: There is a lot of debug code at the moment, this will be trimmed down later on, but for now I think more is better than less). The debugText property is also populated at this time.
- onStateChange : Fired when flashPash state is changed...OR...if the current state is 'waiting' or 'loading' the event is fired at a defined interval, currently hardcoded to every 1.5 seconds, but this may become a property.
So you could start playing a movie on the before event, and update this movie on state changes.  Also, if in a waiting state, the time taken in milliseconds is passed along in the event, and if in loading state, the number of bytes received as well as the total to be received are available for use.

There is comprehensive client-side and server-side debugging available, (server-side debugging now creates nice HTML or plain TEXT files) and it is entirely possible to build everything in the flash client. The demo application shows this by loading the server-side debug script on the 'after' or 'error' events, so you can see exactly what happened, and if there is a problem, whether it happened client or server-side.

There is another component - 'flashPashStash' which will be beta-released once the version 1 of flashPash is sorted. This component is designed to consolidate multiple flashPash methods into the one XML document, which should optimise intialisation of datasets and reduce internet lag. The 4 public methods are shown in the demo, but are not yet functional.

Also, all of the firefly specific actions have been abstracted into separate functions on both the client and server. My goal is create a base class from which any XML friendly component can be used with flashPash (flash MX or otherwise) on the server. Currently only firefly components will work with it, but the opportunity for others now exists. This is not meant as a negative comment on Cybersage or Firefly, the more I work with their software, the more impressed I am with the quality and sheer depth to it. I just want to make flashPash as open and freely available as possible...and tightly binding it to a commercial product will not achieve this goal

So the choice is yours - you can use the doSQL and doMeta methods without needing the firefly library, but for the time being, the other methods will only work with 'firefly' as the dataSourceType property.  flashPash is open source software, meaning that you are able to modify the code. I am also open to ideas and suggestions about integrating with other components.

flashPash and flashPashStash are released under the BSD and LGPL licenses, ie. the same license model as ADOdb, which means, among other things, you are able to build commercial software with it, so long as you observe a few reasonable rules detailed in the flashPash.license.txt file.

The doResolve method is only 90% complete, and there are still problems with getting current and modified data from a firefly dataset, but I believe these are related to the XML resolver not being able to extract key path information properly. This should be fixed when firefly RC2 is released. The server-side PHP demo shows that all of the flashPash methods are working, so it won't take much work to get things sorted out.

Ok, that's it for now, will spam here again once version 1 is available.

peace
si

[Flash-db]

Wow - I'm impressed.  Looks amazing.  Definitly not spam.  

Could you explain the firefly dataset a bit more - I'm not to familar with that?  

Thanks for passing on the great app.  And I'm looking forward to seeing the post on the official release.  

[si]

Hi Jeff,

Thanks for the positive comments, flashPash is not quite at a release stage yet, but it's not too far off.   In answer to your question, the firefly dataset is a core part of a suite of visual and non-visual data-aware components that Cybersage sell.   The architecture of firefly is quite complex, but if you have used Delphi before, or something similar, then you will be familiar and comfortable with it.   Check out the firefly link on my demo, or the posts from Cybersage in this forum for more info, as I don't want to speak for them.

I have been working with firefly since pre-release (RC2 is coming soon) and have been very impressed with the quality and depth of the components, and it is needed if Flash MX is going to be taken seriously as an application development tool.  I'm really hoping it takes off, as the potential for a data-aware o/s, platform and browser neutral client-side environment is *HUGE*    

My hope is that flashPash is seen as a bridge between commercial software (Flash MX and firefly) and open source software (PHP, ADOdb and apache), it was originally tightly binded to the firefly dataset, XML connector and XML resolver (the core non-visual components), but I realised this probably wasn't the best thing to do, as people would only see it as a complementary extension (or plug-in) of firefly, which it is, but the potential to use it for other things also exists...all hail XML!    and since the doSQL method returns a XML result similar to the XML functions in MS SQL, you can use it without firefly, although i'd recommend forking out the do$h.

Once the beta-testers have bashed out a few more bugs, and i've returned from holidays http://www.outbackeclipsefestival.com here we come!!!     the flashPash.mxp file will be released and i'll set up a live data version on the web so you can see it in action.

peace
si

[si]

A 2 minute AVI of the flashPash demo/debug application has just been created.

It can be downloaded from:
http://www.frontierisp.net.au/~si/flashPash.demo.zip [3.3MB]
(Video Codec: Indeo Video 5.10)

It's a bit of a late night rush job as it was needed by Cybersage before I go on holidays today (woohoo!), but it does show a few features. Also learnt from setting up flashPash on my girlfriends PC that there is still some work to do in making the installation process easier. Unless PHP is properly configured with the PEAR XML Tree, DOMXML library enabled and ADOdb is correctly pathed, it is tricky to get working. I will endeavour to create a Wise installation script instead of the current .MXP for version 1, and put some verifying and auto-config options to help with the install.

An update to the beta version was posted a few days ago, so if you are game:
http://www.frontierisp.net.au/~si/flashPash.beta.mxp [187KB]
Please read the documentation in the extension manager and flashPash.readme.txt, and I won't be able to help with any problems until I've returned from the outback.

peace
si

[Ronald Wernecke]

Hi,
correct me if I'm wrong. Isnt Firefly based on Interbase and the Boreland DBE?

[si]

*shrug*   Not sure what you mean.  I see it as an MX implementation of the data structure in Delphi.   It's not the same, but it's not that far off.

peace
si  - who just watched his first total solar eclipse and is still in awe  

[Musicman]

Hi,

just wanted to say that I feel a bit uneasy with a system where sql queries are sent from the client to the server, rather than built on the server.
Many webhosts do not allow to customize database security (i.e. you get one database, and full rights on it, but you may not add another user with restricted rights). So there is some potential of abusing the system by sending sql queries other than those intended

Musicman

[si]

Hi Musicman,

I agree with you.   The doSQL method has the most potential for abuse.   It would be up to the programmer to set the appropriate database user rights (server-side) to prevent malicious use.  flashPash has been designed so that the server side script (which creates the flashPash PHP class object) defines the database user and password, and not the client.  I realise my demo appears to contradict this, as it passes the database type, user and password over HTTP, but this is just to make it as easy as possible to get it working, there is documentation in the code and the demo itself which explains why I have done it this way, and why you shouldn't do it in a production environment  

It would however, be more difficult for a hacker to abuse any of the other methods (ie: doSelect, doInsert, doUpdate, doDelete, doMeta) as the SQL for these methods is built on the server and not the client.  The only SQL passed over HTTP is for filtering (eg: WHERE or ORDER BY statements) as specified in the SQLcondition property of a flashPash MX object, and this is placed onto the end of the SQL statement built on the server.   Only the data and meta-data (field name, type and path) are passed.

There may be ways for a hacker to compromise this, for example, by including a sub query in the SQLcondition property (that would be tricky as you can't comment out the SQL built by the flashPash PHP object on the server), or by changing the meta-data to reflect another table or fields in the XML document sent to the server.   But once again, it would be up to the programmer to define the appropriate user rights.

IMHO any database exposed to the web needs to have very well defined rights, explicity detailing what users are allowed to do.  And if you are paranoid about this sort of stuff, or have sensitive data, then using HTTPS (SSL) has to be the way to go.  

Your comment about webhosts not allowing customising of db rights is cause for concern, and in such an environment I would recommend programmers not allow server-side processing of requests made by the flashPash MX component, or for that matter any client-side technology which passes data (or meta-data) over HTTP.  In those cases I would do everything on the server.

Please be aware that the flashPash PHP class has all of the methods for data manipulation that the client-side MX component does, so if the programmer wishes to do everything server-side, then they can, it just means a bit more work is involved.

Thank you for your comments, security is definitely an important issue and i'm happy to discuss it further.

peace
si

[Musicman]

Hi,

as for the https part - it protects dt in transit from nosey people.  Now consider someone trying to hack some compny's website: they would probably first start as a typical visitor, i.e. viewing the site (and downloading the movie). Now, the movie is likely to expose some of the design details on the server. Even if the sql is not constructed in flash, one will see which tables and columns are queried to, e.g.  display products and prices. Next, an attacker  might just build a movie of  his own that asks the server to update some tables The information required to do that is all availble from the regular movie (and reading the flashpash doc files
So obviously the only way to stop abuse would be either serverside script restrictions (we can only update certain tables / columns) or granular database permissions (i.e. the webshop application is run by a specific user, and is allowed to add new customers, but cannot alter products. If the app maintains a current items in stock count, it will even require distinct rights by column) This is out of  reach for hosting that uses cpanel

Musicman

[si]

Hey again,

The points Musicman raise are valid.  Encryption in transit won't stop someone from poking their nose around a downloaded .swf file.   I'm fairly new to the flash/shockwave world, but from what i've read it's a trival task to disassemble shockwave files.  

And yes, flashPash being open source and having documentation would make it easier for a hacker who RTFM's but I don't believe in security through obsecurity.   I'd much rather talk about them (like we are now) and try to reduce the risk.

The reason I built flashPash was for a free web based rostering program being developed for an organisation I belong to - http://www.ravesafe-sa.org   Some of the information will be sensitive (address, phone numbers, etc) so I have a vested interest in making it as hacker-unfriendly as possible, here's my plan of attack, i'd welcome suggestions on how to strengthen and improve it:

- Session based authentication.   Except for the initial login script, no server-side scripts are run without authenticating first.   ADOdb has db based session management.

- Small shockwave login movie.   This only contains the bare minimum of data, and has it's own db user and server-side script, and will only load the main shockwave movie once user id/pass-phrase are validated.  Main movie won't start unless authentication is verified.  User ID and pass-phrase are hashed for transit and encrypted in the db.  Login attempts are recorded.

- User based roles for db access.  4 different user types: login; standard; organisation admin; webroster admin.

- db updates are all logged.  ie: any time an update/insert/delete occurs, the user doing the update (including IP address) and brief information about the update are recorded into a log table.


So whilst I agree with Musicman's point about a hacker being able to create their own shockwave file (or it could even just be an XML document sent over HTTP) I think there are some reasonably easy ways to minimise the risk to an acceptable level.

Using the doMeta method it is possible to avoid building the database schema into the client-side application.   Instead, the dataset field structure can be loaded at run-time.   Whilst i'm not convinced this is a necessary step (a hacker could guess your table and field names anyway) and might not be worth the extra time and bandwidth required, it would be of use to those who need to separate the presentation layer from the database.

Also, with Cpanel, can't you use phpMyAdmin and run SQL GRANT commands to define db user rights?

But regardless, there is a need to have a security/application design section in the flashPash readme.   I will summarise the points raised from this thread and create the documentation.   I guess what also needs to be stated is that if you aren't able to get complete control over your db, then you should be aware of the dangers.

Keep'em comin' Musicman

peace
si

ps. just saw 'Spirited Away'.  Awesome movie!

[Musicman]

Hi,

as for canel controlled sites: the cpanel creates a database user without grant option for you, and your access in phpmyadmin is restricted to your own database
This is a fairly common setup.
Would it be possible to disable the dosql  part on the server if the app does not need it.
Would it further be possible to add a permissions list on the server (back to the webshop example: updating quantity is vlid operation - at least after a purchase - while updating price is not)

This last example also shows that there is still a potential hole, created by separating operations that would normally be a transaction
when you buy stuff on the web, then payment and reducing quantity in stock go together. If the actions cn be called seperately from the movie, without suitable application interlock provided by a server session, someone (e.g. a competitor) could abuse the shop by just reducing your quantity in stock to 0, while they can still offer next day delivery

Your  own setup should be pretty secure, due to the sessions and the controlled database rights. Logging ip does not help too much (I dont think someone doing the bad thing above would do it from the company's network ... more probably from an internet cafe)
As for analyzing the movie - I have gone to the extreme of creating one-of-a-kind movies for a specific application, so if somebody  wants to be overly smart and extracts the actionscript to a movie of their own, it would not work

Musicman

[si]

hi,

> as for canel controlled sites: the cpanel creates a database user without grant
> option for you, and your access in phpmyadmin is restricted to your own database

ah...now I understand where you are coming from!

In MySQL the users table isn't in the same db as the one that would be available, so you can't touch it and hence can't control database user access.   Bummer.


> Would it be possible to disable the dosql  part on the server if the app does not need it.

Yup, it would be easy to disable doSQL methods from a client, but see my comments below about your suggestion of a permission list.

fwiw, the doSQL method was created because I thought it would be useful for client-side database maintenance in flash (create/drop databases/tables/columns/users).


> Would it further be possible to add a permissions list on the server

Hmmm...possible yes

I do see the value in this and it would be worth implementing, especially if db user access is not available for programmers.   But what it means (as I see it) is that flashPash would essentially be duplicating the functionality that already exists in most databases.  I guess it depends on the level of detail desired, but perhaps something like this:

- You could add a boolean property in flashPash PHP class to turn permission-based rules on.  If it is turned off, assume that the database is handling it and no permission check takes place (status quo).

- Instead of the user rights being in a record in a database, it could be in an INI (or whatever) file that flashPash.php could read.  Maybe there could be another flashPash property to hold the location of this permission file?

- Before any db connection is made, if permission check is set, then user is verified against the permission configuration.   I guess the level of detail is dependant upon the level of control required.  In the example Musicman gives, you would need a fairly high degree of control. ie:  you would probably want a flashPash user to be limited to:
specific databases and tables
specific flashPash methods against db and tables (ie: doSelect, doInsert, doUpdate, doDelete, doMeta, doSQL)

The good thing is it's easy to determine what the user is trying to do, and the tables they're working against...and you could place the permission check after the flashPash properties are assigned (so that way it would work for both server-side and client-side methods) but before any database access takes place.  

So it should not be too difficult to implement (famous last words *grin*), and it may be possible to adapt existing code already available, but it's not something I'm able to do right away...so if someone else want's to tackle it, then please let me know!

If this was to happen, then the doSQL modification wouldn't be needed, as it would be wrapped up into the same changes.


> Logging ip does not help too much

Agreed, but it may be of use if police or legal issues are involved?

Once again, thanks for your input Musicman, i've added permission list to the flashPash wishlist.

peace
si

[si]

Hey Musicman,

how does this sound for a permission-based list structure...

A single file 'flashPash.permission.ini' lives in the same location as flashPash.php
This area should NOT be web-readable (default to c:\php4\includes\flashPash)

If instantiated flashPash object with boolean property of 'permission' is set to true, then no database activity will occur until permission status is verified.  Another property 'flashPashUser' will need to be defined if 'permission' property is set.

Section heading of INI refers to flashPash user name, individual values refer to database name, table name (optional) and column name (optional) separated by dot notation.   precedence always occurs on the lowest order, ie:  column filtering overrides table filtering and table filtering overrides database filtering.

eg # 1
[SomeUser]
myDatabase = 'doSelect, doInsert, doUpdate, doDelete, doMeta, doResolve, doSQL';

[AnotherUser]
myDatabase = 'doSelect, doInsert, doUpdate, doDelete, doMeta, doResolve';

SomeUser can do everything in 'myDatabase'
AnotherUser can do everything in 'myDatabase' except doSQL method.


eg # 2
[SomeUser]
myDatabase = 'doSelect, doInsert, doUpdate, doDelete, doMeta, doResolve, doSQL';
myDatabase.myTable = 'doSelect';

SomeUser can only run doSelect method on 'myTable' in 'myDatabase', but can run all methods for all other tables in 'myDatabase'.

eg # 3
[SomeUser]
myDatabase = 'doSelect, doInsert, doUpdate, doDelete, doMeta, doResolve, doSQL';
myDatabase.myTable = 'doSelect, doInsert, doUpdate, doDelete, doResolve, doMeta';
myDatabase.myTable.price = 'doSelect, doMeta';

SomeUser can only run doSelect or doMeta methods if price column in myTable is involved in request, but can run doInsert, doUpdate, doDelete, doResolve and doMeta methods if the 'price' column is NOT involved, and all other flashPash methods if 'myTable' is not involved.


What do you think?  Is it better to have a do nothing unless told methology or perhaps a do everything unless told not to?   I think security-wise the first option is preferred.

Since flashPash stores all the necessary information (db name, table name, column name, method being invoked) into properties or arrays of the flashPash object, then verification is pretty easy.

The tricky one will be doSQL, as it can be freeform SQL, so it would be necessary to search the SQLcondition property for any reference to a table or column name...and this will cause problems if you have a table name the same as a column name or database, so perhaps doSQL should be the exception and applies at a database level?   Any table or column level filtering are ignored (or a warning given)

The big hole in all this is that flashPash is only providing security whilst using flashPash itself, whereas database level security works for anything touching the db...so that would always be my preferred method...but I can't see any way around this?

fwiw, I have nearly finished the WISE installation script for flashPash, ADOdb and XML_Tree...this should makes things a lot easier in regards to installing and configuring the software.

peace
si

[Ronald Wernecke]

Hi,
still it would be of interest, what dbengine is running behind it.
Or at least, which features the db supports.
If it supports views, triggers and stored procedures, you can hide the structure from a user.

[si]

yup, that's another way of doing it...ADOdb supports a few db's:
http://php.weblogs.com/ADOdb_manual#drivers

A possible problem with that approach is loss of portability, as db's all have different ways of handling triggers, views and stored procedures.  

I guess it's up to others to decide importance of permissions, it would be a good feature, but for me right now it's not a high priority, but it's on the wishlist, along with sockets

peace
si