ImproveSqlPlugin

output of 'svn diff' and a .tgz are attached.

Bind Parameters

=%<nop>SQL{"query" ...parameter ...}%=

 | *Parameter* | *Description* | *Default* |
 | query | sql statement to be executed | | 
+| params | comma separated list of bind parameters for placeholders (?) in query | | 
 | database | connection to be used | first database in connection pool |

Access Controls

+
+---++ Securing database access+
+Underneath the database connections section in =[[%SCRIPTURLPATH{configure}%][configure]]=, there is a section to configure access control.
+Access can be restricted by wiki user / group, by a 'whitelist' of permitted queries, or both.  If no access control is
 specified for
+a database connection, then all access is allowed for that connection.
+
+Here is an example.  Note that the first element of the =queries= list is a regular expression, and
+the rest of the entries are literal string matches.
+
+< verbatim >
+[
+  {
+    'who' => 'KipLubliner',
+    'id' => 'mysql',
+    'queries' => [
+      'SELECT [^;]+',
+      'UPDATE TEAM SET TM_NAME = ? WHERE TMID = ?',
+      'UPDATE PLAYER SET PL_NICKNAME = ?, PL_FIRSTNAME = ?, PL_LASTNAME = ?, PL_TEAM = ? WHERE PLID = ?',
+      'INSERT INTO TEAM( TM_NAME ) VALUES (?)',
+      'INSERT INTO PLAYER( PL_NICKNAME, PL_FIRSTNAME, PL_LASTNAME, PL_TEAM ) VALUES (?, ?, ?, ?)'
+    ]
+  }
+];
+</ verbatim >
+
+See the integrated documentation on the configure screen for more details.

...

+# **PERL**
+# <h3>Access Control</h3>
+# Security Configuration.
+# This structure is an array of hashes, each of which contains a list of
+# queries that are allowed to be run.  Each item in the query list is evaluated as a regular expression
+# to see if the query matches, and also evaluated for exact string equality to see if the query matches.
+# For both of these checks, the input string is converted to ALL UPPERCASE. 
+# If a database connection has no items defined here, then all queries are permitted.
+# Either 'who' or 'queries' can be omitted, but not both.
+# <ul>
+# <li> id - same identifier as in the Databases configuration section.</li>
+# <li> who - User or Group name.</li>
+# <li> queries - List of queries.</li>
+# </ul>
+$Foswiki::cfg{SqlPlugin}{AccessControl} =
+[
+   {
+      id => 'foswiki',
+      who => 'WikiUserOrGroup',
+      queries => [
+                      'select * from table1',
+                      'update table1'
+                 ]
+   },
+];

Perl API to execute SQL (checking access controls)

+=begin TML
+---++ StaticMethod execute($dbconn, $query, @bindvals) -> $sth
+
+Executes the provided $query and returns a DBI Statement handle.  It is the 
+caller's responsibility to call $sth->finish after processing is complete.
+
+   $ $dbconn: The database connection defined in the configure screen.
+   $ $query: The SQL query to be run, possibly with placeholders (?).
+   $ @bindvals: An OPTIONAL list of values to be applied.  Only needed if $query has placeholders.
+
+Throws Error::Simple on errors.
+
+=cut

Discussion

I'm using this to dynamically generate SQL queries in SqlGridPlugin (not yet contributed).

-- KipLubliner - 17 May 2012

Excellent. Feel free the check in under Tasks.Item11870.

-- MichaelDaum - 17 May 2012
Topic revision: r2 - 17 May 2012, MichaelDaum
The copyright of the content on this website is held by the contributing authors, except where stated elsewhere. See Copyright Statement. Creative Commons License    Legal Imprint    Privacy Policy