SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    SitePoint Guru marcel's Avatar
    Join Date
    Nov 2000
    Posts
    920
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    mysql_real_escape_string and mysql_escape_string don't work ?

    I am trying to post content to a database table.
    Specifically this article:End to Japan nuke crisis is years, a fortune away - Yahoo! News

    But mysql_real_escape_string and mysql_escape_string are only able to save the first one word "Tokyo".

    What could I be doing wrong or missing ?

    PHP Code:
    $_POST[articlebody] = mysql_real_escape_string($_POST[articlebody]);


    $insertseed "INSERT INTO table (articlebody) VALUES ('$_POST[articlebody]')";
    mysql_query($insertseed) or die("Couldn't execute query"); 

  2. #2
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    16,426
    Mentioned
    160 Post(s)
    Tagged
    1 Thread(s)
    Is it choking after the first word, or after the space after the first word?
    HTML Code:
    <div class="yn-story-content">
                    <p>TOKYO &ndash; Once Japan's leaky nuclear complex stops spewing radiation and its reactors cool down, making the site safe and removing the ruined equipment is going to be a messy ordeal that could take decades and cost hundreds of millions of dollars.</p>
    Or are you getting it from a feed, maybe inside CDATA?

  3. #3
    SitePoint Guru marcel's Avatar
    Join Date
    Nov 2000
    Posts
    920
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Mittineague View Post
    Is it choking after the first word, or after the space after the first word?
    HTML Code:
    <div class="yn-story-content">
                    <p>TOKYO &ndash; Once Japan's leaky nuclear complex stops spewing radiation and its reactors cool down, making the site safe and removing the ruined equipment is going to be a messy ordeal that could take decades and cost hundreds of millions of dollars.</p>
    Or are you getting it from a feed, maybe inside CDATA?

    I am cutting and pasting it into a textarea, then posting it via $_POST to a mysql database.

    It fails after the first dash...

  4. #4
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    16,426
    Mentioned
    160 Post(s)
    Tagged
    1 Thread(s)
    Only a guess, but I'm thinking it probably has something to do with the MySQL charset. I would have thought that MySQL would mess the bytes and show "funny characters" but maybe it chokes and stops the INSERT/UPDATE when a character is outside of the charset range?

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Mittineague View Post
    ... maybe it chokes and stops the INSERT/UPDATE when a character is outside of the charset range?
    that should be fairly easy to test, yes?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Zealot Ethan-27's Avatar
    Join Date
    Jan 2011
    Posts
    152
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How many characters have you allowed to be input into the relevant database field...? If it's only five then it will only save "Tokyo".

  7. #7
    SitePoint Guru marcel's Avatar
    Join Date
    Nov 2000
    Posts
    920
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Ethan-27 View Post
    How many characters have you allowed to be input into the relevant database field...? If it's only five then it will only save "Tokyo".

    The field is a longtext type using utf8_general_ci

  8. #8
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,398
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Do an echo of $insertseed. What do you see?

    If that is ok, how did you check the data in the database? phpMyAdmin? Another script that retrieves and displays the data?

  9. #9
    SitePoint Guru aamonkey's Avatar
    Join Date
    Sep 2004
    Location
    kansas
    Posts
    953
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Can you post the entire form script and form handling script?
    aaron-fisher.com - PHP articles and more

  10. #10
    SitePoint Guru marcel's Avatar
    Join Date
    Nov 2000
    Posts
    920
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Mittineague View Post
    Only a guess, but I'm thinking it probably has something to do with the MySQL charset. I would have thought that MySQL would mess the bytes and show "funny characters" but maybe it chokes and stops the INSERT/UPDATE when a character is outside of the charset range?

    yes
    I think that's what it is...

    When I change the char set to Binary, all the data is saved...
    But when I switch back to UTF8, only "Tokyo" is saved.

    Wordpress also uses UTF8 but but it is able to save the article, my guess is that it converts the data into a saveable format.

    I don't know how wordpress does this conversion yet.

  11. #11
    SitePoint Guru marcel's Avatar
    Join Date
    Nov 2000
    Posts
    920
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Do an echo of $insertseed. What do you see?

    If that is ok, how did you check the data in the database? phpMyAdmin? Another script that retrieves and displays the data?

    yes, i used phpmyadmin.
    Also used PhpMyAadmin to change the char set

  12. #12
    SitePoint Guru marcel's Avatar
    Join Date
    Nov 2000
    Posts
    920
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by aamonkey View Post
    Can you post the entire form script and form handling script?
    SQL
    Code SQL:
    CREATE TABLE `articletable` (
      `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
      `articlebody` longtext NOT NULL,
      UNIQUE KEY `id` (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;


    PHP Code:

         
    include("database.php");  // your database data goes here
        
    @$dbcon mysql_connect(DB_HOST,DB_USER,DB_PASSWORD);
        @
    $dmotorsConn mysql_select_db (DB_NAME$dbcon);    




    if(
    $_POST[articlebody] !="")
        {
                
    $insertseed "INSERT INTO articletable (articlebody) VALUES ('$_POST[articlebody]')";
                
    mysql_query($insertseed) or die("Couldn't execute query <br>  $insertseed");
        }
                        

    echo 
    "<form method=post>";

    $combine  .= "<h3><font color=green> Step 1:</font> Create New Campaign </h3>";

    $combine  .= "
    Try saving this article: http://news.yahoo.com/s/ap/20110409/ap_on_re_us/as_japan_earthquake_nuclear_endgame
    <table>
    "
    ;

    $combine  .= "
    <tr>
        <td>    
                <strong>Article Body </strong>
        </td>
        <td>    
                <textarea cols=80 rows=7 name=\"articlebody\">
    $cleancontent</textarea>        
        </td>
    </tr>
    <tr>
        <td>    
    &nbsp;
        </td>
        <td>    
    <input type=submit value=\"save it\">
        </td>
    </tr>

    </table>
    "
    ;


            
        
    $combine  .= "</form>";

    echo 
    $combine

  13. #13
    SitePoint Guru aamonkey's Avatar
    Join Date
    Sep 2004
    Location
    kansas
    Posts
    953
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That's not the entire script is it? You didn't even use mysql_real_escape_string() in that script, and you are populating the textarea with the $cleancontent variable which is not defined in your script. What is $cleancontent supposed to be?
    aaron-fisher.com - PHP articles and more

  14. #14
    SitePoint Guru marcel's Avatar
    Join Date
    Nov 2000
    Posts
    920
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by aamonkey View Post
    That's not the entire script is it? You didn't even use mysql_real_escape_string() in that script, and you are populating the textarea with the $cleancontent variable which is not defined in your script. What is $cleancontent supposed to be?

    $cleancontent was left there by accident. ( You can ignore it )
    I can't post the entire script. So I simplified it.
    The same error occurs with other scripts I am using.

    I'm quite sure its has something to do with the mysql charset.
    Wordpress is able to save the entire article content using the same mysql charset.
    Obviously Wordpress surely processes the content much better.

  15. #15
    SitePoint Guru aamonkey's Avatar
    Join Date
    Sep 2004
    Location
    kansas
    Posts
    953
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you use that code snippet that you posted, and mysql_real_escape_string() and remove the $cleancontent variable does it work for you? I tried it and it works fine.
    aaron-fisher.com - PHP articles and more

  16. #16
    SitePoint Guru marcel's Avatar
    Join Date
    Nov 2000
    Posts
    920
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up

    Quote Originally Posted by aamonkey View Post
    If you use that code snippet that you posted, and mysql_real_escape_string() and remove the $cleancontent variable does it work for you? I tried it and it works fine.

    Yes, your right, it's working

    I see what the error is now.
    My external database connection file had extra include files that were modifying the $_POST data. I rewrote a very simple database connection and everything is working just fine now.

    So that solves it...

    Thank You!

    Sample php connection I used, instead of the complicated external database connection.
    PHP Code:

    $dbhost 
    'localhost';
    $dbuser 'xxxxxxx';
    $dbpass 'xxxxxxxxxxx';

    $conn mysql_connect($dbhost$dbuser$dbpass) or die                      ('Error connecting to mysql');

    $dbname 'wordpress';
    mysql_select_db($dbname); 
    Thanks to aamonkey, guido2004, Ethan-27, r937 and Mittineague and everyone else who took the time to look into this. I really appreciate it!

  17. #17
    SitePoint Guru aamonkey's Avatar
    Join Date
    Sep 2004
    Location
    kansas
    Posts
    953
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    glad you got it working
    aaron-fisher.com - PHP articles and more


Tags for this Thread

Bookmarks

Posting Permissions

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