sql plugin

This plugin gives you the ability to execute an SQL query on a database and displays the result as a table.

<sql CONNECTION [OPTION]..>SQL_QUERY</sql>

• CONNECTION is Login ':' Password '@' Database-Server '/' Database-Name
• OPTION may be:
  • wikitext=“enable” or wikitext=“disable”
  • display=“inline” or display=“block”
  • position=“vertical” or position=“horizontal”

SQL_QUERY is the standard SQL query to perform against the database.

<sql db="mysql://login:password@server/database"> select * from table; </sql>

by default SQL plugin parses the database result for wikitext. this can be very slow for long results. to disable wikitext parsing use the wikitext property like this :

<sql db="mysql://login:password@server/database" wikitext="disable"> select * from table; </sql>

if your result has more than 3 or 4 columns, you can chose to position results vertically with the position property like this :

<sql db="mysql://login:password@server/database" position="vertical"> select * from table; </sql>

alternatively you can save space on the page by stacking result tables horizontally with the display property like this :

<sql db="mysql://login:password@server/database" display="inline"> select * from table; </sql>

• PEAR::DB
• On Debian Etch (4.0) hosts install the packages: mysql-client php-pear php-db php5-mysql

1. Create a directory in your plugin directory and name it “sql”.
2. Create a file in the “sql” directory and name it “syntax.php”.
3. Copy/Paste the code below in the “syntax.php” file. make sure <?php is the first line of the file, don't leave any blank line
4. You're done.

<?php
/**
 * Plugin SQL:  executes SQL queries
 * 
 * @license    GPL 2 (http://www.gnu.org/licenses/gpl.html)
 * @author     Slim Amamou <slim.amamou@gmail.com>
 */
 
if(!defined('DOKU_INC')) define('DOKU_INC',realpath(dirname(__FILE__).'/../../').'/');
if(!defined('DOKU_PLUGIN')) define('DOKU_PLUGIN',DOKU_INC.'lib/plugins/');
require_once(DOKU_PLUGIN.'syntax.php');
require_once(DOKU_INC.'inc/parserutils.php');
require_once('DB.php');
 
function property($prop, $xml)
{
	$pattern = $prop ."='([^']*)')";
	if (ereg($pattern, $xml, $matches)) {
		return $matches[1];
	}
	$pattern = $prop .'="([^"]*)"';
	if (ereg($pattern, $xml, $matches)) {
		return $matches[1];
	}
	return FALSE;
}
 
/**
 * All DokuWiki plugins to extend the parser/rendering mechanism
 * need to inherit from this class
 */
class syntax_plugin_sql extends DokuWiki_Syntax_Plugin {
    var $databases = array();
	var $wikitext_enabled = TRUE;
	var $display_inline = FALSE;
	var $vertical_position = FALSE;
 
    /**
     * return some info
     */
    function getInfo(){
        return array(
            'author' => 'Slim Amamou',
            'email'  => 'slim.amamou@gmail.com',
            'date'   => '2007-05-07',
            'name'   => 'SQL Plugin',
            'desc'   => 'connects to a database and executes an SQL query',
            'url'    => 'http://www.dokuwiki.org/wiki:plugins:sql',
        );
    }
 
    /**
     * What kind of syntax are we?
     */
    function getType(){
        return 'substition';
    }
 
    /**
     * Where to sort in?
     */ 
    function getSort(){
        return 555;
    }
 
 
    /**
     * Connect pattern to lexer
     */
    function connectTo($mode) {
      $this->Lexer->addEntryPattern('<sql [^>]*>',$mode,'plugin_sql');
    }
 
    function postConnect() {
      $this->Lexer->addExitPattern('</sql>','plugin_sql');
    }
 
 
    /**
     * Handle the match
     */
    function handle($match, $state, $pos, &$handler){
        switch ($state) {
          case DOKU_LEXER_ENTER : 
			$urn = property('db',$match);
			$wikitext = property('wikitext', $match);
			$display = property('display', $match);
			$position = property('position', $match);
			return array('urn' => $urn, 'wikitext' => $wikitext, 'display' => $display, 'position' => $position);
            break;
          case DOKU_LEXER_UNMATCHED :
			$queries = explode(';', $match);
			if (trim(end($queries)) == "") {
				array_pop($queries);
			}
			return array('sql' => $queries);
            break;
          case DOKU_LEXER_EXIT :
			$this->wikitext_enabled = TRUE;
			$this->display_inline = FALSE;
			$this->vertical_position = FALSE;
			return array('wikitext' => 'enable', 'display' => 'block', 'position' => 'horizontal');
            break;
        }
        return array();
    }
 
    /**
     * Create output
     */
    function render($mode, &$renderer, $data) {
		$renderer->info['cache'] = false;
 
        if($mode == 'xhtml'){
 
			if ($data['wikitext'] == 'disable') {
				$this->wikitext_enabled = FALSE;
			} else if ($data['wikitext'] == 'enable') {
				$this->wikitext_enabled = TRUE;
			}
			if ($data['display'] == 'inline') {
				$this->display_inline = TRUE;
			} else if ($data['display'] == 'block') {
				$this->display_inline = FALSE;
			}
			if ($data['position'] == 'vertical') {
				$this->vertical_position = TRUE;
			} else if ($data['position'] == 'horizontal') {
				$this->vertical_position = FALSE;
			}
			if ($data['urn'] != "") {
				$db =& DB::connect($data['urn']);
				if (DB::isError($db)) {
					$error = $db->getMessage();
					$renderer->doc .= '<div class="error">'. $error .'</div>';
					return TRUE;
				}
				else {
					array_push($this->databases, $db);
				}
			}
			elseif (!empty($data['sql'])) {
			    $db =& array_pop($this->databases);
				if (!empty($db)) {
					foreach ($data['sql'] as $query) {
						$db->setFetchMode(DB_FETCHMODE_ASSOC);
						$result =& $db->getAll($query);
						if (DB::isError($result)) {
							$error = $result->getMessage();
							$renderer->doc .= '<div class="error">'. $error .'</div>';
							return TRUE;
						}
						elseif ($result == DB_OK or empty($result)) {
						}
						else {
 
							if (! $this->vertical_position) {
								if ($this->display_inline) {
									$renderer->doc .= '<table class="inline" style="display:inline"><tbody>';
								} else {
									$renderer->doc .= '<table class="inline"><tbody>';
								}
								$renderer->doc .= '<tr>';
								foreach (array_keys($result[0]) as $header) {
									$renderer->doc .= '<th>';
									if ($this->wikitext_enabled) {
										$renderer->nest(p_get_instructions($header));
									} else {
										$renderer->cdata($header);
									}
									$renderer->doc .= '</th>';
								}
								$renderer->doc .= '</tr>';
								foreach ($result as $row) {
									$renderer->doc .= '<tr>';
									foreach ($row as $cell) {
										$renderer->doc .= '<td>';
										if ($this->wikitext_enabled) {
											$renderer->nest(p_get_instructions($cell));
										} else {
											$renderer->cdata($cell);
										}
										$renderer->doc .= '</td>';
									}
									$renderer->doc .= '</tr>';
								}
								$renderer->doc .= '</tbody></table>';
							} else {
								foreach ($result as $row) {
									$renderer->doc .= '<table class="inline"><tbody>';
									foreach ($row as $name => $cell) {
										$renderer->doc .= '<tr>';
										$renderer->doc .= "<th>$name</th>";
										$renderer->doc .= '<td>';
										if ($this->wikitext_enabled) {
											$renderer->nest(p_get_instructions($cell));
										} else {
											$renderer->cdata($cell);
										}
										$renderer->doc .= '</td>';
										$renderer->doc .= '</tr>';
									}
									$renderer->doc .= '</tbody></table>';
								}
							}
						}
					}
				}
			}
            return true;
        }
        return false;
    }
}

• 07-05-2007 — Made it display resuls inline and vertically¹⁾
• 14-11-2006 — Made it parse query results for wikitext
• 13-06-2006 — Made it accept multiple queries²⁾
• 24-05-2006 — Release name : Kais³⁾ — corrected the caching issue⁴⁾
• 20-05-2006 — Released.

• find a way to disable caching of the page — done.⁵⁾
• test and correct the bugs — done.
• make it accept multiple queries in one <sql></sql> statement — done.
• make it parse database results for wiki markup — done. also added a new property to disable wikitext parsing — Slim Amamou 2006-11-14 13:00
• lobby to make dokuwiki maintainers solve the “show page source” security problem ⁶⁾ — no longer an issue starting from version 2006-11-06 with the new $conf['disableaction'] configuration option
• add an inline mode for the query result tables, so we can save space displaying them aligned horizontally. — done.
• make it trait multiple queries as a transaction.
• refactor to use MDB2 instead of DB.
• have the use of charsets like utf8 or latin1 available

closed by	— Slim Amamou 2006-06-13 15:26

whenever i put more than 1 query in a page, i get this error : Fatal error: Call to undefined function: setfetchmode() in /usr/share/dokuwiki-2005-09-22/lib/plugins/sql/syntax.php on line 75

i installed the plugin and according to the plugin manager, everything works. but the wikipage stops rendering when it hits the ”<sql …” part.” and stays white, no error message or anything. is there a way to find out what's wrong?

solved. DB.php wasn't in phps include_path (SLES 9), thus i put the complete path to DB.php into the syntax.php source, which gets rid of the DB.php not found php error, but the missing include_path still causes DB.php to somehow bail out internally.

Is it possible to connect to an Oracle database with this plugin? It looks like it should be; since it's written using PEAR, I'm assuming it's database-agnostic. Thanks for any advice. —antonio romero 9 may 2007

yes of course. try <sql db="oci8://login:password@server/database"> select * from table; </sql> — Slim Amamou 2007-12-02 01:28

I have a question. Is the 'Show page source' button always present in dokuwiki website? If it's true I think there is a big problem with the syntax. In fact, with the possibility of watching the wiki source's of a page, anybody can catch your database login and password. Perhaps it will be more safe if you use a 'conf' file in your plugin directory (not an ini or inc file ⇒ keep in mind that those files are not always parsed by server)

you can setup acl to restrict editing to authorized users. — Slim Amamou 2006-05-23 00:14

As i can see in my own wiki, when a user doesn't have the rights for editing a page (via ACL), the 'Edit page' button becomes a 'Show pagesource' button. With this, anybody can see the source of a page. For now, I didn't find a way to preventing the display of this button except with code modification. Perhaps I'm wrong but I think that in some case, there will be some security problem. — Takashi 2006-05-23 08:02

There is a discussion on the mailinglist concerning this. Please read and join in. — Christopher Smith 2006-05-24 01:34

Following is a workarournd proposed by Otto Vainio — Slim Amamou 2006-06-22 11:37
This is not enough, because a “hacker” still can see the page source, if he type the correct url: http://yourwikipage.com/?do=edit — Elbandi 2007-09-29 21:10

For the Header. in the template file, near header include line :

    <?php /*old includehook*/ @include(dirname(__FILE__).'/header.html')?>
    <?php if($INFO['perm'] > AUTH_READ){?>
    <div class="bar" id="bar__top">
      <div class="bar-left" id="bar__topleft">
        <?php tpl_button('edit')?>
        <?php tpl_button('history')?>
      </div>
      <div class="bar-right" id="bar__topright">
        <?php tpl_button('recent')?>
        <?php tpl_searchform()?>&nbsp;
      </div>
 
 
      <div class="clearer"></div>
    </div>
 
 
    <?php } ?>

And footer. Between pagefooter and footer include lines :

<?php /*old includehook*/ @include(dirname(__FILE__).'/pagefooter.html')?>
<?php if($INFO['perm'] > AUTH_READ){?>
 
    <div class="bar" id="bar__bottom">
      <div class="bar-left" id="bar__bottomleft">
        <?php tpl_button('edit')?>
        <?php tpl_button('history')?>
      </div>
      <div class="bar-right" id="bar__bottomright">
        <?php tpl_button('subscription')?>
        <?php tpl_button('admin')?>
        <?php tpl_button('profile')?>
        <?php tpl_button('login')?>
        <?php tpl_button('index')?>
        <?php tpl_button('top')?>&nbsp;
      </div>
      <div class="clearer"></div>
    </div>
 
 
 
    <?php } else {?>
      <div class="bar-right" id="bar__bottomright">
         <?php tpl_button('login')?>
      </div>
    <?php }?>
  </div>

in render() add the line $renderer→info['cache'] = false; — Christopher Smith 2006-05-21 19:59

thank you christopher, but it seems to be not enough. it does not cache the page upon SAVE but it seems like the page is cached next time it is accessed. it should be never cached. — Slim Amamou 2006-05-22 10:41

Hmmm, its not something I have had to deal with in my plugins so I am not so familiar with turning off caching. The blog plugin does it, so you may want to check it out. I suspect, the page isn't being cached by DokuWiki but by your browser, so that you need to alter the headers that are being sent out. In which case, you'll want to include an action plugin to handle the ACTION_HEADERS_SEND event, that will go something like this … (UNTESTED CODE)

<?php
/**
 * Action Plugin: 
 * 
 * @license    GPL 2 (http://www.gnu.org/licenses/gpl.html)
 * @author     Christopher Smith <chris@jalakai.co.uk>  
 */
 
if(!defined('DOKU_INC')) define('DOKU_INC',realpath(dirname(__FILE__).'/../../').'/');
if(!defined('DOKU_PLUGIN')) define('DOKU_PLUGIN',DOKU_INC.'lib/plugins/');
require_once(DOKU_PLUGIN.'action.php');
 
/**
 * All DokuWiki plugins to extend the parser/rendering mechanism
 * need to inherit from this class
 */
class action_plugin_sql extends DokuWiki_Action_Plugin {
 
    /**
     * return some info
     */
    function getInfo(){
      return array(
        'author' => 'xxx',
        'email'  => 'xxx@xxx',
        'date'   => '2006/##/##',
        'name'   => 'SQL Plugin',
        'desc'   => '...',
        'url'    => 'http://www.dokuwiki.org/plugin:sql',
      );
    }
 
    /*
     * plugin should use this method to register its handlers with the dokuwiki's event controller
     */
    function register(&$controller) {
      $controller->register_hook('ACTION_HEADERS_SEND', 'BEFORE',  $this, 'sql_headers', NULL);
    }
 
 
    function sql_headers(&$event, $param) {
      $event->data[] = "... appropriate header string";
    }
 
}
 
//Setup VIM: ex: et ts=4 enc=utf-8 :
?>

You'll probably need some more logic in the code to determine if the page has any SQL syntax. The best way to handle that is most likely to utilise the page's metadata - have your syntax plugin write some metadata concerning the page and its SQL use or its caching requirement and then have the action plugin check the metadata before deciding whether or not to send additional headers. — Christopher Smith 2006-05-22 11:30

i found the problem in p_cached_xhtml(). it is trying to get cached instructions unconditionnaly (ignoring $info['cache']). for me it's a bug : it does not make sense to use cached instructions when it's explicitly asked to not use cache at all. what do you think? (BTW p_cached_instructions() is called with $info as it's third argument when it is declared with only two arguments) — Slim Amamou 2006-05-23 01:12

Not a bug. Instructions should be cacheable. $info['cache'] is a renderer value and doesn't exist in the handler. If you need to do something everytime the page is required, do it in the render function and standardise the instruction generated by the handle function. In your case, you might create the SQL statements in the handle() function and then use them to retrieve the data and format it for output in the render() function. — Christopher Smith 2006-05-23 01:31

Thanks Christopher for your help, the problem is now corrected. — Slim Amamou 2006-05-27 00:21

Hey Slim,

When attempting to use your sql plugin I encountered the following error.

Warning: Cannot modify header information - headers already sent by
(output started at
/home/content/m/c/d/mcdairmid/html/lib/plugins/sql/syntax.php:1) in
/home/content/m/c/d/mcdairmid/html/inc/actions.php on line 287

I have the latest version of dokuwiki, I have php5 and have tried it on mySQL 4.1 and 5.0 with the same result.

I followed all the instructions..made the syntax.php file in the sql directory and entered all the information in the wiki page.

<sql db="mysql://login:password@server/database"> select * from table;
</sql>

any clues?

THANKS!, Austen

hi austen,
you probably left an empty line at the beginning of syntax.php.
just remove anything before <?php and it should work. — Slim Amamou 2007-04-17 09:38

yeah that was it…Thanks

Hey Slim,

I was wondering if it might at all be possible to get the table results to come out vertically rather than horizontally.

Also it would be really helpful if the plugin allowed you to get a .csv file of the table information…so that non admin could have a clean looking record of what's in the database if they wanted it.

Thanks, Austen

hi austen,
the new release allows to get table results vertically. but you know it already
thanks for the advice. — Slim Amamou 2007-05-07 18:40

Yeah slim you have a new security problem…if someone searches for sql all the information shows up…so its not >hid….is there a plugin already that will hide certain information?
Thanks

while i was coding on 24th of May 2006, i became the proud father of a beautyful son i called “Kais”. this release is dedicated to Kais Amamou.— Slim Amamou

My DokuWiKi platform is Windows 2003 + IIS + PHP 5.2.4. To get the connection to MS SQL Server you need these:

• PHP Extensions: PECL and PEAR.
  • Im sorry I cannot describe the PECL-installation here. I installed it simultaneously with PHP.
  • PEAR is installed in command prompt⁷⁾ with go-pear.bat in C:\php.
  • Install DB to PEAR with this in C:\php\PEAR:

PEAR.php install DB

• Check installed PEAR components with: PEAR.php list.
  • See here for more: http://www.easysoft.com/developer/languages/php/pear-db-odbc.html.
• Edit PHP.ini to match this:

; Windows Extensions
extension=php_mssql.dll

[MSSQL]
mssql.allow_persistent = On
mssql.max_persistent = -1
mssql.max_links = -1
mssql.min_error_severity = 10
mssql.min_message_severity = 10
mssql.compatability_mode = Off
mssql.secure_connection = off

• Replace C:\php\ntwdblib.dll with this one: http://www.userscape.com/ntwdblib.dll
  • See here for explanation: mssql_connect(): Unable to connect to server
• Restart IIS ⁸⁾.
• Test: Create new file by the name dbtest.php to a your website (C:\Inetpub\dokuwiki or something like it) and copy this in it:

<?php
require_once 'DB.php';
PEAR::setErrorHandling(PEAR_ERROR_DIE);
$db_host = 'your.sqlservers.address.domain.com';
$db_user = 'yourloginusername';
$db_pass = 'yourloginuserpassword';
$db_name = 'yourdatabasename';
$dsn = "mssql://$db_user:$db_pass@$db_host/$db_name";
echo $dsn;
$db = DB::connect($dsn);
$db->setFetchMode(DB_FETCHMODE_OBJECT);
?>

• Test: Modify dbtest.php to match your MS SQL server credentials and databases.
• Test: Open http://mydokuwikiaddress.domain.com/dbtest.php in your browser. If the the connection is successfull, you see smthng like this:

mssql://yourloginusername:yourloginuserpassword@your.sqlservers.address.domain.com/yourdatabasename

— Ciove 26.6.2008

right after Line 146

$db->setFetchMode(DB_FETCHMODE_ASSOC);

insert

$db->Query("SET CHARACTER SET UTF8");
$db->Query("SET NAMES UTF8");

— TTomas 2008-07-13 18:38