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;
}