Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 14 of 14
  1. #1
    New Coder
    Join Date
    Jan 2009
    Posts
    91
    Thanks
    17
    Thanked 1 Time in 1 Post

    Error 1064 and apostrophy

    Hello,

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

    PHP Code:
    $sql "REPLACE INTO current_roster (note,careerID,careerString,rankInAlliance,lastLogin,bearerStatus,lastLoginNumberHour24,zoneID,lastLoginNumberDay,statusNumber,lastLoginNumberMonth,lastLoginNumberYear,memberID,onote,founder,name,titleString,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)

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    if you are using php use mysql_real_escape_string() or use equivalent escaping function in you front end application if not php.

  • #3
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Escape your variables. (You should be doing this anyway to prevent SQL injections)

    Use mysql_real_escape_string() for this.

  • #4
    New Coder
    Join Date
    Jan 2009
    Posts
    91
    Thanks
    17
    Thanked 1 Time in 1 Post
    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.

    PHP Code:
    <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:

    PHP Code:
    <? if ($_POST["data_in"]) { // Check if the data has been posted
    Any suggestions? Did I provide enough info?

  • #5
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,861
    Thanks
    160
    Thanked 2,223 Times in 2,210 Posts
    Blog Entries
    1
    PHP Code:
    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);
         }
     }

    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)

  • Users who have thanked abduraooft for this post:

    Hayyel (03-28-2009)

  • #6
    New Coder
    Join Date
    Jan 2009
    Posts
    91
    Thanks
    17
    Thanked 1 Time in 1 Post
    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.

  • #7
    New Coder
    Join Date
    Jan 2009
    Posts
    91
    Thanks
    17
    Thanked 1 Time in 1 Post
    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.

  • #8
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,861
    Thanks
    160
    Thanked 2,223 Times in 2,210 Posts
    Blog Entries
    1
    Quote Originally Posted by Hayyel View Post
    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?
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)

  • #9
    New Coder
    Join Date
    Jan 2009
    Posts
    91
    Thanks
    17
    Thanked 1 Time in 1 Post
    Current code.

    PHP 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"], 010);  // Get date
        
    $gde_time substr($_POST["data_in"], 1010); // 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($titlein0, -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,lastLoginNumberDay,statusNumber,lastLoginNumberMonth,lastLoginNumberYear,memberID,onote,founder,name,titleString,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(
    $currentmysql_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,lastLoginNumberDay,statusNumber,lastLoginNumberMonth,lastLoginNumberYear,memberID,onote,founder,name,titleString,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.
    Attached Files Attached Files

  • #10
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,861
    Thanks
    160
    Thanked 2,223 Times in 2,210 Posts
    Blog Entries
    1
    You may need to comment
    PHP Code:
    $gdata stripslashes($gdata); 
    after your real_escape call
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)

  • Users who have thanked abduraooft for this post:

    Hayyel (04-01-2009)

  • #11
    New Coder
    Join Date
    Jan 2009
    Posts
    91
    Thanks
    17
    Thanked 1 Time in 1 Post
    Awesome that seems to work. Once the data is submited it results in this being diplayed:

    PHP Code:
    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?

  • #12
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,861
    Thanks
    160
    Thanked 2,223 Times in 2,210 Posts
    Blog Entries
    1
    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 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(by Fou-Lu), which will give you the reason for why L is ignored (and much more), in a simple and detailed manner.
    Last edited by abduraooft; 04-01-2009 at 12:35 PM.
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)

  • #13
    New Coder
    Join Date
    Jan 2009
    Posts
    91
    Thanks
    17
    Thanked 1 Time in 1 Post
    Update:

    I got ahead of myself.. by commenting out
    PHP Code:
    $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.

  • #14
    New Coder
    Join Date
    Jan 2009
    Posts
    91
    Thanks
    17
    Thanked 1 Time in 1 Post
    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
    PHP Code:
    str_replace("'"""$gdata); 
    PHP Code:
    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?


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •