• 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

    • UI is slow but you prefer a slower UI to a snappier UI basically. Everyone is different.
    • XFX 9060 XT 16 GB is $349 on Amazon (shipped & sold by Amazon) https://www.amazon.com/dp/B0F8...p;previewDohDeal=1&th=1 People - never trust any neowin deals posted - they are the worst at seeking deals, and just want to peddle their own to make a quick buck. It's disgusting.
    • It's a grey market key - proceed at your own risk. It's baffling that neowin supports this shady stuff.
    • Arc 1.57.0 by Razvan Serea ARC browser is finally here for Windows, bringing its unique and modern approach to browsing. With a clean interface and a powerful sidebar, ARC makes managing tabs and organizing your workflow a breeze. You can group tabs, pin important pages, and quickly switch between work and personal spaces without clutter. It’s designed to be fast, customizable, and genuinely useful, making it more than just a browser—it’s a productivity tool. If you’re on Windows and want a fresh way to browse and stay organized, ARC is worth checking out. ARC browser key features: Split View: Work with multiple tabs side by side in a single window for efficient multitasking. Tab Grouping: Organize tabs into customizable groups for better workflow management. Pinned Tabs: Keep essential pages permanently accessible in the sidebar. Spaces: Create separate workspaces for different projects or personal use, reducing clutter. Sidebar Integration: Access bookmarks, notes, and tools directly from the sidebar for quick navigation. Customizable Themes: Personalize the browser’s appearance with themes and color schemes. Quick Search: Find tabs, history, or bookmarks instantly with a powerful search bar. Lightning-Fast Performance: Built for speed with optimized resource usage and minimal lag. Built-in Note-Taking: Jot down ideas or save snippets directly within the browser. Focus Mode: Hide distractions and focus on a single tab or task. Cross-Device Syncing: Seamlessly sync your data across multiple devices for a unified experience. Keyboard Shortcuts: Boost productivity with customizable shortcuts for common actions. AI-Powered Suggestions: Get smart recommendations for tabs, bookmarks, and workflows. Privacy Controls: Built-in tools for blocking trackers and managing cookies for enhanced security. Extensions Support: Compatible with popular browser extensions to extend functionality. Arc Browser 1.57.0 release notes: ''Thanks as always for using Arc. This week's release includes a bump to Chromium 137.0.7151.69, which patched three security vulnerabilities. Enjoy!'' Download: Arc Browser 1.57.0 | 1.9 MB (Freeware) View: Arc Browser Website | Screenshot Get alerted to all of our Software updates on Twitter at @NeowinSoftware
    • they put useless AI, new plastick sticker 8K and price +$80 for nothing
  • Recent Achievements

    • First Post
      Uranus_enjoyer earned a badge
      First Post
    • Week One Done
      Uranus_enjoyer earned a badge
      Week One Done
    • 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
  • Popular Contributors

    1. 1
      +primortal
      427
    2. 2
      +FloatingFatMan
      237
    3. 3
      ATLien_0
      213
    4. 4
      snowy owl
      210
    5. 5
      Xenon
      160
  • Tell a friend

    Love Neowin? Tell a friend!