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

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

• PEAR::DB (Not Anymore)
• php-pdo

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

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

• fixed: Error when using PHP8 (2023-06-14 14:52)
• Merge pull request #4 from ALIXSYS/master (2019-05-19 17:55)
• bugfix: Was not handeling errors correctly (2019-04-25 15:40)
• Got rid of PEAR::DB dependency + Ready for PHP7 (2018-11-06 15:24)
• Merge pull request #2 from splitbrain-forks/php7-signature (2016-03-05 12:16)
• Adjust method signatures to match parent (2016-02-03 12:46)
• fixed README (2010-12-26 12:13)
• added README (2010-12-23 12:51)

(Old)

• 07-05-2007 — Made it display results 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
• 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.

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

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

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

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.

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

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.
  • Alternatively you can use this SQL Server ODBC driver here is the connectivity guide
• 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

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

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

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