Hi Folks,

Can anyone please tell me how I can store special charactors in mysql? Brackets, quotes etc

When I try and and insert them into the db, they dont insert at all.

Many thanks

    It depends on which database extension you are using. If you are using MySQLi or PDO, then use a prepared statement. If you are using the legacy MySQL extension, use mysql_real_escape_string since this is presumably string input to be escaped.

      Thanks for your reply

      I've tried this:

      $query = "INSERT INTO productDim(dimproduct, dimtext) VALUES($lastid, 'mysql_real_escape_string($dimtext)')";

      But it inserts it as a string.

      Can you help?

      Thanks again

        I suggest writing it this way:

        $query = sprintf("INSERT INTO productDim(dimproduct, dimtext) VALUES(%d, '%s')",
    $lastid, mysql_real_escape_string($dimtext));

          Just to elaborate... this:

          $query = "INSERT INTO productDim(dimproduct, dimtext) VALUES($lastid, 'mysql_real_escape_string($dimtext)')";

          is wrong because [man]mysql_real_escape_string/man is a PHP function; you can't simply throw function calls into the middle of a string and expect PHP to recognize and execute them. Instead, use concatenation (or a cleaner solution such as laserlight's [man]sprintf/man example).

            Thanks for your replies.

            Once I have 22 \" 22 \" 22 \" stored in my db.

            Could you tell me how I display the text when I echo out the query like:

            22" 22" 22"

            Thanks again

              Use retrieve and print what you retrieved. You may want to use [man]htmlspecialchars/man to escape special characters in HTML.

                Thanks again for your reply.

                When I echo out the string

                <?php echo htmlspecialchars($myrow["dimText"]); ?>

                I get this:

                22 \" 22 \" 22 \"

                I'd like to get this:

                22" 22" 22"

                Is there a way to strip out the '\'?

                Thanks again

                  Yes, but instead of using it, configure php.ini to turn of magic_quotes_gpc.

                    Thanks for you reply really appreciate it.

                    Unfortunately I cannot edit the php.ini file, my hosts wont give me access.

                    Can you please tell me another way?

                    Thanks again

                      Write a function that uses [man]stripslashes/man on its argument if [man]get_magic_quotes_gpc/man returns true (or 1, or something like that), and then returns the result. Use this function prior to using mysql_real_escape_string().

                        Thanks again for your reply.

                        I only know basic PHP, could i not just do something simple like:

                        $textencode = htmlspecialchars($dimtext, ENT_QUOTES);

                        and to display it:

                        echo htmlspecialchars_decode($myrow["dimText"]);

                        Thanks again

                          Unfortunately, that will not work. htmlspecialchars deals with data that will be displayed on the web browser, but here the data has been escaped in a different way for a different purpose.

                          The problem is that magic_quotes_gpc was a misguided attempt to provide a measure of security against SQL injection by automatically (or "automagically") escaping quotes in incoming variables, with the idea that when these incoming variables are used in SQL statements, they would be escaped, thus avoiding SQL injection.

                          The first flaw in this is that unlike mysql_real_escape_string, this escaping does not take into account the character set used by the database, thus specially crafted input can still be used to perform SQL injection. Then, there is the problem that incoming variables are not necessarily intended to be used in SQL statements, so the escaping would be a waste of time to begin with. This is why I suggested that you simply turn off magic_quotes_gpc, but apparently you cannot do that.

                          The workaround therefore is something like this:

                          function undoMagicQuotes($str)
{
    if (get_magic_quotes_gpc())
    {
        $str = stripslashes($str);
    }
    return $str;
}

                          Now you can write:

                          $sql = sprintf("SELECT id FROM Member WHERE username='&#37;s'",
    mysql_real_escape_string(undoMagicQuotes($_POST['username'])));
                            laserlight;10968429 wrote:

                            but apparently you cannot do that.

                            ...which makes me lose all confidence/trust in this web host being used. :p

                              Write a Reply...