Help Needed with Mysql error

[flishess]

I'm trying to get a script working from Insane Visions - OneCMS Page :: OneCMS

The author doesn't seem to be providing much support, so I am coming here. (it's a gpl script)

I'm trying to edit something that I assume is supposed to write to the database, however I am getting this error.

A fatal MySQL error occured.

Query:
Error: (1064)You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'sql = '' WHERE id = '3'' at line 1

I know this isn't much to go on, so let me know what other info you might need to help me get this working.

If anyone feels like installing the script, the error is coming from clicking on Modules Manager and then trying to edit one of the module names. (after selecting edit, then clicking on submit I rename the title and then select submit changes and get the error)

I'm using version 2.5 of the script.
My PHP version is 5.2.5

 

[bbosh]

sql is a reserved word. See MySQL :: MySQL 5.0 Reference Manual :: 8.3 Reserved Words

Look for the code and replace sql with `sql` (backticks).

-Brendon.

 

[PHPRight]

Also I noticed after the three, where it says `id` = '3'', that should only be one ' afterwards, not two.

 

[qbert220]

Also I noticed after the three, where it says `id` = '3'', that should only be one ' afterwards, not two.

I think the second quote after the threee is generated my mysql_error() to indicate that it is quoting part of the query string. It matches the quote just before sql (coloured red in the quote below):

Error: (1064)You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'sql = '' WHERE id = '3'' at line 1

 

[Elven6]

I'm getting the same error, changing the sql setting doesn't work. This is the line of code,

if (($_GET['view'] == "manage") && ($_GET['edit'] == "2")) {
while (list(, $val) = each ($_POST['id'])) {
$r = mysql_query("UPDATE ".$pre."mods SET name = '".$_POST["name_$val"]."', url = '".$_POST["url_$val"]."', installed = '".$_POST["installed_$val"]."', status = '".$_POST["status_$val"]."', version = '".$_POST["version_$val"]."', readme = '".$_POST["readme_$val"]."', sql = '".$_POST["url2_$val"]."' WHERE id = '".$val."'") or die("<font color='red'><b>A fatal MySQL error occured</b></font>.<br><br><b>Query:</b> ".$r."<br><b>Error:</b> (".mysql_errno().")".mysql_error());
}
if ($r == TRUE) {
echo re_direct("1500", "admin.php?load=modules");
echo "The modules has been updated. <a href=\"admin.php?load=modules\">Manage Modules</a>";
}
}

Can't seem to find the culprit, some of the stuff I tired didn't work.

 

[Eric]

Based on just seeing that snippet of code, I'd say find a new CMS quick - there are better free ones. That code is awful.

I'm getting the same error, changing the sql setting doesn't work. This is the line of code,

if (($_GET['view'] == "manage") && ($_GET['edit'] == "2")) {
while (list(, $val) = each ($_POST['id'])) {
$r = mysql_query("UPDATE ".$pre."mods SET name = '".$_POST["name_$val"]."', url = '".$_POST["url_$val"]."', installed = '".$_POST["installed_$val"]."', status = '".$_POST["status_$val"]."', version = '".$_POST["version_$val"]."', readme = '".$_POST["readme_$val"]."', sql = '".$_POST["url2_$val"]."' WHERE id = '".$val."'") or die("<font color='red'><b>A fatal MySQL error occured</b></font>.<br><br><b>Query:</b> ".$r."<br><b>Error:</b> (".mysql_errno().")".mysql_error());
}
if ($r == TRUE) {
echo re_direct("1500", "admin.php?load=modules");
echo "The modules has been updated. <a href=\"admin.php?load=modules\">Manage Modules</a>";
}
}

Can't seem to find the culprit, some of the stuff I tired didn't work.

I've edited it - formatted so I could try to make sense of it - maybe try this:

if (($_GET['view'] == "manage") AND ($_GET['edit'] == "2"))
{
	while (list(, $val) = each($_POST['id']))
	{
		$r = mysql_query("
			UPDATE {$pre}mods
			SET 
				name      = '" . mysql_real_escape_string($_POST["name_$val"]) . "', 
				url       = '" . mysql_real_escape_string($_POST["url_$val"]) . "', 
				installed = '" . mysql_real_escape_string($_POST["installed_$val"]) . "', 
				status    = '" . mysql_real_escape_string($_POST["status_$val"]) . "', 
				version   = '" . mysql_real_escape_string($_POST["version_$val"]) . "', 
				readme    = '" . mysql_real_escape_string($_POST["readme_$val"]) . "', 
				sql       = '" . mysql_real_escape_string($_POST["url2_$val"]) . "'
			WHERE id = '" . mysql_real_escape_string($val) . "'
		") or die("<b style='color: red;'>A fatal MySQL error occured</b>.<br /><br /><b>Query:</b> " . htmlspecialchars($r) . "<br /><b>Error:</b> (" . mysql_errno() . ")" . mysql_error());
	}

	if ($r == TRUE)
	{
		echo re_direct("1500", "admin.php?load=modules");
		echo "The modules has been updated. <a href=\"admin.php?load=modules\">Manage Modules</a>";
	}
}

 

[Elven6]

Based on just seeing that snippet of code, I'd say find a new CMS quick - there are better free ones. That code is awful.



I've edited it - formatted so I could try to make sense of it - maybe try this:

if (($_GET['view'] == "manage") AND ($_GET['edit'] == "2"))
{
	while (list(, $val) = each($_POST['id']))
	{
		$r = mysql_query("
			UPDATE {$pre}mods
			SET 
				name      = '" . mysql_real_escape_string($_POST["name_$val"]) . "', 
				url       = '" . mysql_real_escape_string($_POST["url_$val"]) . "', 
				installed = '" . mysql_real_escape_string($_POST["installed_$val"]) . "', 
				status    = '" . mysql_real_escape_string($_POST["status_$val"]) . "', 
				version   = '" . mysql_real_escape_string($_POST["version_$val"]) . "', 
				readme    = '" . mysql_real_escape_string($_POST["readme_$val"]) . "', 
				sql       = '" . mysql_real_escape_string($_POST["url2_$val"]) . "'
			WHERE id = '" . mysql_real_escape_string($val) . "'
		") or die("<b style='color: red;'>A fatal MySQL error occured</b>.<br /><br /><b>Query:</b> " . htmlspecialchars($r) . "<br /><b>Error:</b> (" . mysql_errno() . ")" . mysql_error());
	}

	if ($r == TRUE)
	{
		echo re_direct("1500", "admin.php?load=modules");
		echo "The modules has been updated. <a href=\"admin.php?load=modules\">Manage Modules</a>";
	}
}

Hi Eric,

Thanks for the response, what's wrong with the code? The reason I am using OneCMS is because it's really the only gaming oriented free CMS on the market after the others "shut down".

I will try that code you posted shortly and let you know how it turns out.

 

[Elven6]

Just tried the code Eric posted, I get the following error,

A fatal MySQL error occured.

Query: 
Error: (1064)You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'sql = '' WHERE id = '14'' at line 9

Can't find anything relating to the problem on line 9, I also checked the functions.php file and the config.php file with no luck.

 

[Kate]

See post #2

 

[Elven6]

See post #2

I tried that but it doesn't seem to do anything, the error still stays.

 

[foryounow]

Hey Elven6,
you cannot call 'sql' is as column name in your query , because it is a reserved word in Mysql, you can view all reserved words here MySQL :: MySQL 5.1 Reference Manual :: 8.3 Reserved Words

 

[Elven6]

Ahh I think I understand now, so basically what I will have to do is call it something else since the word is not allowed?

Edit: Fixed! Previously the call was labeled sqla, I figured it may have been a error on the coders part since it was still generating the errors. In the end it turns out the "call" was right but the coder forgot to change the name in the MySQL table. Changing both of them fixed the issue!

Thanks for all the input!

 

[Elven6]

Since this is also OneCMS related and a MySQL issues I thought I should post here instead of creating a new thread.

The way OneCMS currently handles Games, Cheats, News, etc is by storing them all in a single MySQL table. Is this a good idea in comparison to say giving cheats its own MySQL table, News its own MySQL table, etc within the database?