Rewriting MySQL to MySQLi


#1

Hi, I am rewriting a website from MySQL to MySQLi. I’m almost done, but there were some problems with the mysqli_query () and mysqli_real_escape_string () functions. Here I send my code in and the error messages displayed. Thanks in advance for help!

<?php
/**
 * Opens a SQL connection.
 * @return    SQL connection.
 *         false if opening the connection fails.
 */
function sql_connect() {
    $server = mysqli_connect(getConfig('dbhost'), getConfig('dbuser'), getConfig('dbpass'), getConfig('dbname'));
    /*if($server) {
        if(@mysqli_select_db(getConfig('dbname'))) {
            return $server;
        }
    } */
    return $server;
    //log_system('SQL connect failed: ' . getConfig('dbuser') . '@' . getConfig('dbhost'), LOG_ERROR);
    //return false;
}

/**
 * Executes an SQL select statement.
 * @param $query    SQL query to execute.
 * @return        SQL result.
 * @deprecated
 */
function sql_select($query) {
    $query = sql_optimize($query);
    if(sql_connect() != false) {
        if(getConfig('sql')) {
            log_system($query, LOG_SQL);
        }
        return mysqli_query($server, $query);
    } else {
        log_system('SQL select failed: ' . $query, LOG_ERROR);
    }
    return false;
}

/**
 * Executes an SQL select statement.
 * @param $query    SQL query to execute.
 * @return        SQL result as array.
 */
function sql_selectObj($query, $result = true) {
    $query = sql_optimize($query);
    $return = array();
    if(sql_connect() != false) {
        if(getConfig('sql')) {
            log_system($query, LOG_SQL);
        }
        $result = mysqli_query($server, $query);
        if($result) {
            while($row = @mysqli_fetch_array($result)) {
                array_push($return, $row);
            }
        }
    }
    return $return;
}

/**
 * Executes an SQL select statement.
 * @param $query    SQL query to execute.
 * @return        Single SQL result as array.
 */
function sql_selectSingleObj($query) {
    $query = sql_optimize($query);
    $return = false;
    if(sql_connect() != false) {
        if(getConfig('sql')) {
            log_system($query, LOG_SQL);
        }
        $result = mysqli_query($server, $query);
        if($result) {
            $return = mysqli_fetch_array($result);
        }
    }
    return $return;
}

/**
 * Executes an SQL insert statement.
 * @param $query    SQL query to execute.
 * @return        Id of the inserted data.
 *                  false if inserting failed.
 */
function sql_insert($query) {
    $query = sql_optimize($query);
    $server = sql_connect();
    if($server) {
        if(getConfig('sql')) {
            log_system($query, LOG_SQL);
        }
        if(@mysqli_query($server, $query)) {
            return @mysqli_insert_id($server);
        } else {
            log_system('SQL insert failed: ' . $query, LOG_ERROR);
        }
    }
    return false;
}

/**
 * Executes an SQL update query.
 * @param $query    SQL query to execute.
 */
function sql_update($query) {
    $query = sql_optimize($query);
    if(sql_connect() != false) {
        if(getConfig('sql')) {
            log_system($query, LOG_SQL);
        }
        return @mysqli_query($server, $query);
    }
    return false;
}

/**
 * Executes an SQL delete query.
 * @param $query    SQL query to execute.
 */
function sql_delete($query) {
    $query = sql_optimize($query);
    if(sql_connect() != false) {
        if(getConfig('sql')) {
            log_system($query, LOG_SQL);
        }
        return @mysqli_query($server, $query);
    }
    return false;
}

/**
 * Encodes a part of a SQL parameter.
 * This function prevents SQL injection.
 * @param $input    String to encode.
 * @return        Encoded string.
 */
function sql_encode($input) {
    if(is_array($input)) {
        $return = array();
        foreach($input as $tmp) {
            $return[] = mysqli_real_escape_string($server, $tmp);
        }
        return $return;
    } else {
        return mysqli_real_escape_string($server, $input);
    }
}

/**
 * Returns a SQL select string of groups of a user.
 * @param $user    User to get the groups from.
 *         false if the groups of the logged in user have to be loaded.
 * @return    SQL parameters list of groups.
 */
function sql_getGroups($user = false, $groupfield = 'group') {
    $return = '';

    if(auth_isAdmin($user) == false) {
        /* Loads the groups of the user. */
        $groups = auth_getGroups($user);

        /* Creates the SQL query. */
        if(count($groups) > 0) {
            $return .= ' AND (';
            $return .= '`' . $groupfield . '` IN (' . join(',', array_keys($groups)) . ')';
            foreach(array_keys($groups) as $key) {
                $return .= ' OR `' . $groupfield . '_list` LIKE \'%|' . $key . '|%\'';
                //if(auth_isGroup($key)) {
                //    if($return == '') {
                //        $return .= ' AND (';
                //    } else {
                //        $return .= ' OR ';
                //    }
                //    $return .= '`' . $groupfield . '`=\'' . $key . '\'';
                //}
            }
            $return .= ')';
        } else {
            $return = ' AND 1=2';
        }
    }
    return $return;
}

/**
 * Optimizes the SQL query.
 */
function sql_optimize($query) {
    return str_replace(array("\t","\n","\r"), "", $query);
}

/**
 * Reads all tables of the database.
 */
function sql_getTables() {
    /* Loads all tables. */
    $query = 'SHOW TABLES FROM `' . getConfig('dbname') . '`;';
    $tables = sql_selectObj($query);
    $mytables = array();
    foreach($tables as $table) {
        array_push($mytables,$table[0]);
    }
    return $mytables;
}

/**
 * Reads the fields of a table.
 */
function sql_getTableFields($tableName) {
    /* Loads all fields. */
    $query = 'SHOW COLUMNS FROM `' . $tableName . '`;';
    $fields = sql_selectObj($query);
    return $fields;
}

/**
 * Reads the indices of a table.
 */
function sql_getTableIndices($tableName) {
    /* Loads all fields. */
    $query = 'SHOW INDEXES FROM `' . $tableName . '`;';
    $indeces = sql_selectObj($query);
    return $indeces;
}

#2

A major part of converting from mysql_ to mysqli_ or PDO is to change to use prepared and parameterized queries. This would include removing any code that escapes/mangles data as you no longer need to escape strings.


#3

I am not clear on your use of reconnecting to the server several times !
Along with all the extra code to handle queries.

The use of a sql_connect() function every single time you need to use the database is a waste of time.
It adds tons of useless code. Just do the connection at the top of every page one time and you are done.
Normally, just connect once to the database at the top of the code. Then, run queries as needed and make
them customized to the individual queries. There is a lot of overhead to call functions and pass queries back
and forth from live code to functions and back again. Just write your queries where needed. It only takes two or three lines to run a queries, instead of the dozens you use.

And, I agree with @JimL , PDO is the way to go instead of upgrading to MySQLi…


#4

You are also hiding error messages. (Suppressing errors) Therefore, you might want to removing them for your testing as they might give you more info on your problem.