...

View Full Version : Error 1064 and apostrophy



Hayyel
03-27-2009, 08:17 PM
Hello,

I am receiving a 1064 syntax error when doing the following:


$sql = "REPLACE INTO current_roster (note,careerID,careerString,rankInAlliance,lastLogin,bearerStatus,lastLoginNumberHour24,zoneID,lastL oginNumberDay,statusNumber,lastLoginNumberMonth,lastLoginNumberYear,memberID,onote,founder,name,titl eString,rank,updated) VALUES ('{$firstDimValue['note']}','{$firstDimValue['careerID']}','{$firstDimValue['careerString']}','{$firstDimValue['rankInAlliance']}','{$firstDimValue['lastLogin']}','{$firstDimValue['bearerStatus']}','{$firstDimValue['lastLoginNumberHour24']}','{$firstDimValue['zoneID']}','{$firstDimValue['lastLoginNumberDay']}','{$firstDimValue['statusNumber']}','{$firstDimValue['lastLoginNumberMonth']}','{$firstDimValue['lastLoginNumberYear']}','{$firstDimValue['memberID']}','{$firstDimValue['onote']}','{$firstDimValue['founder']}','{$firstDimValue['name']}','{$firstDimValue['titleString']}','{$firstDimValue['rank']}','{$created_date}')";

The code works... the issue is with the '{$firstDimValue['note']}' field. It kicks back the 1064 error is the data contains an apostrophy. So I cannot have "dog's" or anything of the sort in the field as data.. if I take out the ' then it runs the query perfectly. How can i fix this?

Currently the field 'note' is set to varchar(50)

guelphdad
03-28-2009, 02:53 AM
if you are using php use mysql_real_escape_string() or use equivalent escaping function in you front end application if not php.

Fumigator
03-28-2009, 02:54 AM
Escape your variables. (You should be doing this anyway to prevent SQL injections)

Use mysql_real_escape_string() for this.

Hayyel
03-28-2009, 04:53 AM
I have read up on mysql_real_escape_string. I understand how it works however I have no idea how to use it when submitting the info in the following form.


<div id="roster_form">
<form enctype="multipart/form-data" action="comparison.php" method="POST">
<label>
<textarea name="data_in" cols="50" rows="20" id="data_in"></textarea>
</label>
<label><br />
<input type="submit" name="submit" id="submit" value="Submit" />
</label>
</form>
</div>

After the data is posted it is processed using a file that starts:


<? if ($_POST["data_in"]) { // Check if the data has been posted

Any suggestions? Did I provide enough info?

abduraooft
03-28-2009, 12:15 PM
if ($_POST["data_in"]) { // Check if the data has been posted

//assuming you are using all the values in $_POST array in your query

foreach($_POST as $key=>$value){
if(!ctype_digit($value)){
if (function_exists('get_magic_quotes_gpc') && get_magic_quotes_gpc())
$value=stripslashes($value);
$_POST[$key]=mysql_real_escape_string($value);
}
}
}

Hayyel
03-28-2009, 12:39 PM
Thank you. I understand how it works now... I was trying to use the array as the string... which of course doesnt work. Instead I had to loop through each value and use the mysql_real_escape_string on each value.

Hayyel
03-31-2009, 04:00 PM
Unfortunately after implementing this piece of code it still kicks back the same error.. once I remove the ' from the data then it passes on to the next row with the ' in it.

abduraooft
03-31-2009, 04:29 PM
Unfortunately after implementing this piece of code it still kicks back the same error.. once I remove the ' from the data then it passes on to the next row with the ' in it.
Could you post your current code?

Hayyel
04-01-2009, 02:31 AM
Current code.


<?
// Make a MySQL Connection
include '../dbconnect/dbconnect.php';
include '../dbconnect/opendb.php';

//creates the new array from form that is being posted
if ($_POST["data_in"]) { // Check if the data has been posted

//assuming you are using all the values in $_POST array in your query

foreach($_POST as $key=>$value){
if (function_exists('get_magic_quotes_gpc') && get_magic_quotes_gpc())
$value=stripslashes($value);
$_POST[$key]=mysql_real_escape_string($value);
}
// Function to find the guild data within the log
function substring_between($haystack,$start,$end) {
if (strpos($haystack,$start) === false || strpos($haystack,$end) === false) {
return false;
} else {
$start_position = strpos($haystack,$start)+strlen($start);
$end_position = strpos($haystack,$end);
return substr($haystack,$start_position,$end_position-$start_position);
}
}

// Get time and date of when the log was created

$gdata = substring_between($_POST["data_in"],'{1{','}1}'); // Get guild data between the tags and dump the rest of the log
$gdata = stripslashes($gdata);

$gde_date = substr($_POST["data_in"], 0, 10); // Get date
$gde_time = substr($_POST["data_in"], 10, 10); // Get time

$toremove = array("[", "]"); // Characters to be removed
$gde_date = str_replace($toremove, "", $gde_date); // removed characters from the date string
$gde_time = str_replace($toremove, "", $gde_time); // removed characters from the time string

$gde_date = explode("/", $gde_date);// Seperate date into day, month and year
$gde_time = explode(":", $gde_time);// Seperate time into hour, minute and seconds

// show the arrays for date and time
/*
echo "<pre>";
print_r($gde_date);
print_r($gde_time);
echo "</pre>";
*/

// process the date and time
$logtimestamp = mktime($gde_time[0], $gde_time[1], $gde_time[3], $gde_date[1], $gde_date[2], $gde_date[0]); // Create a timestamp
$created_date = date("F j, Y, g:i a",$logtimestamp); // convert timestamp to date

echo "Log created : ".$created_date. "<br />"; // Show the date/time

$datain = explode("{2{", $gdata); // Process the data
$tableno = 0; // set index for the final array
$finalarr; // Define the array to be populated in the for loop
// So $finalarray[1] would be the first character with all relevant data ie $finalarray[1][name] or $finalarray[1][rank]

for($i = 0; $i < sizeof($datain); ++$i) // for each line in the log
{
if ($datain[$i] != "[N1]=") {
$tableno++;
$datainline = explode(",", $datain[$i]);

for($y = 0; $y < sizeof($datainline); ++$y) // for each Character
{
$datainlinepro = explode("=", $datainline[$y]);
$titlein = substring_between($datainlinepro[0],'"','"');
$titlein = substr($titlein, 0, -1);

if ($titlein == "careerString" || $titlein == "titleString" || $titlein == "name" || $titlein == "note" || $titlein == "onote") {
$valuein = substring_between($datainlinepro[1],'"','"');
} else {
$valuein = str_replace("N", "", $datainlinepro[1]);
}

$finalarr[$tableno][$titlein] = $valuein;

} // End For Loop

} // End if Skip [N1]=



} // End for loop
echo "<pre>";
print_r($finalarr);
echo "</pre>";
//Start array comparison

// Input finalarr into table current_roster
foreach($finalarr as $firstDimKey => $firstDimValue){

//Update Current Roster
$sql = "REPLACE INTO current_roster (note,careerID,careerString,rankInAlliance,lastLogin,bearerStatus,lastLoginNumberHour24,zoneID,lastL oginNumberDay,statusNumber,lastLoginNumberMonth,lastLoginNumberYear,memberID,onote,founder,name,titl eString,rank,updated) VALUES ('{$firstDimValue['note']}','{$firstDimValue['careerID']}','{$firstDimValue['careerString']}','{$firstDimValue['rankInAlliance']}','{$firstDimValue['lastLogin']}','{$firstDimValue['bearerStatus']}','{$firstDimValue['lastLoginNumberHour24']}','{$firstDimValue['zoneID']}','{$firstDimValue['lastLoginNumberDay']}','{$firstDimValue['statusNumber']}','{$firstDimValue['lastLoginNumberMonth']}','{$firstDimValue['lastLoginNumberYear']}','{$firstDimValue['memberID']}','{$firstDimValue['onote']}','{$firstDimValue['founder']}','{$firstDimValue['name']}','{$firstDimValue['titleString']}','{$firstDimValue['rank']}','{$created_date}')";
//echo '<pre>'.$sql.'</pre>';

$result = mysql_query($sql) or die(mysql_errno() . ": " . mysql_error()); // execute the query
}
printf("Records updated/added to Current Roster: %d\n<br />", mysql_affected_rows());
//Get Table Data from current_roster
$query = "SELECT * FROM current_roster";
$result2 = mysql_query($query) or die(mysql_error());
$roster = array();
$pos = 1;
while($current= mysql_fetch_assoc($result2)) {
$roster[$pos++] =$current; // force key to be $pos and increment
}


// Input unguilded_roster into table unguilded
foreach($roster as $firstDimKeyD => $firstDimValueD){
if ($firstDimValueD['updated'] != $created_date) {
echo $firstDimValue['name'];
$sql = "REPLACE INTO unguilded_roster (note,careerID,careerString,rankInAlliance,lastLogin,bearerStatus,lastLoginNumberHour24,zoneID,lastL oginNumberDay,statusNumber,lastLoginNumberMonth,lastLoginNumberYear,memberID,onote,founder,name,titl eString,rank,updated) VALUES ('{$firstDimValueD['note']}','{$firstDimValueD['careerID']}','{$firstDimValueD['careerString']}','{$firstDimValueD['rankInAlliance']}','{$firstDimValueD['lastLogin']}','{$firstDimValueD['bearerStatus']}','{$firstDimValueD['LastLoginNumberHour24']}','{$firstDimValueD['zoneID']}','{$firstDimValueD['lastLoginNumberDay']}','{$firstDimValueD['statusNumber']}','{$firstDimValueD['lastLoginNumberMonth']}','{$firstDimValueD['lastLoginNumberYear']}','{$firstDimValueD['memberID']}','{$firstDimValueD['onote']}','{$firstDimValueD['founder']}','{$firstDimValueD['name']}','{$firstDimValueD['titleString']}','{$firstDimValueD['rank']}','{$firstDimValueD['updated']}')";
echo '<pre>'.$sql.'</pre>';
} }
$result = mysql_query($sql) or die(mysql_errno() . ": " . mysql_error()); // execute the query

printf("Records updated/added to Unguilded Roster: %d\n", mysql_affected_rows());

} else { // No input to parse - Show input form

// Display Form
?>
<link href="../Styles/login.css" rel="stylesheet" type="text/css" />
<div id=roster>Guild Roster Upload
<div id="roster_form">
<form enctype="multipart/form-data" action="comparison.php" method="POST">
<label>
<textarea name="data_in" cols="50" rows="20" id="data_in"></textarea>
</label>
<label><br />
<input type="submit" name="submit" id="submit" value="Submit" />
</label>
</form>
</div></div>
<?

} // end if ( form parse or form show )
?>

The attachment once unzipped just gets copied and pasted into the form.

abduraooft
04-01-2009, 07:35 AM
You may need to comment
$gdata = stripslashes($gdata); after your real_escape call

Hayyel
04-01-2009, 11:36 AM
Awesome that seems to work. Once the data is submited it results in this being diplayed:


Array
(
[1] => Array
(
[note\] => L\"\"
[careerID\] => 101
[careerString\] => L\"Shadow Warrior\"
[rankInAlliance\] => 0
[lastLogin\] => 1238453428
[bearerStatus\] => 0
[lastLoginNumberHour24\] => 18
[zoneID\] => 0
[lastLoginNumberDay\] => 30
[statusNumber\] => 1
[lastLoginNumberMonth\] => 3
[lastLoginNumberYear\] => 2009
[memberID\] => 8413
[onote\] => L\"\"
[founder\] => 0
[name\] => L\"Adariel\"
[titleString\] => L\"Private\"
[rank\] => 21
[0] =>
)

Why are the slashes and "L" ignored in the result in the db?

abduraooft
04-01-2009, 12:19 PM
Why are the slashes ...
Actually an escaping is required only when we use the external data(of type string) in our queries. Thus, my above solution of escaping all the values in $_POST array spoils the removal/deprecation of magic_quotes from PHP's new releases(since mysql_real_escape_string does the same thing when it encounters a single/double quote).

In short, when displaying an external data(of type string), convert them to htmlentites (http://php.net/htmlentites) first.
When using them in mysql_queries, escape all special characters using mysql_real_escape_string.

I'd recommend you to have a look at GPC Stripping Tutorial (http://www.codingforums.com/showthread.php?t=144149)(by Fou-Lu), which will give you the reason for why L is ignored (and much more), in a simple and detailed manner.

Hayyel
04-01-2009, 10:52 PM
Update:

I got ahead of myself.. by commenting out
$gdata = stripslashes($gdata); the dates no longer work which messes things up.

I'll go through that link you provided and see if I can figure something out to fix that issue.

Hayyel
04-02-2009, 02:20 AM
I've gone through and looked at the data contained in $gdata and it makes no sense to me why not stripping the slashes would mess things up.

Anyway - I decided to use
str_replace("'", "", $gdata);


if ($_POST["data_in"]) { // Check if the data has been posted

//assuming you are using all the values in $_POST array in your query

foreach($_POST as $key=>$value){
if (function_exists('get_magic_quotes_gpc') && get_magic_quotes_gpc())
$value=stripslashes($value);
$_POST[$key]=mysql_real_escape_string($value);
}
// Function to find the guild data within the log
function substring_between($haystack,$start,$end) {
if (strpos($haystack,$start) === false || strpos($haystack,$end) === false) {
return false;
} else {
$start_position = strpos($haystack,$start)+strlen($start);
$end_position = strpos($haystack,$end);
return substr($haystack,$start_position,$end_position-$start_position);
}
}

// Get time and date of when the log was created

$gdata = substring_between($_POST["data_in"],'{1{','}1}'); // Get guild data between the tags and dump the rest of the log
$gdata = stripslashes($gdata);
$gdata = str_replace("'", "", $gdata);

Seems to work. Is it ok to use this?



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum