Why is this causing Escape characters in my DB?

doubledee · August 21, 2011

I am having problems understanding how to sanitize my form data so it is safe to INSERT.

If I use this code...

$trimmed = array_map('trim', $_POST);

$body = mysqli_real_escape_string($dbc, $trimmed['body']);

And I enter into my form...

O'Reilly's book

Then I see this in phpMyAdmin...

O\'Reilly\'s book

---------

If I use this code...

$trimmed = array_map('trim', $_POST);

$body = $trimmed['body'];

And I enter into my form...

O'Reilly's book

Then I see this in phpMyAdmin...

O'Reilly's book

Why the strange behavior??

Debbie

Pikachu2000 · August 21, 2011

It's likely that magic_quotes_gpc is set to On in your php.ini file, thus causing everything to be double-escaped.

doubledee · August 21, 2011

It's likely that magic_quotes_gpc is set to On in your php.ini file, thus causing everything to be double-escaped.

According to phpinfo()...

magic_quotes_gpc Off Off

So what else could be the issue?

Debbie

Psycho · August 21, 2011

Echo the values before and after each operation to determine if something is adding the backslashes or if it is coming from the source.

doubledee · August 21, 2011

Echo the values before and after each operation to determine if something is adding the backslashes or if it is coming from the source.

Here is an abridged version of my code...

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">

<head>
</head>

<body>
    <div id="wrapper" class="clearfix">
        <div id="inner">
            <?php
                // <!-- Include BODY HEADER -->

                // Initialize Errors Array.
                $errors = array();

                // Connect to the database.

                // *******************************
                // HANDLE FORM.                 *
                // *******************************
                if ($_SERVER['REQUEST_METHOD']=='POST'){
                    // Form was Submitted (Post).

                    // Trim all form data.
                    $trimmed = array_map('trim', $_POST);


                    // ********************
                    // CHECK FORM DATA.    *
                    // ********************
                    // Check Article Title.
                    if (empty($trimmed['articleTitle'])){
                        $errors['articleTitle'] = 'Please enter an Article Title.';
                    }else{
                        // Not empty.
                        if (preg_match('#^[a-z-]{2,100}$#', $trimmed['articleTitle'])){
                            // Valid characters.

                            // *********************
                            // Check Title Availability.    *
                            // *********************

                            // Build query.
                            $q = 'SELECT article_title
                                        FROM article
                                        WHERE article_title=?';

                            // Prepare statement.
                            $stmt = mysqli_prepare($dbc, $q);

                            // Bind variable.
                            mysqli_stmt_bind_param($stmt, 's', $trimmed['articleTitle']);

                            // Execute query.
                            mysqli_stmt_execute($stmt);

                            // Transfer result set from prepared statement.
                            // (Required for all queries that return results.)
                            mysqli_stmt_store_result($stmt);

                            if (mysqli_stmt_num_rows($stmt)==0){
                                // Unique Article Title.
                                // Escape problematic characters.
                                $articleTitle = mysqli_real_escape_string($dbc, $trimmed['articleTitle']);
                            }else{
                                // Duplicate Article Title.
                                $errors['articleTitle'] = 'This Article Title is already taken.  Please choose a unique one.';
                            }
                        }else{
                            // Invalid entry.
                            $errors['articleTitle'] = 'Article Title must be 2-100 characters (a-z -)';
                        }
                    }


                    // Check Article Body.
                    if (empty($trimmed['body'])){
                        $errors['body'] = 'Please enter an Article Body.';
                    }else{
                            // Escape problematic characters.
                            $body = $trimmed['body'];
//                            $body = mysqli_real_escape_string($dbc, $trimmed['body']);
                    }


                    // Check for Data-Entry Errors.
                    if (empty($errors)){
                        // Form data clean.

                        // Add a new Article.
                        // Build query.

                        $q = "INSERT INTO article(article_title, html_title, meta_description, meta_keywords,
                                                                            page_title, page_subtitle, written_on, author,
                                                                            body, reference_listing, endnote_listing, created_on)
                                        VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())";

                        // Prepare statement.
                        $stmt = mysqli_prepare($dbc, $q);

                        // Bind variable.
                        mysqli_stmt_bind_param($stmt, 'sssssssssss',
                                                                                        $articleTitle, $htmlTitle, $metaDescription, $metaKeywords,
                                                                                        $pageTitle, $pageSubtitle, $writtenOn, $author,
                                                                                        $body, $referenceListing, $endnoteListing);

                        // Execute query.
                        mysqli_stmt_execute($stmt);

                        // Verify Insert.
                        if (mysqli_stmt_affected_rows($stmt)==1){
                            // Insert Succeeded.

                            echo '<div id="box_Content_700b">';
                            echo    '<h1>Article Added</h1>';
                            echo    '<p>Congratulations!</p>
                                            <p>The article: "' . stripslashes($pageTitle) . '" has been added to the database.</p>';
                            echo '</div>';
                        }else{
                            // Insert Failed.
                            echo '<div id="box_Content_700b">';
                            echo    '<h1>Add Article Error</h1>';
                            echo    '<p>The article could not be added due to a system error.</p>';
                            echo '</div>';
                        }// End of VERIFY INSERT.

                        // Close prepared statement.
                        mysqli_stmt_close($stmt);

                        // Close the connection.
                        mysqli_close($dbc);

Debbie

darkfreaks · August 21, 2011

because \\ turns into \ when your using stripslashes

doubledee · August 21, 2011

because // turns into / when your using stripslashes

What do forward-slashes have anything to do with this thread?

Debbie

darkfreaks · August 21, 2011

sorry using the wrong slash obv \ :shrug:

doubledee · August 21, 2011

sorry using the wrong slash obv \

Okay, but where are you getting the double backslashes?

When I look in phpMyAdmin I see...

Debbie\'s Mother\'s Sister\'s i.e. Debbie\'s Aunt\'s name is Debbie also

I thought mysqli_real_escape_string was *not* supposed to embed backslashes in with my data in the database?

Debbie

darkfreaks · August 21, 2011

Returns a string with backslashes stripped off. (\' becomes ' and so on.) Double backslashes (\\) are made into a single backslash (\).

Solution:

function smartstripslashes($str) {
  $cd1 = substr_count($str, "\"");
  $cd2 = substr_count($str, "\\\"");
  $cs1 = substr_count($str, "'");
  $cs2 = substr_count($str, "\\'");
  $tmp = strtr($str, array("\\\"" => "", "\\'" => ""));
  $cb1 = substr_count($tmp, "\\");
  $cb2 = substr_count($tmp, "\\\\");
  if ($cd1 == $cd2 && $cs1 == $cs2 && $cb1 == 2 * $cb2) {
    return strtr($str, array("\\\"" => "\"", "\\'" => "'", "\\\\" => "\\"));
  }
  return $str;

PFMaBiSmAd · August 21, 2011

You should not use mysqli_real_escape_string when using prepared statements, they are mutually exclusive.

doubledee · August 21, 2011

You should not use mysqli_real_escape_string when using prepared statements, they are mutually exclusive.

Other people and articles have led me to believe that Prepared Statements won't protect against embedded quotes inside the bound data parameters (e.g. $body = "I went to Frank's Diner and we used Sally's Dad's credit card to pay!")

That is why I used mysqli_real_escape_string right before I assigned my form input to a variable which would in turn be INSERTed.

Debbie

teynon · August 21, 2011

I'm just going to throw this out there, but if you don't want those backslashes, you could do what the others were saying in your comment validation post, with an additional modifier for quotes before you prepare the sql statement.

htmlspecialchars($value, ENT_QUOTES); This will convert quotes to " and whatever the other single quote one converts to. (I don't remember. )

PFMaBiSmAd · August 21, 2011

Other people and articles have led me to believe that Prepared Statements won't protect against embedded quotes

And when you tried this yourself (putting quotes or injected sql into a string data field using prepared statements without using the mysqli_real_escape_string function), what result did you observe? There's a huge amount of incorrect programming related and every other kind of information posted on the Internet, because anyone now days can become an author and post something that he thinks is true, not supported by actual testing and observed results. The best way to confirm anything in programming is to make a test case, run it, and observe the results yourself.

doubledee · August 21, 2011

I'm just going to throw this out there, but if you don't want those backslashes, you could do what the others were saying in your comment validation post, with an additional modifier for quotes before you prepare the sql statement.

htmlspecialchars($value, ENT_QUOTES); This will convert quotes to " and whatever the other single quote one converts to. (I don't remember. )

It sounds like my Prepared Statement combined with using mysqli_real_escape_string are causing "double escaping" (\\) and so I just need to use one or the other.

Debbie

PFMaBiSmAd · August 21, 2011

@teynon, the issue isn't the quote, it's the \ characters being inserted into the database table.

When data is escaped properly (only once) the \ characters won't be present in the actual database table.

teynon · August 21, 2011

PFMaBiSmAd: Yes, however if the quote doesn't exist, it doesn't need escaped. This would allow the code to work on servers even if the evil magic quotes was enabled and would prevent double slashes on quotes as well. However it would not fix whatever other characters are double escaped.

doubledee · August 21, 2011

Other people and articles have led me to believe that Prepared Statements won't protect against embedded quotes

And when you tried this yourself (putting quotes or injected sql into a string data field using prepared statements without using the mysqli_real_escape_string function), what result did you observe? There's a huge amount of incorrect programming related and every other kind of information posted on the Internet, because anyone now days can become an author and post something that he thinks is true, not supported by actual testing and observed results. The best way to confirm anything in programming is to make a test case, run it, and observe the results yourself.

Well, when I used Prepared Statements I was expecting to see escape characters...

Debbie\'s Mother\'s Sister\'s name is Debbie too!

...but I didn't see any escaping, so I added mysqli_real_escape_string to be doubly-sure.

I didn't realize that my Prepared Statement was doing some kind of escaping even though I couldn't see it in phpMyAdmin?!

Again, wrong information online combined with my lack of knowledge...

Debbie

darkfreaks · August 21, 2011

if you are using preperared statements there is no need for mysqli_real_escape_string

PFMaBiSmAd · August 21, 2011

I was expecting to see escape characters...

Hopefully, this will clear up the problem -

When data is escaped properly (only once) the \ characters won't be present in the actual database table.

doubledee · August 21, 2011

if you are using preperared statements there is no need for mysqli_real_escape_string

I know that now after losing a week of my life?!

Debbie

doubledee · August 21, 2011

I was expecting to see escape characters...

Hopefully, this will clear up the problem -

When data is escaped properly (only once) the \ characters won't be present in the actual database table.

So I was apparently right in choosing to use Prepared Statements and that takes care of escaping quotes during INSERTS. And that combined with doing some data sanitizing in my pre-INSERT form handling, it sounds like that is all I need for my Admin form that allows me to enter a article into my database...

When I switch to my "Add a Comment" form for Members commenting on the Articles I just put in my database, then I guess I also have to use htmlspecialchars when I output their entires to ensure that someone doesn't enter markup into the Comments field and attempt to do something funky.

So if a Member entered

Debbie

teynon · August 21, 2011

doubledee, it would prevent it from executing so long as you don't decode it like you would your admin pages.

Psycho · August 21, 2011

htmlspecialchars($value, ENT_QUOTES); This will convert quotes to " and whatever the other single quote one converts to. (I don't remember. )

You need to be careful before converting input into a display dependent format. There are a several drawbacks to doing that you need to either be aware of or actively code around. For one, if you ever wanted to use that data for some other purpose than in a web page you would have to unencode it, which is not a 100% guaranteed process. 2) You will have to create your database fields to be much larger than the maximum allowed length for the user. There is no way to tell how many characters the user will enter that may be converted to five/six character encoded values. So, you either need to make the DB fields excessively large or risk losing some of the user input.

doubledee · August 21, 2011

htmlspecialchars($value, ENT_QUOTES); This will convert quotes to " and whatever the other single quote one converts to. (I don't remember. )

You need to be careful before converting input into a display dependent format. There are a several drawbacks to doing that you need to either be aware of or actively code around. For one, if you ever wanted to use that data for some other purpose than in a web page you would have to unencode it, which is not a 100% guaranteed process. 2) You will have to create your database fields to be much larger than the maximum allowed length for the user. There is no way to tell how many characters the user will enter that may be converted to five/six character encoded values. So, you either need to make the DB fields excessively large or risk losing some of the user input.

From what I have gathered, I should only use Prepared Statements or mysqli_real_escape_string BEFORE inserting data into my database.

That is, don't use something like htmlspecialchars on the way in. You should only do that when outputting the data.

Debbie

Sign In

Why is this causing Escape characters in my DB?

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information