Return to Mysql.class.php CVS log

Up to [Development] / advokit-installer

- Fixed minor security issues relating to uninitialized variables.
- Enhanced bug reporting.  If a DB error occurs, a form is displayed.
  The user can choose to use it, or not.
- Change app maintainers to ak@voter2voter.org

<?
# ======================================================================
# AdvoKit -- a campaign managment tool
# Copyright (C) 2004  OrchidSuites, Inc. (info@orchidsuites.net)
# 
# This program is free software; you can redistribute it and/or
# modify it under the terms of the AFFERO GENERAL PUBLIC LICENSE
# as published by Affero, Inc.; either version 1
# of the License, or (at your option) any later version.
# 
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# AFFERO GENERAL PUBLIC LICENSE for more details.
# 
# You should have received a copy of the AFFERO GENERAL PUBLIC LICENSE
# along with this program; if not, write to Affero, Inc. at
# 510 Third Street - Suite 225, San Francisco, CA 94107, USA
# or visit <http://www.affero.org>.
# ======================================================================

# ======================================================================
# MySQL implementation of Turban database abstraction functions
# ======================================================================

# ======================================================================
# Metadata object for result sets
# ======================================================================
class MysqlMetaData extends MetaData
{
    #....................................................... MysqlMetaData
    #
    # Constructor.  Pass in object returned by mysql_fetch_field().
    #
    function MysqlMetaData( $fieldinfo )
    {
        $this->longest = $fieldinfo->max_length;
        $this->type    = $fieldinfo->type;
        $this->pk      = $fieldinfo->primary_key ? true : false;
        $this->uk      = $fieldinfo->unique_key ? true : false;
        $this->mk      = $fieldinfo->multiple_key ? true : false;
    }
}

# ======================================================================
# MySQL DB implementation
# ======================================================================
class Mysql
{
    var $host;             # Database host (defaults to localhost)
    var $port;             # Database port (defaults to 3306)
    var $user;             # Database user
    var $pass;             # Database user's password
    var $name;             # Database name (i.e. which database?)
    var $connection;       # Connection object
    var $meta;             # Meta data from last successful select
    var $logger;           # Logger object to record interesting information
    var $errorreported;    # Has an error been reported?
    var $transactions;     # Are we using database transactions?
    var $transbegun;       # Has a transaction been started?

    #.................................................. Mysql
    #
    # Create an instance of Mysql, then establish
    # a connection.
    #
    function Mysql( &$config, &$logger )
    {
        $this->errorreported = false;
        $this->transbegun    = false;

        $this->logger =& $logger;

        $this->host = $config->get( "db_host" );
        $this->port = $config->get( "db_port" );
        $this->user = $config->get( "db_user" );
        $this->pass = $config->get( "db_pass" );
        $this->name = $config->get( "db_name" );

        $this->connect();

        $this->transactions = $config->get( "db_use_transactions" );

        if( $this->transactions )
        {
            if( $config->get( "db_default_til" ) )
            {
                $this->set_til( $config->get( "db_default_til" ) );
            }
            else
            {
                $this->set_til( "SERIALIZABLE" );
            }
            if( $config->get( "db_autocommit" ) )
            {
                $this->turn_on_autocommit();
            }
            else
            {
                $this->turn_off_autocommit();
            }
        }
    }

    #.................................................. report_error
    #
    # Emit HTML for a javascript alert containing
    # the database error.
    #
    # If you somehow use this code other than on a web server,
    # you'll probably want to change or remove this.
    #
    function report_error( $function, $code, $message, $sql=NULL )
    {
        if( ! $this->errorreported )
        {
            echo <<< EOS
        <script language="javascript">
            alert( "Database Error $code in $function: $message.\\nPlease report this error to the code maintainers.  In your report, please include a brief description of what you were doing prior to the error." );
        </script>
EOS;
            $_SESSION["db_error_code"] = $code;
            $_SESSION["db_error_msg"] = $message;
            $_SESSION["db_error_sql"] = $sql;
            $this->errorreported = true;
        }
        $this->logger->error( __FILE__.", $function", "#$code: $message" );
        $this->logger->error( __FILE__.", $function", "SQL: $sql" );
    }
    
    #....................................................... err
    #
    # Helper function to reduce code size.
    #
    function err( $lineno, $sql=NULL )
    {
        $this->report_error( "Mysql.class.php, line $lineno", mysql_errno( $this->conn ), mysql_error( $this->conn ), $sql );
    }
    
    #----------------------------------------------------------------------
    # API implementation
    #----------------------------------------------------------------------
    
    #....................................................... auto_sql_escape
    #
    # Depending on the environment, such as webserver configuration
    # return the escaped version of a given string. For example, 
    # "John's" becomes "John''s".
    #
    # This function exists because some webservers SQL-escape all
    # request parameters, in which case you don't want to do it again.
    #
    # You should use this function for any data received in the
    # request that might need quoting.  For data you generate
    # within the application, use sql_escape().
    #
    function auto_sql_escape( $sql )
    {
        if( ! ini_get( "magic_quotes_gpc" ) )
        {
            if( function_exists( "mysql_real_escape_string" ) )
            {
                return mysql_real_escape_string( $sql );
            }
            elseif( function_exists( "mysql_escape_string" ) )
            {
                return mysql_escape_string( $sql );
            }
            die( "Missing MySQL functions!  Unable to continue!" );
        }
        return $sql;
    }
    
    #....................................................... sql_escape
    #
    # Given a string, returns the escaped version of the
    # string.  For example, "John's" becomes "John''s".
    #
    # You should use this function for any data you generate
    # within the application that might need quoting.  For data
    # received in the request, use auto_sql_escape().
    #
    function sql_escape( $sql )
    {
        if( function_exists( "mysql_real_escape_string" ) )
        {
            return mysql_real_escape_string( $sql );
        }
        elseif( function_exists( "mysql_escape_string" ) )
        {
            return mysql_escape_string( $sql );
        }
        die( "Missing MySQL functions!  Unable to continue!" );
    }
    
    #....................................................... connect
    #
    # Connect to the database specified by the configuration
    # object passed to the constructor.  The $target argument
    # is for databases (such as oracle) that require extra
    # information in order to establish a connection.
    #
    # The connection handle is kept in an instance variable
    # that is referenced by other functions.  We refer to
    # this as "the current connection".
    #
    # Returns nothing.  
    #
    function connect( $target=NULL )
    {
        $host = $this->host ? $this->host : "localhost";
        $port = $this->port ? $this->port : "3306";

        $this->conn = mysql_connect( "$host:$port", $this->user, $this->pass );
        if( ! $this->conn )
        {
            $this->err( __LINE__ );
        }
        else if( ! mysql_select_db( $this->name, $this->conn ) )
        {
            $this->err( __LINE__ );
        }
        else
        {
            $this->logger->debug( __FILE__, "Established connection to db '".$this->name."' on ".$this->host.":".$this->port );
        }
    }
    
    #....................................................... disconnect
    #
    # Frees any resources associated with the current
    # connection, including the connection itself.
    #
    # Returns nothing.
    #
    function disconnect()
    {
        if( ! mysql_close( $this->conn ) )
        {
            $this->err( __LINE__ );
        }
        unset( $this->conn );
    }
    
    #....................................................... begin
    #
    # Begins a transaction.  Theoretically, calls to execute 
    # are not committed until you call commit.
    #
    # Note that some databases may commit your changes for you,
    # depending on what intervening calls to execute() you
    # make.  (e.g. execute( "commit" );
    #
    # Returns nothing.
    #
    function begin()
    {
        if( $this->transactions && ! $this->transbegun )
        {
            if( ! mysql_query( "set session transaction isolation level ".$this->til, $this->conn ) )
            {
                $this->err( __LINE__ );
            }
            else
            {
                if( ! mysql_query( "begin", $this->conn ) )
                {
                    $this->err( __LINE__ );
                }
                else
                {
                    $this->transbegun = true;
                }
            }
        }
    }
    
    #....................................................... commit
    #
    # Commits a transaction.  After commit(), all changes
    # since the last call to begin() (without an intervening
    # call to rollback() or commit()) are made permanent.
    #
    # Note that some databases may have committed some of
    # your changes already, e.g. if you called something like
    # execute( "commit" ), or if the database doesn't support
    # transactions.
    #
    # Returns nothing.
    #
    function commit()
    {
        if( $this->transactions && $this->transbegun )
        {
            if( ! mysql_query( "commit", $this->conn ) )
            {
                $this->err( __LINE__ );
            }
            else
            {
                $this->transbegun = false;
            }
        }
    }
    
    #....................................................... rollback
    #
    # Rolls back a transaction.  After rollback(), all changes
    # since the last call to begin() (without an intervening
    # call to rollback() or commit()) are forgotten.
    #
    # Note that some databases may have committed some of
    # your changes already, e.g. if you called something like
    # execute( "commit" ), or if the database doesn't support
    # transactions.
    #
    # Returns nothing.
    #
    function rollback()
    {
        if( $this->transactions && $this->transbegun )
        {
            if( ! mysql_query( "rollback", $this->conn ) )
            {
                $this->err( __LINE__ );
            }
            else
            {
                $this->transbegun = false;
            }
        }
    }
    
    #....................................................... set_til
    #
    # Sets transaction isolation level.  Your choices are:
    #  "READ-UNCOMMITTED"
    #  "READ-COMMITTED"
    #  "REPEATABLE-READ"
    #  "SERIALIZABLE"
    #
    # If you never set this, then the transaction isolation
    # level defaults to whatever was specified in the Configuration
    # object that was passed to the constructor.  If none was
    # specified, then the default is SERIALIZABLE.
    #
    # Returns nothing.
    #
    function set_til( $til )
    {
        if( $this->transactions )
        {
            $uppercasetil = strtoupper( $til );
        
            if(  "READ-UNCOMMITTED" != $uppercasetil
              && "READ-COMMITTED"   != $uppercasetil
              && "REPEATABLE-READ"  != $uppercasetil
              && "SERIALIZABLE"     != $uppercasetil
              )
            {
                $this->err( __LINE__ );
            }
            else
            {
                $this->til = $til;
            }
        }
    }
    
    #....................................................... turn_on_autocommit
    #
    # Turns autocommit on.  After this function returns,
    # subsequent SQL statements will take effect immediately.
    #
    # Returns nothing.
    #
    function turn_on_autocommit()
    {
        if( $this->transactions )
        {
            if( ! mysql_query( "set autocommit=1", $this->conn ) )
            {
                $this->err( __LINE__ );
            }
        }
    }
    
    #....................................................... turn_off_autocommit
    #
    # Turns autocommit off.  If the database supports transactions,
    # then after calling this function, you must call commit()
    # to make any changes take effect.
    #
    # Returns nothing.
    #
    function turn_off_autocommit()
    {
        if( $this->transactions )
        {
            if( ! mysql_query( "set autocommit=0", $this->conn ) )
            {
                $this->err( __LINE__ );
            }
        }
    }
    
    #....................................................... column_names
    #
    # Returns a reference to a numeric array (an array indexed
    # by numerals) of column names for the given table.
    #
    function &column_names( $table )
    {
        $handle = mysql_list_fields( $this->name, $table, $this->conn );
        $count = mysql_num_fields( $handle );
        if( ! $handle )
        {
            $this->err( __LINE__ );
            return NULL;
        }
        for( $i = 0; $i < $count; $i++ )
        {
            $colname = mysql_field_name( $handle, $i );
            if( mysql_errno( $this->conn ) )
            {
                $this->err( __LINE__ );
                return NULL;
            }
            $colnames[$i] = $colname;
        }
        return $colnames;
    }

    #....................................................... column_types
    #
    # Returns a reference to an associative array (an array indexed
    # by column names) of column types for the given table.
    #
    function &column_types( $table )
    {
        $handle = mysql_list_fields( $this->name, $table, $this->conn );
        $count = mysql_num_fields( $handle );
        if( ! $handle )
        {
            $this->err( __LINE__ );
            return NULL;
        }
        $coltypes = array();
        for( $i = 0; $i < $count; $i++ )
        {
            $colname = mysql_field_name( $handle, $i );
            if( mysql_errno( $this->conn ) )
            {
                $this->err( __LINE__ );
                return NULL;
            }
            $coltypes[$colname] = mysql_field_type( $handle, $i );
            if( mysql_errno( $this->conn ) )
            {
                $this->err( __LINE__ );
                return NULL;
            }
        }
        return $coltypes;
    }

    #....................................................... column_sizes
    #
    # Returns a reference to an associative array (an array indexed
    # by column names) of column sizes for the given table.
    #
    function &column_sizes( $table )
    {
        $handle = mysql_list_fields( $this->name, $table, $this->conn );
        $count = mysql_num_fields( $handle );
        if( ! $handle )
        {
            $this->err( __LINE__ );
            return NULL;
        }
        $colsizes = array();
        for( $i = 0; $i < $count; $i++ )
        {
            $colname = mysql_field_name( $handle, $i );
            if( mysql_errno( $this->conn ) )
            {
                $this->err( __LINE__ );
                return NULL;
            }
            $colsizes[$colname] = mysql_field_len( $handle, $i );
            if( mysql_errno( $this->conn ) )
            {
                $this->err( __LINE__ );
                return NULL;
            }
        }
        return $colsizes;
    }

    #....................................................... execute
    #
    # Execute the specified generic sql on the current
    # connection.  
    # 
    # If you use execute() to do a SELECT, only the NUMBER
    # of selected rows will be returned.  Also, a subsequent call
    # to last_select_columns() will NOT get you the column
    # names in the result from such a select.  You probably
    # should use select_all() or select_one() instead.
    #
    # Returns an integer representing how many rows were affected.
    #
    function execute( $sql )
    {
        $numrows = 0;
        $this->logger->debug( __FILE__, "Executing SQL: '".$sql."'" );
        $handle = mysql_query( $sql, $this->conn );
        if( ! $handle )
        {
            $this->err( __LINE__, $sql );
            return 0;
        }
        if( preg_match( "/^\s*select/i", $sql ) )
        {
            $numrows = mysql_num_rows( $this->conn );
            if( mysql_errno( $this->conn ) )
            {
                $this->err( __LINE__ );
            }
        }
        elseif(  preg_match( "/^\s*insert/i", $sql )
              || preg_match( "/^\s*update/i", $sql )
              || preg_match( "/^\s*delete/i", $sql )
              )
        {
            $numrows = mysql_affected_rows( $this->conn );
            if( mysql_errno( $this->conn ) )
            {
                $this->err( __LINE__ );
            }
        }
        return $numrows;
    }
    
    #....................................................... last_insert_id
    #
    # Returns the numeric id generated by the previous INSERT
    # of a row containing an AUTO_INCREMENT PRIMARY KEY column.
    #
    # Returns 0 if the previous statement was not an insert,
    # or if the result was indeterminate (e.g. the database
    # can't tell you this information).
    #
    function last_insert_id()
    {
        $last_id = mysql_insert_id( $this->conn );
        if( mysql_errno( $this->conn ) )
        {
            $this->err( __LINE__ );
        }
        return $last_id;
    }
    
    #....................................................... select
    #
    # Execute $sql in the current connection.  Unlike
    # select_one and select_all, this function returns 
    # a resource id which you can use to fetch result rows
    # and result metadata.
    #
    # If you have aliased a column in a query, then
    # you must use the alias to access that cell in the
    # associative array.
    #
    function select( $sql )
    {
        if( ! preg_match( "/^\s*select/i", $sql ) )
        {
            $this->report_error( "mysql.php, line: ".__LINE__, 0, "Attempt to use select to execute non-SELECT statement '$sql'" );
            return NULL;
        }
        $results = mysql_query( $sql, $this->conn );
        if( mysql_errno( $this->conn ) )
        {
            $this->err( __LINE__, $sql );
            return NULL;
        }
        return $results;
    }

    #....................................................... fetch_array
    #
    # Returns an associative array representing the results
    # of the query associated with the specified resource id.
    #
    # If you have aliased a column in a query, then
    # you must use the alias to access that cell in the
    # associative array.
    #
    function &fetch_array( $result )
    {
        $row = mysql_fetch_array( $result, MYSQL_ASSOC );
        if( mysql_errno( $this->conn ) )
        {
            $this->err( __LINE__ );
            return NULL;
        }
        return $row;
    }

    #....................................................... fetch_row
    #
    # Returns a numeric array representing the results
    # of the query associated with the specified resource id.
    #
    # This may not be much faster than fetch_array().
    #
    function &fetch_row( $result )
    {
        $row = mysql_fetch_row( $result );
        if( mysql_errno( $this->conn ) )
        {
            $this->err( __LINE__ );
            return NULL;
        }
        return $row;
    }

    #....................................................... fetch_object
    #
    # Returns an object whose properties match the column
    # names of the next row in the results.
    #
    # This is like fetch_array with some syntactic sugar.
    # Instead of writing:
    #   $row["user_id"]
    # You'd write:
    #   $row->user_id
    #
    function &fetch_object( $result )
    {
        $obj = mysql_fetch_object( $result );
        if( mysql_errno( $this->conn ) )
        {
            $this->err( __LINE__ );
            return NULL;
        }
        return $obj;
    }

    #....................................................... select_all
    #
    # Execute $sql in the current connection.  Returns a
    # reference to a numeric array whose cells contain
    # associative arrays indexed by column name.  
    # ALL result rows are included.
    #
    # If you have aliased a column in a query, then
    # you must use the alias to access that cell in the
    # associative array.
    #
    # To get metadata on the select, use last_select_meta().
    #
    function &select_all( $sql, $meta = false )
    {
        $this->meta = NULL;
        if( ! preg_match( "/^\s*select/i", $sql ) )
        {
            $this->report_error( "mysql.php, line: ".__LINE__, 0, "Attempt to use select_all to execute non-SELECT statement '$sql'" );
            return NULL;
        }
        $this->logger->debug( __FILE__, "Executing SQL select: '".$sql."'" );
        $handle = mysql_query( $sql, $this->conn );
        if( ! $handle )
        {
            $this->err( __LINE__, $sql );
            return NULL;
        }
        $index = 0;
        while( $row = mysql_fetch_array( $handle, MYSQL_ASSOC ) )
        {
            if( mysql_errno( $this->conn ) )
            {
                $this->err( __LINE__ );
                $this->meta = NULL;
                return NULL;
            }
            for( $i = 0; $i < mysql_num_fields( $handle ); $i++ )
            {
                $ofield = mysql_fetch_field( $handle, $i );
                if( mysql_errno( $this->conn ) )
                {
                    $this->err( __LINE__ );
                    $this->meta = NULL;
                    return NULL;
                }
                $this->meta[$index][$ofield->name] = new MysqlMetaData( $ofield );
            }
            $resultset[$index++] = $row;
        }
        return $resultset;
    }
    
    #....................................................... select_range
    #
    # Execute $sql in the current connection.  Returns a
    # reference to a numeric array whose cells contain
    # associative arrays indexed by column name.  
    # The $index parameter specifies the 0-based index
    # representing the first record to be included.
    # The $count parameter specifies the maximum number
    # of rows to return, starting at $index.
    #
    # If you have aliased a column in a query, then
    # you must use the alias to access that cell in the
    # associative array.
    #
    # To get metadata on the select, use last_select_meta().
    #
    function &select_range( $sql, $index = 0, $count=1, $meta = false )
    {
        $this->meta = NULL;
        if( ! preg_match( "/^\s*select/i", $sql ) )
        {
            $this->report_error( "mysql.php, line: ".__LINE__, 0, "Attempt to use select_all to execute non-SELECT statement '$sql'" );
            return NULL;
        }
        $sql = "$sql limit $index,$count";
        $this->logger->debug( __FILE__, "Executing SQL select: '".$sql."'" );
        $handle = mysql_query( $sql, $this->conn );
        if( ! $handle )
        {
            $this->err( __LINE__, $sql );
            return NULL;
        }
        $index = 0;
        while( $row = mysql_fetch_array( $handle, MYSQL_ASSOC ) )
        {
            if( mysql_errno( $this->conn ) )
            {
                $this->err( __LINE__ );
                $this->meta = NULL;
                return NULL;
            }
            for( $i = 0; $i < mysql_num_fields( $handle ); $i++ )
            {
                $ofield = mysql_fetch_field( $handle, $i );
                if( mysql_errno( $this->conn ) )
                {
                    $this->err( __LINE__ );
                    $this->meta = NULL;
                    return NULL;
                }
                $this->meta[$index][$ofield->name] = new MysqlMetaData( $ofield );
            }
            $resultset[$index++] = $row;
        }
        return $resultset;
    }

    #....................................................... select_one
    #
    # Execute $sql in the current connection.  Returns a
    # reference to associative array indexed by column name, or
    # false if there were no results.
    #
    # If you have aliased a column in a query, then
    # you must use the alias to access that cell in the
    # associative array.
    #
    # To get metadata on the select, use last_select_meta().
    #
    function &select_one( $sql, $meta = false )
    {
        $this->meta = NULL;
        if( ! preg_match( "/^\s*select/i", $sql ) )
        {
            $this->report_error( "Mysql.class.php, line: ".__LINE__, 0, "Attempt to use select_all to execute non-SELECT statement '$sql'" );
            return NULL;
        }
        if( ! preg_match( "/limit\s+\d\s*$/i", $sql ) )
        {
            $sql .= " limit 1";
        }
        $this->logger->debug( __FILE__, "Executing SQL select: '".$sql."'" );
        $handle = mysql_query( $sql, $this->conn );
        if( ! $handle )
        {
            $this->err( __LINE__, $sql );
            return NULL;
        }
        $row = mysql_fetch_array( $handle, MYSQL_ASSOC );
        if( $meta )
        {
            for( $i = 0; $i < mysql_num_fields( $handle ); $i++ )
            {
                $ofield = mysql_fetch_field( $handle, $i );
                if( mysql_errno( $this->conn ) )
                {
                    $this->err( __LINE__ );
                    $this->meta = NULL;
                    return NULL;
                }
                $this->meta[$ofield->name] = new MysqlMetaData( $ofield );
            }
        }
        return $row;
    }

    #....................................................... last_select_meta
    #
    # If the last select was a select_one, then return a reference
    # to an associative array containing a metadata object for each
    # selected column.  The object has the following properties:
    # 
    # size   - size of the data
    # type   - type of the data
    # pk     - true if the column is a primary key, else false
    # uk     - true if the column is a unique key, else false
    # mk     - true if the column is a non-unique key, else false
    #
    # If a select_all() was last performed, then a reference to
    # a numeric array is returned.  In this case, each cell contains
    # an array as described above.
    #
    # Results are undefined if the last select was not select_one
    # or select_all.
    #
    function &last_select_meta()
    {
        return $this->meta;
    }

    #....................................................... last_error
    #
    # Returns a two-element numeric array containing
    #   'code'    - the last error number or code
    #   'message' - text associated with 'code'
    #
    # If no error occurred, returns false.
    #
    function last_error()
    {
        $errors[0] = mysql_errno( $this->conn );
        if( !$errors[0] )
        {
            $errors[1] = "";
            return NULL;
        }
        $errors[1] = mysql_error( $this->conn );
        return $errors;
    }

    #....................................................... affected_rows
    #
    # Returns a the number of rows affected by the last
    # insert, delete, or update.
    #
    function affected_rows()
    {
        return mysql_affected_rows();
    }
}

?>