doubledee Posted August 21, 2011 Share Posted 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 Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted August 21, 2011 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
doubledee Posted August 21, 2011 Author Share Posted 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 Quote Link to comment Share on other sites More sharing options...
Psycho Posted August 21, 2011 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
doubledee Posted August 21, 2011 Author Share Posted 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 Quote Link to comment Share on other sites More sharing options...
darkfreaks Posted August 21, 2011 Share Posted August 21, 2011 because \\ turns into \ when your using stripslashes Quote Link to comment Share on other sites More sharing options...
doubledee Posted August 21, 2011 Author Share Posted August 21, 2011 because // turns into / when your using stripslashes What do forward-slashes have anything to do with this thread? Debbie Quote Link to comment Share on other sites More sharing options...
darkfreaks Posted August 21, 2011 Share Posted August 21, 2011 sorry using the wrong slash obv \ Quote Link to comment Share on other sites More sharing options...
doubledee Posted August 21, 2011 Author Share Posted 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 Quote Link to comment Share on other sites More sharing options...
darkfreaks Posted August 21, 2011 Share Posted 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; Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted August 21, 2011 Share Posted August 21, 2011 You should not use mysqli_real_escape_string when using prepared statements, they are mutually exclusive. Quote Link to comment Share on other sites More sharing options...
doubledee Posted August 21, 2011 Author Share Posted 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 Quote Link to comment Share on other sites More sharing options...
teynon Posted August 21, 2011 Share Posted 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. ) Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted August 21, 2011 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
doubledee Posted August 21, 2011 Author Share Posted 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 Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted August 21, 2011 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
teynon Posted August 21, 2011 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
doubledee Posted August 21, 2011 Author Share Posted 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 Quote Link to comment Share on other sites More sharing options...
darkfreaks Posted August 21, 2011 Share Posted August 21, 2011 if you are using preperared statements there is no need for mysqli_real_escape_string Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted August 21, 2011 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
doubledee Posted August 21, 2011 Author Share Posted 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 Quote Link to comment Share on other sites More sharing options...
doubledee Posted August 21, 2011 Author Share Posted 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 <script>Some evil code here...</script> then htmspecialchars would in essence disable that mark up so that the browser didn't execute anything, right? Debbie Quote Link to comment Share on other sites More sharing options...
teynon Posted August 21, 2011 Share Posted August 21, 2011 doubledee, it would prevent it from executing so long as you don't decode it like you would your admin pages. Quote Link to comment Share on other sites More sharing options...
Psycho Posted August 21, 2011 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
doubledee Posted August 21, 2011 Author Share Posted 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.