TIP SqlPlugin is not installed on Foswiki.org.

SqlPlugin

SQL interface for Foswiki

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.

Plugin Upgrade Notes

1.03

When the database parameter to %SQL% is present and that value was not present in the configure map, previous versions would use the first value listed by default. Starting with 1.03, the SQL macro call will fail instead. The behavior when the database parameter is omitted is unchanged.

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. "Extensions Operation and Maintenance" Tab -> "Install, Update or Remove extensions" Tab. Click the "Search for Extensions" button. Enter part of the extension name or description and press search. Select the desired extension(s) and click install. If an extension is already installed, it will not show up in the search results.

You can also install from the shell by running the extension installer as the web server user: (Be sure to run as the webserver user, not as root!)
cd /path/to/foswiki
perl tools/extension_installer <NameOfExtension> 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 https://foswiki.org/Support/ManuallyInstallingExtensions for more help.

Dependencies

NameVersionDescription
DBI>=1Required.
Text::ParseWords>=1Required.

Change History

06 May 2022: (4.00) Foswiki:Tasks/Item14987 - add params option to database config to be able to specify driver specific optios such as mysql_enable_utf8; Foswiki:Tasks/Item14849 - fix strange formatted query results when match occurs in start of field names like time and timeStamp (Foswiki:Main/BramVanOosterhout)
09 Sep 2016: (3.03) Foswiki:Tasks/Item14189 - fix checking for access control rules, i.e. when none are defined
29 Sep 2015: (3.02) Foswiki:Tasks/Item13623 - fixed error initializing access rules
25 Sep 2014: (3.00) Foswiki:Tasks/Item13037 - rewrite the core in an object-oriented way
18 Mar 2014: (2.00) Foswiki:Tasks/Item12801 - make logging of sql action configurable
29 Jan 2014: (1.99) Foswiki:Tasks/Item12245 - enhance handing of bind parameters (Foswiki:Main/FrankHoellering)
Foswiki:Tasks/Item12236 - cache bind values to be reused in continue mode (Foswiki:Main/FrankHoellering)
Foswiki:Tasks/Item12737 - fix oracle long column type (Foswiki:Main/HansJosefKoehler)
5 Aug 2012: (1.03) Foswiki:Tasks/Item12029 - more robust 'database' param handling
Foswiki:Tasks/Item12030 - DB errors cause unintended SQL calls
Foswiki:Tasks/Item12018 - queries spread across multiple lines
Foswiki:Tasks/Item12033 - also log access from API and log access control failures (Foswiki:Main/KipLubliner)
18 May 2012: (1.02) Added bind parameters, access control, perl API. (Foswiki:Main/KipLubliner)
14 Jun 2010: removed hard-coded sort of column keys
I Attachment Action Size Date Who Comment
SqlPlugin.md5md5 SqlPlugin.md5 manage 150 bytes 06 May 2022 - 08:58 MichaelDaum  
SqlPlugin.sha1sha1 SqlPlugin.sha1 manage 174 bytes 06 May 2022 - 08:58 MichaelDaum  
SqlPlugin.tgztgz SqlPlugin.tgz manage 10 K 06 May 2022 - 08:58 MichaelDaum  
SqlPlugin.zipzip SqlPlugin.zip manage 14 K 06 May 2022 - 08:58 MichaelDaum  
SqlPlugin_installerEXT SqlPlugin_installer manage 4 K 06 May 2022 - 08:58 MichaelDaum  
Topic revision: r13 - 06 May 2022, MichaelDaum
The copyright of the content on this website is held by the contributing authors, except where stated elsewhere. See Copyright Statement. Creative Commons License    Legal Imprint    Privacy Policy