3 Replies - 1226 Views - Last Post: 07 November 2012 - 11:44 PM

#1 mccabec123  Icon User is offline

  • D.I.C Head

Reputation: 18
  • View blog
  • Posts: 233
  • Joined: 03-March 11

Issue inserting ampersands into MySQL

Posted 07 November 2012 - 05:15 AM

I've been trying to fix this issue for a while, I've spent endless amounts of time on the web searching for a solution for it. So I thought I'd come to you guys since I am at wits end with this problem. Basically I have a CMS system that I designed a while back, I recently updated it to modern standards using PDO and such like, now I seem to have an issue inserting ampersands (&) into the MySQL database, everything is set to 'UTF-8', the PDO connection, the MySQL collation, everything, yet for some reason when there is an ampersand in the form from the textarea's, the data is sent, but is cut off when it reaches the ampersand, as if it's parsing it and cutting it off because it thinks that the ampersand is a vulnerability.

I'm pretty certain that this is to do with the Javascript in the file, as I have a standard save button which solely uses POST variables and sending them to a script via a form.

Here is my code for the insert in PHP:
    $fbl = $_POST['fblValue'];
    $fbr = $_POST['fbrValue'];
    $sbl = $_POST['sblValue'];
    $sbr = $_POST['sbrValue'];
    $tbl = $_POST['tblValue'];
    $tbr = $_POST['tbrValue'];
    $fblDisabled = $_POST['fblDisabled'];
    $fbrDisabled = $_POST['fbrDisabled'];
    $sblDisabled = $_POST['sblDisabled'];
    $sbrDisabled = $_POST['sbrDisabled'];
    $tblDisabled = $_POST['tblDisabled'];
    $tbrDisabled = $_POST['tbrDisabled'];
    $footer = $_POST['footerValue'];
    $copyright = $_POST['copyrightValue'];
        
    $statement = $conn->prepare("UPDATE pages SET FBL = :fbl, 
                                                FBLDisabled = :fblDisabled, 
                                                FBR = :fbr, 
                                                FBRDisabled = :fbrDisabled, 
                                                SBL = :sbl, SBLDisabled = :sblDisabled, 
                                                SBR = :sbr, SBRDisabled = :sbrDisabled, 
                                                TBL = :tbr, TBLDisabled = :tblDisabled, 
                                                TBR = :tbr, TBRDisabled = :tbrDisabled, 
                                                Footer = :footer, 
                                                Copyright = :copyright 
                                                WHERE ID = :pageToEdit");
    $statement->bindParam(":fbl", $fbl);
    $statement->bindParam(":fblDisabled", $fblDisabled);
    $statement->bindParam(":fbr", $fbr);
    $statement->bindParam(":fbrDisabled", $fbrDisabled);
    $statement->bindParam(":sbl", $sbl);
    $statement->bindParam(":sblDisabled", $sblDisabled);
    $statement->bindParam(":sbr", $sbr);
    $statement->bindParam(":sbrDisabled", $sbrDisabled);
    $statement->bindParam(":tbl", $tbl);
    $statement->bindParam(":tblDisabled", $tbDisabled);
    $statement->bindParam(":tbr", $tbr);
    $statement->bindParam(":tbrDisabled", $tbrDisabled);
    $statement->bindParam(":footer", $footer);
    $statement->bindParam(":copyright", $copyright);
    $statement->bindParam(":pageToEdit", $pageToEdit);
    $statement->execute();              


Also may I add, that I use ajax to send the data, is there a possibility that it's something to do with that?

Here is the AJAX/Javascript code:
    <script type="text/javascript">
		
		
			function savePage(str1, str2, str3, str4, str5, str6, str7, str8, str9, str10, str11, str12, str13, str14)
			{
				var xmlhttp;
				
				if (window.XMLHttpRequest)
				{// code for IE7+, Firefox, Chrome, Opera, Safari
				  	xmlhttp=new XMLHttpRequest();
				}
				else
				{// code for IE6, IE5
				  	xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
				}
				
				xmlhttp.open("post", "putEditedPage.php", true);
				xmlhttp.setRequestHeader("Content-type","application/x-www-form-urlencoded");
				xmlhttp.send("fblValue=" + str1 + "&fbrValue=" + str2 + "&sblValue=" + str3 + "&sbrValue=" + str4 + "&tblValue=" + str5 + "&tbrValue=" + str6 + "&fblDisabled=" + str7 + "&fbrDisabled=" + str8 + "&sblDisabled=" + str9 + "&sbrDisabled=" + str10 + "&tblDisabled=" + str11 + "&tbrDisabled=" + str12 + "&footerValue=" + str13 + "&copyrightValue=" + str14);
				document.getElementById('blackout').style.display="block";
				document.getElementById('Alert').style.display="block";
				document.getElementById('Alert').style.marginTop=((window.innerHeight/2)+((window.innerHeight/100) * 35))+"px";
				setTimeout(function(){
					document.getElementById('blackout').style.display='none';			
					document.getElementById('Alert').style.display='none';
				}, 1250);
			}
			function insertTab(o, e)
			{
				var kC = e.keyCode ? e.keyCode : e.charCode ? e.charCode : e.which;
				if (kC == 9 && !e.shiftKey && !e.ctrlKey && !e.altKey)
				{
					var oS = o.scrollTop;
					if (o.setSelectionRange)
					{
						var sS = o.selectionstart;
						var sE = o.selectionend;
						o.value = o.value.substring(0, sS) + "\t" + o.value.substr(sE);
						o.setSelectionRange(sS + 1, sS + 1);
						o.focus();
					}
					else if (o.createTextRange)
					{
						document.selection.createRange().text = "\t";
						e.returnValue = false;
					}
					o.scrollTop = oS;
					if (e.preventDefault)
					{
						e.preventDefault();
					}
					return false;
				}
				return true;
			}
			
		</script>



Thanks in advance guys, cheers.

This post has been edited by mccabec123: 07 November 2012 - 05:18 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Issue inserting ampersands into MySQL

#2 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3525
  • View blog
  • Posts: 10,170
  • Joined: 08-June 10

Re: Issue inserting ampersands into MySQL

Posted 07 November 2012 - 06:55 AM

& has a special meaning in HTTP. if you don’t escape (encodeURIComponent()) it before you send it to the server, it is treated as parameter delimiter, effectively cutting the value off at that place and creating (if possible) a new Request parameter.
also note that if a form is submitted regularly, the browser takes care of the escaping. since & (unless explicitly used as operator) has no special meaning in Javascript, it is not escaped if you retrieve the value through the DOM.

This post has been edited by Dormilich: 07 November 2012 - 06:55 AM

Was This Post Helpful? 0
  • +
  • -

#3 mccabec123  Icon User is offline

  • D.I.C Head

Reputation: 18
  • View blog
  • Posts: 233
  • Joined: 03-March 11

Re: Issue inserting ampersands into MySQL

Posted 07 November 2012 - 12:44 PM

Hey Dormilich, somebody else suggested that to my, and I tried it, but when I used that escaping function I got no values sent to the PHP script, it was bizarre, do you have any suggestions at all?

This post has been edited by Dormilich: 07 November 2012 - 11:44 PM
Reason for edit:: removed unnecessary quote

Was This Post Helpful? 0
  • +
  • -

#4 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3525
  • View blog
  • Posts: 10,170
  • Joined: 08-June 10

Re: Issue inserting ampersands into MySQL

Posted 07 November 2012 - 11:44 PM

sure: var_dump($_POST);. first thing is to make sure that sending the data actually works (well, that could be tested with Firebug/Developer tools as well).
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1