help with textarea, syntax errors in UPDATE

[NiallThistle]

I used to have a straight forward piece of code that updated multiple rows of info using a foreach loop.

 

However, ever since I've introduced a textarea box into the mix, it's been throwing up syntax errors.

 

Here's my textarea box:

 

<textarea id=\"detail[]\" name=\"detail[]\" rows=\"7\" cols=\"35\" wrap=\"hard\">$data[detail]</textarea>

 

And here's my UPDATE query:

 

foreach($_POST['mid'] as $key=>$val) 
{ 
   	mysql_query("UPDATE val_mworks SET
	mworks_percent = ".$_POST['percent'][$key].",
	mworks_detail = ".$_POST['detail'][$key].",  
	mworks_qty = ".$_POST['qty'][$key]."
	WHERE mworks_id = $val
	") or die(mysql_error());   
} 

 

Can anyone see anything obvious that I'm doing wrong?

[NiallThistle]

Here's my whole form, if its of any consequence, but it worked fine before I added the textarea.

 

	echo"
	<form action=\"contract.php?action=amend_mworks&con=$_GET[con]\" name=\"frmEdit\" method=\"post\">
	<br><b>List of Measured Works</b><br><br>
	<table width=\"705\">
	<tr>
	<td width=\"30\" bgcolor=\"#E8EBF0\" style=\"border:solid 1px #ccc\"> <b>Item</b></td>
	<td bgcolor=\"#E8EBF0\" style=\"border:solid 1px #ccc\"><b>Description</b></td>
	<td width=\"80\" bgcolor=\"#E8EBF0\" style=\"border:solid 1px #ccc\" align=\"center\"> <b>Qty</b></td>
	<td width=\"40\" bgcolor=\"#E8EBF0\" style=\"border:solid 1px #ccc\" align=\"center\"> <b>Unit</b></td>
	<td width=\"50\" bgcolor=\"#E8EBF0\" style=\"border:solid 1px #ccc\" align=\"center\"><b>Rate</b></td>
	<td width=\"50\" bgcolor=\"#E8EBF0\" style=\"border:solid 1px #ccc\" align=\"center\"><b>Ext</b></td>
	<td width=\"50\" bgcolor=\"#E8EBF0\" style=\"border:solid 1px #ccc\" align=\"center\"><b>Claimed<br>(%)</b></td>
	<td width=\"50\" bgcolor=\"#E8EBF0\" style=\"border:solid 1px #ccc\" align=\"center\"><b>Claimed<br>To Date</b></td>
	</tr>
	";

			// Display basic row of info for measured works (i.e. item, qty etc)

			$get_mworks = mysql_query("
			SELECT mworks_id as mid,
			mworks_item as item,
			mworks_detail as detail,
			mworks_qty as qty,
			mworks_unit as unit,
			mworks_percent as percent,
			mworks_qty * mworks_rate as ext,
			(mworks_percent / 100) * (mworks_rate * mworks_qty) as ctd,
			mworks_rate as rate
			FROM val_mworks
			WHERE mworks_con = '".$_GET["con"]."'
			ORDER by mworks_id ASC
			",$objConnect);

			while($data = mysql_fetch_array($get_mworks))
			{	
				echo"
				<tr>
					<td valign=\"middle\">$data[item]</td>
					<td valign=\"middle\"><textarea id=\"detail[]\" name=\"detail[]\" rows=\"7\" cols=\"35\" wrap=\"hard\">$data[detail]</textarea></td>
					<td align=\"center\" valign=\"middle\"><input type=\"text\" style=\"background-color: transparent; border-style: solid; border-width: 0px 0px 1px 0px; border-color: #001947\" name=\"qty[]\" size=\"7\" value=\"$data[qty]\" id=\"qty\"></td>
					<td align=\"center\" valign=\"middle\">$data[unit]</td>
					<td align=\"center\" valign=\"middle\">";

				$rate = number_format($data['rate'],2);

			// Calculate and display measured works ext, and percentage and amount already claimed

			echo"
			&#163;$rate</td>
			<td valign=\"middle\" align=\"center\">";

				$ext = number_format($data['ext'],2);

			echo"&#163;$ext</td>
			<td align=\"center\" valign=\"middle\"><input type=\"text\" style=\"background-color: transparent; border-style: solid; border-width: 0px 0px 1px 0px; border-color: #001947\" name=\"percent[]\" size=\"5\" value=\"$data[percent]\" id=\"percent\"></td>
			<td align=\"center\" valign=\"middle\"><input type=\"hidden\" name=\"mid[]\" value=\"$data[mid]\" id=\"mid\">";

				$ctd = number_format($data['ctd'],2);

			echo"&#163;$ctd</td>
			</tr>";
			}

	echo"
	</table>
	<br>
	<input type=\"submit\" name=\"submit\" value=\"Save Changes to Measured Works\"></form>";

[AyKay47]

before i glance at your code.. i will need to see the errors that are being triggered to make my life easier

[NiallThistle]

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'steelwork including cold rolled members and flooring support

 

The 'steelwork including cold rolled members and flooring support' bit comes from one of the textarea entries.  The specific textbox entry its referring to reads "Structural steelwork including cold rolled members and flooring support beams". 

[NiallThistle]

Here is a screengrab of the form.  In this example, there are four rows being updated at once, but it does it no matter how many I'm updating.  As before, this form worked fine when the textarea was a locked field and wasn't part of the update.

 

[attachment deleted by admin]

[Pikachu2000]

A major problem is that your data isn't being validated/sanitized/escaped, leaving you wide open to SQL injection exploits.

 

The problem you're currently seeing the symptoms of is caused by the lack of quotes enclosing the string values in your query string.

$query = "UPDATE table SET string_field = 'string', numeric_field = 2011";

[NiallThistle]

The problem you're currently seeing the symptoms of is caused by the lack of quotes enclosing the string values in your query string.

$query = "UPDATE table SET string_field = 'string', numeric_field = 2011";

 

Thank you, that did it!

 

A major problem is that your data isn't being validated/sanitized/escaped, leaving you wide open to SQL injection exploits.

 

Sorry, a bit of a novice, how would I change that?

[Pikachu2000]

This is by no means intended to be a comprehensive guide, but at a bare minimum, string type data should be escaped with mysql_real_escape_string, and numeric data should be cast to the correct data type before being used in a database query string. So referring to the example code I posted:

 

$num = (int) $_POST['numeric_field'];
$str = mysql_real_escape_string($_POST['string'])'; // connection to db must be established prior to using mysql_real_escape_string()

$query = "UPDATE table SET string_field = '$str', numeric_field = $num";