SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    SitePoint Member
    Join Date
    Sep 2008
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Record not written when apostrophe in text field?

    I have a number of records in a mysql table that I read and then write using some of the fields to another mysql table.

    One of the fields is a surname with name O'Connor in it and this record does not 'INSERT' into the second table?

    I have used a print command to display records after the 'INSERT' command which displays this record O'Connor properly.

    I have proved it is the apostrophe by replacing it with a space in the input file.

    Fields are all utf8_general_ci in both files.

    Any help would be appreciated?

  2. #2
    SitePoint Addict Latox's Avatar
    Join Date
    Dec 2008
    Location
    Australia
    Posts
    389
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What type of field is it?

    text or varchar?

    I also think ' would ruin a query, as would a "

    Use PHP addslashes

    O\'Connor

    stripslashes

    O'Connor
    :-)

  3. #3
    SitePoint Member
    Join Date
    Sep 2008
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Field is text

    did not know about addslashes and stripslashes so am now investigating whether these will sort out the problem.

    Thanks

  4. #4
    SitePoint Addict Latox's Avatar
    Join Date
    Dec 2008
    Location
    Australia
    Posts
    389
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Change the field type to varchar and then when you insert, do it like this:

    PHP Code:
    $text "O'Connor";
    $text addslashes($text);
    mysql_query("INSERT INTO tablename (name) VALUES ('".$text."')"); 
    When selecting, something like this

    PHP Code:
    $qQuery mysql_query("SELECT name FROM tablename");
    while (
    $result mysql_fetch_array($qQuery)) {
    echo 
    stripslashes($result[name])."<br />";

    If you want to test, try this:

    PHP Code:
    $normal "O'Connor";
    $slashes addslashes($normal);
    $noslashes stripslashes($slashes);
    echo 
    "Input: ".$slashes."<br />";
    echo 
    "Output: ".$noslashes
    Demo: http://www.30.com.au/dev/oconnor.php

    This will add extra security to your site too, simply adds slashes to the inserted values so they insert into mySQL like O\'Connor (rules out SQL injection too) and then when selecting the value, use stripslashes and it'll show as O'Connor
    Last edited by Latox; Feb 28, 2009 at 11:09.
    :-)

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    while php has some functions which take advantage of special mysql "escape" characters, the best practice method of handling this situation is to code two single quotes in a row --
    Code:
    INSERT
      INTO people ( surname )
    VALUES ( 'O''Connor' )
    this is the only method which will work in all database systems

    don't use a proprietary mysql method if a better, standard SQL method exists
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Addict Latox's Avatar
    Join Date
    Dec 2008
    Location
    Australia
    Posts
    389
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    :-)

  7. #7
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    no, do not use addslashes(). you should use whatever escape function is available in the mysql library you're using. for example, if you're using mysql, then use mysql_real_escape_string(). or if you're using mysqli, then use prepared statements.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  8. #8
    SitePoint Addict Latox's Avatar
    Join Date
    Dec 2008
    Location
    Australia
    Posts
    389
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It would be a good idea to use addslashes incase of url escaping.

    I'm more of a PHP coder - limited knowledge of mySQL, but I'm pretty sure addslashes would do the trick too.
    :-)

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    the problem with slashes is that using the slash as the escape character is proprietary to mysql and is not standard SQL

    of course, if you don't care about standards, then knock yourself out
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Addict Latox's Avatar
    Join Date
    Dec 2008
    Location
    Australia
    Posts
    389
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Visitors don't see the backend
    :-)

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    exactly my point -- only the developers see the backend, and why make it harder on yourself than it needs to be?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Addict Latox's Avatar
    Join Date
    Dec 2008
    Location
    Australia
    Posts
    389
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Why not go the extra length and code an admin backend?

    Alot easier than viewing everything through PHPMyAdmin ^_^

    Anyway, thats the way I'd do it, I like being complicated ha.
    :-)

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    who said anything about phpmyadmin? and what does that have to do with using a php function in the application? and what would you call the application if not a backend?

    really, i don't understand what you're going on about...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Addict Latox's Avatar
    Join Date
    Dec 2008
    Location
    Australia
    Posts
    389
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Backend as in viewing the data after insertion.
    :-)

  15. #15
    SitePoint Member
    Join Date
    Sep 2008
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am using PHP on a mysql database and am unlikely to need to port to another database.

    addslashes and stripslashes has resolved my problem for now.

    I really appreciate everyone's help on this and will look at the other method in due course.

    Many Thanks Guys!

  16. #16
    SitePoint Addict Latox's Avatar
    Join Date
    Dec 2008
    Location
    Australia
    Posts
    389
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No problem
    :-)

  17. #17
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by dyfuCa View Post
    It would be a good idea to use addslashes incase of url escaping.

    I'm more of a PHP coder - limited knowledge of mySQL, but I'm pretty sure addslashes would do the trick too.
    then it's time to step up your knowledge.

    addslashes() works most of the time, but the only bullet-proof way to PROPERLY escape data for mysql is to use mysql_real_escape_string() or prepared statements.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast


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
  •