• 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

    • Amazon lays off more staff across Goodreads and Kindle divisions by Hamid Ganji Dozens of Amazon employees working on the retailer's book divisions have been laid off. As reported by Reuters, Amazon confirmed that it's cutting jobs across the Goodreads review site and Kindle units, which impacts fewer than 100 workers. Amazon says the recent layoffs across Goodreads and Kindle divisions are meant to improve efficiency and streamline operations. The giant retailer has constantly reduced staff across various divisions over the past few years. According to CEO Andy Jassy, reducing headcounts helps the company to eliminate bureaucracy. "As part of our ongoing work to make our teams and programs operate more efficiently and to better align with our business roadmap, we've made the difficult decision to eliminate a small number of roles within the Books organization," an Amazon spokesperson said. Layoffs recently impacted employees in Amazon's Wondery podcast division, devices and services units, communications, and in-store staff. However, Amazon's Q1 results show the retailer has added about 4,000 jobs compared to Q4 2024. After the Covid pandemic settled down, many companies began laying off thousands of staff they hired during the pandemic to respond to growing demands. The layoff trend among tech firms still exists today, and AI has amplified it. The latest data shows that in 2025, about 62,832 tech employees were laid off across 141 tech companies. Also, 152,922 tech employees across 551 companies were laid off in 2024. More layoffs are expected to occur due to declining economic growth, tariffs, and the expansion of AI across companies. Amazon is also gearing up to double down in AI investments and robotics. The company has recently announced the forming of a new agentic AI team to develop an agentic AI framework for use in robotics. Also, a new report by The Information indicates that Amazon has begun testing humanoid robots for package delivery.
    • Major Privacy 0.98.1.1 Beta by Razvan Serea MajorPrivacy is a cutting-edge privacy and security tool for Windows, offering unparalleled control over process behavior, file access, and network communication. It is a continuation of the PrivateWin10 project. By leveraging advanced kernel-level protections, MajorPrivacy creates a secure environment where user data and system integrity are fully safeguarded. Unlike traditional tools, MajorPrivacy introduces innovative protection methods that ensure mounted encrypted volumes are only accessible by authorized applications, making it the first and only encryption solution of its kind. MajorPrivacy – Ultimate Privacy & Security for Windows key features Process Protection – Isolate processes to block interference from unauthorized apps, even with admin privileges. Software Restriction – Block unwanted apps and DLLs to ensure only trusted software runs. Revolutionary Encrypted Volumes Secure Storage – Create encrypted disk images for sensitive data. Exclusive Access – Unlike traditional tools, only authorized apps can access mounted volumes—blocking all unauthorized processes. File & Folder Protection – Lock down sensitive files and prevent unauthorized access or modifications. Advanced Network Firewall – Control which apps can send or receive data online. DNS Monitoring & Filtering – Track domain access and block unwanted sites (Pi-hole compatible filtering coming soon). Tweak Engine – Disable telemetry, cloud integration, and invasive Windows features for better privacy. Why MajorPrivacy? Kernel-Level Security – Protects at the deepest system level. Unmatched Encryption Protection – Keeps mounted volumes safe from all unauthorized access. Full System Control – Block, isolate, or restrict processes as needed. Enhanced Privacy – Stops Windows & apps from collecting unnecessary data. Perfect for privacy-conscious users, IT pros, and anyone who wants total system control. Major Privacy 0.98.1.1 Beta changelog: The 0.98.1 release of MajorPrivacy introduces significant enhancements and a number of critical fixes aimed at improving usability, localization, and system integration. A major new feature is the introduction of full translation support, allowing the application interface and tweaks to be localized into multiple languages. Initial translations include AI-assisted German and Polish versions, a community-contributed Turkish translation, and Simplified Chinese. Users interested in contributing translations or adding new languages are encouraged to participate via the forum. This version also improves compatibility and deployment by bundling the Microsoft Visual C++ Redistributable with the installer, which is required for the ImDisk user interface. Several important bugs have been resolved. The installer now correctly removes the driver during uninstallation. Tweak definitions have been cleaned up for better consistency. A number of networking issues were addressed, including failures related to network shares and incorrect handling of mapped drive letters. It is now required to use full UNC paths for defining rules involving shared resources. Additionally, configuration persistence issues on system shutdown have been fixed, as well as problems affecting protected folder visibility and rule precedence involving enclave conditions. Finally, the underlying driver code has been refactored, laying the groundwork for better maintainability and future enhancements. MajorPrivacy-v0.98.1.1.exe (0.98.1a) hotfix for #71 Download: Major Privacy 0.98.1.1 Beta | 47.4 MB (Open Source) View: MajorPrivacy Home Page | Github Project page | Screenshot Get alerted to all of our Software updates on Twitter at @NeowinSoftware
    • OpenAI responds to The New York Times' ChatGPT data demands by Pradeep Viswanathan The New York Times has sued OpenAI for the unauthorized use of its news articles to train large language models. As part of the ongoing lawsuit, the NYT recently asked the court to require OpenAI to retain all ChatGPT user content indefinitely. The NYT's argument is that they may find something in the data that supports their case. Brad Lightcap, COO of OpenAI, wrote the following regarding the NYT's sweeping demand: OpenAI has already filed a motion asking the Magistrate Judge to reconsider the preservation order, since indefinite retention of user data breaches industry norms and its own policies. Additionally, OpenAI has also appealed this order with the District Court Judge. Until OpenAI wins its appeal, it will be complying with the court order. The content defined by the court order will be stored separately in a secure system and will be accessed or used only for meeting legal obligations. Only a small, audited OpenAI legal and security team will be able to access this data as necessary to comply with our legal obligations. As of early 2025, ChatGPT has over 400 million weekly active users, and this data retention order will affect a significant number of them. OpenAI confirmed that ChatGPT Free, Plus, Pro, and Teams subscription users, and developers who use the OpenAI API (without a Zero Data Retention agreement) will be affected by this order. ChatGPT Enterprise, ChatGPT Edu, and API customers who are using Zero Data Retention endpoints will not be affected by this court change.
  • 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
      428
    2. 2
      +FloatingFatMan
      239
    3. 3
      ATLien_0
      212
    4. 4
      snowy owl
      211
    5. 5
      Xenon
      157
  • Tell a friend

    Love Neowin? Tell a friend!