SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2006
    Posts
    91
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to insert things into a MySQL database with an ' in?

    Hello,
    When I run this code:
    PHP Code:
    $sql55="INSERT INTO users (username, password, ebay_username, ebay_status, ebay_email, paypal_status, paypal_email, num_purchases, first_name, last_name, address_street, address_city, address_state, address_zip, address_country, address_status, created) VALUES('".$row->account_username."', '".$row->account_password."', '".$row->auction_buyer_id."', '$ebay_status', '".$row->account_email."', '".$row->payer_status."', '".$row->account_email."', 1, '".$row->first_name."', '".$row->last_name."', '".$row->address_street."', '".$row->address_city."', '".$row->address_state."', '".$row->address_zip."', '".$row->address_country."', '".$row->address_status."', '$new_script_received')"
    $result55 mysql_query($sql55) or die( mysql_error() ); 
    I get the 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 'S ROAD',"

    Now it seams like the problem is this field "51 ST SIMONS'S ROAD" and I think the problem is the ', correct me if I'm wrong. How can I solve this problem? (It is a lot of rows I'm running this querey on so I can't just insert it manually).

    Thanks in advance,
    Best Regards
    Oskar R

  2. #2
    SitePoint Author silver trophybronze trophy

    Join Date
    Nov 2004
    Location
    Ankh-Morpork
    Posts
    12,159
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Birnam wood is come to Dunsinane

  3. #3
    SitePoint Wizard bronze trophy Kailash Badu's Avatar
    Join Date
    Nov 2005
    Posts
    2,561
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Now it seams like the problem is this field "51 ST SIMONS'S ROAD" and I think the problem is the ', correct me if I'm wrong.
    It is unlikely to be the cause of the problem.
    print the variable $sql55 to see the exact query that is being executed.

  4. #4
    SitePoint Author silver trophybronze trophy

    Join Date
    Nov 2004
    Location
    Ankh-Morpork
    Posts
    12,159
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Kailash Badu View Post
    It is unlikely to be the cause of the problem.
    On the contrary: it is very likely to be the cause of the problem.

    The INSERT statment is created by string concatenation, enclosing each value in single quotes. If such a value then contains a single quote, you will get a SQL syntax error.

    Each value (like $row->account_username etc.) needs to be passed through mysql_real_escape_string() to make it 'safe' to use in this manner.
    Birnam wood is come to Dunsinane

  5. #5
    SitePoint Wizard bronze trophy Kailash Badu's Avatar
    Join Date
    Nov 2005
    Posts
    2,561
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by AutisticCuckoo
    The INSERT statment is created by string concatenation, enclosing each value in single quotes. If such a value then contains a single quote, you will get a SQL syntax error.
    I failed to see the point previously. you are correct. For some odd reasons, I made out that the OP was using double quotes inside the string. single quotes would definetly trigger error.

  6. #6
    SitePoint Author silver trophybronze trophy

    Join Date
    Nov 2004
    Location
    Ankh-Morpork
    Posts
    12,159
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Kailash Badu View Post
    For some odd reasons, I made out that the OP was using double quotes inside the string.
    It's easy to do. Basically, the code is an assignment like this:
    PHP Code:
    $x "...'" $a "', '" $b "'..."
    It needs to be protected this way:
    PHP Code:
    $x "...'"
       
    mysql_real_escape_string($a)
       . 
    "', '"
       
    mysql_real_escape_string($b)
       . 
    "'..."
    Birnam wood is come to Dunsinane

  7. #7
    SitePoint Zealot backtobasics's Avatar
    Join Date
    Aug 2006
    Posts
    196
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Before I insert into a DB, I use

    PHP Code:
    str_replace("'""& # 3 9"$variable); 
    This will output a ' on a page again.

    Remember and remove the spaces


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
  •