====== SQL Plugin ======

---- plugin ----
description: Execute SQL query and display the result as a table
author     : Slim Amamou
email      : slim.amamou@gmail.com
type       : syntax
lastupdate : 2023-06-14
compatible : 
depends    : 
conflicts  : 
similar    : 
tags       : database search mysql sqlite 

downloadurl: https://github.com/ALIXSYS/plugin-sql/archive/master.zip
sourcerepo:  https://github.com/ALIXSYS/plugin-sql
bugtracker:  https://github.com/ALIXSYS/plugin-sql/issues
----
__The file //plugin.info.txt// is not up to date! And //issues// are not enabled in the repository! Please fix this!__

===== Installation =====

^  Download   | [[https://github.com/ALIXSYS/plugin-sql/archive/master.zip|Current archive from github]] |
^  Repository | [[https://github.com/ALIXSYS/plugin-sql|Repository on github]] |

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

==== Requirements ====

  * <del>[[http://pear.php.net/package/DB|PEAR::DB]]</del> (Not Anymore)
  * php-pdo



===== Examples =====

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

by default SQL plugin parses the database result for [[wikipedia>wikitext]]. this can be very slow for long results. to disable wikitext parsing use the __''wikitext'' property__ like this:

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

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

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

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

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

===== Syntax =====

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

  * ''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 ====
{{rss>https://github.com/ALIXSYS/plugin-sql/commits/master.atom date}}

(Old)
  * 07-05-2007 --- Made it display results inline and vertically((thanks to austen for the advice))
  * 14-11-2006 --- Made it parse query results for [[wp>wikitext]]
  * 13-06-2006 --- Made it accept multiple queries((see [[#to_do]]))
  * 24-05-2006 --- Release name : Kais((see [[#discussion]])) --- corrected the caching issue((see [[#to_do]]))
  * 20-05-2006 --- Released.

==== ToDo/Wish list ====

  * <del>find a way to disable caching of the page</del> --- done.((thanks to Christopher Smith. see [[#discussion]]))
  * <del>test and correct the bugs</del> --- done.
  * <del>make it accept multiple queries in one ''<sql></sql>'' statement</del> --- done.
  * <del>make it parse database results for wiki markup</del> --- done. also added a new property to disable wikitext parsing  --- //[[slim.amamou@gmail.com|Slim Amamou]] 2006-11-14 13:00//
  * <del>lobby to make dokuwiki maintainers solve the "show page source" security problem</del> ((see [[#discussion]])) --- no longer an issue starting from __version 2006-11-06__ with the new ''$conf['disableaction']'' configuration option
  * <del>add an ''inline mode'' for the query result tables, so we can save space displaying them aligned horizontally.</del> --- done.
  * make it trait multiple queries as a transaction.
  * <del>refactor to use [[http://pear.php.net/package/MDB2|MDB2]] instead of DB.</del>
  * <del>have the use of charsets like utf8 or latin1 available</del>
  * 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@gmail.com|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|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@natural-design.net|Takashi]] 2006-05-23 08:02//
>>> There is a [[http://www.freelists.org/archives/dokuwiki/05-2006/msg00456.html|discussion]] on the [[mailinglist]] concerning this.  Please read and join in. --- //[[chris@jalakai.co.uk|Christopher Smith]] 2006-05-24 01:34//
>>>> Following is a **workarournd** proposed by Otto Vainio  --- //[[slim.amamou@gmail.com|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  --- //[[ea333@freemail.hu|Elbandi]] 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.

<code>
**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
</code>

<del>You have to install the [[http://pear.php.net/package/DB|PEAR::DB]] package.
</del>

This is not an issue anymore since today  --- [[user>slim|slim]] //2018-11-23 00:38//

==== Can I use it for Oracle-Queries? ====

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

==== 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
<code>
  $db->setFetchMode(DB_FETCHMODE_ASSOC);
insert
  $db->Query("SET CHARACTER SET UTF8");
  $db->Query("SET NAMES UTF8");
</code>

 --- //[[tarka0@gmail.com|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: [[wp>PHP_Extension_Community_Library|PECL]] and [[wp>PHP_Extension_Community_Library#External_links|PEAR]].
  * Im sorry I cannot describe the PECL-installation here. I installed it simultaneously with PHP.
  * PEAR is installed in command prompt((DOS-window)) with ''go-pear.bat'' in ''C:\php''.
  * Install DB to PEAR with this in ''C:\php\PEAR'':
<code>PEAR.php install DB</code>
  * 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 [[https://devart.com/odbc/sqlserver/|SQL Server ODBC driver]] here is the  [[https://www.devart.com/odbc/sqlserver/docs/driver_configuration_and_conne.htm|connectivity guide]]
  * Edit PHP.ini to match this:
<code>
; 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
</code>

    * Replace ''C:\php\ntwdblib.dll'' with this one: [[http://www.userscape.com/ntwdblib.dll]]
      * See here for explanation: [[http://www.akamarketing.com/blog/99-php-sql-server-connection-problems-mssql_connect-functionmssql-connect-unable-to-connect-to-server.html|mssql_connect(): Unable to connect to server]]
    * Restart IIS ((IISreset)). 
    * 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:
<code>
<?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);
?>
</code>
    * 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:
<code>

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

</code>

--- //Ciove 26.6.2008//

===== Discussion =====


== finding a way to disable caching of the page ==

> in render() add the line  ''$renderer->info['cache'] = false;'' --- //[[chris@jalakai.co.uk|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@gmail.com|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 [[events_list#ACTION_HEADERS_SEND]] event, that will go something like this ... (**UNTESTED CODE**) 


<code php>

    /*
     * 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 :
</code>

\\ 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. --- //[[chris@jalakai.co.uk|Christopher Smith]] 2006-05-22 11:30//

> I found the problem in [[xref>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 [[xref>p_cached_instructions()]] is called with ''$info'' as it's third argument when it is declared with only two arguments) --- //[[slim.amamou@gmail.com|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. --- //[[chris@jalakai.co.uk|Christopher Smith]] 2006-05-23 01:31//
>>> Thanks Christopher for your help, the problem is now corrected.  --- //[[slim.amamou@gmail.com|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
<code>$difference = $this->_difference($Cache,$rs);</code>
to this 
<code>$difference = $this->_difference($rs,$rs);</code>
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 no spam at sequent dot org|Baron]] 2009-09-27//