Problem with extra slashes inserted into database. 2 parts

mod pls delete previous post which was not detailed or clear enough.

ok I spent almost 2 hours editing my program and creating this example to make it as simple as possible so theres not so much code its overwhelming. Everything works fine except for the @#%@#% extra slashes. magic quotes is not enabled.

I’m having a problem with extra slashes being added when i insert into a database. There are 3 files that show the problem. I’m so desperate to figure this out pulling my hair out. There seems like a lot of code but it’s pretty basic coding and i Highlighted the key parts.

The first file problempart1.php inserts the initial 2 rows contained in $foodArray into the database. The function fix_quotes is used to escape all of the values. Yes I know I shouldn’t use addslashes but just for this example.


<?php
if(get_magic_quotes_gpc())
	echo "Magic quotes are enabled<br/>";
else
	echo "Magic quotes are disabled<br/>";

function fix_quotes( $value ) {
	 if ( get_magic_quotes_gpc()==1 ) {
		 return $value;
	 } else {
		 return addslashes($value); 
	 }
}

@ $db = mysqli_connect('localhost', 'root', '', 'mealchamp');

$foodArray = array( array("Honey Nut O's Cereal",	"Generic", 	"Grain Products", "", 						"30 g", 		120, 	0.5,  0,   "",  "",    0,   0,  150, 26,  2, 		  12, 	  2		),
                    array("Teriyaki Style Rice",	"Generic", 	"Grain Products",	"1/45 package", "0.5 g",		160,  0.5,  0.2, "",  "",   "",   0,  520, 34,  0,       4,     3   ));

$colStr = "(food, brand, ftype, servamt, ss, cal, fat, sat, unsat, monosat, trans, chol, sod, carb, fib, sug, pro)";

$valStr = "";
$valStr = $valStr . "('" . fix_quotes($foodArray[0][0]) . "', '" . fix_quotes($foodArray[0][1]) . "', '" . fix_quotes($foodArray[0][2]) . "', '" . fix_quotes($foodArray[0][3]);
$valStr = $valStr . "', '" . fix_quotes($foodArray[0][4]) . "', '" . fix_quotes($foodArray[0][5]) . "', '" . fix_quotes($foodArray[0][6]) . "', '" . fix_quotes($foodArray[0][7]);
$valStr = $valStr . "', '" . fix_quotes($foodArray[0][8]) . "', '" . fix_quotes($foodArray[0][9]) . "', '" . fix_quotes($foodArray[0][10]) . "', '" . fix_quotes($foodArray[0][11]);
$valStr = $valStr . "', '" . fix_quotes($foodArray[0][12]) . "', '" . fix_quotes($foodArray[0][13]) . "', '" . fix_quotes($foodArray[0][14]) . "', '" . fix_quotes($foodArray[0][15]);
$valStr = $valStr . "', '" . fix_quotes($foodArray[0][16]) . "')";
$multiValStr = $valStr;

for ($i = 1; $i < count($foodArray); $i++) {
	$valStr = ", ";	
	$valStr = $valStr . "('" . fix_quotes($foodArray[$i][0]) . "', '" . fix_quotes($foodArray[$i][1]) . "', '" . fix_quotes($foodArray[$i][2]) . "', '" . fix_quotes($foodArray[$i][3]);
	$valStr = $valStr . "', '" . fix_quotes($foodArray[$i][4]) . "', '" . fix_quotes($foodArray[$i][5]) . "', '" . fix_quotes($foodArray[$i][6]) . "', '" . fix_quotes($foodArray[$i][7]);
	$valStr = $valStr . "', '" . fix_quotes($foodArray[$i][8]) . "', '" . fix_quotes($foodArray[$i][9]) . "', '" . fix_quotes($foodArray[$i][10]) . "', '" . fix_quotes($foodArray[$i][11]);
	$valStr = $valStr . "', '" . fix_quotes($foodArray[$i][12]) . "', '" . fix_quotes($foodArray[$i][13]) . "', '" . fix_quotes($foodArray[$i][14]) . "', '" . fix_quotes($foodArray[$i][15]);
	$valStr = $valStr . "', '" . fix_quotes($foodArray[$i][16]) . "')";

	$multiValStr = $multiValStr . $valStr;	
}

$query = "INSERT INTO globalfood $colStr VALUES $multiValStr"; 
echo($query);
$result = mysqli_query($db, $query);

?>

the echo at the bottom is the following, showing the apostrophe in Honey Nut O’s was successfully escaped. However, the slash in ‘1/45 package’ was not escaped. Shouldnt it have been?:

Magic quotes are disabled
INSERT INTO globalfood (food, brand, ftype, servamt, ss, cal, fat, sat, unsat, monosat, trans, chol, sod, carb, fib, sug, pro) VALUES ('Honey Nut O\‘s Cereal’, ‘Generic’, ‘Grain Products’, ‘’, ‘30 g’, ‘120’, ‘0.5’, ‘0’, ‘’, ‘’, ‘0’, ‘0’, ‘150’, ‘26’, ‘2’, ‘12’, ‘2’), (‘Teriyaki Style Rice’, ‘Generic’, ‘Grain Products’, ‘1/45 package’, ‘0.5 g’, ‘160’, ‘0.5’, ‘0.2’, ‘’, ‘’, ‘’, ‘0’, ‘520’, ‘34’, ‘0’, ‘4’, ‘3’)

the second file problempart2.php contains 3 functions. The first getGlobalFood loads onload and uses a second function ajaxModifyTable to make an ajax request to the server and retrieve the 2 rows from the database. The third function insertValues can be used to insert either row from the table back into the database, also using ajaxModifyTable. I’ve inserted big comments where I think the extra slashes may be being added or removed


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>

<script type="text/javascript" src="../javascript/prototype.js"></script>
<script>

function getGlobalFood() {

	function ajaxPost () {
		this.onSuccess = "";
		this.url = "problempart3.php"; 	
		this.methodType = "post"; 							
		this.queryType = "SELECT"; 							
		this.global = "yes"; 										
		this.table = "globalfood"; 							
		this.colNum = 15; 											
		this.colNames = [ "food", "brand", "ftype", "servamt", "ss", "cal", "fat", "sat", "trans", "chol", "sod", "carb", "fib", "sug", "pro" ];					
		this.col0 = ""; 												
	}
	objToSend = new ajaxPost();
	ajaxModifyTable( objToSend );

}

function insertValues( x ) {
	alert(x);

	function ajaxPost () {
		this.onSuccess = "";
		this.url = "problempart3.php"; 	
		this.methodType = "post"; 				
		this.queryType = "INSERT"; 										
		this.global = "yes"; 										
		this.table = "globalfood"; 							
		this.colNum = 15; 											
		this.colNames = [ "food", "brand", "ftype", "servamt", "ss", "cal", "fat", "sat", "trans", "chol", "sod", "carb", "fib", "sug", "pro" ];  
		this.col0 = document.getElementById("cell_" + x + "_0").innerHTML; 							
		this.col1 = document.getElementById("cell_" + x + "_1").innerHTML; 					
		this.col2 = document.getElementById("cell_" + x + "_2").innerHTML; 							
		this.col3 = document.getElementById("cell_" + x + "_3").innerHTML; 							
		this.col4 = document.getElementById("cell_" + x + "_4").innerHTML; 								
		this.col5 = document.getElementById("cell_" + x + "_5").innerHTML; 									
		this.col6 = document.getElementById("cell_" + x + "_6").innerHTML; 									
		this.col7 = document.getElementById("cell_" + x + "_7").innerHTML; 									
		this.col8 = document.getElementById("cell_" + x + "_8").innerHTML; 									
		this.col9 = document.getElementById("cell_" + x + "_9").innerHTML; 										
		this.col10 = document.getElementById("cell_" + x + "_10").innerHTML; 										
		this.col11 = document.getElementById("cell_" + x + "_11").innerHTML; 										
		this.col12 = document.getElementById("cell_" + x + "_12").innerHTML; 										
		this.col13 = document.getElementById("cell_" + x + "_13").innerHTML; 										
		this.col14 = document.getElementById("cell_" + x + "_14").innerHTML; 										
	}
	objToSend = new ajaxPost();
	ajaxModifyTable( objToSend );

}

function ajaxModifyTable ( obj ) {

	var strToSend = Object.toJSON( obj ); //////// extra slash here???
	strToSend = encodeURIComponent( strToSend ); //////// extra slash here???
	alert(strToSend);
	
	new Ajax.Request( obj.url ,
		{
			method: obj.methodType,
			parameters: 'passme=' + strToSend,
			onSuccess: function(transport){
				var response = decodeURIComponent(transport.responseText); //////// extra slash here??? 
				alert("Success! \
\
" + response);
				document.getElementById("responsediv").innerHTML = response;
				var respArray = new Array();
				if (obj.queryType == "SELECT" ) {
					var respObj = response.evalJSON(true); //////// extra slash here???
					var count2 = 0;
					for ( var i = 0; i < parseInt(respObj.items); i++ ) {
						var count1 = 0;			
						respArray[i] = new Array();
						var tempObj			 = new Object();
 						tempObj = respObj.root[i];
						for ( var prop in tempObj ) {
							document.getElementById("cell_" + count2 + "_" + count1).innerHTML = tempObj[prop];
							count1 += 1;
						}
						count2 += 1;
					}
				}		
			},
			onFailure: function(){
				alert('Something went wrong...');
		  }
		});
}

</script>
</head>

<body onload="getGlobalFood()">
<div id="responsediv"></div>

<table>
<tr>
<td id="cell_0_0"></td>
<td id="cell_0_1"></td>
<td id="cell_0_2"></td>
<td id="cell_0_3"></td>
<td id="cell_0_4"></td>
<td id="cell_0_5"></td>
<td id="cell_0_6"></td>
<td id="cell_0_7"></td>
<td id="cell_0_8"></td>
<td id="cell_0_9"></td>
<td id="cell_0_10"></td>
<td id="cell_0_11"></td>
<td id="cell_0_12"></td>
<td id="cell_0_13"></td>
<td id="cell_0_14"></td>
<td><input type="submit" value="Insert" onclick="insertValues(0)" /></td>
</tr>
<tr>
<td id="cell_1_0"></td>
<td id="cell_1_1"></td>
<td id="cell_1_2"></td>
<td id="cell_1_3"></td>
<td id="cell_1_4"></td>
<td id="cell_1_5"></td>
<td id="cell_1_6"></td>
<td id="cell_1_7"></td>
<td id="cell_1_8"></td>
<td id="cell_1_9"></td>
<td id="cell_1_10"></td>
<td id="cell_1_11"></td>
<td id="cell_1_12"></td>
<td id="cell_1_13"></td>
<td id="cell_1_14"></td>
<td><input type="submit" value="Insert" onclick="insertValues(1)" /></td>

</tr>
</table>

</body>
</html>

continued…

the php page called by ajaxModifyTable is contained in problempart3.php, which creates a query string depending on the object properties passed to it, queries the database, then outputs any results for ajaxModifyTable. I’ve added comments where i think the extra slashes may be being added

<?php
	extract( $_POST );

	@ $db = mysqli_connect('localhost', 'root', '', 'mealchamp');
	$user = "gloosemo";
	$userid = 1;
	$colArr[0] = "";

	$objReceived = json_decode(urldecode($passme));  ///extra slashes here???????????
	$queryType		= $objReceived->{'queryType'};
	$global				= $objReceived->{'global'};
	$table 				= $objReceived->{'table'};

	if ($global == "no") {
		$table = $user . $table;
	}
	$colNum				= $objReceived->{'colNum'};
	$colNames			= $objReceived->{'colNames'};
	if ($colNum > 0) {
		$colString = $colNames[0];
	}
	if ($colNum > 1) {
		for ($i = 1; $i < $colNum; $i++) {
			$colString = $colString . ", " . $colNames[$i];
		}
	}
	if ( trim($objReceived->{'col0'}) != "" ) {
		for ($i = 0; $i < $colNum; $i++) {
			$colArr[$i] = trim($objReceived->{'col' . $i});
			$colArr[$i] = mysqli_real_escape_string($db, $colArr[$i]);
		}
	}
	switch ($queryType) {
		case "SELECT" :
			$query = "SELECT " . $colString . " FROM " . $table;
			$returnVar->select = "yes";
			break;
		case "INSERT" :
			$query = "INSERT INTO " . $table . " (" . $colString . ") VALUES ('$colArr[0]'";
			if ($colNum > 1) {
				for ($i = 1; $i < $colNum; $i++) {
					$tempVal1 = $colArr[$i];
					$query = $query . ", '$tempVal1'";
				} 	
			}
			$query = $query . ")";
			$returnVar->select = "no";
			break;
	}

	$result = mysqli_query($db, $query);
	if ( $returnVar->select == "yes" ) {
		$arr = NULL;
		if ($result)
			$num_results = mysqli_num_rows($result);
		else
			$num_results = 0;
		for ($i = 0; $i < $num_results; $i++) {
			$row = mysqli_fetch_assoc($result);
			foreach ( $row as $current ) {
				$current = stripslashes($current);
			}
			$arr[$i] = $row;
		} 
		$returnVar->items = $num_results;
	}	else {
		$arr[0] = $query;
		$returnVar->items = 1;

	}

	$returnVar->root = $arr;
	$z = rawurlencode(json_encode($returnVar));  ///extra slashes here???????????
	echo $z;

?>

OK so the problem is when problempart2.php loads and the html table is populated, the response text from ajaxModifyTable show’s that the slash in “Honey Nut O’s” was removed, but wasn’t in “1\/45 package”.

Response text: {“select”:“yes”,“items”:2,“root”:[{“food”:“Honey Nut O’s Cereal”,“brand”:“Generic”,“ftype”:“Grain Products”,“servamt”:“”,“ss”:“30 g”,“cal”:“120”,“fat”:“0.5”,“sat”:“0”,“trans”:“0”,“chol”:“0”,“sod”:“150”,“carb”:“26”,“fib”:“2”,“sug”:“12”,“pro”:“2”},{“food”:“Teriyaki Style Rice”,“brand”:“Generic”,“ftype”:“Grain Products”,“servamt”:“1\/45 package”,“ss”:“0.5 g”,“cal”:“160”,“fat”:“0.5”,“sat”:“0.2”,“trans”:“”,“chol”:“0”,“sod”:“520”,“carb”:“34”,“fib”:“0”,“sug”:“4”,“pro”:“3”}]}

THEN, when i click to insert the “Honey Nut O’s” row from the table, the response text produced by ajaxModifyTable shows that there are now not 0 but 2 slashes in the response string!!

Response text: {“select”:“no”,“items”:1,“root”:[“INSERT INTO globalfood (food, brand, ftype, servamt, ss, cal, fat, sat, trans, chol, sod, carb, fib, sug, pro) VALUES ('Honey Nut O\\‘s Cereal’, ‘Generic’, ‘Grain Products’, ‘’, ‘30 g’, ‘120’, ‘0.5’, ‘0’, ‘0’, ‘0’, ‘150’, ‘26’, ‘2’, ‘12’, ‘2’)”]}

WTF man there should only be one slash. How are 2 slashes getting inserted?!?!? and no magic quotes are not enabled, as is shown by the output in problempart1.php

thanks in advance. if you can help i will be greatful forever. G.