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