mysql syntax error - not sure why

[sanyibacsi]

Hi There,

 

I get the following error message:

Error: 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 '\'asdfghj\',\'Address\',\'Cont\',\'City\',\'Zip\',\'Akrotiri\',\'61616467\',\'aa' at line 1

 

I try to do an insert - but it does not work.  More interesting, after the error I print the whole query to the browser, copy+paste it into MySQL Query Browser where it successfully runs and inserts the line.  I wonder if mysql_real_escape_string causes the error, or if the reason is the use of UTF-8 (without BOM), all files are saved like that; it works fine for multibyte characters such as őűŐŰ (that is actually used in Hungarian).

 

Probably someone has seen a problem of this nature already, please let me know if you have a clue.

 

Thank you

[Simon Mayer]

have you identified what EXACT query is being executed? Why not try echoing your query to the page. (just noticed you did that already) I suspect that there is something wrong with it, a misplaced quote, a backslash in the wrong place, etc.

 

Once you have echoed it, if you can't work out the problem, post the query here.

[Muddy_Funster]

Yeah, can we see the full query? More often than not the problem lies just before the "syntax to use near" error string.  Regardless it will make life much easier if we can see it in context.

 

Also, please include the code where you build the string as well.

[sanyibacsi]

INSERT INTO user (Name,Address1,Address2,City,Zip,Country,Phone,Email,AcceptedRules,CreditBalance,Referee1,Referee2,Admin,Pwd,DateOfBirth,DefaultLanguage,SeeSpecialStats) VALUES ('asdfghj','Address','Cont','City','Zip','Akrotiri','61616467','aaa@be.com',NOW(),10,0,0,0,'qwerty','1992-02-02','English ',0)

 

That is the exact query above.... copy+pase and works in Query Browser... 

[PFMaBiSmAd]

You are apparently using mysql_real_escape_string() on the WHOLE query statement, not just on the individual string data values being put into the query.

 

Someone already asked that you post the code that is building the query.

 

 

[sanyibacsi]

Hm... I thought I need to escape the whole query... Is not it the way to do it?

 

The code I use look like this... I juggled around a bit with that but have not managed to figure out what the problem is:

function ZeroPad($Variable, $Length) {
for ($i = 1; $i < $Length; $i++) {
  $Variable = "0" . $Variable;
}
return $Variable;
}

class CDbClass {
var $DbResource;
var $Db;

function __construct() {
  $this->DbResource = FALSE;
  $this->Db = FALSE;
}

function CheckConnection() {
  if ($this->DbResource == FALSE) {
   $this->DbResource = mysql_pconnect();
  }
  
  if ($this->DbResource == FALSE) {
   return FALSE;
  }

  if ($this->Db == FALSE) {
   $this->Db = mysql_select_db('mydb', $this->DbResource);
   if ($this->Db == FALSE) {
    return FALSE;
   }
  }

  return TRUE;
}

function Execute($Query) {
  if (!$this->CheckConnection()) {
   exit("DBCONNERR");
   return FALSE;
  }
//echo "Connection OK";

  $Query = mysql_real_escape_string($Query, $this->DbResource);

  return mysql_query($Query);
}
}

   $Command = "INSERT INTO user (Name,Address1,Address2,City,Zip,Country,Phone,Email,AcceptedRules,CreditBalance,Referee1,Referee2,Admin,Pwd,DateOfBirth,DefaultLanguage,SeeSpecialStats) VALUES (";
   $Command .= "'" . $_REQUEST['Name'] . "',"; // Name
   $Command .= "'" . $_REQUEST['Address1'] . "',"; // Address1
   $Command .= "'" . $_REQUEST['Address2'] . "',"; // Address2
   $Command .= "'" . $_REQUEST['City'] . "',"; // City
   $Command .= "'" . $_REQUEST['Zip'] . "',"; // Zip
   $Command .= "'" . $_REQUEST['Country'] . "',"; // Country
   $Command .= "'" . $_REQUEST['Phone'] . "',"; // Phone
   $Command .= "'" . $_REQUEST['Email1'] . "',"; // Email
   $Command .= "NOW(),"; // AcceptedRules timestamp
   $Command .= "10,"; // CreditBalance
   $Command .= "0,"; // Referee1
   $Command .= "0,"; // Referee2
   $Command .= "0,"; // Admin
   $Command .= "'" . $_REQUEST['Password1'] . "',"; // Pwd
   $Command .= "'" . $_REQUEST['BirthYear'] . "-" . ZeroPad($_REQUEST['BirthMonth'], 2) . "-" . ZeroPad($_REQUEST['BirthDay'], 2) . "',"; // DateOfBirth
   $Command .= "'" . $_REQUEST['DefaultLanguage'] . "',"; // DefaultLanguage
   $Command .= "0"; // SeeSpecialStats
   $Command .= ")";

  $Db = new CDbClass();
  $Db->Execute($Command);

[sanyibacsi]

Actually, mysql_real_escape_string documentation says:

"Escapes special characters in the unescaped_string , taking into account the current character set of the connection...."

 

Is there a way to check the connection character set after pconnect?  The database is InnoDB UTF-8 default.  The PHP Manual example also escape the whole query (they have a select there).

 

I am really badly confused.  I would appreciate if someone could please explain....

 

[sanyibacsi]

I still struggle with that...  :'(

 

Any idea?

 

[DavidAM]

Look more closely at the example, it does NOT escape the entire SQL statement, only the values being put into the statement. To correct this, you need to build your command something like this:

   $Command = "INSERT INTO user (Name,Address1,Address2,City,Zip,Country,Phone,Email,AcceptedRules,CreditBalance,Referee1,Referee2,Admin,Pwd,DateOfBirth,DefaultLanguage,SeeSpecialStats) VALUES (";
   $Command .= "'" . mysql_real_escape_string($_REQUEST['Name']) . "',"; // Name
   $Command .= "'" . mysql_real_escape_string($_REQUEST['Address1']) . "',"; // Address1
# AND SO FORTH 

and then remove the mysql_real_escape_string() function call inside the Execute() method.