• 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

    • Copilot in Excel just got a major upgrade by Usama Jawad It's no secret that Microsoft is trying to get as many people to use Copilot as possible. This is being done through various means, including integration with OneNote, Defender, Windows, and more. Microsoft Excel is the most popular spreadsheet software out there, so it makes sense that the Redmond tech firm would integrate and build upon the capabilities of Copilot in this tool too. Now, the company has implemented a major upgrade in this integration. In a blog post, Microsoft has made Copilot smarter in terms of context awareness in Excel. This makes the AI assistant more useful when asking questions about your data in nautral language queries. Previously, Copilot would only make inferences based on the cell that you have selected, and it typically worked quite well in this scenario. However, Microsoft has realized that workbooks and sheets have become more complex, which means that Copilot needs to be smarter too. This means that you no longer need to select relevant data when asking queries about it, you can simply ask questions such as "Show me insights about the data I was just analyzing" and "Sort the table in the top-right", and Copilot should just work. Microsoft says that this major upgrade in Copilot is due to combining cell signals with chat history in order to make inferences. It also supports data ranges, which means that it can serve as the foundation for even broader context in future updates. In order to get customers to trust the inferences that Copilot is making, Microsoft will highlight the data that the AI assistant is using. This enables customers to further refine the dataset that Copilot is using so that they can get the most accurate responses. Smart context awareness is available on the web and the following versions of desktop: Windows: Version 2505 (Build 18623.20058) Mac: Version 16.95 (Build 2506.3090) Meanwhile, the visual highlighting feature is also available on the web and the desktop versions mentioned below: Windows: Version 2505 (Build 18705.20000) Mac: Version 16.96 (Build 2506.4070) Microsoft has requested customers to provide feedback through the thumbs up/down icons present at the bottom of the Copilot response cards.
    • We should probably just focus on how nice it is that finally another company is taking reusable rockets seriously. I can't believe there are so few players and many rockets in service today are still completely expendable. Hopefully Honda can scale up theirs and add some badly needed competition. I want to see planes become irrelevant for global travel and we use rockets instead.
    • Yea. It's obviously being deprecated, in case no one noticed or forgot all the announcements and changes. Not such a big deal.
    • The best thing would be to just allow more options to let users pick the layout they like, it doesn't matter at this point really and it's not that hard to do as evidenced but all the 3rd party menus/tools that let you change it in lots of different ways. If you give the users more options they'll be happy, take them away and you have a problem.
  • Recent Achievements

    • Week One Done
      slackerzz earned a badge
      Week One Done
    • Week One Done
      vivetool earned a badge
      Week One Done
    • Reacting Well
      pnajbar earned a badge
      Reacting Well
    • Week One Done
      TBithoney earned a badge
      Week One Done
    • First Post
      xuxlix earned a badge
      First Post
  • Popular Contributors

    1. 1
      +primortal
      684
    2. 2
      ATLien_0
      285
    3. 3
      Michael Scrip
      225
    4. 4
      +FloatingFatMan
      197
    5. 5
      Steven P.
      136
  • Tell a friend

    Love Neowin? Tell a friend!