Extension talk:SQL2Wiki/old

From mediawiki.org
< Extension talk:SQL2Wiki
Latest comment: 13 years ago by Bytesmiths in topic Use with Extension:InputBox?

Security[edit]

Latest comment: 17 years ago1 comment1 person in discussion

Hmm, how about DELETE * FROM `page`? Looks nice, but like a major security breach. --GunterS 11:03, 18 March 2007 (UTC)Reply

Answer
That should be no concern. You have to handle security issues on database level. Set up a user account on each database you want to query from the wiki, and give it the minimal permissions sufficient to retrieve data.
Dont't do
GRANT ALL ON yourDB.* to 'wikisqlext'@'wikihost'
Instead do
GRANT SELECT ON yourDB.* to 'wikisqlext'@'wikihost'

Incompatible with Mediawiki-1.16.0?[edit]

I can't get this extension to work anymore with 1.16.0 The only output I get is in the apache error log: PHP Warning: Parameter 3 to renderSQL() expected to be a reference, value given in /usr/local/httpd-2.2.15/htdocs/w/includes/parser/Parser.php on line 3333

To fix this edit sql2wiki.php in the ~/wiki/extensions/ directory

Edit line 10, as follows:

Existing - function renderSQL( $input, $argv, &$parser ) {

NEW - function renderSQL( $input, $argv, $parser ) {

That fixes an update in php 5.3, and later 5.2.x that deprecated reference passing.

Incompatible with Mediawiki-1.8.4?[edit]

On my installation, it simply crashes with a segmentation fault (signal 11). The user gets a blank page.

Anyone got this one running on 1.8.x?

Could it be the settings of my php installation, or the mysql client libraries? But MediaWiki itself runs just fine, using (probably) the same php installation and mysql libs..

Update
it is the line
$meta = mysql_fetch_field($result, $i);
that generates the segmentation fault. Hmmm.. mysql_fetch_field should be a fairly often used function... to bad i'm not a php programmer who could fix that.
Google search
says i got a library information problem. Matching my first assumption, this is considered a fact now. I'll reinstall php and keep you updated if that fixed the error.
Major OOps
This should probably go to some php forum. it has nothing to do with the wonderful sql extension.
It turns out that during the linking phase of php, a path gets included that contains (old) mysql-4 libraries, before the path that contains the (correct) mysql-5 libraries. It's not php's fault, nor configure's fault, nor Sun's fault, nor mine.. it's just bad coincidence.
3 possible solutions
  • do some very tricky configure thing
  • hack apachectl so LD_LIBRARY_PATH gets set
  • uninstall that fsckin Sunfreeware-mysql-4 bullshit.
Easy solution number 3 ;-)

Thank you very much for this useful extension!

How to have it worked with Special Pages / Templates?[edit]

Latest comment: 17 years ago3 comments2 people in discussion

Hi,

Thank you for this extension. I've installed it and it works really good. However there are some problems with having it working in the templates or special pages. I get errors like:

Warning: mysql_query(): 80 is not a valid MySQL-Link resource in /wiki/includes/Database.php on line 435

Warning: mysql_errno(): 80 is not a valid MySQL-Link resource in /wiki/includes/Database.php on line 660

Warning: mysql_errno(): 80 is not a valid MySQL-Link resource in /wiki/includes/Database.php on line 660

Warning: mysql_errno(): 80 is not a valid MySQL-Link resource in /wiki/includes/Database.php on line 660

Warning: mysql_query(): 80 is not a valid MySQL-Link resource in /wiki/includes/Database.php on line 435

Warning: mysql_errno(): 80 is not a valid MySQL-Link resource in /wiki/includes/Database.php on line 660

Warning: mysql_errno(): 80 is not a valid MySQL-Link resource in /wiki/includes/Database.php on line 660

Warning: mysql_errno(): 80 is not a valid MySQL-Link resource in /wiki/includes/Database.php on line 660 Unable to free MySQL result

It seems the problem is with line: 

function doQuery( $sql ) {
 if( $this->bufferResults() ) {
  $ret = mysql_query( $sql, $this->mConn );
 } else {
  $ret = mysql_unbuffered_query( $sql, $this->mConn );
 }
 return $ret; 
}

Do you know how to fix it to make it possible to display queries in special pages and templates?

thx in advance,

Regards, Aretai 16:13, 20 March 2007 (UTC)Reply

    • In fact the problem seems to be much wider than I thought. When nothing except this tag is put on the page then I get this error. Earlier on I've also put some php code that connects to the database and then it's working OK.

Aretai 14:47, 23 March 2007 (UTC)Reply

The issue is related to the user you are using to access the db: if you are using the same user as the one which accesses normally the db, you will get this fault. Try creating a new user with limited rights and it should work. It does for me with MW 1.8.2. Jean-Lou Dupont 15:30, 23 March 2007 (UTC)Reply

Love this extension![edit]

This extension solves a lot of issues for me. I've been typically writing individual pages for SQL report output, now this can live directly on the Wiki. I suppose this dynamic content violates some kind of Wiki paradigm, it sure solves some issues for me. So what if somebody can't search on the report output.

Is there a way to individually specify a page in a "non-cached" list?

Appearance[edit]

Latest comment: 16 years ago1 comment1 person in discussion

Any ideas how to get number display automatically right adjusted? --HermannSchmitz 19:42, 15 May 2007 (UTC)Reply

results are wrong[edit]

Latest comment: 16 years ago1 comment1 person in discussion

All of my queries are off by 1. For example if I go into a command line and execute Select * FROM whatever; I get 6 rows. However, if I run the same query using this extension I only get 5 rows. I think maybe the counter is off. Any help?

Acook 22:32, 11 January 2008 (UTC)Reply

no results returned?[edit]

2009/07/02 Mediawiki 1.14

I'm new to mediawiki, also to php. After seeing an 'invalid value' error message on the lines in sql2wiki.php that build the table using $result, that is:

if ( !$link ) { return "<hr><b>Error while connecting to host \"$host\" !</b><hr>"; } mysql_select_db($db, $link); $result = mysql_query ($input);

I added:

if (!$result) { return "<hr><b>No Data Returned?!</b><hr>"; }

Sure enough, I get my error message. But when I enter the identical query using phpMyAdmin, I get the results expected. Clearly the extension is connecting to the host successfully, or I would get that error and get no further. I have tried several different (simple and less simple) queries, with the same results. What am I missing?

Any clues?

Thanks.

UPDATE - Apparently phpMyAdmin is just more forgiving about syntax variations. I have it working now.

Why $wgEnableParserCache = false?[edit]

Latest comment: 14 years ago1 comment1 person in discussion

Why tell people to set $wgEnableParserCache = false? Instead, the extension itself should disable the cache (in its source code). That way, only the articles that use the extension will have their cache disabled. 4.79.245.132 16:28, 7 July 2009 (UTC)Reply

Enhancements[edit]

Latest comment: 14 years ago1 comment1 person in discussion

I have added a few enhancements:

  1. Made the default style "sortable" which puts buttons on the column heads for sorting up or down by any column.
  2. Added the tag <sql2wikiInline>...</sql2wikiInline> which takes the first record of the result and outputs it without a table:
    Very useful for stuff like "Here are <sql2wikiInline>SELECT COUNT(*) FROM my_table</sql2wikiInline> records."
  3. Added a new parameter, count, which if it exists, puts "previous" and "next" links at the top and bottom for paging through big data sets, like my 6,650 entry famous quotes database:
    Show 50 famous quotes at a time: <sql2wiki|database=My_Database|count=50>SELECT * FROM Quotes</sql2wiki>

—Bytesmiths 01:15, 16 December 2009 (UTC)Reply

Here's the code: 

<?php

$wgExtensionFunctions[] = "wfSQL2Wiki";
$wgExtensionFunctions[] = "wfSQL2WikiInline";

function wfSQL2Wiki() {
    global $wgParser;
    $wgParser->setHook( "sql2wiki", "renderSQL" );
}

function wfSQL2WikiInline() {
    global $wgParser;
    $wgParser->setHook( "sql2wikiInline", "renderSQLInline" );
}

function renderSQL( $input, $argv, &$parser ) {

    $db = $argv["database"];
    $count = $argv['count'];

    # Definition der DB-Handler, Auswahl der Datenbank
    #
    # grant select on rt3.* to 'rt3_user' identified by "rt3_password";
    #
    $aDB_handles = array( "EcoReality"   => "localhost;EcoRealityBrowse;EcoRealityBrowse");

    # Parameter zusammenkratzen, schamlos gestohlen von "DynamicArticle"-Extension
    while(list($index,$val)=each($aDB_handles)) {
      if ( "$db" == "$index" ) {
        $aParams = explode(";", $val);

        foreach($aParams as $parameter) {
              if( count( $aParams ) < 3 )
                      return "Error in DB_handler definition !";
              $host = trim($aParams[0]);
              $user = trim($aParams[1]);
              $pass = trim($aParams[2]);
        }
      }
    }

    $output = "";
    # --------------------------------------------
    $link = mysql_connect($host, $user, $pass);
    if ( !$link ) { return "<hr><b>Error while connecting to host \"$host\" !</b><hr>"; }

    mysql_select_db($db, $link);

	$start = $_GET['start'];
	if($count) {
		$input .= ' LIMIT ' . ($start + $count) . ',' . $count;
      		$output .= '<table border="0">';
		$pagingLinks = '<tr><td align="left">';
			if(($start - $count) >= 0) {
				$pagingLinks .= '<a href="';
				$pagingLinks .= $_SERVER['PHP_SELF'];
				$pagingLinks .= '?start=';
				$pagingLinks .= $start - $count;
				$pagingLinks .= '">previous ';
				$pagingLinks .= $count;
			}
		$pagingLinks .= '</a></td><td align="right"><a href="';
			$pagingLinks .= $_SERVER['PHP_SELF'];
			$pagingLinks .= '?start=';
			$pagingLinks .= $start +  $count;
			$pagingLinks .= '">next ';
			$pagingLinks .= $count;
		$pagingLinks .= '</a></td></tr>';
		$output .= $pagingLinks;
		$output .= '<tr><td colspan=2>';
	};
    $result = mysql_query ($input);

    # Tabellenkopf bauen
    $output .= '<table border=1 class="sortable">';
    $output .= "<tr>";

    $i = 0;
    while ($i < mysql_num_fields($result)) {
      $meta = mysql_fetch_field($result, $i);
      $output .= "<td><b>$meta->name</b></td>";
      $i++;
    }
    $output .= "</tr>";

    # Tabelle mit Inhalt fuellen
    while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
      $output .= "<tr>";
      foreach ($line as $col_value){
        $output .= "<td>$col_value</td>";

      }
      $output .= "</tr>";
    }

    $output .= "</table>";
    if($count) {
       $output .= '</td></tr>';
       $output .= $pagingLinks;
       $output .= '</table>';
    }

    # Aufraeumen
    mysql_free_result($result);
    mysql_close($link);

    return $output;
}

function renderSQLInline( $input, $argv, &$parser ) {

    $db = $argv["database"];

    # Definition der DB-Handler, Auswahl der Datenbank
    #
    # grant select on rt3.* to 'rt3_user' identified by "rt3_password";
    #
    $aDB_handles = array( "EcoReality"   => "localhost;EcoRealityBrowse;EcoRealityBrowse");

    # Parameter zusammenkratzen, schamlos gestohlen von "DynamicArticle"-Extension
    while(list($index,$val)=each($aDB_handles)) {
      if ( "$db" == "$index" ) {
        $aParams = explode(";", $val);

        foreach($aParams as $parameter) {
              if( count( $aParams ) < 3 )
                      return "Error in DB_handler definition !";
              $host = trim($aParams[0]);
              $user = trim($aParams[1]);
              $pass = trim($aParams[2]);
        }
      }
    }

    $output = "";
    # --------------------------------------------
    $link = mysql_connect($host, $user, $pass);
    if ( !$link ) { return "<hr><b>Error while connecting to host \"$host\" !</b><hr>"; }

    mysql_select_db($db, $link);

    $result = mysql_query ($input);

    while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
      foreach ($line as $col_value){
        $output .= "$col_value ";
      }
    }

    mysql_free_result($result);
    mysql_close($link);

    return $output;
}
?>

Use with Extension:InputBox?[edit]

Latest comment: 13 years ago2 comments2 people in discussion

Will it be possible to use the SQL2Wiki, somehow, in conjunction with Extension:InputBox extension? The idea is to place a search box in any wiki page, take the input, pass to WHERE clause of an SQL query, execute the query through SQL2Wiki extension. --124.195.200.196 15:33, 5 April 2010 (UTC)Reply

Good idea! I added it to the extension. It takes two more parameters:

  • where -- a WHERE clause
  • message -- something do display with the input box

Here's an example to search my book library: http://www.EcoReality.org/wiki/Library_search

Enjoy! —Bytesmiths 02:40, 23 May 2010 (UTC)Reply

I've edited the line where the value of $start is being determined to first check if #_GET['start'] returns something or not by using the empty() function.

Thanks for the code! 

<?php

$wgExtensionFunctions[] = "wfSQL2Wiki";
$wgExtensionFunctions[] = "wfSQL2WikiInline";

function wfSQL2Wiki() {
    global $wgParser;
    $wgParser->setHook( "sql2wiki", "renderSQL" );
}

function wfSQL2WikiInline() {
    global $wgParser;
    $wgParser->setHook( "sql2wikiInline", "renderSQLInline" );
}

function renderSQL( $input, $argv, &$parser ) {
    global $wgParser;

    $db = $argv["database"];
    $count = $argv["count"];
    $where = $argv['where'];
    $whereMsg = $argv['message'];
    $pattern = $_POST['where'];

    # Definition der DB-Handler, Auswahl der Datenbank
    #
    # grant select on rt3.* to 'rt3_user' identified by "rt3_password";
    #
    $aDB_handles = array( "EcoReality"   => "localhost;EcoRealityBrowse;EcoRealityBrowse",
    		   	  "Quotes" => "localhost;QuotesBrowser;resworBsetouQ",
    		   	  "PatternLanguage" => "localhost;PatternLanguageBrowser;");

    # Parameter zusammenkratzen, schamlos gestohlen von "DynamicArticle"-Extension
    while(list($index,$val)=each($aDB_handles)) {
      if ( "$db" == "$index" ) {
        $aParams = explode(";", $val);

        foreach($aParams as $parameter) {
              if( count( $aParams ) < 3 )
                      return "Error in DB_handler definition !";
              $host = trim($aParams[0]);
              $user = trim($aParams[1]);
              $pass = trim($aParams[2]);
        }
      }
    }


    if($where && !$pattern) {
	$output = '<form action="';
	$output .= $_SERVER['php_self'];
	$output .= '" method="post">';
	$output .= $whereMsg;
	$output .= '<input type="text" name="where" size="20"><input type="submit" name="Search" value="Search"></form>';
    } else {
	if($pattern && $where) {
		$input .= str_replace('%1', $pattern, $where);
	}
	$output = "";
	# --------------------------------------------
	$link = mysql_connect($host, $user, $pass);
	if ( !$link ) { return "<hr><b>Error while connecting to host \"$host\" !</b><hr>"; }

	mysql_select_db($db, $link);

	if(empty($_GET['start']))
                $start = 0;

        else
                $start = $_GET['start'];
	
        if($count) {
		$input .= ' LIMIT ' . $start . ',' . $count;
      		$output .= '<table border="0">';
		$pagingLinks = '<tr><td align="left">';
			if(($start - $count) >= 0) {
				$pagingLinks .= '<a href="';
				$pagingLinks .= $_SERVER['PHP_SELF'];
				$pagingLinks .= '?start=';
				$pagingLinks .= $start - $count;
				$pagingLinks .= '">previous ';
				$pagingLinks .= $count;
			}
		$pagingLinks .= '</a></td><td align="right"><a href="';
			$pagingLinks .= $_SERVER['PHP_SELF'];
			$pagingLinks .= '?start=';
			$pagingLinks .= $start +  $count;
			$pagingLinks .= '">next ';
			$pagingLinks .= $count;
		$pagingLinks .= '</a></td></tr>';
		$output .= $pagingLinks;
		$output .= '<tr><td colspan=2>';
	}

	$result = mysql_query ($input);

	# Tabellenkopf bauen
	$output .= '<table border=1 class="sortable">';
	$output .= "<tr>";

	$i = 0;
	while ($i < mysql_num_fields($result)) {
		$meta = mysql_fetch_field($result, $i);
		$output .= "<td><b>$meta->name</b></td>";
		$i++;
	}
	$output .= "</tr>";

	# Tabelle mit Inhalt fuellen
	while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
		$output .= "<tr>";
		foreach ($line as $col_value){
		$output .= '<td valign="top">';
		$output .= $col_value;
		$output .= '</td>';
	}
	$output .= "</tr>";
	}

	$output .= "</table>";
	if($count) {
		$output .= '</td></tr>';
		$output .= $pagingLinks;
		$output .= '</table>';
	}

	# Aufraeumen
	mysql_free_result($result);
	mysql_close($link);
    }
    $wgParser->disableCache();
    return $output;
}

function renderSQLInline( $input, $argv, &$parser ) {
    global $wgParser;

    $db = $argv["database"];

    # Definition der DB-Handler, Auswahl der Datenbank
    #
    # grant select on rt3.* to 'rt3_user' identified by "rt3_password";
    #
    $aDB_handles = array( "EcoReality"   => "localhost;EcoRealityBrowse;EcoRealityBrowse");

    # Parameter zusammenkratzen, schamlos gestohlen von "DynamicArticle"-Extension
    while(list($index,$val)=each($aDB_handles)) {
      if ( "$db" == "$index" ) {
        $aParams = explode(";", $val);

        foreach($aParams as $parameter) {
              if( count( $aParams ) < 3 )
                      return "Error in DB_handler definition !";
              $host = trim($aParams[0]);
              $user = trim($aParams[1]);
              $pass = trim($aParams[2]);
        }
      }
    }

    $output = "";
    # --------------------------------------------
    $link = mysql_connect($host, $user, $pass);
    if ( !$link ) { return "<hr><b>Error while connecting to host \"$host\" !</b><hr>"; }

    mysql_select_db($db, $link);

    $result = mysql_query ($input);

    while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
      foreach ($line as $col_value){
        $output .= "$col_value ";
      }
    }

    mysql_free_result($result);
    mysql_close($link);

    $wgParser->disableCache();
    return $output;
}
?>

error[edit]

Hello

i got error missing or invalid database? what is that.

i did like this.is it right? $wgExSql2WikiDatabases["orcl"] = array( "type"=> "oracle", "host" => "orcl", "dbname" => "wiki", "user" => "system", "password" => "123");


query <sql2wiki database"orcl"> select * from dual </sql2wiki>

Retrieved from "https://www.mediawiki.org/w/index.php?title=Extension_talk:SQL2Wiki/old&oldid=546977"