• 0

[PHP] Insert into MySQL Removing \n and \r


Question

Howdy Folks,

I have an issue that I'm sure there is a simple answer for. I have some data I'm inserting into a MySQL database. Here is a sample of data being inserted:

alert tcp $EXTERNAL_NET any -> $HTTP_SERVERS $HTTP_PORTS (msg:"WEB-IIS Malformed Hit-Highlighting Argument File Access Attempt"; flow:to_server,established; uricontent:"CiWebHitsFile="; nocase; pcre:"/CiWebHitsFile=\/?([^\r\n\x3b\&]*\.\.\/)?/i"; uricontent:"CiRestriction=none"; nocase; uricontent:"ciHiliteType=Full"; nocase; reference:bugtraq,950; reference:cve,2000-0097; reference:url,www.microsoft.com/technet/security/bulletin/ms00-006.mspx; reference:url,www.securityfocus.com/archive/1/43762; classtype:web-application-attack; sid:1019; rev:17;)

The problem I'm having is that when I insert this data in the DB, PHP is reading the \r and \n and similar type things and stripping it out of the text. Is there a way to prevent it from actually interpreting the newline character and just inserting it as text into the DB? I've searched around and messed with addslashes, stripslashes, etc, but I can't seem to figure it out.

For reference, here is the code block that I'm using for the insert:

for ($i=0; $i < count($rules); $i++) {        
        preg_match($sidregex, $rules[$i], $matches);
        $sid = trim($matches[0], 'sid:;');
    $qry_addrulestodb = "INSERT INTO sp_rules SET sid = '$sid', rule = '$rules[$i]'";
    $qry_delbadrecs = "DELETE FROM sp_rules where sid=0";

    if (!$queryresource = mysql_query($qry_addrulestodb, $dbconn)) {
        trigger_error('Query Error ' . mysql_error() . ' SQL: ' . $sql);
    }
    if (!$queryresource = mysql_query($qry_delbadrecs, $dbconn)) {
        trigger_error('Query Error ' . mysql_error() . ' SQL: ' . $sql);
    }  

11 answers to this question

Recommended Posts

  • 0
  On 21/04/2011 at 15:21, Maverick88 said:

I don't really have the option of manipulating the input file since it comes from an outside source.

But you have to pull this data in somewhere before you put it in the DB, right? Can't you use a replace to replace any \n with \\n? I'm a bit rusty on php, but str_replace("\","\\",your_var) seems like it would do the trick.

Actually, Dogward posted before me with the mysql_real_escape_string function which is what I think you actually want to use.

http://php.net/manual/en/function.mysql-real-escape-string.php

  • 0
  On 21/04/2011 at 15:25, Dogward said:

have you tried using

mysql_real_escape_string()

or maybe

htmlspecialchars()

??

I have, with no positive results. This is what is getting populated into the database from that input.

alert tcp $EXTERNAL_NET any -> $HTTP_SERVERS $HTTP_PORTS (msg:"WEB-IIS Malformed Hit-Highlighting Argument File Access Attempt"; flow:to_server,established; uricontent:"CiWebHitsFile="; nocase; pcre:"/CiWebHitsFile=/?([^
x3b&]*../)?/i"; uricontent:"CiRestriction=none"; nocase; uricontent:"ciHiliteType=Full"; nocase; reference:bugtraq,950; reference:cve,2000-0097; reference:url,www.microsoft.com/technet/security/bulletin/ms00-006.mspx; reference:url,www.securityfocus.com/archive/1/43762; classtype:web-application-attack; sid:1019; rev:17;)

  • 0
  On 21/04/2011 at 15:32, Maverick88 said:

I have, with no positive results. This is what is getting populated into the database from that input.

alert tcp $EXTERNAL_NET any -> $HTTP_SERVERS $HTTP_PORTS (msg:"WEB-IIS Malformed Hit-Highlighting Argument File Access Attempt"; flow:to_server,established; uricontent:"CiWebHitsFile="; nocase; pcre:"/CiWebHitsFile=/?([^
x3b&]*../)?/i"; uricontent:"CiRestriction=none"; nocase; uricontent:"ciHiliteType=Full"; nocase; reference:bugtraq,950; reference:cve,2000-0097; reference:url,www.microsoft.com/technet/security/bulletin/ms00-006.mspx; reference:url,www.securityfocus.com/archive/1/43762; classtype:web-application-attack; sid:1019; rev:17;)

I'll dig around on this a bit more. It may be possible I'm just putting those functions in the wrong place.

  • 0

if using mysql_real_escape_string(), you could put it directly in the query ( "INSERT INTO table (my_field) VALUE (mysql_real_escape_string($my_var))" ) (although not the cleanest thing to do... ;-) )

it really should populate all visible and invisible characters correctly...

  • 0
  On 21/04/2011 at 15:43, Dogward said:

if using mysql_real_escape_string(), you could put it directly in the query ( "INSERT INTO table (my_field) VALUE (mysql_real_escape_string($my_var))" ) (although not the cleanest thing to do... ;-) )

it really should populate all visible and invisible characters correctly...

You're absolutely right. Turns out I was just implementing the function in the wrong place this whole time. Many thanks for the help.

Updated code block is here just in case somebody finds their way to this page with a similar question via Google:

for ($i=0; $i < count($rules); $i++) {		
        preg_match($sidregex, $rules[$i], $matches);
        $sid = trim($matches[0], 'sid:;');
        $saferule = mysql_real_escape_string($rules[$i]);
	$qry_addrulestodb = "INSERT INTO sp_rules SET sid = '$sid', rule = '$saferule'";
	$qry_delbadrecs = "DELETE FROM sp_rules where sid=0";

	if (!$queryresource = mysql_query($qry_addrulestodb, $dbconn)) {
		trigger_error('Query Error ' . mysql_error() . ' SQL: ' . $sql);
	}
	if (!$queryresource = mysql_query($qry_delbadrecs, $dbconn)) {
		trigger_error('Query Error ' . mysql_error() . ' SQL: ' . $sql);
	}

  • 0

Well I'm a PHP noob myself, but here's something I tend to use after following the lynda.com tutorials.

function mysql_prep($value) { // Prevents invalid database insertions
	$magic_quotes_active = get_magic_quotes_gpc();
	$new_enough_php = function_exists('mysql_real_escape_string'); // i.e. PHP >= v4.3.0

	if($new_enough_php) { // PHP v4.3.0 or higher
		// undo any magic quote effects so mysql_real_escape_string can do the work
		if ($magic_quotes_active) { $value = stripslashes($value); }
		$value = mysql_real_escape_string($value);
	} else { // before PHP v4.3.0
		// if magic quotes aren't already on then add slashes manually
		if (!$magic_quotes_active) { $value = addslashes($value); }
		// if magic quotes are active, then the slashes already exist
	}

	return $value;
}

  • 0
  On 21/04/2011 at 15:50, Sir Ali said:

Well I'm a PHP noob myself, but here's something I tend to use after following the lynda.com tutorials.

function mysql_prep($value) { // Prevents invalid database insertions
	$magic_quotes_active = get_magic_quotes_gpc();
	$new_enough_php = function_exists('mysql_real_escape_string'); // i.e. PHP >= v4.3.0

	if($new_enough_php) { // PHP v4.3.0 or higher
		// undo any magic quote effects so mysql_real_escape_string can do the work
		if ($magic_quotes_active) { $value = stripslashes($value); }
		$value = mysql_real_escape_string($value);
	} else { // before PHP v4.3.0
		// if magic quotes aren't already on then add slashes manually
		if (!$magic_quotes_active) { $value = addslashes($value); }
		// if magic quotes are active, then the slashes already exist
	}

	return $value;
}

Very nice! Thanks for sharing. I'll be implementing this moving forward.

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Posts

    • How can all of these Neowin deals be "an all time low price". I find that pretty hard to believe.
    • Oh wow, a patch for an Unreal game to improve performance? We'll see...
    • Get this massive 4TB WD_BLACK SN7100 SSD at a new all-time low price by Taras Buria Xbox owners recently received a new storage upgrade option, which allows them to equip the Xbox Series X|S with 4TB of extra space. That card, however, has an eye-watering price tag—nearly as much as the 1TB Xbox Series S. On the PC side, though, things are much better. Right now, if you need a 4TB SSD without selling a kidney, you can get the WD_BLACK SN7100 PCIe Gen4 SSD. It is now available on Amazon at a new all-time low price after a 16% discount (nearly half the cost of the new 4TB Xbox Storage Expansion Card). The SN7100 is a fast, reliable, and, more importantly, affordable Gen4 solid-state drive. Its speeds are rated up to 7,000MB/s sequential read and 6,700MB/s sequential write, while random input-output speeds are rated at 900K IOPS read and 1,350K IOPS write. These specs are not record-breaking, but they are fast enough for modern gaming (DirectStorage is supported), fast loading times in games and apps, and quick file transfer. The WD_BLACK SN7100 has a limited five-year warranty and endurance rating up to 2,400 TBW. You can monitor the health of your drive in the WD_Black Dashboard app on Windows. As for compatibility, you can use the SN7100 in any PC that accommodates M.2 2280 PCIe Gen4 drives, including PlayStation 5. It is compatible with PCIe Gen3 systems, but the maximum speeds will be limited. 4TB WD_BLACK SN7100 PCIe Gen4 Solid-State Drive - $249.99 | 16% off on Amazon US This Amazon deal is US-specific and not available in other regions unless specified. If you don't like it or want to look at more options, check out the Amazon US deals page here. Get Prime (SNAP), Prime Video, Audible Plus or Kindle / Music Unlimited. Free for 30 days. As an Amazon Associate, we earn from qualifying purchases.
    • Snagit 2025.2.0 by Razvan Serea Snagit is the most complete screen capture utility available. Showing someone exactly what you see on your screen is sometimes the quickest and clearest way to communicate. With Snagit, you can select anything on your screen – an area, image, article, Web page, or error message – and capture it. Then, save the screen capture to a file, send it to Snagit​'s editor to add professional effects, share it by e-mail, or drop it into PowerPoint®, Word®, or another favorite application. Capture and share images, text or video from your PC. Create beautiful presentations, flawless documentation and quickly save online content. The latest version of Snagit offers a totally new interface and workflow - making SnagIt easier for beginners to use, while still providing maximum convenience and flexibility for the screen capture experts. Snagit 2025.2.0 changelog: Edit Images from Camtasia Snagit can now be used to seamlessly edit images from Camtasia. Requires Camtasia version 2025.2.0 or later. In the Camtasia Media Bin, right-click an image and select the Edit in Snagit option. In Snagit, make your edits. When finished, click Send in the "Send changes to Camtasia" notification to replace the image on your Camtasia timeline. Step Capture Improved the Step Capture template to accommodate longer auto-populated step text. Annotations such as Callout or Arrow tool objects now anchor to and move with sections as sections are added to, reordered, or deleted from templates. Subscription Software Updated the Account dropdown to open automatically when new subscription related messages are present. Performance Improvements Improved the startup time for Snagit Capture application. Updates for IT Administrators Updated BouncyCastle.Cryptography package to address CVE-2024-29857 and CVE-2024-30172. Removed dependency on Xceed Zip. Updated LeadTools DLLs. Fixed an issue where Snagit might not recognize offline subscription activation. Fixed an issue where the video recording toolbar could be hidden from users when using Snagit in virtual environments or with remote desktop applications. Bug Fixes Fixed an issue where using Step Capture with the Share destination set to File in the Capture Window could result in data loss. Fixed a crash that could occur when capturing on some HDR monitors. Fixed an issue where the cursor object in a capture might include some of the background image in certain situations. Fixed an issue with the Box share destination to use the default browser for authentication. Fixed an issue where the Blur tool Color property could show when the blur or pixelate Type was selected. Fixed an issue where the privacy policy link in Snagit's installer might not open in the expected language. (PONRPD) Download: Snagit 64-bit | 419.0 MB (Shareware) Links: SnagIt Home Page | Release Notes Get alerted to all of our Software updates on Twitter at @NeowinSoftware
  • Recent Achievements

    • Week One Done
      jfam earned a badge
      Week One Done
    • First Post
      survivor303 earned a badge
      First Post
    • Week One Done
      CHUNWEI earned a badge
      Week One Done
    • One Year In
      survivor303 earned a badge
      One Year In
    • Week One Done
      jbatch earned a badge
      Week One Done
  • Popular Contributors

    1. 1
      +primortal
      420
    2. 2
      +FloatingFatMan
      185
    3. 3
      snowy owl
      183
    4. 4
      ATLien_0
      179
    5. 5
      Xenon
      140
  • Tell a friend

    Love Neowin? Tell a friend!