DokuWiki

It's better when it's simple

User Tools

Site Tools


plugin:sql

SQL Plugin

Compatible with DokuWiki

No compatibility info given!

plugin Execute SQL query and display the result as a table

Last updated on
2023-06-14
Provides
Syntax
Repository
Source

Similar to dbquery, sqlcomp, sqlraw

Tagged with database, mysql, search, sqlite

The file plugin.info.txt is not up to date! And issues are not enabled in the repository! Please fix this!

Installation

Use this address to install this plugin by using the plugin manager.

Requirements

Examples

<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>

Syntax

<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.

Development

Changelog

(Old)

  • 07-05-2007 — Made it display results inline and vertically1)
  • 14-11-2006 — Made it parse query results for wikitext
  • 13-06-2006 — Made it accept multiple queries2)
  • 24-05-2006 — Release name : Kais3) — corrected the caching issue4)
  • 20-05-2006 — Released.

ToDo/Wish list

  • find a way to disable caching of the page — done.5)
  • 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 6) — 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
  • Set the db name, user name and password in the plugin configuration page not the wiki page to remove this information from public view, eg in config set comma separated triplets : mydb1:username1:password1, mydb2:username2,password2. Then the wiki page tag can just refer to which db. The user name and password can be pulled from the connection page to set up the connection keeping the connection more secure.

Known Bugs

"show page source" security problem

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=editElbandi 2007-09-29 21:10

More than one query on a page

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

FAQ

Failed opening required DB.php

I installed this plugin and this is the message when I opened my website.

**Warning:** require_once(DB.php) [function.require-once]: failed to open stream: No such file or directory in C:\wamp\www\manual\lib\plugins\sql\syntax.php on line 13

**Fatal error:** require_once() [function.require]: Failed opening required 'DB.php' (include_path='.;C:\php5\pear') in C:\wamp\www\manual\lib\plugins\sql\syntax.php on line 13

You have to install the PEAR::DB package.

This is not an issue anymore since today — slim 2018-11-23 00:38

Can I use it for Oracle-Queries?

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

No output at all

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.

Solution: The 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.

Change encoding to UTF-8

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

Configuration for Windows-Installations

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 prompt7) with go-pear.bat in C:\php.
  • Install DB to PEAR with this in C:\php\PEAR:
PEAR.php install DB
; 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
<?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);
?>
mssql://yourloginusername:yourloginuserpassword@your.sqlservers.address.domain.com/yourdatabasename

Ciove 26.6.2008

Discussion

finding a way to disable caching of the page
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)
    /*
     * plugin should use this method to register its handlers with the dokuwiki's event controller
     */
    function register(Doku_Event_Handler $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 utilize 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 unconditionally (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 every time the page is required, do it in the render function and standardize 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
finding a way to disable cache comparison every time the page loads

The _query function in the syntax.php file can be changed from this

$difference = $this->_difference($Cache,$rs);

to this

$difference = $this->_difference($rs,$rs);

and that will get rid of the cache comparison every time the page loads. Not the best way to go about it, but it works. — 2011-08-18 14:13

Spurious new paragraphs

When I view the source of the page (the HTML source, not the wiki source) I see every cell has a new paragraph inside of it. This causes a lot of extra whitespace that I don't want (the cells get too big and I have to scroll, lines wrap…). This goes away if I disable wikitext. What's happening, and can I disable it while keeping wikitext parsing? — Baron 2009-09-27

1)
thanks to austen for the advice
2) , 4)
see to_do
5)
thanks to Christopher Smith. see discussion
7)
DOS-window
8)
IISreset
plugin/sql.txt · Last modified: 2024-02-01 18:54 by 2a01:190:5:1001:af4e:b82e:cc0a:bede

Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Share Alike 4.0 International
CC Attribution-Share Alike 4.0 International Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki