SqlPlugin

On this page:

This plugin adds access to SQL databases using wiki apps.

Syntax

SQL

Executes an SQL statement. This can be either a select or any other SQL statement. The result of a select can be stored under a specific id to reuse it in further SQLFORMAT statements (see below).

%SQL{"query" ...parameter ...}%

Parameter	Description	Default
query	sql statement to be executed
params	comma separated list of bind parameters for placeholders (?) in query
database	connection to be used	first database in connection pool
id	identifies the query result to be reusable via SQLFORMAT
decode	values are "url" or "entity"; specifies the encoding of the query before being executed	none
format	format string to render each hit of a search result	`$id`
header	header srting prepended to the rendered result
footer	footer string appended to the rendered result
separator	separator to be put in between each hit rendered using the `format` parameter	`,` (comma)
hidenull	values are "on" or "off": flag to hide any rendered result when an sql search returned no hit; `off` means any output will be suppressed when nothing was found; `on` means that a header and footer will always be printed no matter how many hits have been found	`off`
limit	maximum number of search hits to render; a 0 (zero) will render all hits found	0
skip	skip the number of hits before starting to render the search result	0

If format, header and footer are all undefined, a standard foswiki-table will be generated.

SQLFORMAT

Format the result of a previous %SQL select.

%SQLFORMAT{"id" ...parameter...}%

Parameter	Description	Default
id	identifies the result set as given to `%SQL`
continue	valies are "on" or "off"; when switched on, a previously used statetment in `%SQL` will be reused; if switched off, the statement as specified by the corresponding `%SQL` will be executed again by the database engine	off
`format`, `header`, `footer`, `hidenull`, `skip`, `limit`	see above

The format parameter may contain variables of the form $colname, where colname is the name of the column as returned by an sql select. Standard escapes like $percnt, $nop, $n and $dollar can be used in format, header, footer and separator to delay the execution on TML until after the %SQL statement has finished.

Configuring database connections

Connections to a database are configured by specifying a list of connections in configure. It is stored in an array in the variable $Foswiki::cfg{SqlPlugin}{Databases}.

Example:

$Foswiki::cfg{SqlPlugin}{Databases} = [
  {
    'id' => 'mysql',
    'dsn' => 'dbi:mysql:foswiki:localhost',
    'username' => 'foswiki_user',
    'password' => 'foswiki_password',
  },
  {
    'id' => 'sqlite',
    'dsn' => 'dbi:SQLite:dbname=/var/www/foswiki/working/work_areas/SqlPlugin/sqlite.db'
  },
  {
    'id' => 'csv',
     'dsn' => 'dbi:CSV:f_dir=/vaar/www/foswiki/working/work_areas/SqlPlugin/csv'
  },
];

This setting configures three connections - known under the ids mysql, sqlite and csv using different drivers. The first sets up a connection to a mysql database called "foswiki" on the localhost server, protected by the given user and password settings. The second connects to an sqlite database stored at the given path; the third one connects to a database of CSV files stored at the giveh directory. Note, you will need to make sure that the specified driver in the dsn parameter is installed on your system. Please look up the individual driver documentation how to specify a correct value for dsn.

Securing database access

Underneath the database connections section in configure, there is a section to configure access control. Access can be restricted by wiki user / group, by a 'whitelist' of permitted queries, or both. If no access control is specified for a database connection, then all access is allowed for that connection.

Here is an example. Note that the first element of the queries list is a regular expression, and the rest of the entries are literal string matches.

[
  {
    'who' => 'KipLubliner',
    'id' => 'mysql',
    'queries' => [
      'SELECT [^;]+',
      'UPDATE TEAM SET TM_NAME = ? WHERE TMID = ?',
      'UPDATE PLAYER SET PL_NICKNAME = ?, PL_FIRSTNAME = ?, PL_LASTNAME = ?, PL_TEAM = ? WHERE PLID = ?',
      'INSERT INTO TEAM( TM_NAME ) VALUES (?)',
      'INSERT INTO PLAYER( PL_NICKNAME, PL_FIRSTNAME, PL_LASTNAME, PL_TEAM ) VALUES (?, ?, ?, ?)'
    ]
  }
];

See the integrated documentation on the configure screen for more details.

Installation Instructions

You do not need to install anything in the browser to use this extension. The following instructions are for the administrator who installs the extension on the server.

Open configure, and open the "Extensions" section. Use "Find More Extensions" to get a list of available extensions. Select "Install".

If you have any problems, or if the extension isn't available in configure, then you can still install manually from the command-line. See http://foswiki.org/Support/ManuallyInstallingExtensions for more help.