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:

Column Name	Description
db_name	The name of the database in which the table_name is found
db_sid	The SID of the database in which the table_name is found (optional: used for Oracle)
description	This field acts as the look-up mechanism for the indirection. A TWiki page would reference this name instead of having to publicly specify user names, passwords, etc.
driver	Database driver name (currently only mysql has been tested)
hostname	The name of the host on which the specified db_name lives
ID	Standard auto_increment column to allow for unique db entries (optional if a real database)
ro_password	The password for the specified ro_username
ro_username	A user name providing read-only access to the specified table
table_name	The name of the table found in the db_name database

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