I get always Error messages with this part:
function sql_insert($query) {
$query = sql_optimize($query);
$server = mysqli_connect(getConfig(‘dbhost’), getConfig(‘dbuser’), getConfig(‘dbpass’), getConfig(‘dbname’));
if($server) {
if(getConfig(‘sql’)) {
log_system($query, LOG_SQL);
};
if(@mysqli_query($server, $query)) {
return @mysqli_insert_id($server);
} else {
log_system('mysql.php (102) SQL insert failed: ’ . $query, LOG_ERROR);
}
}
return false;
What´s my mistake?
stop suppressing error messages (@) then return with the error message you get. Impossible to know without the error and query.
can you explain what you mean with “then return with the error message you get” ?
Remove the error suppression, make sure your dev environment (local web server) is set up to display all errors, then refresh the page and copy the error message you get and paste it here. preferably along with more of the code from this (ie server side)
<?php
if(!defined('INTRANET'))
exit();
$server = mysqli_connect(getConfig('dbhost'), getConfig('dbuser'), getConfig('dbpass'), getConfig('dbname'));
/**
* 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) {
$server = mysqli_connect(getConfig('dbhost'), getConfig('dbuser'), getConfig('dbpass'), getConfig('dbname'));
$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) {
$server = mysqli_connect(getConfig('dbhost'), getConfig('dbuser'), getConfig('dbpass'), getConfig('dbname'));
$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 = mysqli_connect(getConfig('dbhost'), getConfig('dbuser'), getConfig('dbpass'), getConfig('dbname'));
if($server) {
if(getConfig('sql')) {
log_system($query, LOG_SQL);
};
if(mysqli_query($server, $query)) {
return mysqli_insert_id($server);
} else {
log_system('mysql.php (102) SQL insert failed: ' . $query, LOG_ERROR);
}
}
return false;
}
/**
* Executes an SQL update query.
* @param $query SQL query to execute.
*/
function sql_update($query) {
$server = mysqli_connect(getConfig('dbhost'), getConfig('dbuser'), getConfig('dbpass'), getConfig('dbname'));
$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) {
$server = mysqli_connect(getConfig('dbhost'), getConfig('dbuser'), getConfig('dbpass'), getConfig('dbname'));
$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) {
$server = mysqli_connect(getConfig('dbhost'), getConfig('dbuser'), getConfig('dbpass'), getConfig('dbname'));
$return[] = mysqli_real_escape_string($server, $tmp);
}
return $return;
} else {
$server = mysqli_connect(getConfig('dbhost'), getConfig('dbuser'), getConfig('dbpass'), getConfig('dbname'));
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;
}
This is the whole side
03.01.2019 17:43:36 - file item.php, function sql_insert(), line 184 - (JBeetz) ERROR: mysql.php (102) SQL insert failed: INSERT INTO
helferportal_item(title,description,contact,phone,group,group_list,date,dateend,type,mark,location,public,subscribe,minsubscribe,publicsubscribe,maxsubscribe,keywords,association,modified,parent,owner,ip,clicks,deleted) VALUES (‘test’,’’,’’,’’,’’,‘1’,’|1|’,‘1547337600’,‘1547337600’,‘4’,‘1’,’’,‘0’,‘0’,‘99’,‘0’,‘0’,’’,’’,‘1546537416’,‘0’,‘37’,‘2a02:908:4c19:f8a0:61ff:3c3d:d510:b9f1’,‘0’,‘0’);
and this error is logged
That is not the entire error message, so you’re making your life quite a bit harder by obfuscating what is actually happening.
Your SQL query is invalid. Just paste it into any SQL validator or use it in command line / phpmyadmin or similar and it will spit out the real error messages
“group” is a reserved word
’ ’ is the wrong kind of ticks, use either ’ or "
And you really shouldn’t concatenate (insert) variables directly into the query string. Use prepared and parameterized queries.
But this error appeared first when i updated my code
to MySQLi
Unless your application was running on an ancient version of mysql then it should not have accepted that query as “group” has been a reserved word for years.
With this forum software, appearances can be deceiving. The mangling of the quotes and perhaps operation on any back-ticks in the posted information could be due to the forum software.
You need to find out why the query is failing. Whoever originally wrote these database functions, wrote a lot of unnecessary code and didn’t ever get around to using the actual error information. You need to modify the information being logged to include the mysqli_error($server) information.
If you are going to continue using these database functions, you need to fix the things they are doing that are consuming multiple connection, hiding errors, and simplify all the code.
The sql_connect() function needs a static variable to hold the connection and it should only make a connection if there is not already one. Any function that needs the connection should call this function. The error handling for the connection should be in this function.
There should be a (one) main query function that executes a query, with the query logging and error handling logic it in. All the other functions that need to execute a query should call this main query function.
This will eliminate all the repetitive code, allowing you to see the forest (what the code is actually doing) for the trees (simplifying all the implementation details.)
How much main logic does this application have? It may in fact be quicker to just update the code to use a class that extends the php PDO class with a general purpose query method, and any other methods that you need, which will allow you to use the much simpler syntax that prepared queries use.