• 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

    • Seconded on the question. This is otherwise great for testing out systems as well.
    • thanks for that yeah its real its not fake guys i got it of amazon  how do i fix this please the good thing its not fake  
    • Oh Ok thank you,   i'll try to trace which one is the front fans and go from there, i forgot i posted on this issue before oopsy.        But they might already be connected right all i know lol.       I tend to worry way too much i think  
    • //merged your topics as they are the same thing. Same answer as well...    
    • Facebook's mobile app is finally getting support for passkeys by Usama Jawad Companies and social media platforms are embracing passkeys more readily nowadays. Microsoft made new accounts passwordless by default just last month, Windows 11 has supported passkeys for quite a while, and Google hasn't been left behind either. Interestingly, while passkeys work on WhatsApp too, Facebook hasn't enjoyed the same functionality, even though both apps are owned by the same parent company, Meta. Well, that changes today. Meta has announced support for passkeys in Facebook's mobile app. For those unaware, passkeys are essentially an alternative for passwords, as they allow you to sign in to a service securely using the same means that you use to unlock your device. This includes biometric authentication mechanisms such as facial and fingerprint recognition, as well as PIN. Since you don't need to memorize or externally store a complex password, and the methods highlighted above can't be easily duped, you effectively become more secure when you leverage passkeys. Passkeys were developed by the FIDO Alliance, and Meta is a member of this collective too. Apart from highlighting the benefits of passkeys, such as being pretty much immune to phishing and password spray attacks, Meta has revealed that they are coming to the Facebook mobile app on Android and iOS. They will also be arriving on Messenger in the next few months, and both services will share the same passkey for a more streamlined experience. Meta will also allow customers to utilize this passkey to securely autofill payment information when leveraging Meta Pay. Customers can head over to the Accounts Center in the Settings area to create and manage their passkey, or alternatively, they'll also be prompted to create one when they log in to Facebook next. It is important to note that Meta isn't doing away with passwords on Facebook just yet, as all devices may not be compatible with passkeys.
  • Recent Achievements

    • First Post
      TIGOSS earned a badge
      First Post
    • 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
  • Popular Contributors

    1. 1
      +primortal
      690
    2. 2
      ATLien_0
      284
    3. 3
      Michael Scrip
      221
    4. 4
      +FloatingFatMan
      197
    5. 5
      Steven P.
      132
  • Tell a friend

    Love Neowin? Tell a friend!