Results 1 to 4 of 4

Thread: single quotes & double quotes insert into mysql

  1. #1
    Join Date
    Mar 2007
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default single quotes & double quotes insert into mysql

    Hi

    I have a php news like script where I can submit news etc. Now in textarea if I have a news that has "kjlk" double quotes like that then it gives out an error when you submit it to be saved in database. How can I make the double quotes or single quotes or any other character that is illegal to be inserted in mysql db to be saved in the mysql database without any error.

    Thanks alot and please do show me with an example. I am lil newbie at all this.

  2. #2
    Join Date
    Mar 2006
    Location
    Illinois, USA
    Posts
    12,164
    Thanks
    265
    Thanked 690 Times in 678 Posts

    Default

    First, this might just be a PHP error.

    Using echo, as an example, here are some problems and solutions:

    echo 'It can't happen.';
    That is wrong because the single quote ends the string.
    You can escape it like this:
    echo 'It can\'t happen.';
    Alternatively, use the other kind of quotes:
    echo "It can't happen.";

    Within double quotes, single quotes are fine; within single, double are.

    Escaping the same quote as used to surround the string is done with a slash.


    Now, for MySQL:
    If you were to include a user defined variable, like from a form, directly into a MySQL database, this is a major security risk. If they were to use as the value: "something; DELETE DATABASE `mydb`;", then this would end the current command and start another, giving that user full control.

    This is an important thing to know, if somewhat unrelated to your current problem.

    The way to solve both, though, is the same. Using mysql_real_escape_string(), you can make this safe, converting characters to escaped data, so it will be inserted but not executed.
    Before using anything in the query, you can change it like so:
    $variable = mysql_real_escape_string($variable);

    You could do this to a portion of the query itself, though it would likely cause trouble for anything that would need to use command characters, like quotes, etc.

    http://php-mysql-tutorial.com is a good site to learn.
    Daniel - Freelance Web Design | <?php?> | <html>| espa˝ol | Deutsch | italiano | portuguŕs | catalÓ | un peu de franšais | some knowledge of several other languages: I can sometimes help translate here on DD | Linguistics Forum

  3. #3
    Join Date
    Nov 2007
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default This is a million dollar hint about the double qoutes issue

    I laboured attempting to insert data from one mysql database to the other...after executing numerous php- mysql codes, i kept running into the problem associated with "double qoute" errors. I then used the stripslash() addslash() html entitities("" enqoute), str_replace() etc. But all in vain the above solutions could only solve one or the other...the errors where either from "html based content" or "double quoted text content", and in other cases the mysql engine was executing key words such as 'from' 'and' 'as' from my data content instead of storing the data.

    After almost six hours of head aching and numerous attempts, a search with "php mysql insert into double qoutes problem" on google brought me here to dynamic drive. With just this simple hint; "mysql_real_escape_string()" my job was done in 2 minutes. Thanks "djr33" for this simplistic yet a very million dollar hint.

    here is the simple but effective php script that I used on www.acholinet.com. It might help some other amatuer like me save time in copying data from one mysql table to the other without running into these so called 'double quotes' etc problems

    <?
    //inlcuded code to connect to mysql database

    include ("connecttodatabse.php");

    //sql query section
    //got data from mysql table text_content to insert in mysql table data_content
    //notice the detail mysql error catch..after all my fustrations

    $count = 0;
    $q1 = "select * from text_content";
    $r1 = mysql_query($q1)or die("<b>A MySQL error occured</b> Query: " . $q1 . " <br /> Error: (" . mysql_errno() . ") " . mysql_error());

    while($a1 = mysql_fetch_array($r1))
    {
    $count++;
    $title = mysql_real_escape_string($a1[title]);
    $catid = mysql_real_escape_string($a1[catid]);
    $introtext = mysql_real_escape_string($a1[introtext]);
    $full_text = mysql_real_escape_string($a1[full_text]);

    //inserted data into the columns of the new table called 'display'

    $q2 = "insert ignore into display set news_subject = \"$title\",
    news_cat = \"$catid\",
    news_news = \"$introtext\",
    news_extended = \"$full_text\" ";
    $f1 = mysql_query($q2) or die("<b>A MySQL error occured at content: $count</b> Query: " . $q2 . " <br /> Error:
    (" . mysql_errno() . ") " . mysql_error());


    }
    echo " Number of records copied is: $count";
    Last edited by acholinet.com; 11-25-2007 at 04:39 PM.

  4. #4
    Join Date
    Mar 2006
    Location
    Illinois, USA
    Posts
    12,164
    Thanks
    265
    Thanked 690 Times in 678 Posts

    Default

    In your mysql statement, you're using double quotes. I don't see the point. Using single quotes should solve it (I think, though I haven't checked everything else), and that's what I've learned to do (though double may be technically valid as well).
    Daniel - Freelance Web Design | <?php?> | <html>| espa˝ol | Deutsch | italiano | portuguŕs | catalÓ | un peu de franšais | some knowledge of several other languages: I can sometimes help translate here on DD | Linguistics Forum

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
  •