Extension talk:SQL2Wiki/old

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


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

GRANT ALL ON yourDB.* to 'wikisqlext'@'wikihost' GRANT SELECT ON yourDB.* to 'wikisqlext'@'wikihost'
 * Dont't do
 * Instead do

Incompatible with Mediawiki-1.8.4?
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..

$meta = mysql_fetch_field($result, $i);
 * Update: it is the line
 * 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?
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)


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

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)

Love this extension!
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
Any ideas how to get number display automatically right adjusted? --HermannSchmitz 19:42, 15 May 2007 (UTC)

results are wrong
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)

no results returned?
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 " Error while connecting to host \"$host\" ! "; } mysql_select_db($db, $link); $result = mysql_query ($input);

I added:

if (!$result) { return " No Data Returned?! "; }

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

Enhancements
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 ... which takes the first record of the result and outputs it without a table:
 * Very useful for stuff like "Here are SELECT COUNT(*) FROM my_table records."
 * 1) 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: SELECT * FROM Quotes

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

Here's the code:

";   if($count) {       $output .= '  ';       $output .= $pagingLinks;       $output .= ' ';    }

# 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 " Error while connecting to host \"$host\" ! "; }

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

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)

";	if($count) {		$output .= ' ';		$output .= $pagingLinks;		$output .= ' ';	}

# 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 " Error while connecting to host \"$host\" ! "; }

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; } ?>