DatabasePlugin (Version 1.3)

Provide secure access (read and write [mysql only]) to data in a database allowing the user to specify the resulting output format as well as editable tables. 'secure', in this case, just means not putting passwords in clear text into a TWiki page but placing it either in a 'secure' flat TWiki file or in a database.

NOTE:
Security is only achieved if:

1. File bin/DatabasePluginConfig.pm is not readable through a web page and has been locked by updating the .htaccess file in bin (or locking via whatever your security procedure is on your web server)
2. File bin/DatabasePluginEdit has been locked (requiring a valid user) by updating the .htaccess file in bin (or locking via whatever your security procedure is on your web server)
3. People with login/ftp access to your web server do not have read access to bin/DatabasePluginConfig.pm

This plugin references bin/DatabasePluginConfig.pm. This file contains one required section and one optional section.

• Required - Description of where the primary DatabasePlugin database is located and
• Optional - Any local database specific information if the above required section says that the db_driver is Local.

Again, this plugin provides access to database information in a semi-secure manner. Since clear text passwords in a TWiki page is not good, the passwords to databases are themselves placed into a DatabasePlugin database (either located in a real database or located in the bin/DatabasePluginConfig.pm file). This database contains the following fields:

To secure the database information allowing access to the above database, a Perl .pm is created and placed into bin where the .htaccess file (or whatever your security mechanism is) is updated such that this file can NOT be viewed from a web browser. This file contains variables defining access information to the above database. The variables are:

Syntax Rules

• %DATABASE_TABLE{description="db1" headers="hdr1,hdr2,hdr3" columns="col1,col2,col3"}%

  Key	Default	Description of key
  description		Lookup "key" in the DatabasePlugin database to get the info on the database to access
  columns (optional)	"*"	The columns in the table to return
  headers (optional)	columns values	Table headers.

• %DATABASE_SQL_TABLE{description="db1" headers="hdr1,hdr2,hdr3" command="sql command"}%

  Key	Default	Description of key
  description		Lookup "key" in the DatabasePlugin database to get the info on the database to access
  headers		Table headers.
  command		Any SQL command

• %DATABASE_REPEAT{description="table_description" columns="col1,col2,col3"}% .... user formatting .... %DATABASE_REPEAT%

  Key	Default	Description of key
  description		Lookup "key" in the DatabasePlugin database to get the info on the database to access
  columns (optional)	"*"	The columns in the table to return

• %DATABASE_SQL_REPEAT{description="table_description" command="..SQL COMMAND.." columns="col1,col2,col3"}% .... user formatting .... %DATABASE_SQL_REPEAT%

  Key	Default	Description of key
  description		Lookup "key" in the DatabasePlugin database to get the info on the database to access
  columns (optional)	"*"	The columns in the table to return
  command		Any SQL command

• %DATABASE_REPEAT{description="table_description" columns="col1,col2,col3"}% .... user formatting .... %DATABASE_REPEAT%

  Key	Default	Description of key
  description		Lookup "key" in the DatabasePlugin database to get the info on the database to access
  columns (optional)	"*"	The columns in the table to return

• %DATABASE_EDIT{description="table_description" display_text="HTML link text"}%

  Key	Default	Description of key
  description		Lookup "key" in the DatabasePlugin database to get the info on the database to access
  display_text (optional)	"edit"	The columns in the table to return

Example

Assume that the DatabasePluginConfig.pm file (locked away in bin so that a user can not view the contents) contains the following:

$db_driver      = "Oracle";
$db_database    = "mydb";
$db_sid         = "sid";
$db_table       = "DatabasePlugin";
$db_username    = "user";
$db_password    = "123456"; # (Note: this is the clear text password)
$db_hostname    = "localhost";

Assume that the database specified above contains the following information:

Now if I have the following in a TWiki page and the DatabasePlugin installed, the results should be a table containing 4 columns of information.

%DATABASE_TABLE{description="mysql_user_info" columns="Host,User,Password,Select_priv"}%

If I want table column headings that differ from the database column names, I could use: %DATABASE_TABLE{description="mysql_user_info" columns="Host,User,Password,Select_priv" headers="Host Name,User Name, Password,Select Privs"}%

An optional way to do the same thing would be to use: %DATABASE_SQL_TABLE{description="mysql_user_info" command="SELECT Host,User,Password,Select_priv FROM user" headers="Host Name,User Name, Password,Select Privs"}%

Using the REPEAT functionality, you can define how the database data is displayed wrapping it in any formatting you choose. For example, if you wanted to create a single table cell containing the information for 3 fields of the Kalendus calendar, you could use the following: %DATABASE_REPEAT{description="calendar_events"}%| %startdate%
%subject%
%body% | %DATABASE_REPEAT%

Or lets say you wanted to display the next two upcoming scheduled events in the Kalendus calendar. %DATABASE_SQL_REPEAT{description="calendar_events" command="SELECT subject,body,startdate FROM kalendus_event WHERE to_days(startdate) >= to_days(now()) order by startdate limit 2"}%| %startdate%
%subject%
%body% | %DATABASE_SQL_REPEAT%

Now lets say that you wanted to give the user the ability to edit the contents of a table. NOTE: This feature assumes the availability of phpMyAdmin since it uses phpMyAdmin to provide table editing. The following is how you would edit the table described by calendar_events. %DATABASE_EDIT{description="calendar_events" display_text="Edit the above table"}%

DatabasePlugin Settings

Plugin settings are stored as preferences variables. To reference a plugin setting write %_%, i.e. Link =ExternalSite:Page text to external sites based on aliases defined in the InterWikis topic=

• One line description, is shown in the TextFormattingRules topic:
  • Set SHORTDESCRIPTION = Provide somewhat secure access to data in a database returning the data in either table form or in a user defined format

• Debug plugin: (See output in data/debug.txt)
  • Set DEBUG = 0

• Set security mode (0 = allow viewing of the contents of the master DatabasePlugin database, 1 = don't allow viewing of the master database)
• Set SECURITY = 1
• Set SECURITY_MESSAGE = Error: For security reasons, you can't specify the main DatabasePlugin database since it contains clear text passwords.

Plugin Installation Instructions

• Download the ZIP file from the Plugin web (see below)
• Unzip DatabasePlugin.zip in your twiki installation directory. Content:

  File:	Description:
  data/TWiki/DatabasePlugin.txt	Plugin topic
  lib/TWiki/Plugins/DatabasePlugin.pm	Plugin Perl module
  bin/DatabasePluginConfig.pm	Plugin configuration/initialization information
  bin/DatabasePluginEdit	Routine which generates a frame based HTML page for editing a MySql table. NOTE: For security reasons, you MUST add this application to your .htaccess list as requiring a valid user since the output from this script will embed the database username and password allowing write access to the tables

• Test if the plugin is correctly installed:
  • You will need to configure your installation to know if it is working as expected.

Plugin Info

Related Topics: TWikiPreferences, TWikiPlugins

-- TaitCyrus^? - 5 May 2003

to top