[Phpmyadmin-devel] RFD: New feature, Interface implementation (not a feature request! :-)

[Robin Johnson]

(Note: please read thru all of this before replying to parts of it...)

On Tue, Jan 28, 2003 at 09:45:46PM +0100, Garvin Hicking wrote:
> > First a general opinion: suggestion 2 pleases me, it goes in the general 
> > direction of the pma db containing special settings.
I concur with that, the PMAdb is where custom data should go.

> > I wonder if we need more than one transformation type for each field?
Definetly we need it.

> I think so. As for me, I would best like to define my own PHP functions as
> possible transformation types. Generally they can be easy functions
> accepting a single input variable and all of them have to return a single
> value field, just like htmlentities().
I'm against having custom defined functions, but instead I propose a
better layout.

In the existing system for column comments, we add a field for the
mimetype of the data, as well as fields for transform choice and
additional transform parameters. The last two are optional, in that as
long as you have a mimetype for data, there is a sensible default
transform applied (configued via the config file again).

Now say we have data types like text/plain, text/html, text/xml,
image/jpeg, image/png etc.

Now add a directory:
libraries/transforms/
Files 
take the name format of:
(basetype)[_(subtype)][.(transform_name)].inc.php3
Note the important fact that we can have a general classes of:
(basetype).inc.php3
(basetype).(subtype).inc.php3
For the data types I listed above, we have the following file that
define helper functions for all of the transform if needed.
text.inc.php3
text_plain.inc.php3
text_html.inc.php3
text_xml.inc.php3
image.inc.php3
image_jpeg.inc.php3
image_png.inc.php3

if the data type is image/png, then include all the source of
image.inc.php3, image.*.inc.php3, image_png.inc.php3,
image_png.*.inc.php3 in that order, to have a complete list of
transforms possible for that piece of data.

For all image types, we have a general transform of:
image.display.inc.php3

which can just display the image. For the most part this is as simple as
sending the data with an extra HTTP header of the mime type (part of my
choice of mime type for controlling transforms).

image.display_thumbnail.inc.php3 - displays just a 100x100 thumbnail of
the image. possibly the size is customizable via the additional
parameters, but we have sensible defaults for it already (provided via
the config file).

If you wanted additional pre-processing, say to convert an image format
that is not directly displayable (using XBM format as example), you
would have 'image_x-xbitmap.display.inc.php3' that would convert it to
something that could be displayed in the browser and send that.
There could possibly be more image transforms through GD or anything
that you wanted, via the additional tranform paramaters field.

The text ones could be like:
text.none.inc.php3 - does no formatting at all
text.string.inc.php3 - takes a parameter like '<img src="%" />' showing
how to mangle the data for displaying it...
text_html.simpleformat.inc.php3 - does the minimal HTML conversion as
you suggested before.

> [transform examples converted into ideas above]
> These are rather odd examples, and I can't think of any more. But I think it
> is easy to code and would otherwise if left out restrict the user in his
> creativity.
Adding a new transform should be as easy as putting a new file into that
directory with the correct name, and possibly adding the transform
identifier to an array somewhere.

> This would be quite a great feature, but I can't think of it's
> implementation. This can't be used as easy as a 'return string - function($buffer)'
> replacement, because you would have to call a wrapper script, which then has
> to create a temporary image file, insert this into a <img ...>-Tag and
> afterwards somhow garbage-collect and delete the temporary file. Or is there
> a possibility yet to compatibly render inline binary pictures in HTML?
<img src="transform.php3?(select query for data)&transform=(transform
id)" /> removes the need for temporary files by shoving the data from
the DB to the user directly.

> > Other things: look at our big T, to show Full texts or
> > reduced texts. It operates on the whole page and transforms the output.
> > But often I would like to see the full text of only one cell. I agree with
> > you to avoid a lot of pull-downs for each column header (or each cell).
> This is a good example - the 'big T' could be replaced using my proposed
> functionality. We could provide a 'full text' function and a 'reduced text'
> function. The latter one would only output the first 50 chars (or words, if
> you prefer).
text.fulltext.inc.php3
text.reduced.inc.php3
:-)

> > Also, should we display the new modes in table view (many records), or only 
> > when we go in Edit mode (where we clearly have only one record to deal with, and we 
> > can display more freely pull-downs for all rendering options)?
> I think it best to only show the transformations in table view, for
> comparison reasons. I can't really think of why a user should want to have a
> transformation applied on a page, where he wants to edit data?
Agreed. ONLY the view page should have the transforms applied, partially
for the reason you supplied, but additionally because MANY of the
tranforms are not reversible so display the data in the transformed
format in the edit page is not sensible.

> > Sorry for the lots of questions :)
> No way! Thanks for your valuable input. That's what my posting was for.
Ideas and code always welcome :-).

> Regarding implementation, I think of the following approach (everything as a
> 'stream of consciousness', don't expect miracles ;-)
> 
> 1. We need a new table, call it transformations using the structure:
> 
> TABLE 'transformations'
> - table
> - field
> - transformation_rule
> 
> 'table' references to the table name where you want a transformation
> applied.
> 
> 'field' references the fieldname for the transformation. (Examples
> following)
> 
> 'transformation_rule' will contain an id-reference to the table
> 'transformation_rules':
I've added this in by adding those three fields to column comments,
because this is an extension of what the data in that column is about.

> TABLE 'transformation_rules'
> - transformation_rule [foreign key for 'transformations.transformation_rule
> - function_description
> - function_name
> - function_php
> 
> 'transformation_rules' will be a auto_increment id.
> 
> 'function_description' is what will be shown in a dropdown for selecting
> which transformation you will like to be applied to a field
> 
> 'function_name' contains the non-conflicting php function name.
> 
> 'function_php' contains the function, which can be eval'd(). (which raises
> security issues discussed later on)
This has serious security ramifications, and is actually more of a pain
to manage when PMA is upgraded. Instead we will have just many files of
transforms along with an array somewhere with a list of transform
identifiers.

> Now we can display a simple HTML-form. Say we have a table 'dummy'
> containing the fields 'id', 'image', 'description', 'owner'. You will then
> have the form:
> 
> id		(BIGINT unsigned)	[transformation_pulldown]
> image   	(VARCHAR 255)           [transformation_pulldown]
> description	(TEXT)			[transformation_pulldown]
> owner		(BIGINT unsigned)	[transformation_pulldown]
> 
> You will then have the transformation functions 'Shorten Text', 'HTML View',
> 'Plain', 'HTML Formatting Tags Only'. These are represented in the
> 'transformation_rules'-table. If I now insert the Rule 'Shorten Text' for
> the field 'description' I get an entry:
This is added in via the column pages, in that after they add a
mime-type, they can choose from an applicable list of transforms and
specify additional options as needed.

> You will then only have to clean up the transformations-database everytime a
> database is dropped or you change a transformation for a certain field.
My approach saves from this.

> How is this solved in the 'relation'-Database?
The relation database has a further problem in that if you rename
database/tables/columns things don't make link up anymore.
It isn't solved in the relation database at the moment, but I've been
toying with ideas on how it chould be solved.

> Now you can go to the table view, where you can click on a Button similar to
> the 'big T' which then turns on transformations for every field found in the
> 'transformations'-table by querying the table for the current field name.
> Performancewise, this only has to be called once, I think.
Since we already make calls for relation data about columns, there is
minimal overhead involved here, only once per page/table call.

> Concerning security, I don't know how much of a risk it is to allow
> eval'uation of PHP-Code coming from a SQL-DB. On the one hand only the
> control- or superuser should be able to create transformations, so if he
> decides not to use our provided transformations and insteads implements his
> own, it's up to him whether he uses 'dangerous' php-code or not.
Serious issues in this, the other reason I would go against this, is
that if you combine it with issues of data-injection security holes, it
would become possible to inject code like: `shutdown -p` into the PHP
field (say overwriting one of the generic transforms for text),
and then if your PHP is running as root under apache like many
users not knowing better, when that transform is run your server shuts
down or worse...

> Another solution could be not to store the php-code in a database, but
> instead hard-code it into the script. This will lose additional complexity
> and abilities to customize code, but could be much safer.
I see no loss of customizing code, and it would actually become easier
to manege. I don't get where you see the loss of complexity?

> I guess, I'm too much into babbling now, so I'll settle for the day and see
> if you can take any reason out of my writing. I'm open to any suggestion to
> make, I'm not yet fixed to a certain solution and reading of today's
> cross-site scripting attacks, a feature like this should be well
> thought-out. I guess. ;-)
Read up on XST attacks and data-injection attacks as well.

-- 
Robin Hugh Johnson
E-Mail     : robbat2 at orbis-terrarum.net
Home Page  : http://www.orbis-terrarum.net/?l=people.robbat2
ICQ#       : 30269588 or 41961639
GnuPG FP   : 11AC BA4F 4778 E3F6 E4ED  F38E B27B 944E 3488 4E85
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 232 bytes
Desc: not available
URL: <http://lists.phpmyadmin.net/pipermail/developers/attachments/20030128/320cff0f/attachment.sig>