<?php
/*
    OracleEditor.php
    $Revision: 1.20 $
    by Tim Strehle <tim@strehle.de>

    http://oracleeditor.sourceforge.net/

    OracleEditor.php is a standalone PHP script which allows you to browse
    your Oracle database tables and insert, update and delete rows in any
    table. It requires no installation and no configuration.

    I wrote it for situations where you quickly need to do some small data
    manipulation and don't have a better tool available. OracleEditor.php is
    free and Open Source.
    Feel free to contact me at tim@strehle.de with questions/comments.

    Disclaimer:
    Use this software at your own risk. I won't guarantee that it works, and
    I won't be held liable for any damage caused by it!

    Please make sure to protect this script with a .htaccess password or the
    like - never ever allow public access to it. Anyone capable of guessing
    your database login will be able to cause severe damage to your database.

    $Id: OracleEditor.php,v 1.20 2006/05/12 21:30:40 tim Exp $

    $Log: OracleEditor.php,v $
    Revision 1.20  2006/05/12 21:30:40  tim
    Allow slash and colon in service name for Oracle Instant Client connection
    syntax. Allow dollar sign in user name.
    Now works with php.ini setting "short_open_tag = Off".
    
    Revision 1.19  2005/06/28 09:11:54  tim
    For UTF-8 detection, use getenv('NLS_LANG') instead of $_SERVER[ 'NLS_LANG' ].

    Revision 1.18  2005/06/10 14:49:09  tim
    Added PHP version and required module check.
    Added "online help" via the HTML title attribute.
    Selecting a new table or modifying the SQL statement now cancels export mode.
    Make the browser scroll to the row to insert/update/delete row.
    When switching from popup to manual SQL entry mode, the current SELECT
    statement is used to pre-fill the SQL statement textarea.

    Revision 1.17  2004/11/17 11:04:22  tim
    Now working with the php.ini setting "magic_quotes_gpc = On" as well.
    Added accesskeys to buttons and links and automatically focusing form
    fields on page load for fast keyboard navigation.
    Added set size "1000".
    Displaying some Oracle-related environment variables at the bottom.

    Revision 1.16  2004/04/27 12:33:02  tim
    Beautified the displayed version string.

    Revision 1.15  2004/04/27 12:19:31  tim
    Added export format "HTML table".
    The table popup now shows other Oracle user's tables if the current user
    is allowed to access them.
    Use CVS revision number for the version number.

    Revision 1.14  2004/01/07 09:59:00  tim
    Allow dollar sign in table names.

    Revision 1.13  2003/11/28 13:40:39  tim
    Fixed empty columns not being exported in CSV format.

    Revision 1.12  2003/11/28 13:07:44  tim
    Added XML and CSV export.
    Session ID is now passed along with GET/POST requests, allowing you to
    run OracleEditor in multiple browser windows (which wasn't possible with
    the cookie-based session ID).
    Fixed password validation bug, which filtered special characters from the
    password entered (you couldn't log in if your password was "$pecial").
    Added ocisetprefetch(), which might improve performance.

    Revision 1.11  2003/10/20 11:10:01  tim
    Set HTML character set to UTF-8 when the Oracle connection runs in UTF-8
    mode (as defined in the NLS_LANG environment variable).

    Revision 1.10  2003/10/10 09:24:39  tim
    Cosmetics - fixed "Insert new row" line missing one column.

    Revision 1.9  2003/09/26 11:31:02  tim
    Fixed rownumber INSERT bug.

    Revision 1.8  2003/09/24 08:38:30  tim
    Added row number display.

    Revision 1.7  2003/08/21 06:45:47  tim
    Added "Switch to manual SQL entry" option, allowing you to run arbitrary
    SQL including DML/DDL statements.

    Revision 1.6  2003/08/05 10:31:00  tim
    Display views as well.

    Revision 1.5  2003/05/26 09:45:17  tim
    Now rows from all tables can be edited, not only from tables which have a primary
    key defined. And the primary key need not be in the SELECT clause anymore to
    be able to edit rows.
    Debug mode logs all SQL statements in the PHP error_log.
    Fixed not moving to the first set after changes to the WHERE clause.


    Requirements:
    PHP 4 (version 4.1 or greater) with Session and OCI8 support.
    Works fine with the php.ini setting "register_globals = off",
    and regardless of the "magic_quotes_gpc" setting.
*/

// Don't write PHP warnings into HTML. Watch your PHP error_log file!

ini_set('display_errors', 0);

// Format version string

$version = trim(substr('$Revision: 1.20 $', 10, -1));

// Fix magic_quotes_gpc garbage

if (get_magic_quotes_gpc())
  { function stripslashes_deep($value)
      { return (is_array($value) ? array_map('stripslashes_deep', $value) : stripslashes($value));
      }

    $_REQUEST = array_map('stripslashes_deep', $_REQUEST);
  }

// To allow multiple independent OracleEditor sessions,
// propagate session ID in the URL instead of a cookie.

ini_set('session.use_cookies', '0');

// We'll add the session ID to URLs ourselves - disable trans_sid

ini_set('url_rewriter.tags', '');

// Initialize session ID

$sid = '';

if (isset($_REQUEST[ 'sid' ]))
  $sid = substr(trim(preg_replace('/[^a-f0-9]/', '', $_REQUEST[ 'sid' ])), 0, 13);

if ($sid == '')
  $sid = uniqid('');

// Start PHP session

session_id($sid);
session_name('OracleEditor');
session_start();

$setsizes = array( 10, 25, 50, 100, 1000 );

$exportformats = array(
    'xml'  => array( 'XML', 'text/xml' ),
    'csv'  => array( 'CSV', 'text/comma-separated-values' ),
    'html' => array( 'HTML table', 'text/html' ),
    );

if (! isset($_SESSION[ 'exportformat' ]))
  $_SESSION[ 'exportformat' ] = 'xml';

// Initialize database connection parameters

if ((! isset($_SESSION[ 'connection' ])) || isset($_REQUEST[ 'disconnect' ]))
  pof_blanksession();

if (isset($_REQUEST[ 'connection' ]))
  if (is_array($_REQUEST[ 'connection' ]))
    { pof_blanksession();

      if (isset($_REQUEST[ 'connection' ][ 'user' ]))
        $_SESSION[ 'connection' ][ 'user' ] = substr(trim(preg_replace('/[^a-zA-Z0-9$_-]/', '', $_REQUEST[ 'connection' ][ 'user' ])), 0, 30);

      if (isset($_REQUEST[ 'connection' ][ 'password' ]))
        $_SESSION[ 'connection' ][ 'password' ] = substr(trim($_REQUEST[ 'connection' ][ 'password' ]), 0, 30);

      if (isset($_REQUEST[ 'connection' ][ 'service' ]))
        $_SESSION[ 'connection' ][ 'service' ] = substr(trim(preg_replace('|[^a-zA-Z0-9:.() =/_-]|', '', $_REQUEST[ 'connection' ][ 'service' ])), 0, 2000);
    }

// Rather dumb character set detection:
// Try switching to UTF-8 automagically on stuff like "NLS_LANG=american_america.UTF8"

$charset = 'ISO-8859-1';

if (getenv('NLS_LANG'))
  if (strtoupper(substr(getenv('NLS_LANG'), -5)) == '.UTF8')
    $charset = 'UTF-8';

// Initialize debug mode

if (! isset($_SESSION[ 'debug' ])) $_SESSION[ 'debug' ] = false;
if (isset($_REQUEST[ 'debug' ])) $_SESSION[ 'debug' ] = ($_REQUEST[ 'debug' ] == 1);

// Initialize / drop DDL cache

if (! isset($_SESSION[ 'cache' ])) $_SESSION[ 'cache' ] = array();
if (isset($_REQUEST[ 'dropcache' ])) $_SESSION[ 'cache' ] = array();

// Initialize entry mode

if (! isset($_SESSION[ 'entrymode' ])) $_SESSION[ 'entrymode' ] = 'popups';

// Initialize SQL filter fields

if (! isset($_SESSION[ 'sql'     ])) $_SESSION[ 'sql'     ] = '';
if (! isset($_SESSION[ 'table'   ])) $_SESSION[ 'table'   ] = '';
if (! isset($_SESSION[ 'select'  ])) $_SESSION[ 'select'  ] = '*';
if (! isset($_SESSION[ 'where'   ])) $_SESSION[ 'where'   ] = '';
if (! isset($_SESSION[ 'set'     ])) $_SESSION[ 'set'     ] = 1;
if (! isset($_SESSION[ 'setsize' ])) $_SESSION[ 'setsize' ] = $setsizes[ 0 ];

if (isset($_REQUEST[ 'select' ])) $_SESSION[ 'select' ] = trim($_REQUEST[ 'select' ]);

// Initialize export mode

$exportmode = false;

if (isset($_REQUEST[ 'export' ]))
  $exportmode = true;

// Switch back from export mode

if ($exportmode)
  { // SQL input fields changed?

    $check_fields = array( 'sql', 'table', 'select', 'where' );

    foreach ($check_fields as $field)
      if (isset($_REQUEST[ $field ]))
        if ($_REQUEST[ $field ] != $_SESSION[ $field ])
          { $exportmode = false;
            break;
          }

    // History item selected?

    if (isset($_REQUEST[ 'history' ]))
      if ($_REQUEST[ 'history' ] != '')
        $exportmode = false;
  }

// Action + record set?

$action = '';

if (isset($_REQUEST[ 'action' ]))
  if (($_REQUEST[ 'action' ] == 'edit') || ($_REQUEST[ 'action' ] == 'delete'))
    $action = $_REQUEST[ 'action' ];

$actionrecord = false;

if ($action != '')
  if (isset($_REQUEST[ 'record' ]))
    if (is_array($_REQUEST[ 'record' ]))
      if (isset($_REQUEST[ 'record' ][ 'table' ]) && isset($_REQUEST[ 'record' ][ 'rowid' ]))
        $actionrecord = $_REQUEST[ 'record' ];

if (! is_array($actionrecord))
  $action = '';

// edit or delete cancelled?

if (isset($_REQUEST[ 'editcancel' ]) || isset($_REQUEST[ 'deletecancel' ]))
  { $action = '';
    $actionrecord = false;
  }

// set changed?

if (isset($_REQUEST[ 'set' ]))
  if ($_REQUEST[ 'set' ] != $_SESSION[ 'set' ])
    { $val = intval($_REQUEST[ 'set' ]);
      if ($val > 0)
        $_SESSION[ 'set' ] = $val;
    }

// setsize changed?

if (isset($_REQUEST[ 'setsize' ]))
  if ($_REQUEST[ 'setsize' ] != $_SESSION[ 'setsize' ])
    if (in_array($_REQUEST[ 'setsize' ], $setsizes))
      { $_SESSION[ 'setsize' ] = $_REQUEST[ 'setsize' ];
        $_SESSION[ 'set'     ] = 1;
      }

// empty column list means *

if ($_SESSION[ 'select' ] == '') $_SESSION[ 'select' ] = '*';

// entry mode changed?

if (isset($_REQUEST[ 'entrymode' ]))
  if (($_REQUEST[ 'entrymode' ] == 'popups') || ($_REQUEST[ 'entrymode' ] == 'manual'))
    { $_SESSION[ 'sql'    ] = '';

      // Switch from "popups" to "manual"? Prefill SQL statement...
      if (($_SESSION[ 'entrymode' ] == 'popups') && ($_REQUEST[ 'entrymode' ] == 'manual') && ($_SESSION[ 'table' ] != '') && ($_SESSION[ 'select' ] != ''))
        $_SESSION[ 'sql' ] = 'SELECT ' . $_SESSION[ 'select' ] . ' from ' . $_SESSION[ 'table' ] . ' ' . $_SESSION[ 'where' ];

      $_SESSION[ 'table'  ] = '';
      $_SESSION[ 'select' ] = '*';
      $_SESSION[ 'where'  ] = '';
      $_SESSION[ 'set'    ] = 1;

      $_SESSION[ 'entrymode' ] = $_REQUEST[ 'entrymode' ];
    }

// sql changed? (entrymode=manual)

if (isset($_REQUEST[ 'sql' ]))
  if ($_REQUEST[ 'sql' ] != $_SESSION[ 'sql' ])
    { $_SESSION[ 'sql' ] = trim($_REQUEST[ 'sql' ]);
      $_SESSION[ 'set' ] = 1;
    }

// where changed? (entrymode=popups)

if (isset($_REQUEST[ 'where' ]))
  if ($_REQUEST[ 'where' ] != $_SESSION[ 'where' ])
    { $_SESSION[ 'where' ] = trim($_REQUEST[ 'where' ]);
      $_SESSION[ 'set'   ] = 1;
    }

// table changed? (entrymode=popups)

if (isset($_REQUEST[ 'table' ]))
  if ($_REQUEST[ 'table' ] != $_SESSION[ 'table' ])
    { $newtable = substr(trim(preg_replace('/[^a-zA-Z0-9$#_.-]/', '', $_REQUEST[ 'table' ])), 0, 61);

      if ($newtable != $_SESSION[ 'table' ])
        { $_SESSION[ 'table'  ] = $newtable;
          $_SESSION[ 'select' ] = '*';
          $_SESSION[ 'where'  ] = '';
          $_SESSION[ 'set'    ] = 1;
        }

      // We need a way to set both table + where in HREFs
      if (isset($_REQUEST[ 'keepwhere' ]))
        $_SESSION[ 'where' ] = $_REQUEST[ 'keepwhere' ];
    }

// history item selected?

if (! isset($_SESSION[ 'history' ])) $_SESSION[ 'history' ] = array();

$dont_execute = false;

if (isset($_REQUEST[ 'history' ]))
  if ($_REQUEST[ 'history' ] != '')
    { $tmp = intval($_REQUEST[ 'history' ]);
      if ($tmp >= 0)
        if (isset($_SESSION[ 'history' ][ $tmp ]))
          { $_SESSION[ 'entrymode' ] = $_SESSION[ 'history' ][ $tmp ][ 'entrymode' ];
            $_SESSION[ 'set'       ] = $_SESSION[ 'history' ][ $tmp ][ 'set'     ];
            $_SESSION[ 'setsize'   ] = $_SESSION[ 'history' ][ $tmp ][ 'setsize' ];

            if ($_SESSION[ 'history' ][ $tmp ][ 'entrymode' ] == 'popups')
              { $_SESSION[ 'table'   ] = $_SESSION[ 'history' ][ $tmp ][ 'table'   ];
                $_SESSION[ 'select'  ] = $_SESSION[ 'history' ][ $tmp ][ 'select'  ];
                $_SESSION[ 'where'   ] = $_SESSION[ 'history' ][ $tmp ][ 'where'   ];
                $_SESSION[ 'sql'     ] = '';
              }
            else
              { $_SESSION[ 'sql'     ] = $_SESSION[ 'history' ][ $tmp ][ 'sql' ];
                $_SESSION[ 'table'   ] = '';
                $_SESSION[ 'select'  ] = '';
                $_SESSION[ 'where'   ] = '';
              }

            // Non-SELECT statements should only be shown, not automatically executed
            // when switching to them (to avoid unwanted DELETEs etc.)

            if ($_SESSION[ 'history' ][ $tmp ][ 'type' ] != 'SELECT')
              $dont_execute = true;
          }
    }

// Build main SQL statement

$main_sql = '';

if ((($_SESSION[ 'table' ] != '') || ($_SESSION[ 'sql' ] != '')) && (! $dont_execute))
  {    if ($_SESSION[ 'entrymode' ] == 'popups')
      { // Always select the ROWID - we're using this for "Actions" support instead of the primary key

        $main_sql = 'select ';

        // Prevent "ORA-00936: missing expression":
        //   "select *, ROWID" is incorrect, we have to use "select tablename.*, ROWID" instead

        if (trim($_SESSION[ 'select' ]) == '*')
          $main_sql .= $_SESSION[ 'table' ] . '.';

        $rowidsql = ', rowidtochar(ROWID) as ROWID_';

        $main_sql .= trim($_SESSION[ 'select' ] . $rowidsql . ' from ' . $_SESSION[ 'table' ] . ' ' . $_SESSION[ 'where' ]);
      }
    else
      $main_sql = $_SESSION[ 'sql' ];
  }

// Initialize connection

$conn = false;

if (($_SESSION[ 'connection' ][ 'user' ] != '') && ($_SESSION[ 'connection' ][ 'password' ] != ''))
  pof_connect();

// Do export?

$doexport = false;
$export_errormsg = '';

if (isset($_REQUEST[ 'export' ]))
  if (is_array($_REQUEST[ 'export' ]))
    if (isset($_REQUEST[ 'export' ][ 'doit' ]) && isset($_REQUEST[ 'export' ][ 'format' ]) && isset($_REQUEST[ 'export' ][ 'limit' ]))
      $doexport = true;

if ($doexport)
  { // Do the export
    // Exporting may take a while

    set_time_limit(0);

    // Initialize export settings

    $exportlimit = abs(intval($_REQUEST[ 'export' ][ 'limit' ]));

    $_SESSION[ 'exportformat' ] = $_REQUEST[ 'export' ][ 'format' ];

    if (! isset($exportformats[ $_SESSION[ 'exportformat' ] ]))
      $_SESSION[ 'exportformat' ] = 'xml';

    // Send Content-type header

    header(sprintf('Content-Type: %s; name="dbexport.%s"', $exportformats[ $_SESSION[ 'exportformat' ] ][ 1 ], $_SESSION[ 'exportformat' ]));
    header(sprintf('Content-disposition: attachment; filename="dbexport.%s"', $_SESSION[ 'exportformat' ]));

    // Loop through results

    $ok = false;

    $cursor = pof_opencursor($main_sql);

    if ($cursor)
      if (ocistatementtype($cursor) == 'SELECT')
        $ok = true;

    if ($ok)
      { // Get column list

        $columns = array();
        $numcols = ocinumcols($cursor);

        for ($j = 1; $j <= $numcols; $j++)
          if (ocicolumnname($cursor, $j) != 'ROWID_')
            $columns[ (ocicolumnname($cursor, $j)) ] = array(
                'type' => ocicolumntype($cursor, $j),
                'size' => ocicolumnsize($cursor, $j)
                );

        // Header

        if ($_SESSION[ 'exportformat' ] == 'xml')
          { echo sprintf('<' . '?xml version="1.0" encoding="%s"?' . '>', $charset) . "\n";
            echo "<!-- Generated by OracleEditor.php (http://oracleeditor.sourceforge.net/) -->\n";

            $userstr = $_SESSION[ 'connection' ][ 'user' ];
            if ($_SESSION[ 'connection' ][ 'service' ] != '')
              $userstr .= '@' . $_SESSION[ 'connection' ][ 'service' ];

            echo sprintf('<rowset exported="%s" user="%s" server="%s">', date('Y-m-d\TH:i:s'), $userstr, $_SERVER[ 'SERVER_NAME' ]) . "\n";
            echo sprintf("\t<sql>%s</sql>\n", htmlspecialchars($main_sql));

            // Column aliases: We can use column names as tag names only if
            // they're valid XML names - <count(MYFIELD)> won't work.

            $i = 0;
            foreach ($columns as $name => $column)
              { $i++;

                if (preg_match('/^[a-zA-Z][a-zA-Z0-9_-]*$/', $name) == 0)
                  $columns[ $name ][ 'alias' ] = 'ALIAS' . $i;
              }

            echo "\t<columns>\n";
            foreach ($columns as $name => $column)
              echo sprintf("\t\t" . '<column name="%s" type="%s" size="%s"%s/>' . "\n",
                htmlspecialchars($name),
                $column[ 'type' ],
                $column[ 'size' ],
                (isset($column[ 'alias' ]) ? ' alias="' . $column[ 'alias' ] . '"' : '')
                );
            echo "\t</columns>\n";
          }
        elseif ($_SESSION[ 'exportformat' ] == 'csv')
          { $first = true;

            foreach ($columns as $name => $column)
              if ($name != 'ROWID_')
                { if (! $first) echo ', ';
                  echo sprintf('"%s"', str_replace('"', '""', $name));
                  $first = false;
                }

            echo "\n";
          }
        elseif ($_SESSION[ 'exportformat' ] == 'html')
          { ?>

            <html>
            <head>
            <meta http-equiv="content-type" content="text/html; charset=<?php echo $charset; ?>">
            <meta name="date" content="<?php echo date('Y-m-d\TH:i:s'); ?>">
            <meta name="generator" content="OracleEditor.php (http://oracleeditor.sourceforge.net/)">
            <title>Exported Oracle data (by OracleEditor.php)</title>
            </head>
            <body>

            <h1>Exported Oracle data</h1>

            <?php
            $userstr = $_SESSION[ 'connection' ][ 'user' ];
            if ($_SESSION[ 'connection' ][ 'service' ] != '')
              $userstr .= '@' . $_SESSION[ 'connection' ][ 'service' ];
            ?>

            <p>The Oracle user <em><?php echo htmlspecialchars($userstr); ?></em> exported this data on <em><?php echo date('r'); ?></em>
            by running the following SQL statement in <a href="http://<?php echo $_SERVER[ 'HTTP_HOST' ]; ?><?php echo $_SERVER[ 'PHP_SELF' ]; ?>">a local copy of OracleEditor.php</a> on <em><?php echo $_SERVER[ 'SERVER_NAME' ]; ?></em>:<br />
            <pre><?php echo htmlspecialchars($main_sql); ?></pre></p>

            <table border="1">
            <tr>

            <?php

            foreach ($columns as $name => $column)
              echo sprintf('<th>%s<br />(%s, %s)</th>' . "\n",
                htmlspecialchars($name),
                $column[ 'type' ],
                $column[ 'size' ]
                );

            ?>

            </tr>

            <?php
          }

        // Rows

        $i = 1;

        while (true)
          { if (! ocifetchinto($cursor, $row, OCI_ASSOC | OCI_RETURN_LOBS))
              break;

            if ($_SESSION[ 'exportformat' ] == 'xml')
              { echo sprintf("\t<row%s>\n", (isset($row[ 'ROWID_' ]) ? (' id="' . htmlspecialchars($row[ 'ROWID_' ]) . '"') : ''));

                foreach ($row as $fieldname => $value)
                  if ($fieldname != 'ROWID_')
                    echo sprintf("\t\t<%1\$s>%2\$s</%1\$s>\n",
                        (isset($columns[ $fieldname ][ 'alias' ]) ? $columns[ $fieldname ][ 'alias' ] : $fieldname ),
                        htmlspecialchars($value));

                echo "\t</row>\n";
              }
            elseif ($_SESSION[ 'exportformat' ] == 'csv')
              { $first = true;

                foreach ($columns as $fieldname => $column)
                  if ($fieldname != 'ROWID_')
                    { if (! $first) echo ', ';
                      if (isset($row[ $fieldname ]))
                        echo sprintf('"%s"', str_replace('"', '""', $row[ $fieldname ]));
                      else
                        echo '""';
                      $first = false;
                    }

                echo "\n";
              }
            elseif ($_SESSION[ 'exportformat' ] == 'html')
              { echo "<tr>\n";

                foreach ($columns as $fieldname => $column)
                  if ($fieldname != 'ROWID_')
                    { echo "\t<td>";
                      if (isset($row[ $fieldname ]))
                        echo htmlspecialchars($row[ $fieldname ]);
                      echo "</td>\n";
                    }

                echo "</tr>\n";
              }

            if (($exportlimit > 0) && ($exportlimit <= ++$i))
              break;
          }

        // Footer

        if ($_SESSION[ 'exportformat' ] == 'xml')
          { echo "</rowset>\n";
          }
        elseif ($_SESSION[ 'exportformat' ] == 'html')
          { ?>

            </table>
            <p>HTML generated by <a href="http://oracleeditor.sourceforge.net/">OracleEditor.php</a> <?php echo $version; ?> &copy; 2006 by <a href="http://tim.digicol.de/">Tim Strehle</a> &lt;<a href="mailto:tim@strehle.de">tim@strehle.de</a>&gt;</p>
            </body>
            </html>

            <?php
          }

        pof_closecursor($cursor);

        session_write_close();
        exit;
      }
    else
      $export_errormsg = 'Unable to export';
  }


function pof_blanksession()
{ global $setsizes;

  $_SESSION[ 'connection' ] = array(
        'user'     => '',
        'password' => '',
        'service'  => ''
        );

  $_SESSION[ 'cache'   ] = array();
  $_SESSION[ 'debug'   ] = false;
  $_SESSION[ 'sql'     ] = '';
  $_SESSION[ 'table'   ] = '';
  $_SESSION[ 'select'  ] = '*';
  $_SESSION[ 'where'   ] = '';
  $_SESSION[ 'set'     ] = 1;
  $_SESSION[ 'setsize' ] = $setsizes[ 0 ];
  $_SESSION[ 'history' ] = array();
}


function pof_sqlline($msg, $error = false)
{ if ($error)
    $class = 'sqllineerr';
  else
    $class = 'sqlline';

  $html = '<table class="' . $class . '"><tr><td>' . htmlspecialchars($msg) . '</td></tr></table>' . "\n";

  return $html;
}


function pof_connect()
{ global $conn;

  $conn = ocilogon($_SESSION[ 'connection' ][ 'user' ], $_SESSION[ 'connection' ][ 'password' ], $_SESSION[ 'connection' ][ 'service' ]);

  $err = ocierror();

  if (is_array($err))
    echo htmlspecialchars('Logon failed: ' . $err[ 'message' ]) . '<br />' . "\n";
}


function pof_disconnect()
{ global $conn;

  if ($conn)
    ocilogoff($conn);
}


function pof_opencursor($sql, $bind = false)
{ global $conn;

  $cursor = ociparse($conn, $sql);

  if (! $cursor)
    { $err = ocierror($conn);
      if (is_array($err))
        echo pof_sqlline('Parse failed: ' . $err[ 'message' ], true);
    }
  else
    { // This might improve performance?
      ocisetprefetch($cursor, $_SESSION[ 'setsize' ]);

      if (is_array($bind))
        foreach ($bind as $fieldname => $value)
          ocibindbyname($cursor, ':' . $fieldname, $bind[ $fieldname ], -1);

      $ok = ociexecute($cursor);

      if (! $ok)
        { $err = ocierror($cursor);

          if (is_array($err))
            echo pof_sqlline('Execute failed: ' . $err[ 'message' ], true);

          pof_closecursor($cursor);

          $cursor = false;
        }
    }

  return $cursor;
}


function pof_closecursor($cursor)
{ if ($cursor)
    ocifreestatement($cursor);
}


function pof_gettables()
{ if (! isset($_SESSION[ 'cache' ][ '_alltables' ]))
    { $_SESSION[ 'cache' ][ '_alltables' ] = array();

      $sql = sprintf(
        "select ' ' as OWNER, TABLE_NAME from USER_TABLES " .
        "union " .
        "select OWNER, TABLE_NAME from USER_TAB_PRIVS where PRIVILEGE = 'SELECT' and GRANTEE = '%1\$s' " .
        "order by OWNER, TABLE_NAME",
        strtoupper($_SESSION[ 'connection' ][ 'user' ])
        );

      if ($_SESSION[ 'debug' ]) error_log($sql);

      $cursor = pof_opencursor($sql);

      if ($cursor)
        { while (true)
            { if (! ocifetchinto($cursor, $row, OCI_ASSOC | OCI_RETURN_LOBS))
                break;

              if (trim($row[ 'OWNER' ]) == '')
                $_SESSION[ 'cache' ][ '_alltables' ][ ] = $row[ 'TABLE_NAME' ];
              else
                $_SESSION[ 'cache' ][ '_alltables' ][ ] = $row[ 'OWNER' ] . '.' . $row[ 'TABLE_NAME' ];
            }

          pof_closecursor($cursor);
        }
    }

  return $_SESSION[ 'cache' ][ '_alltables' ];
}


function pof_getviews()
{ if (! isset($_SESSION[ 'cache' ][ '_allviews' ]))
    { $_SESSION[ 'cache' ][ '_allviews' ] = array();
      $sql = 'select VIEW_NAME from USER_VIEWS order by VIEW_NAME';
      if ($_SESSION[ 'debug' ]) error_log($sql);

      $cursor = pof_opencursor($sql);

      if ($cursor)
        { while (true)
            { if (! ocifetchinto($cursor, $row, OCI_ASSOC | OCI_RETURN_LOBS))
                break;

              $_SESSION[ 'cache' ][ '_allviews' ][ ] = $row[ 'VIEW_NAME' ];
            }

          pof_closecursor($cursor);
        }
    }

  return $_SESSION[ 'cache' ][ '_allviews' ];
}


function pof_getpk($table)
{ if (! isset($_SESSION[ 'cache' ][ $table ])) $_SESSION[ 'cache' ][ $table ] = array();

  if (! isset($_SESSION[ 'cache' ][ $table ][ 'pk' ]))
    { $_SESSION[ 'cache' ][ $table ][ 'pk' ] = '';

      $sql = "select COLUMN_NAME from USER_CONS_COLUMNS col, USER_CONSTRAINTS con where con.TABLE_NAME=:TABLE_NAME and con.CONSTRAINT_TYPE='P' and col.CONSTRAINT_NAME=con.CONSTRAINT_NAME";
      $bind = array( 'TABLE_NAME' => $table );
      if ($_SESSION[ 'debug' ]) error_log($sql);

      $cursor = pof_opencursor($sql, $bind);

      if ($cursor)
        { if (ocifetchinto($cursor, $row, OCI_NUM))
            $_SESSION[ 'cache' ][ $table ][ 'pk' ] = $row[ 0 ];
          pof_closecursor($cursor);
        }
    }

  return $_SESSION[ 'cache' ][ $table ][ 'pk' ];
}


function pof_getcoldefs($table)
{ if (! isset($_SESSION[ 'cache' ][ $table ])) $_SESSION[ 'cache' ][ $table ] = array();

  if (! isset($_SESSION[ 'cache' ][ $table ][ 'coldefs' ]))
    { $_SESSION[ 'cache' ][ $table ][ 'coldefs' ] = array();

      $sql = "select COLUMN_NAME, NULLABLE, DATA_DEFAULT from USER_TAB_COLUMNS where TABLE_NAME=:TABLE_NAME";
      $bind = array( 'TABLE_NAME' => $table );
      if ($_SESSION[ 'debug' ]) error_log($sql);

      $cursor = pof_opencursor($sql, $bind);

      if ($cursor)
        { while (true)
            { if (! ocifetchinto($cursor, $row, OCI_ASSOC))
                break;

              $_SESSION[ 'cache' ][ $table ][ 'coldefs' ][ $row[ 'COLUMN_NAME' ] ] = array(
                'nullable' => true,
                'default'  => ''
                );

              if (isset($row[ 'NULLABLE' ]))
                if ($row[ 'NULLABLE' ] == 'N')
                  $_SESSION[ 'cache' ][ $table ][ 'coldefs' ][ $row[ 'COLUMN_NAME' ] ][ 'nullable' ] = false;

              if (isset($row[ 'DATA_DEFAULT' ]))
                $_SESSION[ 'cache' ][ $table ][ 'coldefs' ][ $row[ 'COLUMN_NAME' ] ][ 'default' ] = trim(strtr($row[ 'DATA_DEFAULT' ], '()', '  '));
            }

          pof_closecursor($cursor);
        }
    }

  return $_SESSION[ 'cache' ][ $table ][ 'coldefs' ];
}


function pof_getforeignkeys($table)
{ if (! isset($_SESSION[ 'cache' ][ $table ])) $_SESSION[ 'cache' ][ $table ] = array();

  if (! isset($_SESSION[ 'cache' ][ $table ][ 'constraints' ]))
    { $_SESSION[ 'cache' ][ $table ][ 'constraints' ] = array( 'from' => array(), 'to' => array() );

      // Find own + remote foreign key constraint names
      // XXX foreign tables might belong to a different user! take R_OWNER into account!

      $sql =
        "select CONSTRAINT_NAME, R_CONSTRAINT_NAME from USER_CONSTRAINTS where TABLE_NAME=:TABLE_NAME and CONSTRAINT_TYPE='R' and STATUS='ENABLED' " .
        "union " .
        "select CONSTRAINT_NAME, R_CONSTRAINT_NAME from USER_CONSTRAINTS where R_CONSTRAINT_NAME in " .
        "(select CONSTRAINT_NAME from USER_CONSTRAINTS where TABLE_NAME=:TABLE_NAME) ".
      &