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

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.

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 2002, only MySQL and Sybase have been tested, but as long as PEAR::DB has complete support for your type of database (Oracle, Postgres, DB2 etc), SQLView should work without modification.

SQLView is available at SourceForge.

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

Since SQLView is coded in PHP, the local_config.php can modifiy default values for background colors or database details based on parameters passed to the initial program (e.g. .../sqlview/?what2show=dev).

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. Your final line of defense is to 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 need to enable delete for a table. You have been warned.