PHP SQLVIEW, a.k.a. SQLView

Users often have data in SQL databases and while they are keenly interested in the data in the tables, they sometimes have little motivation or ability to use command line interfaces or generate SQL commands to see or modify the data.

SQLView provides a user friendly interface for browsing a SQL database and modifying SQL tables using a web browser. This is stickly an application to give users access to view and modify their SQL tables. It is not a tool to help database administrators (other than perhaps getting users to do manage their own data).

Using a local configuration file to set default values, the user might be prompted for all information to connect to a database (type of database, host, userid, password etc.) or as little as one field.

SQLView is available at SourceForge.

SQLView allows one to walk through a table, viewing all rows. If the tables have a primary key and SQLView is so enabled, the users may modify any given column for a row, may add a row or even delete a row. Additionally, the user may export data from the table in a convenient comma separated value (CSV) format.

In addition to the expected "Add Record" and "Del" (delete), one convenient form is "Copy". In this case an existing row is editted and the user is expected to modify the primary key. Since the database is modified with an INSERT SQL statement, the primary key provided by the user must be unique and may not already exist.

Once connected to a database, the user may select which table to display or see a detailed description of the columns and definitions for a table.

Databases Supported

SQLView is a PHP application. It makes use of PEAR::DB for its database access and this means that most common databases should work. As of Dec 2003, MySQL, Postgres and Sybase have been tested, but as long as PEAR::DB has complete support for your type of database, SQLView should work without modification.

Note that version 2.8 no longer uses PEAR::DB since many sites do not install it. As of 2.8, only MySQL is supported, but support for others can be added with relative ease (see DBMySQL.php).

Configuration

SQLView reads a local PHP configuration file (named local_config.php) which sets a number of variables:

• Title of the prompting page
• Details about the database (host, type, userid etc.)
• Whether to enable update, insert or delete of rows
• Background colors for various tables
• Additional links in the footer

More complexity and control is provided by projects which are 'named' configurations which allow one installation of the application to provide many different views of the data. Read this for details on installation and configuration.

Security

SQLView will check that the SQL statement provided is a SELECT, however, malicious users might attempt to construct SQL statements that could modify or delete data unexpectedly. Here are a few (but hardly all) ideas how you might protect your data:

• Use a secure protocol (https) when using SQLView.
  
• Protect uses of SQLView with web server access controls like those described at the Apache site.
  
• Make sure the database userid being used has the minimal capabilities required. For instance, don't use a userid which can delete rows if you do not want to enable delete for a table.
  

Take the time to protect access to your databases. You have been warned.

Direct questions to: Terry Gliedt tpg@hps.com (Include 'SQLView' in the subject line so my SPAM filter lets your message through.) This is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation. See http://www.gnu.org/copyleft/gpl.html