SQL syntax error ?

[MacRedrum]

I have this piece of code that is giving me an error like this (bit censored):

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 'Random Name', e_id=1, pname='Owner', world=2 WHERE id_p=11' at line 1

 

The code:

$newOwner is a string

$newEid, $current are integers

$query ="UPDATE table SET leader='$newOwner', e_id=$newEid, pname='Owner', world=2 WHERE id_p=$current";
mysql_query($query); echo mysql_error();

 

I've gotten same errors before on this server and I've been able to get rid of them by randomly setting ". ." or '' to the variables. It just seems that the same query might work elsewhere and then give a error later on. Getting a feeling this all might be server related issue, but don't know if I can configure the server as it's a shared host based hosting. Maybe at max setting some php.ini options. Anyways, i've ran out of ideas what to do now and this is a crucial part of the code I'm doing.

 

Would apriciate any insight what might be causing the error.

[Pikachu2000]

How is the value assigned to $newOwner, and are you escaping the data?

[MacRedrum]

How is the value assigned to $newOwner, and are you escaping the data?

 

$newOwner is gotten from a previous query, same as newEID.

I'm not escaping any of it, since it's an internal script. Users don't have any way to input to it, so I didn't think it would be needed.

[Pikachu2000]

It should still be escaped to prevent any problematic characters such as apostrophes from cratering things.  Did you echo the query string so you can check its structure?

[PFMaBiSmAd]

Is your table name actually table as that is a reserved keyword and would break the syntax of your query.

[MacRedrum]

Yeah. I usually add a debug print to these.

 

the output:

UPDATE table SET leader='Random Name', e_id=18, pname='Owner', world=2 WHERE id_p=11

 

I can run that on the sites phpmyadmin and it goes through without errors. That's also a part that's wrecking me How can it work in there and not in the code. Both use the same php after all. I do hope I don't have to add escapes to all my queries... it would mean a lot of work for me.

 

The "table" is just a substitute to the real name. I had to censor it for here

[Pikachu2000]

You should escape them all. What happens when a value returned from one query is, for example, O'Malley[, and then it gets used in another query? Anyhow, without the actual output and error, that's as far as I can take this one.

[MacRedrum]

oh well, dunno how much good it will do to give the real names, but here goes. The code is part of a ticker, that's why I didn't bother so much with the escaping. I've broken down the code and I also divided the query to 4 different ones:

 

$query =sprintf("UPDATE `eq_planets` SET `leader`='%s' WHERE `id_planet`=$currentBattle", mysql_real_escape_string($newOwner));
if($debugSorter>1)
{
print ("<br>Capping 1query: $query <br>");
}
mysql_query($query); echo mysql_error(); 

$query ="UPDATE `eq_planets` SET `empire_id`=$newEid WHERE `id_planet`=$currentBattle";
if($debugSorter>1)
{
print ("<br>Capping 2query: $query <br>");
}
mysql_query($query); echo mysql_error(); 

$query ="UPDATE `eq_planets` SET `planetname`='Colony' WHERE `id_planet`=$currentBattle";
if($debugSorter>1)
{
print ("<br>Capping 3query: $query <br>");
}
mysql_query($query); echo mysql_error(); 

$query ="UPDATE `eq_planets` SET `homeworld`=2 WHERE `id_planet`=$currentBattle";
if($debugSorter>1)
{
print ("<br>Capping 4query: $query <br>");
}
mysql_query($query); echo mysql_error(); 

 

The print queries:

Capping 1query: UPDATE `eq_planets` SET `leader`='Redrum' WHERE `id_planet`=61

 

Capping 2query: UPDATE `eq_planets` SET `empire_id`=18 WHERE `id_planet`=61

 

Capping 3query: UPDATE `eq_planets` SET `planetname`='Colony' WHERE `id_planet`=61

 

Capping 4query: UPDATE `eq_planets` SET `homeworld`=2 WHERE `id_planet`=61

 

and the error I get is:

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 'Redrum' WHERE `id_planet`=61

 

Capping 2query: UPDATE `eq_planets` SET `em' at line 1

 

 

It's almost like it's executing only parts of the queries and continuing on a unrelated print command.

[PFMaBiSmAd]

Because you are unconditionally echoing the mysql_error() value, the error message that you see can actually be due to a previous failed query in your code and not necessarily the query immediately before where you echo it.

[MacRedrum]

yeah, but I've also checked the previous queries. All they do is get the 2 values to that 1 update. Also the values are right, as the query print shows.

[MacRedrum]

So: I've made sure that the variables are what I want via var_dump and setting their datatype manually. Still problems.

 

if I comment out these queries out. I get no errors.

$query ="UPDATE `eq_planets` SET `eq_planets`.`leader`='$newOwner' WHERE `eq_planets`.`id_planet`=$currentBattle";

$query ="UPDATE `eq_planets` SET `planetname`='Colony' WHERE `id_planet`=$currentBattle";

 

but the moment I add them, with or without escaping. They produce the error. Even if I manually type the values to input it still gives the error.

 

[PFMaBiSmAd]

I'm going to guess that you are including a file using a URL and are expecting to get the php code and php variables from that file into the current program, but are actually getting just the output from that file.

 

Post a complete set of code that produces/reproduces the problem so that someone can get the big picture of what you are doing. Just posting the bits and pieces as you have been in this thread doesn't provide enough information to help you. If you are using a URL somewhere in your code and you don't want to post the actual URL, xxxxx it out, but don't change any of the actual syntax you are using in your code.

[MacRedrum]

I'm going to guess that you are including a file using a URL and are expecting to get the php code and php variables from that file into the current program, but are actually getting just the output from that file.

 

Post a complete set of code that produces/reproduces the problem so that someone can get the big picture of what you are doing. Just posting the bits and pieces as you have been in this thread doesn't provide enough information to help you. If you are using a URL somewhere in your code and you don't want to post the actual URL, xxxxx it out, but don't change any of the actual syntax you are using in your code.

 

Well like I have said: It gets it's values from the database, calculates some things with them and then updates/inserts/deletes based on the results of the calculation. So there should be no outside influence in it.

 

I'll paste the queries that I used to get the values to those variables, BUT I already print them out with var_dump, so I know they are correct. The whole file is around 1700 lines long, but luckily I've isolated the problem to this part. It only happens when this part of the code is being used. (flag)

 

currentBattle == same as for ($i) and $getsThePlanet = int value calculated before the script.

//fetch the new eid and planet/ruler name
$query ="SELECT empire_id FROM eq_user_fleet WHERE fleet_id='$getsThePlanet'";
$result=mysql_query($query); echo mysql_error();
$row=mysql_fetch_row($result);
$newEid=(int)$row[0];

$query ="SELECT planet FROM users WHERE empire_id=$newEid";
$result=mysql_query($query); echo mysql_error();
$row=mysql_fetch_row($result);
$newOwner=$row[0];

$query =sprintf("UPDATE `eq_planets` SET `leader`='%s' WHERE `id_planet`=$currentBattle", mysql_real_escape_string($newOwner));
mysql_query($query); echo mysql_error();

$query ="UPDATE `eq_planets` SET `empire_id`=$newEid WHERE `id_planet`=$currentBattle";
mysql_query($query); echo mysql_error(); 

$query ="UPDATE `eq_planets` SET `planetname`='Colony' WHERE `id_planet`=$currentBattle";
mysql_query($query); echo mysql_error();

$query ="UPDATE `eq_planets` SET `homeworld`=2 WHERE `id_planet`=$currentBattle";
mysql_query($query); echo mysql_error();

 

the funniest part is, that it doesn't seem to stop the queries from running and updating the tables at the moment. It is however stopping my logging that records the output into the database as well.