SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,027
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    The inclusion of " ' " is breaking MySQL inserts

    Hello,

    Background:
    We offer a free Blog service.
    so of course what happens is that after someone writes an article, a story, for their Blog it is inserted into a MySQL table.

    I just realized that the inclusion of " ' " in a sentence is breaking MySQL insert commands. For example if a sentence is like: "This won't make it right............", that will break the MySQL Insert
    Whereas "This wont make it right............", is Ok.

    Producing this Error message like:

    There was an Error with your Article Submission: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 't into this ', '

    How do I address this issue/problem?

    Regards,

    Anoox search engine volunteer

    www.anoox.com

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    mysql_real_escape_string() must be used on all values to be placed into a query, unless its a numeric value.

  3. #3
    SitePoint Addict Phidev's Avatar
    Join Date
    Oct 2008
    Location
    Texas
    Posts
    204
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My recommendation would be to use regular expressions and preg_replace() to cover all 'non-usual' characters such as &, ' , ñ, á and so on, to be replaced with their html entity encoding then your ' should be replaced by ´ and & by & this way your code will be valid XHTML. Same for " = "

    Because sooner or later someone will input those values.

    Also I would do what crmalibu suggests just for database sanitizing.

    Good Luck

  4. #4
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Phidev, you can just use HTMLEntities() for that - RegEx would be a bit over the top for this situation (It's not the most efficient of things).

    But that's for output. When it's being input to the database, mysql_escape_real_string() is fine.

    Even better, you could use prepared statements in PDO - then there is no way to inject code at all.
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  5. #5
    SitePoint Wizard Young Twig's Avatar
    Join Date
    Dec 2003
    Location
    Albany, New York
    Posts
    1,355
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by crmalibu View Post
    mysql_real_escape_string() must be used on all values to be placed into a query, unless its a numeric value.
    If it's a numeric value, make sure you do some type of typecasting or something. Typically intval or floatval will do what you want for numbers.

  6. #6
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,027
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Greetings,

    Thanks for your tip.
    But how do I actually use this?

    I mean lets say that the body of the article is:

    $body = $_POST['body']

    and it is inserted into the MySQL table with an insert command:

    INSERT INTO xyz (body, user, ip) VALUES ('$body', '$user_id', '$ip')

    So how do I use the command that you have suggested to remove the unwanted characters that are going to break MySQL before inserting
    the $body into this MySQL table?

    Regards,

    Quote Originally Posted by crmalibu View Post
    mysql_real_escape_string() must be used on all values to be placed into a query, unless its a numeric value.

    Anoox search engine volunteer

    www.anoox.com

  7. #7
    SitePoint Wizard
    Join Date
    Mar 2008
    Posts
    1,149
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    "INSERT INTO xyz (body, user, ip) VALUES ('".mysql_real_escape_string($body)."', '".mysql_real_escape_string($user_id)."', '".mysql_real_escape_string($ip)."')" 
    Although I recommend using PDO and prepared statements/binding your parameters, because it lowers the chance of you accidentally introducing an exploit (it happens...), and for the sheer fact that reading it in your code is friendlier on the eyes.

    If you are unable or unwilling, you can use my http://php.net/manual/en/function.mysql-query.php#81188 as a compromise.

  8. #8
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by WorldNews View Post
    So how do I use the command that you have suggested to remove the unwanted characters that are going to break MySQL before inserting
    the $body into this MySQL table?
    The php manual offers documentation, and often examples on how to use functions. There's usually also tons of user contributed notes which offer additional information.

  9. #9
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,027
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    So mysql_real_escape_string is a PHP function.
    I was not clear about this from your 1st message.
    Now that I know it is a function then obviously that answers
    the question. And FYI: it works fine of course.

    Now about the rest of your message: why are you suggesting
    that I use your php code listed via the URL you have provided?
    What sort of (real) danger, by example please, can mysql_real_escape_string
    lead to?

    Regards,


    Quote Originally Posted by sk89q View Post
    PHP Code:
    "INSERT INTO xyz (body, user, ip) VALUES ('".mysql_real_escape_string($body)."', '".mysql_real_escape_string($user_id)."', '".mysql_real_escape_string($ip)."')" 
    Although I recommend using PDO and prepared statements/binding your parameters, because it lowers the chance of you accidentally introducing an exploit (it happens...), and for the sheer fact that reading it in your code is friendlier on the eyes.

    If you are unable or unwilling, you can use my http://php.net/manual/en/function.mysql-query.php#81188 as a compromise.

    Anoox search engine volunteer

    www.anoox.com

  10. #10
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,027
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Real funny
    Of course I know how to use a function.
    I was just not clear that mysql_real_escape_string was a function.
    In hindsight I should have been sure that it was a function.
    I just needed to make sure.

    Thanks anyway.

    Quote Originally Posted by crmalibu View Post
    The php manual offers documentation, and often examples on how to use functions. There's usually also tons of user contributed notes which offer additional information.

    Anoox search engine volunteer

    www.anoox.com


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
  •